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

POI EasyExcel 自定义行列样式 字体样式 宽高 动态头 实时头写入周末好 有什么看不懂 可留言 如果我会的话 我会解答 如果帮助到你了 一键三联呦 爱你 笔芯 EasyExcel 官方文档 效果图 Model History2014E package com proapplysvr model history import java util Date

大家好,我是讯享网,很高兴认识大家。

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

EasyExcel 官方文档:

效果图:
在这里插入图片描述
讯享网

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; } 

注意:

在这里插入图片描述

在这里插入图片描述

讯享网 <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> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml-schemas</artifactId> <version>${poi-ooxml.version}</version> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.poi/poi --> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>${poi-ooxml.version}</version> </dependency> <!-- https://mvnrepository.com/artifact/cglib/cglib-nodep --> <dependency> <groupId>cglib</groupId> <artifactId>cglib-nodep</artifactId> <version>${cglib-nodep.version}</version> <scope>test</scope> </dependency> <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-collections4 --> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-collections4</artifactId> <version>${commons-collections4.version}</version> </dependency> <!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>${alibaba-easyexcel.version}</version> </dependency> <!--map 转为对象的依赖,使用百度一下--> <dependency> <groupId>com.xiaoleilu</groupId> <artifactId>hutool</artifactId> <version>${hutool.version}</version> </dependency> 

另一种标头

在这里插入图片描述

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); } } 

在这里插入图片描述
在这里插入图片描述

在这里插入图片描述

小讯
上一篇 2025-01-29 09:42
下一篇 2025-02-26 08:28

相关推荐

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容,请联系我们,一经查实,本站将立刻删除。
如需转载请保留出处:https://51itzy.com/kjqy/77360.html