update export excel

1821349743@qq.com

2023-04-17 b92fd92933fce8e97fef05207596217bb746cd4d

update export excel

 src/main/java/com/product/module/data/controller/SystemDataExportController.java

@@ -32,71 +32,49 @@
public class SystemDataExportController extends AbstractBaseController {


    @Autowired
    SystemDataExportService systemDataExportService;
    @Autowired
    SystemDataExportService systemDataExportService;

    @PostMapping("general-list/{version}")
    @ApiVersion(1)
    public String generalListDataExport(HttpServletRequest request){
        try{
            FieldSetEntity fse = null;
            Object bean = request.getAttribute(CoreConst.API_POST_REQUEST_DATA);
            if (bean != null) {
                RequestParameterEntity reqp = (RequestParameterEntity) bean;
                fse = reqp.getFormData();
            }
            if (bean == null || fse == null) {
                SpringMVCContextHolder.getSystemLogger().error(CmnCode.SYSTEM_FORM_NODATA.getValue(), CmnCode.SYSTEM_FORM_NODATA.getText());
                return this.error(CmnCode.SYSTEM_FORM_NODATA.getValue(), CmnCode.SYSTEM_FORM_NODATA.getText());
            }
            // åˆ¤æ–­å‚数是否正常
            if (StringUtils.isEmpty(fse.getString(CmnConst.FILE_NAME))) {
                SpringMVCContextHolder.getSystemLogger().error(CmnCode.EXPORT_FILE_NAME_NOT_EMPTY.getValue(), CmnCode.EXPORT_FILE_NAME_NOT_EMPTY.getText());
                return this.error(CmnCode.EXPORT_FILE_NAME_NOT_EMPTY.getValue(), CmnCode.EXPORT_FILE_NAME_NOT_EMPTY.getText());
            }
            if (StringUtils.isEmpty(fse.getString(CmnConst.UPLOAD_API_URL))) {
                SpringMVCContextHolder.getSystemLogger().error(CmnCode.EXPORT_UPLOAD_URL_NOT_EMPTY.getValue(), CmnCode.EXPORT_UPLOAD_URL_NOT_EMPTY.getText());
                return this.error(CmnCode.EXPORT_UPLOAD_URL_NOT_EMPTY.getValue(), CmnCode.EXPORT_UPLOAD_URL_NOT_EMPTY.getText());
            }
            if(BaseUtil.dataTableIsEmpty(fse.getSubDataTable(CmnConst.EXPORT_PARAM))){
                SpringMVCContextHolder.getSystemLogger().error(CmnCode.EXPORT_PARAMS_NOT_EMPTY.getValue(), CmnCode.EXPORT_PARAMS_NOT_EMPTY.getText());
                return this.error(CmnCode.EXPORT_PARAMS_NOT_EMPTY.getValue(), CmnCode.EXPORT_PARAMS_NOT_EMPTY.getText());
            }
            systemDataExportService.generalListDataExport(fse.clones());
            return OK();
        }catch (BaseException e){
            e.printStackTrace();
            SpringMVCContextHolder.getSystemLogger().error(e);
            return error(e);
        }
        catch (Exception e){
            e.printStackTrace();
            SpringMVCContextHolder.getSystemLogger().error(e);
            return error(CmnCode.EXPORT_GENERAL_LIST_DATA_IMPORT_FIAL.getValue(),CmnCode.EXPORT_GENERAL_LIST_DATA_IMPORT_FIAL.getText()+e.getMessage());
        }
    }
    @PostMapping("report/{version}")
    @ApiVersion(1)
    public String reportDataExport(HttpServletRequest request){
        try{
            FieldSetEntity fse = null;
            Object bean = request.getAttribute(CoreConst.API_POST_REQUEST_DATA);
            if (bean != null) {
                RequestParameterEntity reqp = (RequestParameterEntity) bean;
                return OK_Add(systemDataExportService.reportDataExport(reqp));
            }
            return OK();
        }catch (BaseException e){
            e.printStackTrace();
            SpringMVCContextHolder.getSystemLogger().error(e);
            return error(e);
        }
        catch (Exception e){
            e.printStackTrace();
            SpringMVCContextHolder.getSystemLogger().error(e);
            return error(CmnCode.EXPORT_GENERAL_LIST_DATA_IMPORT_FIAL.getValue(),CmnCode.EXPORT_GENERAL_LIST_DATA_IMPORT_FIAL.getText()+e.getMessage());
        }
    }
    @PostMapping("general-list/{version}")
    @ApiVersion(1)
    public String generalListDataExport(HttpServletRequest request) {
        try {
            FieldSetEntity fse = null;
            Object bean = request.getAttribute(CoreConst.API_POST_REQUEST_DATA);
            if (bean != null) {
                RequestParameterEntity reqp = (RequestParameterEntity) bean;
                fse = reqp.getFormData();
            }
            if (bean == null || fse == null) {
                SpringMVCContextHolder.getSystemLogger().error(CmnCode.SYSTEM_FORM_NODATA.getValue(), CmnCode.SYSTEM_FORM_NODATA.getText());
                return this.error(CmnCode.SYSTEM_FORM_NODATA.getValue(), CmnCode.SYSTEM_FORM_NODATA.getText());
            }
            // åˆ¤æ–­å‚数是否正常
            if (StringUtils.isEmpty(fse.getString(CmnConst.FILE_NAME))) {
                SpringMVCContextHolder.getSystemLogger().error(CmnCode.EXPORT_FILE_NAME_NOT_EMPTY.getValue(), CmnCode.EXPORT_FILE_NAME_NOT_EMPTY.getText());
                return this.error(CmnCode.EXPORT_FILE_NAME_NOT_EMPTY.getValue(), CmnCode.EXPORT_FILE_NAME_NOT_EMPTY.getText());
            }
            if (StringUtils.isEmpty(fse.getString(CmnConst.UPLOAD_API_URL))) {
                SpringMVCContextHolder.getSystemLogger().error(CmnCode.EXPORT_UPLOAD_URL_NOT_EMPTY.getValue(), CmnCode.EXPORT_UPLOAD_URL_NOT_EMPTY.getText());
                return this.error(CmnCode.EXPORT_UPLOAD_URL_NOT_EMPTY.getValue(), CmnCode.EXPORT_UPLOAD_URL_NOT_EMPTY.getText());
            }
            if (BaseUtil.dataTableIsEmpty(fse.getSubDataTable(CmnConst.EXPORT_PARAM))) {
                SpringMVCContextHolder.getSystemLogger().error(CmnCode.EXPORT_PARAMS_NOT_EMPTY.getValue(), CmnCode.EXPORT_PARAMS_NOT_EMPTY.getText());
                return this.error(CmnCode.EXPORT_PARAMS_NOT_EMPTY.getValue(), CmnCode.EXPORT_PARAMS_NOT_EMPTY.getText());
            }
            systemDataExportService.generalListDataExport(fse.clones());
            return OK();
        } catch (BaseException e) {
            e.printStackTrace();
            SpringMVCContextHolder.getSystemLogger().error(e);
            return error(e);
        } catch (Exception e) {
            e.printStackTrace();
            SpringMVCContextHolder.getSystemLogger().error(e);
            return error(CmnCode.EXPORT_GENERAL_LIST_DATA_IMPORT_FIAL.getValue(), CmnCode.EXPORT_GENERAL_LIST_DATA_IMPORT_FIAL.getText() + e.getMessage());
        }
    }



}

 src/main/java/com/product/module/data/service/SystemDataExportService.java

@@ -22,6 +22,7 @@
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
@@ -35,261 +36,289 @@
 */
@Service
public class SystemDataExportService extends AbstractBaseService {
    @Autowired
    SystemApiToMethods systemApiToMethods;
    @Autowired
    SystemApiToMethods systemApiToMethods;

    @Override
    public BaseDao getBaseDao() {
        return super.getBaseDao();
    }
    @Override
    public BaseDao getBaseDao() {
        return super.getBaseDao();
    }

    /**
     * æ•°æ®æŠ¥è¡¨å¯¼å‡º
     *
     * @param reqp
     * @return è¿”回Base64 EXCEL.XLSX
     * @throws BaseException
     * @throws IOException
     */
    public String reportDataExport(RequestParameterEntity reqp) throws BaseException {
    public void reportDataExport(FieldSetEntity fse) throws BaseException {
        //报表uuid
        String uuid = fse.getUUID();
        //是否当前页
        boolean exportCurrentPage = fse.getBoolean("exportCurrentPage");
        //当前页号
        int pageIndex = fse.getInteger(CmnConst.CPAGE);

        FieldSetEntity formData = reqp.getFormData();
        //总列数
        Integer totalColumn = formData.getInteger("totalColumn");
        //总行数
        Integer totalRows = formData.getInteger("totalRows");
        //头部区域行数
        Integer headCount = formData.getInteger("headCount");
        Map<String, File> files = reqp.getFiles();
        //固定文件名
        File portExcel = files.get("portExcel.xlsx");
        try (InputStream inputStream = new FileInputStream(portExcel); XSSFWorkbook wb = new XSSFWorkbook(inputStream)) {
            XSSFSheet sheet = wb.getSheetAt(0);
            //获取头部样式
            XSSFCellStyle headStyle = getReportStyle(wb, IndexedColors.BLACK.getIndex(), true, (short) 15, new java.awt.Color(191, 191, 191));
            //获取数据区样式
            XSSFCellStyle dataStyle = getReportStyle(wb, IndexedColors.BLACK.getIndex(), false, (short) 13, new java.awt.Color(255, 255, 255));
            //所有同列最宽
            Map<Integer, Integer> m = new HashMap<>();
            for (int i = 0; i < totalRows; i++) {
                XSSFRow row = sheet.getRow(i);
                XSSFCellStyle style = null;
                if (i < headCount) {
                    //头部区域样式
                    style = headStyle;
                } else {
                    //数据区样式
                    style = dataStyle;
                }
                //遍历每一个单元格
                for (int j = 0; j < totalColumn; j++) {
                    //获取单元格对象
                    XSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        //填充空的单元格
                        cell = row.createCell(j, CellType.STRING);
                    }
                    //设置单元格样式
                    cell.setCellStyle(style);
                    //获取单元格值
                    String val = cell.getStringCellValue();
                    if (val != null) {
                        //根据字节计算大致宽度
                        int i2 = val.getBytes().length * 256;
                        //宽度不能超过 256*256
                        if (i2 > 256 * 256) {
                            i2 = 256 * 255;
                        }
                        if (m.get(j) == null) {
                            m.put(j, i2);
                        } else {
                            //当前行的列是否比其他行的列宽
                            Integer width = m.get(j);
                            if (width < i2) {
                                m.put(j, i2);
                            }
                        }
                    }
    }

                }
            }
            //设置每列的宽度
            for (Map.Entry<Integer, Integer> v : m.entrySet()) {
                sheet.setColumnWidth(v.getKey(), v.getValue());
            }
            //将workbook转换为字节流
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            //将excel输出到字节流
            wb.write(byteArrayOutputStream);
            //将字节流转为字节
            byte[] bytes = byteArrayOutputStream.toByteArray();
            //将字节转换为Base64
            String encode = Base64.getEncoder().encodeToString(bytes);
            //删除传入的文件
            if (portExcel != null && portExcel.exists()) {
                portExcel.delete();
            }
            //返回Base64字符串 æ‹¼æŽ¥XLSX文件格式前缀
            return "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64," + encode;
        } catch (Exception e) {
            throw new BaseException(CmnCode.EXPORT_REPORT_DATA_FIAL.getValue(),CmnCode.EXPORT_REPORT_DATA_FIAL.getValue()+e.getMessage());
        }
    }
    /**
     * æ•°æ®æŠ¥è¡¨å¯¼å‡º
     *
     * @param reqp
     * @return è¿”回Base64 EXCEL.XLSX
     * @throws BaseException
     * @throws IOException
     */
    public String reportDataExport(RequestParameterEntity reqp) throws BaseException {

    /**
     * å•å…ƒæ ¼æ ·å¼
     *
     * @param workbook
     * @param color    å­—体颜色
     * @param bold     æ˜¯å¦åŠ ç²—
     * @param fontSize å­—体大小
     * @param bgc      èƒŒæ™¯è‰²
     * @return
     */
    public XSSFCellStyle getReportStyle(XSSFWorkbook workbook, short color, boolean bold, short fontSize, java.awt.Color bgc) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        //左右居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //上下居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // ä¸‹è¾¹æ¡†
        cellStyle.setBorderBottom(BorderStyle.THIN);
        // å·¦è¾¹æ¡†
        cellStyle.setBorderLeft(BorderStyle.THIN);
        // ä¸Šè¾¹æ¡†
        cellStyle.setBorderTop(BorderStyle.THIN);
        // å³è¾¹æ¡†
        cellStyle.setBorderRight(BorderStyle.THIN);
        //设置背景色
        FieldSetEntity formData = reqp.getFormData();
        //总列数
        Integer totalColumn = formData.getInteger("totalColumn");
        //总行数
        Integer totalRows = formData.getInteger("totalRows");
        //头部区域行数
        Integer headCount = formData.getInteger("headCount");
        Map<String, File> files = reqp.getFiles();
        //固定文件名
        File portExcel = files.get("portExcel.xlsx");
        try (InputStream inputStream = new FileInputStream(portExcel); XSSFWorkbook wb = new XSSFWorkbook(inputStream)) {
            XSSFSheet sheet = wb.getSheetAt(0);
            //获取头部样式
            XSSFCellStyle headStyle = getReportStyle(wb, IndexedColors.BLACK.getIndex(), true, (short) 15, new java.awt.Color(191, 191, 191));
            //获取数据区样式
            XSSFCellStyle dataStyle = getReportStyle(wb, IndexedColors.BLACK.getIndex(), false, (short) 13, new java.awt.Color(255, 255, 255));
            //所有同列最宽
            Map<Integer, Integer> m = new HashMap<>();
            for (int i = 0; i < totalRows; i++) {
                XSSFRow row = sheet.getRow(i);
                XSSFCellStyle style = null;
                if (i < headCount) {
                    //头部区域样式
                    style = headStyle;
                } else {
                    //数据区样式
                    style = dataStyle;
                }
                //遍历每一个单元格
                for (int j = 0; j < totalColumn; j++) {
                    //获取单元格对象
                    XSSFCell cell = row.getCell(j);
                    if (cell == null) {
                        //填充空的单元格
                        cell = row.createCell(j, CellType.STRING);
                    }
                    //设置单元格样式
                    cell.setCellStyle(style);
                    //获取单元格值
                    String val = cell.getStringCellValue();
                    if (val != null) {
                        //根据字节计算大致宽度
                        int i2 = val.getBytes().length * 256;
                        //宽度不能超过 256*256
                        if (i2 > 256 * 256) {
                            i2 = 256 * 255;
                        }
                        if (m.get(j) == null) {
                            m.put(j, i2);
                        } else {
                            //当前行的列是否比其他行的列宽
                            Integer width = m.get(j);
                            if (width < i2) {
                                m.put(j, i2);
                            }
                        }
                    }

                }
            }
            //设置每列的宽度
            for (Map.Entry<Integer, Integer> v : m.entrySet()) {
                sheet.setColumnWidth(v.getKey(), v.getValue());
            }
            //将workbook转换为字节流
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            //将excel输出到字节流
            wb.write(byteArrayOutputStream);
            //将字节流转为字节
            byte[] bytes = byteArrayOutputStream.toByteArray();
            //将字节转换为Base64
            String encode = Base64.getEncoder().encodeToString(bytes);
            //删除传入的文件
            if (portExcel != null && portExcel.exists()) {
                portExcel.delete();
            }
            //返回Base64字符串 æ‹¼æŽ¥XLSX文件格式前缀
            return "data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64," + encode;
        } catch (Exception e) {
            throw new BaseException(CmnCode.EXPORT_REPORT_DATA_FIAL.getValue(), CmnCode.EXPORT_REPORT_DATA_FIAL.getValue() + e.getMessage());
        }
    }

    /**
     * å•å…ƒæ ¼æ ·å¼
     *
     * @param workbook
     * @param color    å­—体颜色
     * @param bold     æ˜¯å¦åŠ ç²—
     * @param fontSize å­—体大小
     * @param bgc      èƒŒæ™¯è‰²
     * @return
     */
    public XSSFCellStyle getReportStyle(XSSFWorkbook workbook, short color, boolean bold, short fontSize, java.awt.Color bgc) {
        XSSFCellStyle cellStyle = workbook.createCellStyle();
        //左右居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        //上下居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // ä¸‹è¾¹æ¡†
        cellStyle.setBorderBottom(BorderStyle.THIN);
        // å·¦è¾¹æ¡†
        cellStyle.setBorderLeft(BorderStyle.THIN);
        // ä¸Šè¾¹æ¡†
        cellStyle.setBorderTop(BorderStyle.THIN);
        // å³è¾¹æ¡†
        cellStyle.setBorderRight(BorderStyle.THIN);
        //设置背景色
//        cellStyle.setFillForegroundColor(new XSSFColor());
        //填充模式
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        XSSFFont font = workbook.createFont();
        //字体颜色
        font.setColor(color);
        //加粗
        font.setBold(bold);
        //字体大小
        font.setFontHeightInPoints(fontSize);
        //字体样式
        font.setFontName("微软雅黑");
        //自动换行
        cellStyle.setWrapText(true);
        cellStyle.setFont(font);
        return cellStyle;
    }
        //填充模式
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        XSSFFont font = workbook.createFont();
        //字体颜色
        font.setColor(color);
        //加粗
        font.setBold(bold);
        //字体大小
        font.setFontHeightInPoints(fontSize);
        //字体样式
        font.setFontName("微软雅黑");
        //自动换行
        cellStyle.setWrapText(true);
        cellStyle.setFont(font);
        return cellStyle;
    }


    /**
     * é€šç”¨åˆ—表导出
     *
     * @param fse
     * @throws BaseException
     */
    public void generalListDataExport(FieldSetEntity fse) throws BaseException {
        //导出文件名称
        String file_name = fse.getString(CmnConst.FILE_NAME);
        //数据接口
        String upload_api_url = fse.getString(CmnConst.UPLOAD_API_URL);
        //数据接口所需参数
        FieldSetEntity export_param = fse.getSubDataTable(CmnConst.EXPORT_PARAM).getFieldSetEntity(0);
        HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
        RequestParameterEntity requestParameterEntity = (RequestParameterEntity) request.getAttribute(CoreConst.API_POST_REQUEST_DATA);
        FieldSetEntity fs = new FieldSetEntity();
        fs.setTableName(fse.getString(CmnConst.TABLE_NAME));
        //开始页
        int start_cpage = export_param.getInteger(CmnConst.START_CPAGE);
        //结束页
        int end_cpage = export_param.getInteger(CmnConst.END_CPAGE);
        int pagesize = export_param.getInteger(CmnConst.PAGESIZE);
        export_param.setValue(CmnConst.CPAGE, ((start_cpage) * pagesize) / pagesize);
        export_param.setValue(CmnConst.PAGESIZE, pagesize * ((end_cpage - start_cpage) + 1));
        export_param.remove(CmnConst.START_CPAGE);
        export_param.remove(CmnConst.END_CPAGE);
        Map<Object, Object> values = export_param.getValues();
        for (Map.Entry<Object, Object> v : values.entrySet()) {
            fs.setValue(v.getKey().toString(), v.getValue());
        }
        requestParameterEntity.setFormData(fs);
        String result = (String) systemApiToMethods.run(upload_api_url, 1);
    /**
     * é€šç”¨åˆ—表导出
     *
     * @param fse
     * @throws BaseException
     */
    public void generalListDataExport(FieldSetEntity fse) throws BaseException {
        //导出文件名称
        String file_name = fse.getString(CmnConst.FILE_NAME);
        //数据接口
        String upload_api_url = fse.getString(CmnConst.UPLOAD_API_URL);
        //数据接口所需参数
        FieldSetEntity export_param = fse.getSubDataTable(CmnConst.EXPORT_PARAM).getFieldSetEntity(0);
        HttpServletRequest request = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();
        RequestParameterEntity requestParameterEntity = (RequestParameterEntity) request.getAttribute(CoreConst.API_POST_REQUEST_DATA);
        FieldSetEntity fs = new FieldSetEntity();
        fs.setTableName(fse.getString(CmnConst.TABLE_NAME));
        //开始页
        int start_cpage = export_param.getInteger(CmnConst.START_CPAGE);
        //结束页
        int end_cpage = export_param.getInteger(CmnConst.END_CPAGE);
        int pagesize = export_param.getInteger(CmnConst.PAGESIZE);
        export_param.setValue(CmnConst.CPAGE, ((start_cpage) * pagesize) / pagesize);
        export_param.setValue(CmnConst.PAGESIZE, pagesize * ((end_cpage - start_cpage) + 1));
        export_param.remove(CmnConst.START_CPAGE);
        export_param.remove(CmnConst.END_CPAGE);
        Map<Object, Object> values = export_param.getValues();
        for (Map.Entry<Object, Object> v : values.entrySet()) {
            fs.setValue(v.getKey().toString(), v.getValue());
        }
        requestParameterEntity.setFormData(fs);
        String result = (String) systemApiToMethods.run(upload_api_url, 1);

        if (JSON.isValidObject(result)) {
            JSONObject resultJson = JSON.parseObject(result);
            if (200 == resultJson.getInteger("code")) {
                //获取数据成功
                Object data = resultJson.get("data");
                if (data instanceof JSONObject) {
                } else if (data instanceof JSONArray) {
                    DataTableEntity export_field = export_param.getSubDataTable("export_field");
                    try {
                        writeExcel((JSONArray) data, export_field, file_name);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            } else {
                throw new BaseException(resultJson.getString("code"), resultJson.getString("msg"));
            }
        } else {
            System.out.println("未知的数据类型");
        }
    }
        if (JSON.isValidObject(result)) {
            JSONObject resultJson = JSON.parseObject(result);
            if (200 == resultJson.getInteger("code")) {
                //获取数据成功
                Object data = resultJson.get("data");
                if (data instanceof JSONObject) {
                } else if (data instanceof JSONArray) {
                    DataTableEntity export_field = export_param.getSubDataTable("export_field");
                    try {
                        writeExcel((JSONArray) data, export_field, file_name);
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            } else {
                throw new BaseException(resultJson.getString("code"), resultJson.getString("msg"));
            }
        } else {
            System.out.println("未知的数据类型");
        }
    }

    /**
     * è¾“出excel
     *
     * @param data
     * @param fieldInfo
     * @param main_title
     * @throws IOException
     */
    public void writeExcel(JSONArray data, DataTableEntity fieldInfo, String main_title) throws IOException {
        //标题
        List<List<String>> headTitles = Lists.newArrayList();
        List<String> fields = Lists.newArrayList();
        //导出的数据集
        List<List<Object>> exportData = Lists.newArrayList();
        List<String> titles = Lists.newArrayList();
        List<String> titleTemplate = Lists.newArrayList();
        titleTemplate.add(main_title);
        for (int i = 0; i < fieldInfo.getRows(); i++) {
            titles.add(fieldInfo.getString(i, CmnConst.FIELD_DESC));
            fields.add(fieldInfo.getString(i, CmnConst.FIELD_NAME));
        }
        List<List<Object>> lists = contentData(data, fields);
        titles.forEach(title -> {
            List<String> secondTitle = Lists.newArrayList(titleTemplate);
            secondTitle.add(title);
            headTitles.add(secondTitle);
        });
        HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        // è¿™é‡ŒURLEncoder.encode可以防止中文乱码 å½“然和easyexcel没有关系
        // è¿™é‡Œéœ€è¦è®¾ç½®ä¸å…³é—­æµ
        EasyExcel.write(response.getOutputStream()).
                registerWriteHandler(new Custemhandler()).head(headTitles).sheet(main_title).doWrite(lists);
    }
    /**
     * è¾“出excel
     *
     * @param data
     * @param fieldInfo
     * @param main_title
     * @throws IOException
     */
    public void writeExcel(JSONArray data, DataTableEntity fieldInfo, String main_title) throws IOException {
        //标题
        List<List<String>> headTitles = Lists.newArrayList();
        List<String> fields = Lists.newArrayList();
        //导出的数据集
        List<String> titles = Lists.newArrayList();
        List<String> titleTemplate = Lists.newArrayList();
        titleTemplate.add(main_title);
        for (int i = 0; i < fieldInfo.getRows(); i++) {
            titles.add(fieldInfo.getString(i, CmnConst.FIELD_DESC));
            fields.add(fieldInfo.getString(i, CmnConst.FIELD_NAME));
        }
        List<List<Object>> lists = contentData(data, fields);
        titles.forEach(title -> {
            List<String> secondTitle = Lists.newArrayList(titleTemplate);
            secondTitle.add(title);
            headTitles.add(secondTitle);
        });
        HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        // è¿™é‡ŒURLEncoder.encode可以防止中文乱码 å½“然和easyexcel没有关系
        writeExcel(headTitles, lists, main_title, response);
    }

    /**
     * ç»„装数据
     *
     * @param dataArray
     * @param fields
     * @return
     */
    private static List<List<Object>> contentData(JSONArray dataArray, List<String> fields) {
        List<List<Object>> contentList = Lists.newArrayList();
        dataArray.forEach(data -> {
            JSONObject dataJson = (JSONObject) data;
            List<Object> content = Lists.newArrayList();
            fields.forEach(field -> {
                content.add(dataJson.get(field));
            });
            contentList.add(content);
        });
        return contentList;
    }
    /**
     * è¾“出excel
     *
     * @param excelHeaders excel表头
     * @param dataRows     æ•°æ®è¡Œ
     * @param sheetName    sheet名称
     * @throws IOException
     */
    public void writeExcel(List<List<String>> excelHeaders, List dataRows, String sheetName, HttpServletResponse response) throws IOException {

        response.setContentType("multipart/form-data");
        response.setCharacterEncoding("utf-8");
        // è¿™é‡ŒURLEncoder.encode可以防止中文乱码 å½“然和easyexcel没有关系
        // è¿™é‡Œéœ€è¦è®¾ç½®ä¸å…³é—­æµ
        try (ServletOutputStream outputStream = response.getOutputStream();) {
            EasyExcel.write(outputStream).
                    registerWriteHandler(new Custemhandler()).head(excelHeaders).sheet(sheetName).doWrite(dataRows);
        }
    }


    /**
     * ç»„装数据
     *
     * @param dataArray
     * @param fields
     * @return
     */
    private static List<List<Object>> contentData(JSONArray dataArray, List<String> fields) {
        List<List<Object>> contentList = Lists.newArrayList();
        dataArray.forEach(data -> {
            JSONObject dataJson = (JSONObject) data;
            List<Object> content = Lists.newArrayList();
            fields.forEach(field -> {
                content.add(dataJson.get(field));
            });
            contentList.add(content);
        });
        return contentList;
    }


}

 src/main/java/com/product/module/data/utli/CustomMergeStrategy.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,98 @@
package com.product.module.data.utli;

import cn.hutool.core.collection.CollUtil;
import com.alibaba.excel.metadata.Head;
import com.alibaba.excel.write.merge.AbstractMergeStrategy;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.concurrent.atomic.AtomicInteger;

public class CustomMergeStrategy extends AbstractMergeStrategy {

    /**
     * åˆ†ç»„，每几行合并一次
     */
    private List<List<Integer>> mergeColDataGroupCountList;

    /**
     * ç›®æ ‡åˆå¹¶åˆ—index
     */
    private List<Integer> targetColumnIndex;
    /**
     *     éœ€è¦å¼€å§‹åˆå¹¶å•å…ƒæ ¼çš„首行index
      */
    private Integer rowIndex;

    /**
     *     mergeColDataList为待合并目标列的值
      */
    public CustomMergeStrategy(List<List<String>> mergeColDataList, List<Integer> targetColumnIndex) {
        this.mergeColDataGroupCountList = getGroupCountList(mergeColDataList);
        this.targetColumnIndex = targetColumnIndex;
    }


    @Override
    protected void merge(Sheet sheet, Cell cell, Head head, Integer relativeRowIndex) {

        if (null == rowIndex) {
            rowIndex = cell.getRowIndex();
        }
        // ä»…从首行以及目标列的单元格开始合并，忽略其他
        if (cell.getRowIndex() == rowIndex && targetColumnIndex.contains(cell.getColumnIndex())) {
            //找到对应的需要合并的列
            AtomicInteger i = new AtomicInteger(0);
            Optional<Integer> first = targetColumnIndex.stream().filter(col -> {
                i.getAndIncrement();
                return col == cell.getColumnIndex();
            }).findFirst();
            mergeGroupColumn(sheet, first.get());
        }
    }

    private void mergeGroupColumn(Sheet sheet, Integer index) {
        int rowCount = rowIndex;
        for (Integer count : mergeColDataGroupCountList.get(index)) {
            if (count == 1) {
                rowCount += count;
                continue;
            }
            // åˆå¹¶å•å…ƒæ ¼
            CellRangeAddress cellRangeAddress = new CellRangeAddress(rowCount, rowCount + count - 1,
                    targetColumnIndex.get(index), targetColumnIndex.get(index));
            sheet.addMergedRegionUnsafe(cellRangeAddress);
            rowCount += count;
        }
    }

    /**
     *     è¯¥æ–¹æ³•å°†ç›®æ ‡åˆ—根据值是否相同连续可合并，存储可合并的行数
      */
    private List<List<Integer>> getGroupCountList(List<List<String>> exportDataList) {
        if (CollUtil.isEmpty(exportDataList)) {
            return new ArrayList<>();
        }
        List<List<Integer>> groupCountListList = new ArrayList<>();
        exportDataList.forEach(dataList->{
            List<Integer> groupCountList = new ArrayList<>();
            int count = 1;
            for (int i = 1; i < dataList.size(); i++) {
                if (dataList.get(i).equals(dataList.get(i - 1))) {
                    count++;
                } else {
                    groupCountList.add(count);
                    count = 1;
                }
            }
            // å¤„理完最后一条后
            groupCountList.add(count);
            groupCountListList.add(groupCountList);
        });
        return groupCountListList;
    }
}