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列开始