导出Excel的技术分享-综合篇

快来打我* 2023-10-15 18:07 4阅读 0赞

![在这里插入图片描述][7aea7188edc0434db59a05e358937412.png_pic_center]

## **导出Excel的技术分享-综合篇** ##

#### **简单的EasyExcel使用** ####

/**
         * 最简单的写
         */
        public void simpleWrite() {
        
            // 注意 simpleWrite在数据量不大的情况下可以使用（5000以内，具体也要看实际情况），数据量大参照 重复多次写入
    
            // 写法1 JDK8+
            // since: 3.0.0-beta1
            String fileName = "simpleWrite" + System.currentTimeMillis() + ".xlsx";
            // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
            // 如果这里想使用03 则 传入excelType参数即可
            EasyExcel.write(fileName, DemoData.class)
                    .sheet("模板")
                    .doWrite(data());
        }
     private List<DemoData> data() {
        
            List<DemoData> list = ListUtils.newArrayList();
            for (int i = 0; i < 10; i++) {
        
                DemoData data = new DemoData();
                data.setString("字符串" + i);
                data.setDate(new Date());
                data.setDoubleData(0.56);
                list.add(data);
            }
            return list;
        }

#### **行标题设置** ####

public static void main(String[] args) {
        
            String powerTitle = "行单元格样例演示\n" +
                   "行单元格样例演示\n";
            ExcelWriter excelWriter = EasyExcel.write("行标题设置.xlsx").build();
            WriteSheet writeSheet = EasyExcel.writerSheet(0,"行标题设置").head(DemoData.class)
                    .relativeHeadRowIndex(2)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerWriteHandler(new IndexStandardStyleHandler(powerTitle)).build();
            excelWriter.write(data(),writeSheet);
            excelWriter.finish();
        }

public class IndexStandardStyleHandler implements SheetWriteHandler {
        
    
    
        private String tableTitle;
    
        public IndexStandardStyleHandler(String title) {
        
            this.tableTitle = title;
        }
    
        @Override
        public void afterSheetCreate(SheetWriteHandlerContext context) {
        
            // 在创建工作表之后执行的操作
            WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder();
    //        ExcelWriteHeadProperty excelWriteHeadProperty = writeSheetHolder.getExcelWriteHeadProperty();
    //        excelWriteHeadProperty.setHeadRowNumber(3);
            // 设置行高和样式
            // 创建第一行并设置样式
            Row row = writeSheetHolder.getSheet().createRow(0);
            row.setHeight((short) 1000);
            Cell cell = row.createCell(0);
            cell.setCellValue(tableTitle);
            //设置标题
            // 准备样式
            CellStyle cellStyle = context.getWriteWorkbookHolder().getWorkbook().createCellStyle();
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            Font font = context.getWriteWorkbookHolder().getWorkbook().createFont();
    //        font.setBold(true);
            font.setFontHeight((short) 500);
            font.setFontName("宋体");
            font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
            cellStyle.setFont(font);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setWrapText(true);
            cell.setCellStyle(cellStyle);
            //writeWorkbookHolder.createCellStyle(style,null);
            Sheet sheet = context.getWriteSheetHolder().getSheet();
            sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 1, 0, 2));
        }
    
        @Override
        public int order() {
        
            return 3;
        }
    }

#### **单元格线设置** ####

public static void main(String[] args) {
        
            String powerTitle = "行单元格样例演示\n" +
                   "行单元格样例演示\n";
            ExcelWriter excelWriter = EasyExcel.write("单元格线设置.xlsx").build();
            WriteSheet writeSheet = EasyExcel.writerSheet(0,"单元格线设置").head(DemoData.class)
                    .relativeHeadRowIndex(2)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerWriteHandler(new CelllineStyleHandler())
                    .registerWriteHandler(new IndexStandardStyleHandler(powerTitle)).build();
            excelWriter.write(data(),writeSheet);
            excelWriter.finish();
        }

public class CelllineStyleHandler implements CellWriteHandler {
        
    
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        
            for (int i = 0; i < cellDataList.size(); i++) {
        
                WriteCellData<?> writeCellData = cellDataList.get(i);
                WriteCellStyle writeCellStyle = writeCellData.getWriteCellStyle();
                writeCellStyle.setBorderLeft(THIN);
                writeCellStyle.setBorderRight(THIN);
                writeCellStyle.setBorderTop(THIN);
                writeCellStyle.setBorderBottom(THIN);
                writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
                writeCellStyle.setShrinkToFit(true);
            }
        }
    
        @Override
        public int order() {
        
            return 1;
        }
    }

#### **下拉框设置** ####

public static void main(String[] args) {
            String powerTitle = "行单元格样例演示\n" +
                   "行单元格样例演示\n";
            ExcelWriter excelWriter = EasyExcel.write("下拉框设置.xlsx").build();
            WriteSheet writeSheet = EasyExcel.writerSheet(0,"下拉框设置").head(DemoData.class)
                    .relativeHeadRowIndex(2)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerWriteHandler(new CelllineStyleHandler())
                    .registerWriteHandler(new CellDropDownBoxWriteHandler())
                    .registerWriteHandler(new IndexStandardStyleHandler(powerTitle)).build();
            excelWriter.write(data(),writeSheet);
            excelWriter.finish();
        }

public class CellDropDownBoxWriteHandler implements SheetWriteHandler {
        
    
        /**
         * 定义一个map key是需要添加下拉框的列的index value是下拉框数据
         */
        Map<Integer, String[]> mapDropDown = new HashMap<>();
    
        String[] checkFrequency = {
        "字符串0","字符串1","字符串2","字符串3","字符串4"};
    
        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        
            //下拉选在Excel中对应的列
            Field[] fields = writeSheetHolder.getClazz().getDeclaredFields();
            for (int i = 0; i < fields.length; i++) {
        
                Field field = fields[i];
                if (field.isAnnotationPresent(ExcelProperty.class) && field.isAnnotationPresent(ExcelProperty.class)){
        
                    mapDropDown.put(i,checkFrequency);
                }
            }
    
            //获取工作簿
            Sheet sheet = writeSheetHolder.getSheet();
            ///开始设置下拉框
            DataValidationHelper helper = sheet.getDataValidationHelper();
            //设置下拉框
            for (Map.Entry<Integer, String[]> entry : mapDropDown.entrySet()) {
        
                /*起始行、终止行、起始列、终止列  起始行为1即表示表头不设置**/
                //这里设置65535可能又问题，因为这个是excel的最大行数，如果数据量超过这个数，就会报错
                CellRangeAddressList addressList = new CellRangeAddressList(1, 65535, entry.getKey(), entry.getKey());
                /*设置下拉框数据**/
                DataValidationConstraint constraint = helper.createExplicitListConstraint(entry.getValue());
                DataValidation dataValidation = helper.createValidation(constraint, addressList);
    
                //阻止输入非下拉选项的值
                dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                dataValidation.setShowErrorBox(true);
                dataValidation.setSuppressDropDownArrow(true);
                dataValidation.createErrorBox("提示", "输入值与单元格定义格式不一致");
                dataValidation.createPromptBox("填写说明", "填写内容只能为下拉数据集中的类型");
    
                sheet.addValidationData(dataValidation);
            }
        }
    
        @Override
        public int order() {
        
            return 1;
        }
    }

#### **标题和内容字体样式设置不一样** ####

public static void main(String[] args) {
        
            String powerTitle = "行单元格样例演示\n" +
                   "行单元格样例演示\n";
            ExcelWriter excelWriter = EasyExcel.write("标题和内容字体样式不一样设置.xlsx").build();
            WriteSheet writeSheet = EasyExcel.writerSheet(0,"标题和内容字体样式不一样设置").head(DemoData.class)
                    .relativeHeadRowIndex(2)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerWriteHandler(new CelllineStyleHandler())
                    .registerWriteHandler(new CellDropDownBoxWriteHandler())
                    .registerWriteHandler(new FontStyleWriteHandler())
                    .registerWriteHandler(new IndexStandardStyleHandler(powerTitle)).build();
            excelWriter.write(data(),writeSheet);
            excelWriter.finish();
        }

public class FontStyleWriteHandler implements CellWriteHandler {
        
        /**
         * 设置字体样式
         * @param writeSheetHolder
         * @param writeTableHolder Nullable.It is null without using table writes.
         * @param cellDataList     Nullable.It is null in the case of add header.There may be several when fill the data.
         * @param cell
         * @param head             Nullable.It is null in the case of fill data and without head.
         * @param relativeRowIndex Nullable.It is null in the case of fill data.
         * @param isHead           It will always be false when fill data.
         */
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        
            for (int i = 0; i < cellDataList.size(); i++) {
        
                WriteCellData<?> writeCellData = cellDataList.get(i);
                WriteCellStyle writeCellStyle = writeCellData.getWriteCellStyle();
                if (2 == cell.getRowIndex()) {
        
                    WriteFont writeFont = new WriteFont();
                    writeFont.setBold(true);
                    writeFont.setFontHeightInPoints((short) 13);
                    writeFont.setFontName("楷体");
                    writeCellStyle.setWriteFont(writeFont);
                } else {
        
                    WriteFont writeFont = new WriteFont();
                    writeFont.setFontName("宋体");
                    writeCellStyle.setBorderLeft(THIN);
                    writeCellStyle.setBorderRight(THIN);
                    writeCellStyle.setBorderTop(THIN);
                    writeCellStyle.setBorderBottom(THIN);
                    writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                    writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
                    writeCellStyle.setWriteFont(writeFont);
    //                writeCellStyle.setShrinkToFit(true);
                }
            }
        }
    
        @Override
        public int order() {
        
            return 1;
        }
    }

#### **单元格合并设置** ####

public static void main(String[] args) {
            String powerTitle = "行单元格样例演示\n" +
                   "行单元格样例演示\n";
            ExcelWriter excelWriter = EasyExcel.write("单元格合并设置.xlsx").build();
            WriteSheet writeSheet = EasyExcel.writerSheet(0,"单元格合并设置").head(DemoData.class)
                    .relativeHeadRowIndex(2)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerWriteHandler(new CelllineStyleHandler())
                    .registerWriteHandler(new CellDropDownBoxWriteHandler())
                    .registerWriteHandler(new FontStyleWriteHandler())
                    .registerWriteHandler(new ExcelFillCellMergeStrategyHandler(2,new int[]{0,1,2}))
                    .registerWriteHandler(new IndexStandardStyleHandler(powerTitle)).build();
            excelWriter.write(data(),writeSheet);
            excelWriter.finish();
        }

public class ExcelFillCellMergeStrategyHandler implements CellWriteHandler {
        
    
        /**
         * 合并字段的下标，如第一到五列new int[]{0,1,2,3,4}
         */
        private int[] mergeColumnIndex;
        /**
         * 从第几行开始合并，如果表头占两行，这个数字就是2
         */
        private int mergeRowIndex;
    
        public ExcelFillCellMergeStrategyHandler(int mergeRowIndex, int[] mergeColumnIndex) {
        
            this.mergeRowIndex = mergeRowIndex;
            this.mergeColumnIndex = mergeColumnIndex;
        }
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                     List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
        
    
            //当前行
            int curRowIndex = cell.getRowIndex();
            //当前列
            int curColIndex = cell.getColumnIndex();
    
    
            if (curRowIndex > mergeRowIndex) {
        
                for (int i = 0; i < mergeColumnIndex.length; i++) {
        
                    if (curColIndex == mergeColumnIndex[i]) {
        
                        mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                        break;
                    }
                }
            }
    
            for (int i = 0; i < list.size(); i++) {
        
                WriteCellData<?> writeCellData = list.get(i);
                WriteCellStyle writeCellStyle = writeCellData.getWriteCellStyle();
                writeCellStyle.setBorderLeft(THIN);
                writeCellStyle.setBorderRight(THIN);
                writeCellStyle.setBorderTop(THIN);
                writeCellStyle.setBorderBottom(THIN);
                writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
                writeCellStyle.setShrinkToFit(true);
            }
        }
    
        private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
        
            //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
            Object curData = cell.getCellType() == CellType.STRING ? cell.getStringCellValue() :
                    cell.getNumericCellValue();
            Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
            Object preData = preCell.getCellType() == CellType.STRING ? preCell.getStringCellValue() :
                    preCell.getNumericCellValue();
            // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
            //
            if (curData.equals(preData)) {
        
                Sheet sheet = writeSheetHolder.getSheet();
                List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
                boolean isMerged = false;
                for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
        
                    CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                    // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                    if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
        
                        sheet.removeMergedRegion(i);
                        cellRangeAddr.setLastRow(curRowIndex);
                        sheet.addMergedRegion(cellRangeAddr);
                        isMerged = true;
                    }
                }
                // 若上一个单元格未被合并，则新增合并单元
                if (!isMerged) {
        
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex,
                            curColIndex);
                    sheet.addMergedRegion(cellRangeAddress);
                }
            }
        }
    
        @Override
        public int order() {
        
            return 1;
        }
    }

#### **自定义多个sheet页导出设置** ####

public class Test07 {
        
        public static void main(String[] args) throws NoSuchFieldException, ClassNotFoundException, IllegalAccessException {
        
            String powerTitle = "行单元格样例演示\n" +
                   "行单元格样例演示\n";
            ExcelWriter excelWriter = EasyExcel.write("自定义多个sheet页导出设置.xlsx").build();
            // 第一个sheet的写入
            WriteSheet writeSheet01 = EasyExcel.writerSheet(0,"自定义多个sheet页导出设置").head(DemoData.class)
                    .relativeHeadRowIndex(2)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerWriteHandler(new CelllineStyleHandler())
                    .registerWriteHandler(new CellDropDownBoxWriteHandler())
                    .registerWriteHandler(new FontStyleWriteHandler())
                    .registerWriteHandler(new ExcelFillCellMergeStrategyHandler(2,new int[]{
        0,1,2}))
                    .registerWriteHandler(new IndexStandardStyleHandler(powerTitle)).build();
            excelWriter.write(data(),writeSheet01);
            // 后续sheet写入
            Map map = listGroupInfo(DemoData.class, dataList());
            int i = 1;
            Map<String,String> orgNameMap = new HashMap<>();
            orgNameMap.put("自定义sheet页1","自定义sheet页1");
            orgNameMap.put("自定义sheet页2","自定义sheet页2");
            orgNameMap.put("自定义sheet页3","自定义sheet页3");
            orgNameMap.put("自定义sheet页4","自定义sheet页4");
            for (Map.Entry<String, String> entry : orgNameMap.entrySet()) {
        
                String OrgCode = entry.getKey();
                String orgName = entry.getValue();
                Object list = map.get(OrgCode);
                // 设置非供电单位写入标题
                String noPowerTitle = "行单元格样例演示\n" +OrgCode+
                        "行单元格样例演示\n";;
                WriteSheet sheet = EasyExcel.writerSheet(i, orgName).head(DemoData.class)
                        .relativeHeadRowIndex(2)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                        .build();
                excelWriter.write((Collection<?>) list,sheet);
                i = i + 1;
            }
            excelWriter.finish();
        }
    
        private static Map listGroupInfo(Class<?> head1, List<?> dataList1) throws ClassNotFoundException, NoSuchFieldException, IllegalAccessException {
        
            String groupField = "string";
            String name = head1.getName();
            Class<?> clazz = Class.forName(name);
            Field field = clazz.getDeclaredField(groupField);
            field.setAccessible(true);
            Map<Object, ? extends List<?>> map = dataList1.stream()
                    .collect(Collectors.groupingBy(obj -> {
        
                        try {
        
                            return field.get(obj);
                        } catch (IllegalAccessException e) {
        
                            e.printStackTrace();
                            return null;
                        }
                    }));
            Set<Object> objects = map.keySet();
            Iterator<Object> iterator = objects.iterator();
    //        while (iterator.hasNext()) {
        
    //            List<?> list = map.get(iterator.next());
    //            for (int i = 0; i < list.size(); i++) {
        
    //                Object o = list.get(i);
    //                Field fieldSerialNumber = clazz.getDeclaredField("serialNumber");
    //                fieldSerialNumber.setAccessible(true);
    //                fieldSerialNumber.set(o,String.valueOf(i+1));
    //            }
    //        }
            return map;
        }
    
        private static List<DemoData> data() {
        
            List<DemoData> list = ListUtils.newArrayList();
            for (int i = 0; i < 10; i++) {
        
                DemoData data = new DemoData();
                data.setString("字符串" + i);
                data.setDate(new Date());
                data.setDoubleData(0.56);
                list.add(data);
            }
            return list;
        }
    
        private static List<DemoData> dataList() {
        
            List<DemoData> list = ListUtils.newArrayList();
            for (int i = 0; i < 10; i++) {
        
                DemoData data1 = new DemoData();
                data1.setString("自定义sheet页1");
                data1.setDate(new Date());
                data1.setDoubleData(111.111);
                list.add(data1);
            }
            for (int i = 0; i < 10; i++) {
        
                DemoData data2 = new DemoData();
                data2.setString("自定义sheet页2");
                data2.setDate(new Date());
                data2.setDoubleData(222.222);
                list.add(data2);
            }
            for (int i = 0; i < 10; i++) {
        
                DemoData data3 = new DemoData();
                data3.setString("自定义sheet页3");
                data3.setDate(new Date());
                data3.setDoubleData(333.333);
                list.add(data3);
            }
            for (int i = 0; i < 10; i++) {
        
                DemoData data3 = new DemoData();
                data3.setString("自定义sheet页4");
                data3.setDate(new Date());
                data3.setDoubleData(444.444);
                list.add(data3);
            }
            return list;
        }
    
    
    }

#### **单元格锁设置** ####

public static void main(String[] args) {
        
            String powerTitle = "行单元格样例演示\n" +
                   "行单元格样例演示\n";
            ExcelWriter excelWriter = EasyExcel.write("单元格锁设置.xlsx").build();
            WriteSheet writeSheet = EasyExcel.writerSheet(0,"单元格锁设置").head(DemoDataLock.class)
                    .relativeHeadRowIndex(2)
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerWriteHandler(new CelllineStyleHandler())
                    .registerWriteHandler(new LockHandler())
                    .registerWriteHandler(new IndexStandardStyleHandler(powerTitle)).build();
            excelWriter.write(data(),writeSheet);
            excelWriter.finish();
        }

public class LockHandler extends LongestMatchColumnWidthStyleStrategy implements SheetWriteHandler {
        
    
    
        @Override
        public void afterCellDispose(CellWriteHandlerContext context) {
        
            // 锁定有内容过的单元格(方法1)
            Class clazz = context.getWriteSheetHolder().getClazz();
            Field[] fields = clazz.getDeclaredFields();
            Field field = fields[context.getColumnIndex()];
            if (field.isAnnotationPresent(UnLockCell.class) && field.isAnnotationPresent(ExcelProperty.class)) {
        
                WriteCellStyle writeCellStyle = context.getFirstCellData().getOrCreateStyle();
                writeCellStyle.setLocked(false);
            }
        }
    
        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        
            // 设置保护密码
            writeSheetHolder.getSheet().protectSheet("ZhiChengKeJi");
            // 锁定单元格不可选中(防止别人直接复制内容到其他excel修改)
            ((SXSSFSheet) writeSheetHolder.getSheet()).lockSelectLockedCells(false);
    //        ((SXSSFSheet) writeSheetHolder.getSheet()).lockSelectLockedCells(true);
        }
    }

#### **设置大标题行样式** ####

![在这里插入图片描述][75d471fffeb649f18b7dd51528fc3748.png]

@PostMapping("/exportExcel")
        public void exportExcel(HttpServletResponse response) throws IOException {
        
            // 数据库获取数据
            List<Profile> list = iProfileService.exportExcel(response);
            List<ProfileExcel> profileExcels = ObjectConvertUtil.convertList(list, ProfileExcel.class);
    //        List<ProfileExcel> profileExcels = ExcelConvert.INSTANCE.converList(list);
            // 输出
            ExcelUtils.write(response, "大佬信息", "岗位列表", ProfileExcel.class, profileExcels);
    
        }

展示工具类中的代码

/**
         * 将列表以Excel的形式响应给前端
         *
         * @param response 响应
         * @param fileName 文件名
         * @param sheetName Excel sheet 名
         * @param head Excel head 头
         * @param data 数据列表
         * @param <T> 泛型，保证 head 和 data 类型的一致性
         * @throws IOException 写入失败的情况
         */
        public static <T> void write(HttpServletResponse response, String fileName, String sheetName, Class<T> head, List<T> data) throws IOException {
        
            // 这一部分是设置编码样式
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8") + ".xlsx");
            // 输出Excel
            EasyExcel.write(response.getOutputStream(),head)
                    .relativeHeadRowIndex(2)
                    // 不要自动关闭，交给 Servlet 自己处理
                    //.autoCloseStream(false)
                    .registerWriteHandler(new ExportIndexTypeSheetWriteHandler())
                    .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                    .registerWriteHandler(new ExcelFillCellMergeStrategy(3,new int[]{
        0,1,2,3,4,5,6}))
                    .registerWriteHandler(new FontStyleWriteHandler())
                    .sheet(sheetName).doWrite(data);
        }

核心注册类代码展示

实现SheetWriteHandler策略接口，在sheet创建之前完成对样式进行提前的设置

public class ExportIndexTypeSheetWriteHandler implements SheetWriteHandler {
        
    
        @Override
        public void afterSheetCreate(SheetWriteHandlerContext context) {
        
            // 在创建工作表之后执行的操作
            WriteSheetHolder writeSheetHolder = context.getWriteSheetHolder();
    //        ExcelWriteHeadProperty excelWriteHeadProperty = writeSheetHolder.getExcelWriteHeadProperty();
    //        excelWriteHeadProperty.setHeadRowNumber(3);
            // 设置行高和样式
            // 创建第一行并设置样式
            Row row = writeSheetHolder.getSheet().createRow(0);
            // 设置第一行的高为
            row.setHeight((short) 1000);
            // 提前创建第一行单元格并进行设置
            Cell cell = row.createCell(0);
            // 设置需要展示的文字信息
            String title = "大佬们的信息展示\n" +
                    "这个是演示的数据标题\r\n";
            // 单元格填充内容
            cell.setCellValue(title);
            //设置标题
            // 准备样式
            // 从上下文内容中获取工作簿并创建样式
            CellStyle cellStyle = context.getWriteWorkbookHolder().getWorkbook().createCellStyle();
            // 这个接口在ss。POI的核心模块中存在
            // 设置单元格的垂直对齐类型 ： 垂直对齐以单元格高度为中心。
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 设置单元格的水平对齐样式：水平对齐居中，这意味着文本在单元格中居中。
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 从上下文获取字体
            Font font = context.getWriteWorkbookHolder().getWorkbook().createFont();
            // 设置字体加粗
            font.setBold(true);
            // 设置字体行高
            font.setFontHeight((short) 500);
            // 设置字体的样式名称
            font.setFontName("宋体");
            // 设置字体的颜色
            font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
            // 需要在单元格样式中填充字体
            cellStyle.setFont(font);
            // 单元格设置为水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 设置单元格为允许换行，不然上述title中的单元格换行无效
            cellStyle.setWrapText(true);
            // 最后需要将单元格的样式填充到单元格中
            cell.setCellStyle(cellStyle);
            //writeWorkbookHolder.createCellStyle(style,null);
            // 有点重复操作，从上下文中获取默认第一个sheet页的样式
            Sheet sheet = context.getWriteSheetHolder().getSheet();
            // 设置单元格的合并区域，给坐标的方式。哪行开始哪行结束。哪列开始那列结束
            sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 1, 0, 5));
        }
    }

#### 标题行的样式填充颜色改变 ####

![在这里插入图片描述][6ec5cf93d19447b8878617fde0512ede.png]

![在这里插入图片描述][0e23ea4495ca47239d4561cdd345e75e.png]

颜色9是透明色。

#### **相同单元格的样式合并** ####

![在这里插入图片描述][5a240d620b68491da475638cb807c132.png]

这种编码方式需要在代码使用的时候将那几列放到方法中。

public class ExcelFillCellMergeStrategy implements CellWriteHandler {
        /**
         * 合并字段的下标，如第一到五列new int[]{0,1,2,3,4}
         */
        private int[] mergeColumnIndex;
        /**
         * 从第几行开始合并，如果表头占两行，这个数字就是2
         */
        private int mergeRowIndex;
    
        public ExcelFillCellMergeStrategy() {
        }
    
        public ExcelFillCellMergeStrategy(int mergeRowIndex, int[] mergeColumnIndex) {
            this.mergeRowIndex = mergeRowIndex;
            this.mergeColumnIndex = mergeColumnIndex;
        }
    
        @Override
        public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row,
                                     Head head, Integer integer, Integer integer1, Boolean aBoolean) {
    
        }
    
        @Override
        public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                                    Head head, Integer integer, Boolean aBoolean) {
    
        }
    
    
    
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder,
                                     List<WriteCellData<?>> list, Cell cell, Head head, Integer integer, Boolean aBoolean) {
            //当前行
            int curRowIndex = cell.getRowIndex();
            //当前列
            int curColIndex = cell.getColumnIndex();
    
            if (curRowIndex > mergeRowIndex) {
                for (int i = 0; i < mergeColumnIndex.length; i++) {
                    if (curColIndex == mergeColumnIndex[i]) {
                        mergeWithPrevRow(writeSheetHolder, cell, curRowIndex, curColIndex);
                        break;
                    }
                }
            }
        }
    
        private void mergeWithPrevRow(WriteSheetHolder writeSheetHolder, Cell cell, int curRowIndex, int curColIndex) {
            //获取当前行的当前列的数据和上一行的当前列列数据，通过上一行数据是否相同进行合并
            Object curData = cell.getCellTypeEnum() == CellType.STRING ? cell.getStringCellValue() :
                    cell.getNumericCellValue();
            Cell preCell = cell.getSheet().getRow(curRowIndex - 1).getCell(curColIndex);
            Object preData = preCell.getCellTypeEnum() == CellType.STRING ? preCell.getStringCellValue() :
                    preCell.getNumericCellValue();
            // 比较当前行的第一列的单元格与上一行是否相同，相同合并当前单元格与上一行
            //
            if (curData.equals(preData)) {
                Sheet sheet = writeSheetHolder.getSheet();
                List<CellRangeAddress> mergeRegions = sheet.getMergedRegions();
                boolean isMerged = false;
                for (int i = 0; i < mergeRegions.size() && !isMerged; i++) {
                    CellRangeAddress cellRangeAddr = mergeRegions.get(i);
                    // 若上一个单元格已经被合并，则先移出原有的合并单元，再重新添加合并单元
                    if (cellRangeAddr.isInRange(curRowIndex - 1, curColIndex)) {
                        sheet.removeMergedRegion(i);
                        cellRangeAddr.setLastRow(curRowIndex);
                        sheet.addMergedRegion(cellRangeAddr);
                        isMerged = true;
                    }
                }
                // 若上一个单元格未被合并，则新增合并单元
                if (!isMerged) {
                    CellRangeAddress cellRangeAddress = new CellRangeAddress(curRowIndex - 1, curRowIndex, curColIndex,
                            curColIndex);
                    sheet.addMergedRegion(cellRangeAddress);
                }
            }
        }
    
    }

#### 单元格的横线设置 ####

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

public class FontStyleWriteHandler implements CellWriteHandler {
        
        /**
         * 设置字体样式
         * @param writeSheetHolder
         * @param writeTableHolder Nullable.It is null without using table writes.
         * @param cellDataList     Nullable.It is null in the case of add header.There may be several when fill the data.
         * @param cell
         * @param head             Nullable.It is null in the case of fill data and without head.
         * @param relativeRowIndex Nullable.It is null in the case of fill data.
         * @param isHead           It will always be false when fill data.
         */
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        
            // 创建字体样式
            // 设置内容字体样式
            WriteFont writeFont = new WriteFont();
            writeFont.setFontName("宋体");
            for (int i = 0; i < cellDataList.size(); i++) {
        
                WriteCellData<?> writeCellData = cellDataList.get(i);
                WriteCellStyle writeCellStyle = writeCellData.getWriteCellStyle();
                // 横线的设置
                writeCellStyle.setBorderLeft(THIN);
                writeCellStyle.setBorderRight(THIN);
                writeCellStyle.setBorderTop(THIN);
                writeCellStyle.setBorderBottom(THIN);
                writeCellStyle.setWriteFont(writeFont);
                writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
                writeCellStyle.setShrinkToFit(true);
            }
        }
    
    }

#### 导出字体的样式自定义 ####

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

public class FontStyleWriteHandler implements CellWriteHandler {
        
        /**
         * 设置字体样式
         * @param writeSheetHolder
         * @param writeTableHolder Nullable.It is null without using table writes.
         * @param cellDataList     Nullable.It is null in the case of add header.There may be several when fill the data.
         * @param cell
         * @param head             Nullable.It is null in the case of fill data and without head.
         * @param relativeRowIndex Nullable.It is null in the case of fill data.
         * @param isHead           It will always be false when fill data.
         */
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<WriteCellData<?>> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) {
        
            // 创建字体样式
            // 设置内容字体样式
            WriteFont writeFont = new WriteFont();
            writeFont.setFontName("宋体");
            // 核心设置字体样式
            writeFont.setColor(IndexedColors.RED.getIndex());
            for (int i = 0; i < cellDataList.size(); i++) {
        
                WriteCellData<?> writeCellData = cellDataList.get(i);
                WriteCellStyle writeCellStyle = writeCellData.getWriteCellStyle();
                writeCellStyle.setBorderLeft(THIN);
                writeCellStyle.setBorderRight(THIN);
                writeCellStyle.setBorderTop(THIN);
                writeCellStyle.setBorderBottom(THIN);
                writeCellStyle.setWriteFont(writeFont);
                writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
                writeCellStyle.setShrinkToFit(true);
            }
        }
    
    }

#### 多级标题实现 ####

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

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

[7aea7188edc0434db59a05e358937412.png_pic_center]: https://img-blog.csdnimg.cn/7aea7188edc0434db59a05e358937412.png#pic_center
[75d471fffeb649f18b7dd51528fc3748.png]: https://img-blog.csdnimg.cn/75d471fffeb649f18b7dd51528fc3748.png
[6ec5cf93d19447b8878617fde0512ede.png]: https://img-blog.csdnimg.cn/6ec5cf93d19447b8878617fde0512ede.png
[0e23ea4495ca47239d4561cdd345e75e.png]: https://img-blog.csdnimg.cn/0e23ea4495ca47239d4561cdd345e75e.png
[5a240d620b68491da475638cb807c132.png]: https://img-blog.csdnimg.cn/5a240d620b68491da475638cb807c132.png
[c7c9b0c5ea5e44c5b2ceb1682885f2de.png]: https://img-blog.csdnimg.cn/c7c9b0c5ea5e44c5b2ceb1682885f2de.png
[d981ef85aa90465b95aa464b9f993b23.png]: https://img-blog.csdnimg.cn/d981ef85aa90465b95aa464b9f993b23.png
[e01152ae512445fa995d3ab1aec10808.png]: https://img-blog.csdnimg.cn/e01152ae512445fa995d3ab1aec10808.png
[d797302e7cdd4e239678ff0d063a1d2d.png]: https://img-blog.csdnimg.cn/d797302e7cdd4e239678ff0d063a1d2d.png