java 导出之excel
Springboot之Excel导出
1.简介
excel导出用的工具是easyExcel,其中导出主要步骤是准备环境，导出模板文件流，封装表头，封装数据list，书写策略改模式。其他看官方文档。
2.环境
//具体哪个真正有用不知道，这三个绝对够了
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>4.1.2</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> <!-- alibaba easyExcel --> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.6</version> </dependency>
3.导出文件流
1 //创建输入流
ByteArrayOutputStream outputStream = new ByteArrayOutputStream(); 2 EasyExcel.write(outputStream, CmXcmzVo.class).excelType(ExcelTypeEnum.XLSX) 3 // 是否自动关流 4 .autoCloseStream(Boolean.FALSE)
// 添加表单的绑定策略 5 .registerWriteHandler(new CmXczwUtils(mergeRowIndex, mergeColumnIndex,MyMergeList))
// 添加的表头 6 .head(sinkRemoveHead(bean)) 7 .sheet("显示的名字")
// 要导出的数据 8 .doWrite(list); 9 HttpHeaders httpHeaders = new HttpHeaders(); 10 String fileName = new String("xcmz.xlsx".getBytes("UTF-8"), "iso-8859-1"); 11 httpHeaders.add("content-disposition", "attachment;filename=" + fileName); 12 httpHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM); 13 ResponseEntity<byte[]> filebyte = new ResponseEntity<byte[]>(outputStream.toByteArray(), httpHeaders, 14 HttpStatus.CREATED);
4.封装表头
//三行表头，表头的样式可调
private List<List<String>> sinkRemoveHead(CmZwkqDto bean) { String biaotou = ""; String erbiaotou=""; SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日"); List<List<String>> list = new ArrayList<List<String>>(); List<String> head0 = new ArrayList<String>(); head0.add(biaotou); head0.add(erbiaotou); head0.add("序号"); List<String> head1 = new ArrayList<String>(); head1.add(biaotou); head1.add(erbiaotou); head1.add("姓名"); list.add(head0); list.add(head1); list.add(head2); return list; }
5.封装数据
按照导出表中对应的VO对象封装list即可
VO对象
@Data
@NoArgsConstructor
@AllArgsConstructor
@Builder
@ContentRowHeight(25) //单元格内容高度
@HeadRowHeight(35) //表头
@ColumnWidth(17) //列宽，以属性为准
//头背景设置成红色 IndexedColors.RED.getIndex()
@HeadStyle(fillPatternType = FillPatternType.SOLID_FOREGROUND, fillForegroundColor = 9)
public class CmXcmzVo {
@ColumnWidth(8) //属性列宽
private String xh;
@ColumnWidth(12)
private String name;
@ColumnWidth(32)
private String zwmc;
@ColumnWidth(12)
private String csny;
}
6.书写策略
/** * * @FileName: ExcelFillCellMergeStrategy.java * * @creator lee * * @date Dec 28, 2020 * * @editor * @Description: * * @version V1.0 * */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.ss.usermodel.*; import org.apache.poi.ss.util.CellRangeAddress; import java.awt.BorderLayout; import java.util.ArrayList; import java.util.List; public class CmXczwUtils implements CellWriteHandler { private int[] mergeColumnIndex; private int mergeRowIndex; private ArrayList<int[]> MyMergeList; public CmXczwUtils() { } // 构造函数当前行和当前列 public CmXczwUtils(int mergeRowIndex, int[] mergeColumnIndex, ArrayList<int[]> MyMergeList) { this.mergeRowIndex = mergeRowIndex; this.mergeColumnIndex = mergeColumnIndex; this.MyMergeList = MyMergeList; } @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) { } @SuppressWarnings("rawtypes") @Override public void afterCellDataConverted(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, CellData cellData, Cell cell, Head head, Integer integer, Boolean aBoolean) { } @SuppressWarnings("rawtypes") @Override public void afterCellDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, List<CellData> 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; } } } // 修改样式表头 if (curRowIndex == 1) { Workbook workbook = writeSheetHolder.getSheet().getWorkbook(); CellStyle cellStyle = workbook.createCellStyle(); // 字体 cellStyle.setAlignment(HorizontalAlignment.LEFT); // 设置垂直对齐的样式为居左对齐; cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setBorderRight(BorderStyle.THIN); cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); cellStyle.setBorderLeft(BorderStyle.THIN); cell.setCellStyle(cellStyle); } if (curRowIndex >= 3) { Workbook workbook = writeSheetHolder.getSheet().getWorkbook(); CellStyle cellStyle = workbook.createCellStyle(); // 字体 cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置垂直对齐的样式为居中对齐; cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cell.setCellStyle(cellStyle); Font cellFont = workbook.createFont(); // 加粗 cellFont.setBold(false); // 设置自动换行 cellStyle.setWrapText(true); } } /** * 当前单元格向上合并 * * @param writeSheetHolder * @param cell 当前单元格 * @param curRowIndex 当前行 * @param curColIndex 当前列 */ 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); } } */ } }
策略可以很灵活的控制导出的excel导出。主要有合并单元格，列宽，换行，单元格边框，颜色和字体。
通过判断可以定向改变内容。例如可以将阿拉伯数字改为中国的大写数字。
样式判断：用行和列做判断时是指这些行列已经渲染，例如当前列= =4，是指渲染第五列触发这个策略，从0列开始
