一、内容简介
本文主要介绍easyExcel写Excel、填充excel两种方式;
写文件和模板填充方式
二、easyExcel
2.1 简介
alibaba的开源程序,提供excel文件各种操作方式,具有使用方便,省内存的特点
2.2 官方地址
三、配置
pom文件中添加依赖
<!-- easyExcel-->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>3.2.1</version>
<!-- 此处排除部分组件是因为和原有项目产生冲突了,视情况而定-->
<exclusions>
<exclusion>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</exclusion>
</exclusions>
</dependency>
四、写excel
4.1 自定义输出实体类
根据想要输出表的列标题创建实体类;
使用easyExcel提供的注解,设置表头,表内容样式;
- @ColumnWidth(20) 设置列宽;
- @ExcelProperty(value = “单位”,index = 1) 设置列标题,index设置列位置;若不设置按字段先后顺序排列;
- @Data是SpringBoot的注解,用来省略getter,setter方法书写,lombok工具类库提供的啦😁
package com.wms.entity;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.annotation.write.style.*;
import com.alibaba.excel.enums.BooleanEnum;
import com.alibaba.excel.enums.poi.BorderStyleEnum;
import com.alibaba.excel.enums.poi.FillPatternTypeEnum;
import com.alibaba.excel.enums.poi.HorizontalAlignmentEnum;
import com.alibaba.excel.enums.poi.VerticalAlignmentEnum;
import lombok.Data;
import lombok.EqualsAndHashCode;
/**
* @ClassName Inventory
* @Description TODO
* @Author lmx
* @Date 2023/12/12
* @Version 1.0
**/
@Data
@HeadRowHeight(25)
//设置Excel head样式
@HeadStyle(wrapped = BooleanEnum.TRUE,fillPatternType = FillPatternTypeEnum.NO_FILL,
borderTop = BorderStyleEnum.THIN,borderBottom = BorderStyleEnum.THIN,
borderLeft = BorderStyleEnum.THIN,borderRight = BorderStyleEnum.THIN,topBorderColor = 8,
bottomBorderColor = 8,leftBorderColor = 8,rightBorderColor = 8,
verticalAlignment = VerticalAlignmentEnum.CENTER,horizontalAlignment = HorizontalAlignmentEnum.CENTER)
//设置Excel head字体
@HeadFontStyle(fontName = "宋体",fontHeightInPoints = 10,bold = BooleanEnum.FALSE)
//设置Excel默认列宽
@ColumnWidth(12)
//设置Excel 正文行高
@ContentRowHeight(18)
//设置Excel 正文内容样式
@ContentStyle(wrapped = BooleanEnum.TRUE,borderTop = BorderStyleEnum.THIN,borderBottom = BorderStyleEnum.THIN,
borderLeft = BorderStyleEnum.THIN,borderRight = BorderStyleEnum.THIN,topBorderColor = 8,
bottomBorderColor = 8,leftBorderColor = 8,rightBorderColor = 8,
verticalAlignment = VerticalAlignmentEnum.CENTER,horizontalAlignment = HorizontalAlignmentEnum.CENTER)
//设置Excel 正文字体样式
@ContentFontStyle(fontName = "宋体",fontHeightInPoints = 10)
@EqualsAndHashCode(callSuper = false)
public class Inventory {
@ColumnWidth(6)
@ExcelProperty(value = "序号")
private String index;
/**
* 宽度为50
*/
@ColumnWidth(20)
@ExcelProperty(value = "名称及规格")
private String name;
@ColumnWidth(6)
@ExcelProperty(value = "单位")
private String unit;
@ExcelProperty(value = "单价(元)")
private Double price;
@ExcelProperty(value = "截止日账面库存数量")
private Double quantity;
@ExcelProperty(value = "截止日账面金额")
private Double totalPrice;
@ExcelProperty(value = "实际盘点数量")
private Double actualQuantity;
@ExcelProperty(value = "单价(元)")
private Double actualPrice;
@ExcelProperty(value = "实际盘点金额")
private String actualTotalPrice;
@ExcelProperty(value = "盘盈或盘亏金额")
private Double difference;
@ExcelProperty(value = "调整后资产")
private Double adjustProperty;
@ExcelProperty(value = "备注")
private String notes;
}
4.2 输出接口
- 以请求流的形式输出;
- 写多张结构相同的sheet表,给sheet表设置10行默认,不足10行时按照十行输出,以空白行填补;
- 自定义的实体类用于构建sheet表的列标题
Inventory.class
,承载表中数据; - 表头添加重写了 afterSheetCreate方法,添加了自定义合并策略,详情见4.3
@GetMapping("/export")
public void exportSheet(HttpServletResponse response){
LocalDateTime exportTime = LocalDateTime.now();
System.out.println(exportTime);
try{
// 创建生成文件输出位置
// Inventory.class加上之后才会按照定义的实体类形式输出
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(),Inventory.class)
.build();
// 根据科目查询,设置不同的sheet表格
QueryWrapper<Type> queryWrapper = new QueryWrapper();
List<Type> types = typeMapper.selectList(queryWrapper);
// 遍历科目,生成多个sheet表格
int i = 0;
for (Type type : types){
List<GoodsRes> goodsRes = goodsService.inventory(type.getTypeId());
// 具体数据及对应列名
List<Inventory> inventoryList = new ArrayList<>();
// 序列号
int j = 0;
// 总计
Double sum = 0D;
// 表尾插入位置记录
int localIndex = 0;
if (goodsRes.size() <= 10){
// 相关科目数量不够10的时候
for (GoodsRes goods1 : goodsRes){
Inventory inventory = new Inventory();
inventory.setIndex(String.valueOf(++j));
inventory.setName(goods1.getGName());
inventory.setUnit(goods1.getUnit());
inventory.setPrice(goods1.getPrice());
inventory.setQuantity(goods1.getQuantity());
inventory.setTotalPrice(goods1.getPriceTotal());
inventory.setActualPrice(goods1.getPrice());
// 保留两位小数
inventory.setActualTotalPrice(String.format("%.2f",goods1.getPriceTotal()));
inventoryList.add(inventory);
// 记录总数目
sum += goods1.getPriceTotal();
}
int tempSize = 10 - goodsRes.size();
// 填充空白
for (int t = 1; t <= tempSize; t++){
Inventory inventory =new Inventory();
inventory.setIndex(String.valueOf(goodsRes.size() + t));
inventoryList.add(inventory);
}
localIndex = 10;
// sum 为0时,设置为null
if (Math.abs(sum - 0.0) < 1e-6){
sum = null;
}
} else {
// 数量超过10的时候
for (GoodsRes goods1 : goodsRes) {
Inventory inventory = new Inventory();
inventory.setIndex(String.valueOf(++j));
inventory.setName(goods1.getGName());
inventory.setUnit(goods1.getUnit());
inventory.setPrice(goods1.getPrice());
inventory.setQuantity(goods1.getQuantity());
inventory.setTotalPrice(goods1.getPriceTotal());
inventory.setActualPrice(goods1.getPrice());
// inventory.setActualQuantity(goods1.getQuantity());
inventory.setActualTotalPrice(String.format("%.2f", goods1.getPriceTotal()));
inventoryList.add(inventory);
sum += goods1.getPriceTotal();
}
localIndex = goodsRes.size();
}
// 填充表尾
Inventory inventoryTotal = new Inventory();
inventoryTotal.setIndex("合计:");
inventoryTotal.setTotalPrice(sum);
if (sum == null ){
inventoryTotal.setActualTotalPrice("");
}else {
inventoryTotal.setActualTotalPrice(String.format("%.2f",sum));
}
inventoryList.add(inventoryTotal);
Inventory inventoryTail = new Inventory();
inventoryTail.setIndex("参与盘点人员:");
inventoryTail.setActualTotalPrice("盘点日期:");
inventoryList.add(inventoryTail);
inventoryList.add(inventoryTail);
// 单行合并规则
MyMergeStrategy myMergeStrategy = new MyMergeStrategy(
localIndex+5,localIndex+5,0,1);
MyMergeStrategy myMergeStrategy1 = new MyMergeStrategy(
localIndex+6,localIndex+7,0,7);
MyMergeStrategy myMergeStrategy2 = new MyMergeStrategy(
localIndex+6,localIndex+7,8,11);
// 按照合并规则写入数据
WriteSheet writeSheet = EasyExcel.writerSheet(i++,type.getTypeName())
.relativeHeadRowIndex(4)
.registerWriteHandler(new inventorySheetWriteHandler())
.registerWriteHandler(myMergeStrategy)
.registerWriteHandler(myMergeStrategy1)
.registerWriteHandler(myMergeStrategy2)
.build();
excelWriter.write(inventoryList,writeSheet);
}
// 关闭 excelWriter,不然导出数据失效
excelWriter.finish();
response.flushBuffer();
} catch (Exception e){
e.printStackTrace();
}
}
4.3 合并策略
4.3.1 自定义策略添加表头
- 重写afterSheetCreate方法,添加表头;注意:该方法生效于数据填充之前
- CellStyle进行样式设计;Font设计字体;颜色,背景等样式类似;
- sheet.addMergedRegionUnsafe合并单元格;
public class inventorySheetWriteHandler implements SheetWriteHandler {
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
Sheet sheet = writeSheetHolder.getSheet();
// 样式设计
CellStyle cellStyle2 = workbook.createCellStyle();
cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle2.setAlignment(HorizontalAlignment.CENTER);
Font font2 = workbook.createFont();
font2.setFontHeight((short) 220);
font2.setFontName("仿宋体");
cellStyle2.setFont(font2);
// 第一行
Row row1 = sheet.createRow(0);
row1.setHeight((short) 420);
Cell cell = row1.createCell(0);
cell.setCellValue("存货盘点表");
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle.setAlignment(HorizontalAlignment.CENTER);
Font font = workbook.createFont();
font.setBold(true);
font.setFontHeight((short) 400);
font.setFontName("仿宋体");
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
sheet.addMergedRegionUnsafe(new CellRangeAddress(0, 0, 0, 11));
// 第二行
Row row2 = sheet.createRow(1);
row2.setHeight((short) 402);
Cell cell1 = row2.createCell(0);
cell1.setCellValue("单位名称:XXXXXXXXXXXXXX");
CellStyle cellStyle1 = workbook.createCellStyle();
cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
cellStyle1.setAlignment(HorizontalAlignment.LEFT);
Font font1 = workbook.createFont();
font1.setFontHeight((short) 220);
font1.setFontName("仿宋体");
cellStyle1.setFont(font1);
cell1.setCellStyle(cellStyle1);
sheet.addMergedRegionUnsafe(new CellRangeAddress(1, 1, 0, 4));
// 第三行
Row row3 = sheet.createRow(2);
row3.setHeight((short) 402);
Cell cell2 = row3.createCell(0);
cell2.setCellValue("所属仓库分类:" + writeSheetHolder.getSheetName());
cell2.setCellStyle(cellStyle1);
sheet.addMergedRegionUnsafe(new CellRangeAddress(2, 2, 0, 2));
System.out.println(writeSheetHolder.getSheetName());
// 第四行
Row row4 = sheet.createRow(3);
row4.setHeight((short) 402);
Cell cell3 = row4.createCell(0);
cell3.setCellValue("会计期间或截止日: " + LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")));
cell3.setCellStyle(cellStyle1);
sheet.addMergedRegionUnsafe(new CellRangeAddress(3, 3, 0, 3));
4.3.2 自定义策略合并表尾
- 类似于inventorySheetWriteHandler 合并策略,只是传输参数不一样;
- 根据传输的四个参数进行单元格合并;
- 该方法建立在知道需要合并的表尾其实坐标;
public class MyMergeStrategy implements SheetWriteHandler {
/**
* First row
*/
private final int firstRowIndex;
/**
* Last row
*/
private final int lastRowIndex;
/**
* First column
*/
private final int firstColumnIndex;
/**
* Last row
*/
private final int lastColumnIndex;
public MyMergeStrategy(int firstRowIndex, int lastRowIndex, int firstColumnIndex, int lastColumnIndex) {
if (firstRowIndex < 0 || lastRowIndex < 0 || firstColumnIndex < 0 || lastColumnIndex < 0) {
throw new IllegalArgumentException("All parameters must be greater than 0");
}
this.firstRowIndex = firstRowIndex;
this.lastRowIndex = lastRowIndex;
this.firstColumnIndex = firstColumnIndex;
this.lastColumnIndex = lastColumnIndex;
}
public MyMergeStrategy(MyMergeStrategy myMergeStrategy) {
this(myMergeStrategy.getFirstRowIndex(), myMergeStrategy.getLastRowIndex(),
myMergeStrategy.getFirstColumnIndex(), myMergeStrategy.getLastColumnIndex());
}
private int getFirstRowIndex() { return firstRowIndex; }
private int getLastRowIndex(){ return lastRowIndex; }
private int getFirstColumnIndex(){ return firstColumnIndex;}
private int getLastColumnIndex(){ return lastColumnIndex; }
}
4.3.3 前端请求及下载
- 前端采用get方法请求;
- 数据返回形式采用blob形式;
- 虚拟a标签下载excel文件;
//导出盘点表
exportExcel(){
this.$axios.get("/goods/export",{responseType:'blob'}).then(res=>{
const blob = new Blob([res.data],{type:'application/vnd.ms-excel'});
// 通过URL.createObjectURL生成文件路径
const url = window.URL.createObjectURL(blob);
// 创建a标签
const ele = document.createElement('a');
ele.style.display = 'none';
// 设置href属性为文件路径,download属性可以设置文件名称
ele.href = url;
// ele.download = postData.name || '基础分析';
const fileName = decodeURI(res.headers['Content-Disposition']);
// const fileName = "test"
ele.setAttribute('download', fileName);
// 将a标签添加到页面并模拟点击
document.querySelectorAll('body')[0].appendChild(ele);
ele.click();
// 移除a标签
ele.remove();
})
},
五、模板填充
5.1 模板填充变量
- 单个变量 {variable}
- 列表变量 {.variable}
如下图所示:
5.2 模板填充
5.2.1 模板填充主要方法
- 模板填充的主要方法如下,和写方法的主要区别是采用的fill;
- 分为四个步骤:创建excelWriter、创建writerSheet、填充表内容、停止;
// EasyExcel 创建 excelWriter
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(excelTemplatePath).build();
// 创建writerSheet
WriteSheet writeSheet = EasyExcel.writerSheet("XXXXXXXXXXX").build();
// 填充表内容
excelWriter.fill(billTemplates, writeSheet);
// 填充单变量
excelWriter.fill(map, writeSheet);
// 结束
excelWriter.finish();
5.2.2 模板填充方法,全套代码;
- 模板路径采用的绝对路径,还需优化;
- 单变量填充的时候采用Map方法,填充对应变量值;
/**
*
* @author lmx
* @date 14:47 2024/1/5
* @param response
* @param formId
**/
@GetMapping("/expoetExcel")
public void exportExcel(HttpServletResponse response, @RequestParam(value = "formId") Integer formId) throws IOException {
// 日期格式
DateFormat dateFormat = new SimpleDateFormat("yyyy年MM月dd日");
// 模板路径
String excelTemplatePath = "D:\\project\\template\\出库单模板.xlsx";
String excelTemplatePathNew = "D:\\project\\template\\出库单模板_无默认.xlsx";
// 获取主表信息
OutboundMain outboundMain = outboundMainService.getByRecords(formId);
// 获取明细信息
List<OutboundDetail> outboundDetails = outboundDetailService.getByFormId(formId);
// 填充模板结构
List<BillTemplate> billTemplates = new ArrayList<>();
int i = 0;
Double summary = 0D;
// 遍历明细,赋值给模板结构,计算总价
for (OutboundDetail instockDetail : outboundDetails){
BillTemplate billTemplate = new BillTemplate();
billTemplate.setIndex(++i);
billTemplate.setFirstCell("");
billTemplate.setLastCell("");
billTemplate.setName(instockDetail.getGName());
billTemplate.setUnit(instockDetail.getUnit());
billTemplate.setNumber(instockDetail.getQuantity());
billTemplate.setPrice(instockDetail.getPrice());
billTemplate.setTotalPrice(instockDetail.getTotalPrice());
billTemplate.setNotes(instockDetail.getComment());
// 计算总价
summary += instockDetail.getTotalPrice();
// 添加至数据列表
billTemplates.add(billTemplate);
}
// 单变量填充
Map<String, Object> map = new HashMap<>();
map.put("updateTime", outboundMain.getApplicantTime().format(DateTimeFormatter.ofPattern("yyyy年MM月dd日")));
map.put("subject", typeService.getNameById(outboundMain.getKemu().longValue()));
map.put("supplier", depoService.getNameById(outboundMain.getBumen().longValue()));
map.put("recordId", outboundMain.getRecordId());
map.put("applicantName", userService.getNameById(outboundMain.getApplicantId()));
if (outboundMain.getApproverId() != null) {
map.put("approveName", userService.getNameById(outboundMain.getApproverId()));
}
map.put("summary",summary);
// 判断明细数据量大小是否超过默认长度10
if (billTemplates.size() <= 10){
// 小于10使用默认模板,有10行空白
// EasyExcel 创建 excelWriter
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(excelTemplatePath).build();
// 创建writerSheet
WriteSheet writeSheet = EasyExcel.writerSheet("XXXXXXXXXXX").build();
// 填充表内容
excelWriter.fill(billTemplates, writeSheet);
// 填充单变量
excelWriter.fill(map, writeSheet);
// 结束写
excelWriter.finish();
} else {
// 大于10,使用无默认行的模板
// EasyExcel 创建 excelWriter
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(excelTemplatePathNew).build();
// forceNewRow,将数据放入内存
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
// 创建writerSheet
WriteSheet writeSheet = EasyExcel.writerSheet("XXXXXXXXXXXX").build();
// 填充表内容
excelWriter.fill(billTemplates, fillConfig,writeSheet);
// 填充单变量
excelWriter.fill(map, writeSheet);
// 结束写
excelWriter.finish();
}
// 输出流
response.flushBuffer();
}
5.2.3 表尾添加详解
(1) 设计好模板,变量填充
- 填充数据时聚焦新的一行,需要将所有数据放入内存;
- 代码上只需要 将
forceNewRow(Boolean.Ture)
设置好,并且在填充的时候将填充配置加上即可;
// EasyExcel 创建 excelWriter
ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(excelTemplatePathNew).build();
// forceNewRow,将数据放入内存
FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
// 创建writerSheet
WriteSheet writeSheet = EasyExcel.writerSheet("XXXXXXX").build();
// 填充表内容
excelWriter.fill(billTemplates, fillConfig,writeSheet);
// 填充单变量
excelWriter.fill(map, writeSheet);
// 结束写
excelWriter.finish();
(2)表尾采用 write方法写
- 此处采用列表填充数据,指定单元格前面填充null;
- 表尾固定,基本不变化;
// 添加表尾
List<List<String>> tailList = ListUtils.newArrayList();
List<String> tail = ListUtils.newArrayList();
tailList.add(tail);
tail.add(null);
List<String> tailLast = ListUtils.newArrayList();
tailList.add(tailLast);
tailLast.add(null);
tailLast.add(null);
tailLast.add(null);
tailLast.add("审核人:");
tailLast.add(null);
tailLast.add(null);
tailLast.add(null);
tailLast.add(null);
tailLast.add(null);
tailLast.add("制表人:");
excelWriter.write(tailList,writeSheet);
六、自定义策略——最终的解决途径
- 样式设置
- 合并策略
- 打印设置
需要自己写策略,重写afterSheetCreate等方法满足需求;
代码示例,打印设置
public class inventorySheetWriteHandler implements SheetWriteHandler {
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
/*
* 打印设置
*/
//设置将所有列调整为一页
sheet.setFitToPage(true);
//设置打印内容水平居中显示
sheet.setHorizontallyCenter(true);
//设置打印页面边距
sheet.setMargin(Sheet.TopMargin, 0.3);
sheet.setMargin(Sheet.BottomMargin, 0.5);
sheet.setMargin(Sheet.LeftMargin, 0.3);
sheet.setMargin(Sheet.RightMargin, 0.3);
//打印设置对象
PrintSetup print = sheet.getPrintSetup();
//并缩减打印输出只有一页宽
print.setFitHeight((short)0);
//设置竖屏打印(false),横屏打印(true)
print.setLandscape(true);
//设置A4纸打印
print.setPaperSize(PrintSetup.A4_PAPERSIZE);
}
}
无限进步💪