EasyExcel：
想要简单了解入手的 看官方文档使用说明 · 语雀 ()
这里主要是数据写入excel 并且将单元格合并
第一步 引入EasyExcel依赖
<dependency><groupId>com.alibaba</groupId><artifactId>easyexcel</artifactId><version>2.2.8</version></dependency>
第二步 创建合并单元格坐标类
import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;/*** @author ShawnWang* @create -04-23 11:10*/@Data@AllArgsConstructor@NoArgsConstructorpublic class RowRangeDto {private int start;private int end;}
第三步 工具类
import com.alibaba.excel.metadata.Head;import com.alibaba.excel.write.merge.AbstractMergeStrategy;import com.alibaba.excel.write.metadata.style.WriteCellStyle;import com.alibaba.excel.write.metadata.style.WriteFont;import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;import com.analysis.learning.situation.entity.pojo.ReportCardHisPOJO;import com.analysis.learning.situation.entity.vo.RowRangeDto;import org.apache.poi.ss.usermodel.*;import org.apache.poi.ss.util.CellRangeAddress;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/*** @param* @description: 合并策略和样式* @return*/public class BizMergeStrategy extends AbstractMergeStrategy {private Map<String, List<RowRangeDto>> strategyMap;private Sheet sheet;public BizMergeStrategy(Map<String, List<RowRangeDto>> strategyMap) {this.strategyMap = strategyMap;}@Overrideprotected void merge(Sheet sheet, Cell cell, Head head, Integer integer) {this.sheet = sheet;//如果没有标题，只有表头的话，这里的 cell.getRowIndex() == 1//注意cell.getRowIndex() 从你除表头第一个坐标开始if (cell.getRowIndex() == 4 && cell.getColumnIndex() == 0) {/*** 保证每个cell被合并一次，如果不加上面的判断，因为是一个cell一个cell操作的，* 例如合并A2:A3,当cell为A2时，合并A2,A3，但是当cell为A3时，又是合并A2,A3，* 但此时A2,A3已经是合并的单元格了*/for (Map.Entry<String, List<RowRangeDto>> entry : strategyMap.entrySet()) {Integer columnIndex = Integer.valueOf(entry.getKey());entry.getValue().forEach(rowRange -> {//添加一个合并请求sheet.addMergedRegionUnsafe(new CellRangeAddress(rowRange.getStart(),rowRange.getEnd(), columnIndex, columnIndex));});}}}public static Map<String, List<RowRangeDto>> addAnnualMerStrategy(List<ReportCardHisPOJO> projectDtoList) {Map<String, List<RowRangeDto>> strategyMap = new HashMap<>();ReportCardHisPOJO preUser = null;for (int i = 0; i < projectDtoList.size(); i++) {ReportCardHisPOJO curUser = projectDtoList.get(i);//如果名字一样，将名字合并（真正开发中一般不会通过名字这样字段，而是通过一些关联的唯一值，比如父id）if (preUser != null) {if (curUser.getClassName().equals(preUser.getClassName())){ // 名字相同则合并第一列//这里的index值应该对应merge方法中cell.getRowIndex()-1的值BizMergeStrategy.fillStrategyMap(strategyMap, "0", i+3);}}preUser = curUser;}return strategyMap;}/*** @description: 新增或修改合并策略map* @param strategyMap* @param key* @param index* @return*/private static void fillStrategyMap(Map<String, List<RowRangeDto>> strategyMap, String key, int index){List<RowRangeDto> rowRangeDtoList = strategyMap.get(key) == null ? new ArrayList<>() : strategyMap.get(key);boolean flag = false;for (RowRangeDto dto : rowRangeDtoList) {//分段list中是否有end索引是上一行索引的，如果有，则索引+1if (dto.getEnd() == index) {dto.setEnd(index + 1);flag = true;}}//如果没有，则新增分段if (!flag) {rowRangeDtoList.add(new RowRangeDto(index, index + 1));}strategyMap.put(key, rowRangeDtoList);}/*** @description: 表格样式* @return*/public static HorizontalCellStyleStrategy CellStyleStrategy(){WriteCellStyle headWriteCellStyle = new WriteCellStyle();//设置头字体WriteFont headWriteFont = new WriteFont();headWriteFont.setFontHeightInPoints((short)13);headWriteFont.setBold(true);headWriteCellStyle.setWriteFont(headWriteFont);//设置头居中headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);//内容策略WriteCellStyle contentWriteCellStyle = new WriteCellStyle();contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);//细实线contentWriteCellStyle.setBorderTop(BorderStyle.THIN);contentWriteCellStyle.setBorderRight(BorderStyle.THIN);contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);//设置 水平居中contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);return horizontalCellStyleStrategy;}}
第四步 进行调用
//你的数据list List<ReportCardHisPOJO> pojos = xxxService.xxx(xx, xx);Map<String, List<RowRangeDto>> strategyMap = BizMergeStrategy.addAnnualMerStrategy(pojos);String filename = "D:\\write.xlsx";EasyExcel.write(filename, ReportCardExcel.class).excelType(ExcelTypeEnum.XLSX).head(ReportCardExcel.class)//设置默认样式及写入头信息开始的行数.registerWriteHandler(new BizMergeStrategy(strategyMap)) // 注册合并策略.registerWriteHandler(BizMergeStrategy.CellStyleStrategy()).sheet("测试").doWrite(pojos);
表头如果是定死的就写固定表头，非定死 动态化 此处我是定死的
import com.alibaba.excel.annotation.ExcelIgnore;import com.alibaba.excel.annotation.ExcelProperty;import com.alibaba.excel.annotation.write.style.ColumnWidth;import com.alibaba.excel.annotation.write.style.ContentRowHeight;import com.alibaba.excel.annotation.write.style.HeadRowHeight;import com.alibaba.excel.annotation.write.style.HeadStyle;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import java.io.Serializable;import java.math.BigDecimal;/*** @author ShawnWang* @create -04-23 8:49*/@Data@AllArgsConstructor@NoArgsConstructor@ColumnWidth(20)@HeadRowHeight(30)@ContentRowHeight(20)public class ReportCardExcel implements Serializable {@ExcelProperty(value = {"班级成绩单","班级成绩单","考试名称"}, index = 1)private String examName;@ExcelProperty(value = {"班级成绩单","班级成绩单","班级"}, index = 0)private String className;@ExcelIgnoreprivate Integer rowCount;@ExcelIgnoreprivate String classStyle;@ExcelProperty(value = {"班级成绩单","班级成绩单","总分","赋分成绩"}, index = 2)private BigDecimal weightScore;@ExcelProperty(value = {"班级成绩单","班级成绩单","总分","原始成绩"}, index = 3)private BigDecimal basicScore;@ExcelProperty(value = {"班级成绩单","班级成绩单","总分","校排名"}, index = 4)private Integer schoolRank;@ExcelProperty(value = {"班级成绩单","班级成绩单","总分","市排名"}, index = 5)private Integer totalRank;}
效果如下：
因涉及具体隐私 暂时就截图这些
