POI EasyExcel 自定义行列样式字体样式宽高动态头实时头写入

雨点打透心脏的1/2处 2022-12-08 15:31 242阅读 0赞

### 周末好 有什么看不懂 可留言 如果我会的话 我会解答 如果帮助到你了 一键三联呦 爱你 笔芯 ###

[ EasyExcel 官方文档：][EasyExcel]

效果图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjYzMzEzMQ_size_16_color_FFFFFF_t_70_pic_center]

### Model : History2014ExportModel ###

package com.proapplysvr.model.history;
    
    import java.util.Date;
    
    import com.alibaba.excel.annotation.ExcelIgnore;
    import com.alibaba.excel.annotation.ExcelProperty;
    import com.alibaba.excel.metadata.BaseRowModel;
    
    //@Data
    public class History2014ExportModel { 
    
        @ExcelProperty(value = "xx1", index = 0)
        private String PERSON_JSZG_XZG;
    
        @ExcelProperty(value = "xx2", index = 1)
        private String PERSON_GWMC;
    
        @ExcelProperty(value = "xx3", index = 2)
        private String sbdw;
    
        public String getPERSON_JSZG_XZG() { 
            return PERSON_JSZG_XZG;
        }
    
        public void setPERSON_JSZG_XZG(String PERSON_JSZG_XZG) { 
            this.PERSON_JSZG_XZG = PERSON_JSZG_XZG;
        }
    
        public String getPERSON_GWMC() { 
            return PERSON_GWMC;
        }
    
        public void setPERSON_GWMC(String PERSON_GWMC) { 
            this.PERSON_GWMC = PERSON_GWMC;
        }
    
        public String getSbdw() { 
            return sbdw;
        }
    
        public void setSbdw(String sbdw) { 
            this.sbdw = sbdw;
        }
    }

### 主要设计样式代码 History2014ExportCellWriteHandler ###

package com.proapplysvr.model.history;
    
    import com.alibaba.excel.metadata.CellData;
    import com.alibaba.excel.metadata.Head;
    import com.alibaba.excel.write.handler.CellWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.ss.usermodel.*;
    
    import javax.swing.*;
    import java.util.List;
    
    /** * @description: * @author: maofangfang * @time: 2020/9/20 9:54 */
    public class History2014ExportCellWriteHandler implements CellWriteHandler { 
        @Override
        public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { 
    
        }
    
        @Override
        public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { 
    
        }
    
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { 
            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
            Font font = workbook.createFont();
            if (cell.getRowIndex() == 0) { 
                font.setFontHeightInPoints((short) 18);
                font.setFontName("宋体");
                font.setBold(true);
                cellStyle.setFont(font);
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                //设置 自动换行
                cellStyle.setWrapText(true);
                Row row = cell.getRow();
                row.setHeightInPoints(30);
            }
            if (cell.getRowIndex() == 1) { 
                font.setFontHeightInPoints((short) 11);
                font.setFontName("宋体");
                font.setBold(true);
                cellStyle.setFont(font);
                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                //设置 自动换行
                cellStyle.setWrapText(true);
                Row row = cell.getRow();
                row.setHeightInPoints(26);
            }
            if (cell.getRowIndex() == 2) { 
                //设置字体大小
                font.setFontHeightInPoints((short) 11);
                //设置字体样式
                font.setFontName("宋体");
                //设置加粗
                font.setBold(true);
                //设置字体样式
                cellStyle.setFont(font);
                //设置 文字左右居中 【水平居中需要使用以下两行】
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                //设置文字居中 上下居中
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                //设置 自动换行
                cellStyle.setWrapText(true);
                //获取行
                Row row = cell.getRow();
                //设计行高
                row.setHeightInPoints(31);
    
            }
            cell.setCellStyle(cellStyle);
    
        }
    }

public void exportExcel(Map<String, Object> map, HttpServletResponse response) throws IOException { 
            Map<String, Object> params = this.params(map);
            //参数
            Map<String, Object> paramMap = (Map<String, Object>) params.get("paramMap");
            //结果集 
            List<Map<String, Object>> list = oracle2014Mapper.selectList(paramMap);
    
            // 头的策略
            WriteCellStyle headWriteCellStyle = new WriteCellStyle();
            // 内容的策略
            WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
            // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
            HorizontalCellStyleStrategy horizontalCellStyleStrategy =
                    new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            List<History2014ExportModel> personnelPunchModels = list.stream().map(map1 -> BeanUtil.mapToBean(map1, History2014ExportModel.class)).collect(Collectors.toList());
            EasyExcel.write(response.getOutputStream(), History2014ExportModel.class).registerWriteHandler(horizontalCellStyleStrategy).registerWriteHandler(new History2014ExportCellWriteHandler()).head(head()).sheet("模板").doWrite(personnelPunchModels);
    
        }
    
    
        private List<List<String>> head() { 
            List<List<String>> list = new ArrayList<List<String>>();
            List<String> head0 = new ArrayList<String>();
            head0.add("标题");
            head0.add("二标题");
            head0.add("xx");
            List<String> head1 = new ArrayList<String>();
            head1.add("标题");
            head1.add("二标题");
            head1.add("xx2");
            List<String> head2 = new ArrayList<String>();
            head2.add("标题");
            head2.add("二标题");
            head2.add("xx3");
            list.add(head0);
            list.add(head1);
            list.add(head2);
            return list;
        }

### 注意： ###

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjYzMzEzMQ_size_16_color_FFFFFF_t_70_pic_center 1]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjYzMzEzMQ_size_16_color_FFFFFF_t_70_pic_center 2]

<poi-ooxml.version>3.17</poi-ooxml.version>
            <cglib-nodep.version>2.2</cglib-nodep.version>
            <commons-collections4.version>4.1</commons-collections4.version>
            <alibaba-easyexcel.version>2.1.6</alibaba-easyexcel.version>
            <hutool.version>2.16.0</hutool.version>
     <!
     -- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
                <dependency>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml</artifactId>
                    <version>${poi-ooxml.version}</version>
                </dependency>
    
                
                <dependency>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi-ooxml-schemas</artifactId>
                    <version>${poi-ooxml.version}</version>
                </dependency>
    
                
                <dependency>
                    <groupId>org.apache.poi</groupId>
                    <artifactId>poi</artifactId>
                    <version>${poi-ooxml.version}</version>
                </dependency>
    
                
                <dependency>
                    <groupId>cglib</groupId>
                    <artifactId>cglib-nodep</artifactId>
                    <version>${cglib-nodep.version}</version>
                    <scope>test</scope>
                </dependency>
    
                
                <dependency>
                    <groupId>org.apache.commons</groupId>
                    <artifactId>commons-collections4</artifactId>
                    <version>${commons-collections4.version}</version>
                </dependency>
    
                
                <dependency>
                    <groupId>com.alibaba</groupId>
                    <artifactId>easyexcel</artifactId>
                    <version>${alibaba-easyexcel.version}</version>
                </dependency>
    
                <dependency>
                    <groupId>com.xiaoleilu</groupId>
                    <artifactId>hutool</artifactId>
                    <version>${hutool.version}</version>
                </dependency>

### 另一种标头 ###

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

package com.proapplysvr.model.history;
    
    import com.alibaba.excel.metadata.CellData;
    import com.alibaba.excel.metadata.Head;
    import com.alibaba.excel.write.handler.CellWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteTableHolder;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.ss.usermodel.*;
    
    import javax.swing.*;
    import java.util.List;
    
    /** * @description: * @author: maofangfang * @time: 2020/9/20 9:54 */
    public class History2014ExportCellWriteHandler implements CellWriteHandler { 
    
    
        @Override
        public void beforeCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Head head, Integer columnIndex, Integer relativeRowIndex, Boolean isHead) { 
    
        }
    
        @Override
        public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { 
    
        }
    
        @Override
        public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> cellDataList, Cell cell, Head head, Integer relativeRowIndex, Boolean isHead) { 
            Workbook workbook = writeSheetHolder.getSheet().getWorkbook();
            CellStyle cellStyle = workbook.createCellStyle();
    
            Font font = workbook.createFont();
            if (cell.getRowIndex() == 0) { 
                font.setFontHeightInPoints((short) 18);
                font.setFontName("宋体");
                font.setBold(true);
                cellStyle.setFont(font);
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                //设置 自动换行
                cellStyle.setWrapText(true);
                Row row = cell.getRow();
                row.setHeightInPoints(30);
            } else if (cell.getRowIndex() == 1) { 
                font.setFontHeightInPoints((short) 11);
                font.setFontName("宋体");
                font.setBold(true);
                cellStyle.setFont(font);
                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                //设置 自动换行
                cellStyle.setWrapText(true);
    
                Row row = cell.getRow();
                row.setHeightInPoints(26);
            } else if (cell.getRowIndex() == 2) { 
                //设置边框
                cellStyle.setBorderBottom(BorderStyle.THIN);
                cellStyle.setBorderLeft(BorderStyle.THIN);
                cellStyle.setBorderRight(BorderStyle.THIN);
                cellStyle.setBorderTop(BorderStyle.MEDIUM);
                //设置字体大小
                font.setFontHeightInPoints((short) 11);
                //设置字体样式
                font.setFontName("宋体");
                //设置加粗
                font.setBold(true);
                //设置字体样式
                cellStyle.setFont(font);
                //设置 文字左右居中 【水平居中需要使用以下两行】
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                //设置文字居中 上下居中
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                //设置 自动换行
                cellStyle.setWrapText(true);
                //获取行
                Row row = cell.getRow();
                //设计行高
                row.setHeightInPoints(31);
    
            } else { 
                //设置边框
                cellStyle.setBorderBottom(BorderStyle.THIN);
                cellStyle.setBorderLeft(BorderStyle.THIN);
                cellStyle.setBorderRight(BorderStyle.THIN);
                cellStyle.setBorderTop(BorderStyle.THIN);
                //居中
                cellStyle.setAlignment(HorizontalAlignment.CENTER);
                cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                Row row = cell.getRow();
                //设计行高
                row.setHeightInPoints(31);
            }
            cell.setCellStyle(cellStyle);
        }
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjYzMzEzMQ_size_16_color_FFFFFF_t_70_pic_center 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjYzMzEzMQ_size_16_color_FFFFFF_t_70_pic_center 4]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjYzMzEzMQ_size_16_color_FFFFFF_t_70_pic_center 5]

[EasyExcel]: https://www.yuque.com/easyexcel/doc/write#b4b9de00
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjYzMzEzMQ_size_16_color_FFFFFF_t_70_pic_center]: /images/20221123/9f4fd4a0bb1443109eec26e2369ca042.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjYzMzEzMQ_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221123/153714c16fc144f3a1481ffa890be50e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjYzMzEzMQ_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221123/ec0f4499f7ca4fc1905dcd0dd4567728.png
[20201024093209380.png_pic_center]: /images/20221123/9068672b537946db93c24107898dc2b9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjYzMzEzMQ_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221123/884dafe244b949ed8f130d1df453e7e6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjYzMzEzMQ_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221123/ec9d9f5d4eb54761a97a3e69c8b10f1d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MjYzMzEzMQ_size_16_color_FFFFFF_t_70_pic_center 5]: /images/20221123/cdd3c964782046ceb44862e86fe87b52.png