easyexcel复杂表头导出

叁歲伎倆 2024-02-05 17:32 21阅读 0赞

easyexcel复杂表头导出

/**
     * @ClassName ColumnWidthStyleStrategy
     * @Description: excel导出列长度
     **/
    public class ExcelWidthStyleStrategy extends AbstractColumnWidthStyleStrategy {
        
    
        private static final int MAX_COLUMN_WIDTH = 200;
    
        private  final Map<Integer, Map<Integer, Integer>> CACHE = new HashMap<Integer, Map<Integer, Integer>>(8);
    
        @Override
        protected void setColumnWidth(WriteSheetHolder writeSheetHolder, List<CellData> cellDataList, Cell cell, Head head,
                                      Integer relativeRowIndex, Boolean isHead) {
        
            boolean needSetWidth = isHead || !CollectionUtils.isEmpty(cellDataList);
            if (!needSetWidth) {
        
                return;
            }
            Map<Integer, Integer> maxColumnWidthMap = CACHE.get(writeSheetHolder.getSheetNo());
            if (maxColumnWidthMap == null) {
        
                maxColumnWidthMap = new HashMap<Integer, Integer>(10);
                CACHE.put(writeSheetHolder.getSheetNo(), maxColumnWidthMap);
            }
            Integer columnWidth = dataLength(cellDataList, cell, isHead);
            if (columnWidth < 0) {
        
                return;
            }
            if (columnWidth > MAX_COLUMN_WIDTH) {
        
                columnWidth = MAX_COLUMN_WIDTH;
            }
            Integer maxColumnWidth = maxColumnWidthMap.get(cell.getColumnIndex());
            if (maxColumnWidth == null || columnWidth > maxColumnWidth) {
        
                maxColumnWidthMap.put(cell.getColumnIndex(), columnWidth);
                writeSheetHolder.getSheet().setColumnWidth(cell.getColumnIndex(), columnWidth * 200);
            }
        }
    
        private Integer dataLength(List<CellData> cellDataList, Cell cell, Boolean isHead) {
        
            if (isHead) {
        
                return cell.getStringCellValue().getBytes().length;
            }
            CellData cellData = cellDataList.get(0);
            CellDataTypeEnum type = cellData.getType();
            if (type == null) {
        
                return -1;
            }
            switch (type) {
        
                case STRING:
                    return cellData.getStringValue().getBytes().length;
                case BOOLEAN:
                    return cellData.getBooleanValue().toString().getBytes().length;
                case NUMBER:
                    return cellData.getNumberValue().toString().getBytes().length;
                default:
                    return -1;
            }
        }
    }

public static void main(String[] args) {
        
            //配置字体，表头背景等
            HorizontalCellStyleStrategy horizontalCellStyleStrategy = setConfigure();
            List<List<Object>> lists = new ArrayList<List<Object>>();
            for(int i = 0 ; i<20 ; i++){
        
                List<Object> list = new ArrayList<>();
                for(int j = 0 ; j<20 ; j++){
        
                    list.add("测试"+i);
                }
                lists.add(list);
            }
            try {
        
                //设置请求信息(获取HttpServletResponse，可通过HttpServletResponse给导出数据命名)
         /*       response.setContentType("multipart/form-data");
                response.setCharacterEncoding("utf-8");
                response.setHeader("Content-disposition", "attachment;filename=sjbkzjap.xlsx");
                ExcelWriter excelWriter = EasyExcelFactory.getWriter(response.getOutputStream());
                */
                String outPath = "c:/demo.xlsx";
                File file = new File(outPath);
                if(file.exists()){
        
                    // 文件存在
                    file.delete();
                }
                ExcelWriter excelWriter = EasyExcelFactory.getWriter(new FileOutputStream(outPath));
                WriteSheet writeSheet = EasyExcel.writerSheet(0, "sheet")
                        .registerWriteHandler(horizontalCellStyleStrategy)
                        .registerWriteHandler(new ExcelWidthStyleStrategy())
                        .build();
                // 创建一个表格
                WriteTable table = new WriteTable();
                // 动态添加 表头 headList --> 所有表头行集合
                //表头数据
                List<List<String>> headList = setHeadList();
                table.setHead(headList);
                excelWriter.write(lists,writeSheet,table);
                excelWriter.finish();
                System.out.println("导出成功!");
    
            }catch (IOException e){
        
                e.printStackTrace();
            }
        }
    		
    	//设置表头数据
        public static List<List<String>>  setHeadList(){
        
            List<String> headTitle0 = new ArrayList<String>();
            List<String> headTitle1 = new ArrayList<String>();
            List<String> headTitle2 = new ArrayList<String>();
            List<String> headTitle3 = new ArrayList<String>();
            List<String> headTitle4 = new ArrayList<String>();
            List<String> headTitle5 = new ArrayList<String>();
            List<String> headTitle6 = new ArrayList<String>();
            List<String> headTitle7 = new ArrayList<String>();
            List<String> headTitle8 = new ArrayList<String>();
            List<String> headTitle9 = new ArrayList<String>();
            List<String> headTitle14 = new ArrayList<String>();
            List<String> headTitle15 = new ArrayList<String>();
            List<String> headTitle16 = new ArrayList<String>();
            List<String> headTitle17 = new ArrayList<String>();
            List<String> headTitle18 = new ArrayList<String>();
            List<String> headTitle23 = new ArrayList<String>();
            List<String> headTitle24 = new ArrayList<String>();
            List<String> headTitle25 = new ArrayList<String>();
    
            //设置第一列为项目导出标题
            headTitle0.add("测试导出");
            headTitle1.add("测试导出");
            headTitle2.add("测试导出");
            headTitle3.add("测试导出");
            headTitle4.add("测试导出");
            headTitle5.add("测试导出");
            headTitle6.add("测试导出");
            headTitle7.add("测试导出");
            headTitle8.add("测试导出");
            headTitle9.add("测试导出");
            headTitle14.add("测试导出");
            headTitle15.add("测试导出");
            headTitle16.add("测试导出");
            headTitle17.add("测试导出");
            headTitle18.add("测试导出");
            headTitle23.add("测试导出");
            headTitle24.add("测试导出");
            headTitle25.add("测试导出");
    
            headTitle0.add("");
            headTitle1.add("");
            headTitle2.add("");
            headTitle3.add("");
            headTitle4.add("");
            headTitle5.add("");
            headTitle6.add("");
            headTitle7.add("");
            headTitle8.add("");
            headTitle9.add("");
            headTitle14.add("");
            headTitle15.add("");
            headTitle16.add("");
            headTitle17.add("");
            headTitle18.add("");
            headTitle23.add("");
            headTitle24.add("");
            headTitle25.add("单位：元");
    
            headTitle0.add("编号");
            headTitle1.add("姓名");
            headTitle2.add("年龄");
            headTitle3.add("居住地");
            headTitle4.add("父亲");
            headTitle5.add("母亲");
            headTitle6.add("工作单位");
            headTitle7.add("工资");
            headTitle8.add("转正前");
            headTitle9.add("转正前");
            headTitle14.add("转正前");
            headTitle15.add("转正前");
            headTitle16.add("平均工资");
            headTitle17.add("转正后");
            headTitle18.add("转正后");
            headTitle23.add("转正后");
            headTitle24.add("转正后");
            headTitle25.add("平均工资");
    
            headTitle0.add("编号");
            headTitle1.add("姓名");
            headTitle2.add("年龄");
            headTitle3.add("居住地");
            headTitle4.add("父亲");
            headTitle5.add("母亲");
            headTitle6.add("工作单位");
            headTitle7.add("工资");
            headTitle8.add("第一个月");
            headTitle9.add("第一个月");
            headTitle14.add("第二个月");
            headTitle15.add("第二个月");
            headTitle16.add("平均工资");
            headTitle17.add("第一个月");
            headTitle18.add("第一个月");
            headTitle23.add("第三个月");
            headTitle24.add("第三个月");
            headTitle25.add("平均工资");
    
            headTitle0.add("编号");
            headTitle1.add("姓名");
            headTitle2.add("年龄");
            headTitle3.add("居住地");
            headTitle4.add("父亲");
            headTitle5.add("母亲");
            headTitle6.add("工作单位");
            headTitle7.add("工资");
            headTitle8.add("日期");
            headTitle9.add("金额");
            headTitle14.add("日期");
            headTitle15.add("金额");
            headTitle16.add("平均工资");
            headTitle17.add("日期");
            headTitle18.add("金额");
            headTitle23.add("日期");
            headTitle24.add("金额");
            headTitle25.add("平均工资");
    
            //列数据保存
            List<List<String>> headList = new ArrayList<List<String>>();
            headList.add(headTitle0);
            headList.add(headTitle1);
            headList.add(headTitle2);
            headList.add(headTitle3);
            headList.add(headTitle4);
            headList.add(headTitle5);
            headList.add(headTitle6);
            headList.add(headTitle7);
            headList.add(headTitle8);
            headList.add(headTitle9);
            headList.add(headTitle14);
            headList.add(headTitle15);
            headList.add(headTitle16);
            headList.add(headTitle17);
            headList.add(headTitle18);
    
            //临时插入(可动态获取)
            List<String> maxList = new ArrayList<String>();
            List<String> maxList2 = new ArrayList<String>();
            maxList.add("测试导出");
            maxList.add("");
            maxList.add("转正后");
            maxList.add("第二个月");
            maxList.add("日期");
            maxList.add("日期");
    
            maxList2.add("测试导出");
            maxList2.add("");
            maxList2.add("转正后");
            maxList2.add("第二个月");
            maxList2.add("金额");
            maxList2.add("金额");
            headList.add(maxList);
            headList.add(maxList2);
            headList.add(headTitle23);
            headList.add(headTitle24);
            headList.add(headTitle25);
            return headList;
        }
    
    
        //配置字体，表头背景等
        private static HorizontalCellStyleStrategy setConfigure() {
        
            // 头的策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 背景色
            headWriteCellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
            WriteFont headWriteFont = new WriteFont();
            headWriteFont.setFontHeightInPoints((short) 10);
            headWriteCellStyle.setWriteFont(headWriteFont);
    
    
            // 内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 字体策略
            WriteFont contentWriteFont = new WriteFont();
            // 字体大小
            contentWriteFont.setFontHeightInPoints((short) 10);
            contentWriteCellStyle.setWriteFont(contentWriteFont);
            //边框
            //导出数据垂直居中
            contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            //导出数据水平居中
            contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
            contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
            contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
            contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
            contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
    
            //设置 自动换行
            contentWriteCellStyle.setWrapped(true);
            //设置
            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        }

![在这里插入图片描述][18a434e759cb489e99e7b224f0f705d7.png]

[18a434e759cb489e99e7b224f0f705d7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/01/30/d8413c6e27a047fcb9ce1cc9149fb368.png