SpringBoot学习⼩结之EasyExcel
前⾔
java处理excel表格常⽤的框架库有: jxl，POI, EasyExcel，EasyPoi, hutool。
, 韩国⼈开发的⼀套解析excel Java库，不⽀持xlsx。09年后⼀直没有更新，功能⽐较少。
，apache提供的⼀套java解析office⼯具，包含excel。我之前有⼀篇介绍了⼀些POI中关于Excel API使⽤，阿⾥开源的JAVA解析Excel⼯具，对于POI解析耗内存进⾏了优化
，对POI进⾏封装的⼀套⼯具库，简化POI代码编写
，国⼈开发的⼯具库，其中包含excel的读写
最主要还是POI, 后三个都依赖于它。由于⼯作中项⽬重点使⽤阿⾥的EasyExcel，以下重点介绍EasyExcel
⼀、EasyExcel
以⼀个进销存表格为例⼦，演⽰EasyExcel如何导⼊导出Excel。下⾯是Excel模板实体，需要先导⼊pom
依赖
@Data
public class InventoryItem {
@ExcelIgnore
private Integer id;
@ExcelProperty(index =0, value ={"序号","序号"})
private String no;
@ExcelProperty(index =1, value ={"商品名称","商品名称"})
private String productName;
@ExcelProperty(index =2, value ={"规格","规格"})
private String spec;
@ExcelProperty(index =3, value ={"单位","单位"})
private String unit;
@ExcelProperty(index =4, value ={"上⽉结存","数量"})
private String lastMonthStoreNum;
@ExcelProperty(index =5, value ={"上⽉结存","单价"})
private String lastMonthStoreUnitPrice;
@ExcelProperty(index =6, value ={"上⽉结存","⾦额"})
private String lastMonthStoreMount;
@ExcelProperty(index =7, value ={"本⽉购⼊","数量"})
private String thisMonthPurchaseNum;
@ExcelProperty(index =8, value ={"本⽉购⼊","单价"})
private String thisMonthPurchaseUnitPrice;
@ExcelProperty(index =9, value ={"本⽉购⼊","⾦额"})
private String thisMonthPurchaseMount;
@ExcelProperty(index =10, value ={"本⽉发出","数量"})
private String thisMonthSendNum;
@ExcelProperty(index =11, value ={"本⽉发出","单价"})
private String thisMonthSendUnitPrice;
@ExcelProperty(index =12, value ={"本⽉发出","⾦额"})
private String thisMontSendhMount;
@ExcelProperty(index =13, value ={"本⽉结存","数量"})
private String thisMonthStoreNum;
@ExcelProperty(index =14, value ={"本⽉结存","单价"})
private String thisMonthStoreUnitPrice;
@ExcelProperty(index =15, value ={"本⽉结存","⾦额"})
private String thisMonthStoreMount;
@ExcelProperty(index =16, value ={"⽉末盘点数量","⽉末盘点数量"}) private String endOfMonthCheckNum;
@ExcelProperty(index =17, value ={"差额","差额"})
private String diff;
}
1.1 pom依赖
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.3</version>
</dependency>
1.2 导⼊
读取Excel需要设置
public class InventoryItemAnalysis extends AnalysisEventListener<InventoryItem>{
List<InventoryItem> list =new ArrayList<>();
@Override
public void invoke(InventoryItem data, AnalysisContext context){
list.add(data);
}
@Override
public void doAfterAllAnalysed(AnalysisContext context){
}
public List<InventoryItem>getList(){
return list;
}
public void setOtnList(List<InventoryItem> list){
this.list = list;
}
}
本地环境可以根据⽂件路径读取
InventoryItemAnalysis analysis =new InventoryItemAnalysis();
System.out.List());
在web环境下可以⽤流读取
@PostMapping("/upload")
@ResponseBody
public Object excelImport(@RequestParam("uploadFile") MultipartFile file
)throws Exception {
InventoryItemAnalysis analysis =new InventoryItemAnalysis();
List();
}
1.3 导出
本地导出
EasyExcel.write("C:\\Users\\root\\Desktop\\进销存导出.xlsx", InventoryItem.class).sheet().List());
web导出
@GetMapping("/download")
public void download(HttpServletResponse response){
String fileName ="进销存数据.xlsx";
List<InventoryItem> list =new ArrayList<>();
setResponseHeader(response, fileName);
try{
EasyExcel.OutputStream(), InventoryItem.class)
.sheet("总表").doWrite(list);
}catch(IOException e){
e.printStackTrace();
}
}
private void setResponseHeader(HttpServletResponse response, String fileName){
try{
response.setContentType("application/octet-stream;charset=ISO8859-1");
response.setHeader("Content-Disposition","attachment;filename="+ de(fileName,"UTF-8"));
response.addHeader("Pargam","no-cache");
response.addHeader("Cache-Control","no-cache");
}catch(Exception ex){
ex.printStackTrace();
}
}
1.4 ⾃定义样式或内容
EasyExcel默认样式不美观，需要⾃定义样式。EasyExcel提供⼀个类HorizontalCellStyleStrategy，可⽤来配置⼀些表头和内容样式。
public static WriteHandler headStyle(){
// 头部样式策略
WriteCellStyle headWriteCellStyle =new WriteCellStyle();
headWriteCellStyle.setFillForegroundColor(Index());
WriteFont headWriteFont =new WriteFont();
headWriteFont.setFontHeightInPoints((short)12);
headWriteFont.setBold(false);
headWriteCellStyle.setWriteFont(headWriteFont);
// 内容样式策略
WriteCellStyle contentWriteCellStyle =new WriteCellStyle();
WriteCellStyle contentWriteCellStyle =new WriteCellStyle();
contentWriteCellStyle.setWrapped(true);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 设置内容边框样式
contentWriteCellStyle.setBorderLeft(THIN);
contentWriteCellStyle.setBorderTop(THIN);
contentWriteCellStyle.setBorderRight(THIN);
contentWriteCellStyle.setBorderBottom(THIN);
HorizontalCellStyleStrategy horizontalCellStyleStrategy =new HorizontalCellStyleStrategy(headWriteCellStyle,
contentWriteCellStyle);
return horizontalCellStyleStrategy;
}
更细粒度控制可以通过实现l.write.handler.WriteHandler的四个⼦接⼝来⾃定义样式或者内容。注意：多个Handler如果作⽤在同⼀个单元格，后⾯会覆盖前⾯的样式。
l.write.handler.CellWriteHandler单元格处理器
beforeCellCreate
afterCellCreate
afterCellDataConverted ⼀个单元格数据转换后
afterCellDispose⼀个单元格所有操作完成后调⽤这个⽅法
l.write.handler.RowWriteHandler ⾏处理器
beforeRowCreate
afterRowCreate
afterRowDispose ⼀⾏所有操作完成后调⽤这个⽅法
l.write.handler.SheetWriteHandler Sheet处理器
beforeSheetCreateSheet
afterSheetCreateSheet
l.write.handler.WorkbookWriteHandler ⼯作簿处理器
beforeWorkbookCreate
afterWorkbookCreate
afterWorkbookDispose ⼯作簿所有操作完成后调⽤这个⽅法
⾃定义案例
案例1：在表格表头前添加标题等信息
public class CustomWriteHandler implements SheetWriteHandler {
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder){
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder){
Workbook workbook = Workbook();
Sheet sheet = SheetAt(0);
//设置标题
Row row1 = ateRow(0);
row1.setHeight((short)800);
Cell cell1 = ateCell(0);
cell1.setCellValue("某某公司进销存报表");
CellStyle cellStyle = ateCellStyle();
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
springboot框架的作用

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。