周末好 有什么看不懂 可留言 如果我会的话 我会解答 如果帮助到你了 一键三联呦 爱你 笔芯
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); } }



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