导出excel【动态表头，多个sheet】_excelexportservice 多个表格

Service实现类

@Service
public class ExportService {
    @Autowired
    private BidExportMapper bidExportMapper;
    @Autowired
    private ExcelMapper excelMapper;

    /**
     * 导出excel - 单个sheet
     *
     * @param packageId 
     * @param request  HttpServletRequest
     * @param response HttpServletResponse
     * @throws Exception
     */
    public void exportSupplierQuotationDetailsAboutPackage(String packageId, HttpServletRequest request, HttpServletResponse response) throws Exception {
        //获取表头列表
        Map<String, String> headerTitle = getHeaderTitle(packageId);
        //获取数据内容
        List<Map<String,Object>> resultList = getResultList(packageId);
        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter(true);
        //重命名当前sheet
        writer.renameSheet("导出excel的sheet名称");
        //合并单元格后的标题行，取消默认标题样式
        writer.merge(headerTitle.size()-1, "表格的标题",false);
        //设置列名
        writer.setHeaderAlias(headerTitle);
        //如果想只写出加了别名的字段，可以调用此方法排除之
        writer.setOnlyAlias(true);
        //如果没有内容，则写出标题【表头列】
        if (CollectionUtil.isEmpty(resultList)){
            writer.writeHeadRow(headerTitle.values());
        }
        // 一次性写出内容，使用默认样式，强制输出标题
        writer.write(resultList,true);
        //设置样式
        setStyle(writer, headerTitle.size());
        //导出下载
        exportDownload(response,writer,"导出excel标题");
    }
    /**
     * 导出excel - 多个sheet
     *              
     *              
     * @param tenderProjectId 
     * @param request HttpServletRequest
     * @param response HttpServletResponse
     * @throws Exception
     */
    public void exportSupplierQuotationDetailsTotal(String tenderProjectId, HttpServletRequest request, HttpServletResponse response) throws Exception {
        //根据采购方案ID，获取包件列表
        List<PurTenderProjectPackagesVo> packageList = bidExportMapper.packageListByTenderProjectId(tenderProjectId);
        //通过工具类创建writer
        ExcelWriter writer = ExcelUtil.getWriter(true);
        packageList.stream().forEach(item->{
            //获取表头列表
            Map<String, String> headerTitle = getHeaderTitle(item.getId());
            //获取数据内容
            List<Map<String,Object>> resultList = getResultList(item.getId());
            if (CollectionUtil.isNotEmpty(resultList)){
                //自定义需要读取或写出的Sheet，如果给定的sheet不存在，创建之。
                writer.setSheet(item.getCode());
                //合并单元格后的标题行，使用默认标题样式
                writer.merge(headerTitle.size()-1, "表格的标题",false);
                //设置列名
                writer.setHeaderAlias(headerTitle);
                //如果想只写出加了别名的字段，可以调用此方法排除之
                writer.setOnlyAlias(true);
                // 一次性写出内容，使用默认样式，强制输出标题
                writer.write(resultList,true);
                //设置样式
                setStyle(writer, headerTitle.size());
            }
        });
        //删除sheet1，因为sheet1为空
        writer.getWorkbook().removeSheetAt(0);
        //导出下载
        exportDownload(response,writer,"导出excel标题");
    }
    /**
     * 获取标题列表
     *			这里的标题列表，指的是表头列表，我的业务需求是在表中获取标题列表信息
     *	        注意：根据自己业务需求进行标题变更
     * @param packageId 包件ID
     * @return 标题
     */
    public Map<String,String> getHeaderTitle(String packageId){
        //根据包件ID，获取报价模板
        List<PurTenderProjectPriceTemplateItemVo> templateList =  bidExportMapper.offerTemplateByPackageId(packageId);
        //1.标题信息
        Map<String, String> headerTitle= new LinkedHashMap<>();
        headerTitle.put("vendorName","供应商名称");
        templateList.stream().forEach(item->{
            headerTitle.put(item.getColumnName(),item.getShowName());
        });
        return headerTitle;
    }
    /**
     * 获取数据内容
     *          1. 即导出的数据，根据自己业务需求进行更改
     * @param packageId 包件ID
     * @return 导出的数据
     */
    public List<Map<String,Object>> getResultList(String packageId) {
        //1.根据包件ID，获取供应商报价明细
        PackageInfo packageInfo = excelMapper.selectPackageInfos(packageId);
        List<PurTenderBidInfoItemsVo> detailedList = bidExportMapper.offerDetailedByPackageId(packageId);
        List<PurTenderProjectPriceTemplateItemVo> templateList =  bidExportMapper.offerTemplateByPackageId(packageId);
        Map<String, PurTenderProjectPriceTemplateItemVo> itemVoMap = templateList.stream().collect(Collectors.toMap(PurTenderProjectPriceTemplateItemVo::getColumnName, Function.identity()));
        //2.导出的供应商报价明细
        List<Map<String,Object>> resultList = new ArrayList<>();
        if(CollectionUtil.isNotEmpty(detailedList)){
            detailedList.forEach(element->{
                if (StringUtils.isNotBlank(element.getRegionName())){
                    element.setDeliveryAddressAll(element.getRegionName() + element.getProvinceName() + element.getCityName() + element.getOfregionName() + element.getDeliveryAddress());
                }else {
                    element.setDeliveryAddressAll(element.getDeliveryAddress());
                }

                Map<String, Object> map = BeanUtil.beanToMap(element);
                resultList.add(map);
            });
        }
        //3.判断未公布报价或者未解密价格展示为 *
        if (CollectionUtil.isNotEmpty(resultList)){
            resultList.forEach(map->{
                if (StringUtils.equals(packageInfo.getPublicState(),"0") || StringUtils.equals(map.get("isEncrypt").toString(),"0")){
                    Field[] declaredFields = PurTenderBidInfoItemsVo.class.getDeclaredFields();
                    for (Field declaredField : declaredFields) {
                        String name = declaredField.getName();
                        if (itemVoMap.get(name) != null
                                && StringUtils.equals(itemVoMap.get(name).getValueType(),"2")
                                && declaredField.getType() == BigDecimal.class
                                && !StringUtils.equals(name,"purCount")
                        ){
                            map.put(name,"*");
                        }
                    }
                }
            });
        }
        return resultList;
    }
    /**
     * 写出到客户端下载
     *
     * @param response  HttpServletResponse
     * @param writer ExcelWriter
     * @param fileName 文件名称
     */
    public void  exportDownload(HttpServletResponse response,ExcelWriter writer,String fileName) throws Exception {
        //response为HttpServletResponse对象
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
        String codedFileName = java.net.URLEncoder.encode(fileName, "UTF8");
        response.setHeader("Content-Disposition","attachment;filename="+ codedFileName + ".xlsx");
        //out为OutputStream，需要写出到的目标流
        ServletOutputStream outputStream  = response.getOutputStream();
        writer.flush(outputStream, true);
        // 关闭writer，释放内存
        writer.close();
        //此处记得关闭输出Servlet流
        IoUtil.close(outputStream);
    }
    /**
     * 设置样式
     *       
     * @param writer ExcelWriter
     * @param headerSize 标题长度
     */
    public void setStyle(ExcelWriter writer, int headerSize){
        //设置筛选
        HutoolExcelUtil.setFilter(writer, "A2:"+HutoolExcelUtil.cellName(2,headerSize));
        //全局样式设置
        StyleSet styleSet = HutoolExcelUtil.setBaseGlobalStyle(writer,HutoolExcelUtil.createFont(writer,false,false,"宋体",12),HorizontalAlignment.RIGHT,VerticalAlignment.CENTER);
        //数字保留小数
        CellStyle cellStyleForNumber = styleSet.getCellStyleForNumber();
        cellStyleForNumber.setDataFormat((short)2);
        //合并单元格样式
        CellStyle cellStyle = HutoolExcelUtil.createCellStyle(writer,HutoolExcelUtil.createFont(writer,true,false,"宋体",16),true,VerticalAlignment.CENTER,HorizontalAlignment.CENTER);
        writer.getSheet().getRow(0).getCell(0).setCellStyle(cellStyle);
        //标题样式
        CellStyle cellStyleTitle = HutoolExcelUtil.createCellStyle(writer,HutoolExcelUtil.createFont(writer,true,false,"宋体",12),true,VerticalAlignment.CENTER,HorizontalAlignment.CENTER);;
        //设置背景
        cellStyleTitle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        //如果不设置，则背景无效
        cellStyleTitle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        HutoolExcelUtil.setBorderStyle(cellStyleTitle, BorderStyle.THIN, BorderStyle.THIN, BorderStyle.THIN, BorderStyle.THIN);
        Row row = writer.getSheet().getRow(1);
        for (int i = 0; i < headerSize; i++) {
            if (null!= row && null!=row.getCell(i)){
                row.getCell(i).setCellStyle(cellStyleTitle);
            }
        }
        //自适应列宽
        HutoolExcelUtil.setSizeAutoColumn(writer.getSheet(), headerSize-1);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199

样式工具类

/**
 * 导出工具类
 *
 * @Date: 2023-02-04
 */
public class HutoolExcelUtil {
    /**
     * 全局基础样式设置
     *
     * 默认单元格边框颜色为黑色，细线条
     * 默认背景颜色为白色
     *
     * @param writer writer
     * @param font   字体样式
     * @param horizontalAlignment 水平排列方式
     * @param verticalAlignment   垂直排列方式
     * @return cn.hutool.poi.excel.StyleSet
     */
    public static StyleSet setBaseGlobalStyle(ExcelWriter writer, Font font,HorizontalAlignment horizontalAlignment,VerticalAlignment verticalAlignment) {
        //全局样式设置
        StyleSet styleSet = writer.getStyleSet();
        //设置全局文本居中
        styleSet.setAlign(horizontalAlignment, verticalAlignment);
        //设置全局字体样式
        styleSet.setFont(font, false);
        //设置背景颜色 第二个参数表示是否将样式应用到头部
        styleSet.setBackgroundColor(IndexedColors.WHITE, false);
//        //设置自动换行 当文本长于单元格宽度是否换行
//        styleSet.setWrapText();
        // 设置全局边框样式
        styleSet.setBorder(BorderStyle.THIN, IndexedColors.BLACK);
        return styleSet;
    }

    /**
     * 设置标题的基础样式
     *
     * @param styleSet            StyleSet
     * @param font                字体样式
     * @param horizontalAlignment 水平排列方式
     * @param verticalAlignment   垂直排列方式
     * @return org.apache.poi.ss.usermodel.CellStyle
     */
    public static CellStyle createHeadCellStyle(StyleSet styleSet, Font font,
                                                HorizontalAlignment horizontalAlignment,
                                                VerticalAlignment verticalAlignment) {
        CellStyle headCellStyle = styleSet.getHeadCellStyle();
        headCellStyle.setAlignment(horizontalAlignment);
        headCellStyle.setVerticalAlignment(verticalAlignment);
        headCellStyle.setFont(font);
        return headCellStyle;
    }

    /**
     *设置基础字体样式字体 这里保留最基础的样式使用
     *
     * @param writer    writer
     * @param bold      是否加粗
     * @param italic     是否斜体
     * @param fontName 字体名称
     * @param fontSize 字体大小
     * @return org.apache.poi.ss.usermodel.Font
     */
    public static Font createFont(ExcelWriter writer, boolean bold, boolean italic, String fontName, int fontSize) {
        Font font = writer.getWorkbook().createFont();
        //设置字体名称
        font.setFontName(fontName);
        //设置是否斜体
        font.setItalic(italic);
        //设置字体大小 以磅为单位
        font.setFontHeightInPoints((short) fontSize);
        //设置是否加粗
        font.setBold(bold);
        return font;
    }

    /**
     *设置行或单元格基本样式
     *
     * @param writer              writer
     * @param font                字体样式
     * @param verticalAlignment   垂直居中
     * @param horizontalAlignment 水平居中
     * @return CellStyle
     */
    public static CellStyle createCellStyle(ExcelWriter writer, Font font, boolean wrapText,
                                            VerticalAlignment verticalAlignment,
                                            HorizontalAlignment horizontalAlignment) {
        CellStyle cellStyle = writer.getWorkbook().createCellStyle();
        cellStyle.setVerticalAlignment(verticalAlignment);
        cellStyle.setAlignment(horizontalAlignment);
        cellStyle.setWrapText(wrapText);
        cellStyle.setFont(font);
        return cellStyle;
    }

    /**
     *设置边框样式
     *
     * @param cellStyle 样式对象
     * @param bottom    下边框
     * @param left      左边框
     * @param right     右边框
     * @param top       上边框
     */
    public static void setBorderStyle(CellStyle cellStyle, BorderStyle bottom, BorderStyle left, BorderStyle right,
                                      BorderStyle top) {
        cellStyle.setBorderBottom(bottom);
        cellStyle.setBorderLeft(left);
        cellStyle.setBorderRight(right);
        cellStyle.setBorderTop(top);
    }
    /**
     * 方法描述: 自适应宽度(中文支持)
     *
     * @param sheet 页
     * @param size  因为for循环从0开始，size值为 列数-1
     */
    public static void setSizeAutoColumn(Sheet sheet, int size) {
        for (int columnNum = 0; columnNum <= size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }
                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == CellType.STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }
    /**
     * 自适应宽度(中文支持)
     *
     * @param sheet Sheet
     * @param headerSize 标题长度
     */
    public static void setSizeColumn(Sheet sheet, int headerSize) {
        for (int i = 0; i < headerSize; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * 10 / 10);
        }
    }

    /**
     *手动宽度(中文支持)
     *
     * @param excelWriter ExcelWriter
     * @param list 列的列表：Arrays.asList(5, 12, 16, 15, 14, 15, 30, 15, 15, 12)
     * @param size 标题长度
     */
    public static void setSizeColumn(ExcelWriter excelWriter, List<Integer> list, int size) {
        for (int columnNum = 0; columnNum <= size; columnNum++) {
            excelWriter.setColumnWidth(columnNum, list.get(columnNum));
        }
    }
    /**
     * 设置下拉列表框
     *
     * @param writer   ExcelWriter
     * @param str      内容
     * @param firstRow 第一行
     * @param lastRow  第二行
     * @param firstCol 第一列
     * @param lastCol  第二列
     */
    public static void setSelect(ExcelWriter writer, String[] str, int firstRow, int lastRow, int firstCol, int lastCol) {
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidationHelper helper = writer.getSheet().getDataValidationHelper();
        DataValidationConstraint constraint = helper.createExplicitListConstraint(str);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        writer.addValidationData(dataValidation);
    }

    /**
     * 设置筛选功能
     *
     * @param writer ExcelWriter
     * @param str    要筛选的坐标  例如：A2:D6
     */
    public static void setFilter(ExcelWriter writer, String str) {
        Sheet sheet = writer.getSheet();
        CellRangeAddress c = CellRangeAddress.valueOf(str);
        sheet.setAutoFilter(c);
    }

    /**
     * 通过给定一个行号，列号，得到一个单元格名称框，比如：T2
     *
     * @param row 行号
     * @param col 列号
     * @return 单元格名称
     */
    public static String cellName(int row,int col){
        ArrayList<Character> list = new ArrayList<>();
        while(col>0){
            list.add((char)(col%26+'A'-1));
            col/=26;
        }
        StringBuffer buffer = new StringBuffer();
        for (int i = list.size()-1; i >= 0; i--) {
            buffer.append(list.get(i));
            buffer.append(""+row);
        }
        return buffer.toString();
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220