excel模板导入导出的工具类

Bertha 。 2022-10-22 09:23 163阅读 0赞

package com.hhh.framework.tools.utils;
    
    import com.hhh.framework.tools.utils.custom.CustomizedXlsCommentAreaBuilder;
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.ss.usermodel.*;
    import org.jxls.common.Context;
    import org.jxls.expression.JexlExpressionEvaluator;
    import org.jxls.transform.Transformer;
    import org.jxls.transform.poi.PoiTransformer;
    import org.jxls.util.JxlsHelper;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import javax.servlet.ServletOutputStream;
    import javax.servlet.http.HttpServletRequest;
    import javax.servlet.http.HttpServletResponse;
    import java.io.*;
    import java.net.URLEncoder;
    import java.text.NumberFormat;
    import java.text.SimpleDateFormat;
    import java.util.*;
    
    /**
     * excel模板导出的工具类
     */
    public class JxlsUtils {
        private final static Logger LOG = LoggerFactory.getLogger(JxlsUtils.class);
    
        public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model){
            try {
                Context context = PoiTransformer.createInitialContext();
                if (model != null) {
                    for (String key : model.keySet()) {
                        context.putVar(key, model.get(key));
                    }
                }
                JxlsHelper jxlsHelper = JxlsHelper.getInstance();
                Transformer transformer  = jxlsHelper.createTransformer(is, os);
                //获得配置
                JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
                //函数强制，自定义功能
                Map<String, Object> funcs = new HashMap<String, Object>();
                funcs.put("utils", new JxlsUtils());    //新增自定义功能
                evaluator.getJexlEngine().setFunctions(funcs);
                //必须要这个，否者表格函数统计会错乱
                jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
            } catch (IOException e) {
                LOG.error("导出excel失败 ",e);
            }finally {
                if (is != null) {
                    try {
                        is.close();
                    } catch (IOException e) {
                        LOG.error("关闭输入流失败", e);
                    }
                }
                if (os != null) {
                    try {
                        os.close();
                    } catch (IOException e) {
                        LOG.error("关闭输出流失败", e);
                    }
                }
            }
        }
    
        public static void exportExcel(File xls, File out, Map<String, Object> model) throws FileNotFoundException, IOException {
            exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
        }
    
        public static void exportExcel(String templatePath, OutputStream os, Map<String, Object> model) throws Exception {
            File template = getTemplate(templatePath);
            if(template != null){
                exportExcel(new FileInputStream(template), os, model);
            } else {
                throw new Exception("Excel 模板未找到。");
            }
        }
    
        //获取jxls模版文件
        public static File getTemplate(String path){
            File template = new File(path);
            if(template.exists()){
                return template;
            }
            return null;
        }
        
        public static OutputStream getOutputStream(String fileName, HttpServletRequest request, HttpServletResponse response) throws Exception {
            response.setHeader("Content-Disposition", "attachment;filename=" + JxlsUtils.getDownloadFileName(fileName, request));
            response.setContentType("application/vnd.ms-excel");
            ServletOutputStream outputStream = response.getOutputStream();
            return outputStream;
        }
    
        /**
         * 获取模板文件名，使用多种浏览器防止乱码
         * @param fileName
         * @param request
         * @return
         * @throws Exception
         */
        public static String getDownloadFileName(String fileName, HttpServletRequest request) throws Exception {
            String userAgent = getBrowserName(request.getHeader("USER-AGENT").toLowerCase());
            String downloadFile = null;
            if (userAgent.contains("firefox")) {//火狐浏览器
                downloadFile = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
            } else {
                downloadFile = URLEncoder.encode(fileName, "UTF8");//其他浏览器
            }
            return downloadFile;
        }
    
        private static String getBrowserName(String agent) {
            if(agent.indexOf("msie 7")>0){
                return "ie7";
            }else if(agent.indexOf("msie 8")>0){
                return "ie8";
            }else if(agent.indexOf("msie 9")>0){
                return "ie9";
            }else if(agent.indexOf("msie 10")>0){
                return "ie10";
            }else if(agent.indexOf("msie")>0){
                return "ie";
            }else if(agent.indexOf("opera")>0){
                return "opera";
            }else if(agent.indexOf("firefox")>0){
                return "firefox";
            }else if(agent.indexOf("webkit")>0){
                return "webkit";
            }else if(agent.indexOf("gecko")>0 && agent.indexOf("rv:11")>0){
                return "ie11";
            }else{
                return "Others";
            }
        }
    
        // 日期格式化
        public String dateFmt(Date date, String fmt) {
            if (date == null) {
                return "";
            }
            try {
                SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
                return dateFmt.format(date);
            } catch (Exception e) {
                e.printStackTrace();
            }
            return "";
        }
    
        // if判断
        public Object ifelse(boolean b, Object o1, Object o2) {
            return b ? o1 : o2;
        }
    
        /**
         * 提取【导入】的数据列表
         *
         * @param modelFilePath
         *            数据导入时对应的数据模板文件
         * @param stream
         *            用户上传时的流文件
         * @return
         * @throws IOException
         */
        public static List<Map<String, String>> getDataList(String modelFilePath, InputStream stream)
                throws IOException {
            List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
            FileInputStream fi = null;
            Workbook hwb = null;
            Row row = null;
            Cell cell = null;
            HashMap<String, String> cellField = new HashMap<String, String>();
            int dataRowStart = 0;
            // 获取绑定及对应列位置
            try {
                fi = new FileInputStream(modelFilePath);
                hwb = WorkbookFactory.create(fi);
                Sheet sheet = hwb.getSheetAt(0);
                int rows = sheet.getLastRowNum();
                int cols = -1;
                for (int i = 0; i <= rows; i++) {
                    row = sheet.getRow(i);
                    if (row == null) {
                        continue;
                    }
                    cols = row.getLastCellNum();
                    for (int j = 0; j < cols; j++) {
                        cell = row.getCell(j);
                        String value = "";
                        if (null != cell) {
                            value = cell.getRichStringCellValue().toString();
                            if (value.indexOf("${") != -1) {
                                // 数据位置
                                dataRowStart = i;
                                cellField.put("" + j,
                                        value.substring(value.indexOf("{") + 1, value.indexOf("}")));
                            }
    
                        }
                    }
                }
                // 根据绑定的字段获取数据列表
                if (cellField.keySet() != null) {
                    hwb = WorkbookFactory.create(stream);
                    sheet = hwb.getSheetAt(0);
                    rows = sheet.getLastRowNum();
                    cols = -1;
                    for (int i = dataRowStart; i <= rows; i++) {
                        row = sheet.getRow(i);
                        if (row == null) {
                            continue;
                        }
                        cols = row.getLastCellNum();
                        Map<String, String> rowData = new HashMap<String, String>();
                        // 当前行是否所有列都为空
                        boolean isAllNull = false;
                        for (int j = 0; j < cols; j++) {
                            cell = row.getCell(j);
                            String cellValue = "";
                            if (null != cell) {
                                switch (cell.getCellType()) {
                                    case HSSFCell.CELL_TYPE_NUMERIC:
                                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                            double d = cell.getNumericCellValue();
                                            Date date = HSSFDateUtil.getJavaDate(d);
                                            SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");
                                            cellValue = dformat.format(date);
                                        } else {
                                            NumberFormat nf = NumberFormat.getInstance();
                                            nf.setGroupingUsed(false); // true时的格式：1,234,567,890
                                            cellValue = nf.format(cell.getNumericCellValue()); // 数值类型的数据为double，所以需要转换一下
                                        }
                                        break;
                                    case HSSFCell.CELL_TYPE_STRING:
                                        cellValue = cell.getStringCellValue();
                                        break;
                                    case HSSFCell.CELL_TYPE_BOOLEAN:
                                        cellValue = String.valueOf(cell.getBooleanCellValue());
                                        break;
                                    case HSSFCell.CELL_TYPE_FORMULA:
                                        cellValue = String.valueOf(cell.getCellFormula());
                                        break;
    
                                    default:
                                        cellValue = "";
                                        break;
                                }
                                // 获取对应绑定字段（包含表名标识）
                                String fullField = cellField.get("" + j) == null ? "" : cellField.get(
                                        "" + j).toString();
                                if (!"".equals(fullField)) {
                                    rowData.put(fullField, cellValue);
                                }
                                if (!"".equals(cellValue)) {
                                    isAllNull = true;
                                }
                            }
                        }
                        if (isAllNull) {
                            dataList.add(rowData);
                        }
                        isAllNull = false;
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (fi != null) {
                    fi.close();
                }
            }
            return dataList;
        }
    
        public static void exportExcelCanMerge(String templatePath, OutputStream os, Map<String, Object> model) throws Exception {
            File template = getTemplate(templatePath);
            if(template != null){
                exportExcelCanMerge(new FileInputStream(template), os, model);
            } else {
                throw new Exception("Excel 模板未找到。");
            }
        }
    
        public static void exportExcelCanMerge(InputStream is, OutputStream os, Map<String, Object> model) {
    
            try {
                Context context = PoiTransformer.createInitialContext();
                if (model != null) {
                    for (String key : model.keySet()) {
                        context.putVar(key, model.get(key));
                    }
                }
                JxlsHelper jxlsHelper = JxlsHelper.getInstance();
                Transformer transformer  = jxlsHelper.createTransformer(is, os);
                //获得配置
                JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
                //函数强制，自定义功能
                Map<String, Object> funcs = new HashMap<String, Object>();
                funcs.put("utils", new JxlsUtils());    //新增自定义功能
                evaluator.getJexlEngine().setFunctions(funcs);
                //必须要这个，否者表格函数统计会错乱
                jxlsHelper.setAreaBuilder(new CustomizedXlsCommentAreaBuilder());
                jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
            } catch (IOException e) {
                LOG.error("导出excel失败 ",e);
            }finally {
                if (is != null) {
                    try {
                        is.close();
                    } catch (IOException e) {
                        LOG.error("关闭输入流失败", e);
                    }
                }
                if (os != null) {
                    try {
                        os.close();
                    } catch (IOException e) {
                        LOG.error("关闭输出流失败", e);
                    }
                }
            }
        }
    }