Java中Excel处理
本文记录Java中常用的Excel类库,以便查用。
EasyExcel
快速、简单避免OOM的java处理Excel工具。
https://github.com/alibaba/easyexcel
https://mvnrepository.com/artifact/com.alibaba/easyexcel
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>easyexcel</artifactId>
<version>2.2.6</version>
</dependency>
使用
使用基础数据类型
List<String> head = Lists.newArrayList("日期", "价格");
List<List<Object>> data = Lists.newArrayList(
Lists.newArrayList("2020-09-24", 100),
Lists.newArrayList("2020-09-25", 110),
Lists.newArrayList("2020-09-26", 120),
Lists.newArrayList("2020-09-27", 90));
File outFile = new File(FileSystemView.getFileSystemView().getHomeDirectory(), "价格趋势.xlsx");
//写Excel
EasyExcelFactory.write(outFile).sheet().head(head.stream().map(Collections::singletonList).collect(Collectors.toList())).doWrite(data);
//读Excel 同步读取 可以改变ReadListener实现异步读取避免OOM
List<LinkedHashMap<Integer, Object>> mapList = EasyExcel.read(outFile, new SyncReadListener()).doReadAllSync();
//[{0:"2020-08-23 12:07:56",1:"12"},{0:"2020-08-22 12:07:56",1:"41"}]
使用Java实体对象
//定义实体对象
public class OrderDTO {
@ExcelProperty("订单号")
private String orderId;
@ExcelProperty("买家名称")
private String buyerName;
@ExcelProperty("订单总额")
@NumberFormat(",##0.00")
private BigDecimal orderAmount;
@ExcelProperty("创建时间")
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
private Date createTime;
//constructer getter setter 略
}
List<OrderDTO> orderDTOS = Lists.newArrayList(
new OrderDTO("202001010005", "超级买家", new BigDecimal("120019.001"), new Date()),
new OrderDTO("202001010006", "一般买家", new BigDecimal("5421487.45"), new Date()));
File outFile = new File(FileSystemView.getFileSystemView().getHomeDirectory(), "订单数据.xlsx");
//写Excel
EasyExcel.write(outFile).sheet().head(OrderDTO.class).doWrite(orderDTOS);
//读Excel
List<OrderDTO> objects = EasyExcel.read(outFile, OrderDTO.class, new SyncReadListener()).doReadAllSync();
//[{"buyerName":"超级买家","createTime":1598155887000,"orderAmount":120019.0,"orderId":"202001010005"},
//{"buyerName":"一般买家","createTime":1598155887000,"orderAmount":5421487.45,"orderId":"202001010006"}]
说明
EasyExcel是一个快速的能够避免OOM的Excel类库,支持使用基础数据类型直接读写Excel,直接Java对象映射Excel文档读写。支持同步/异步读写。
XXL-TOOL
一个灵活的Java对象和Excel文档相互转换的工具。一行代码完成Java对象和Excel文档之间的转换。同时保证性能和稳定。
https://www.xuxueli.com/xxl-tool/
https://mvnrepository.com/artifact/com.xuxueli/xxl-tool
<dependency>
<groupId>com.xuxueli</groupId>
<artifactId>xxl-tool</artifactId>
<version>1.2.0</version>
</dependency>
使用
//定义Java实体对象
@ExcelSheet(name = "商户列表", headColor = HSSFColor.HSSFColorPredefined.LIGHT_GREEN)
public class ShopDTO {
@ExcelField(name = "商户ID")
private int shopId;
@ExcelField(name = "商户名称")
private String shopName;
//constructer getter setter 略
}
List<ShopDTO> sheet1Data = new ArrayList<>();
sheet1Data.add(new ShopDTO(1, "一号商户"));
sheet1Data.add(new ShopDTO(2, "二号商户"));
//写Excel文件
ExcelTool.exportToFile(Collections.singletonList(sheet1Data), "商户数据.xlsx");
//读Excel文件
List<Object> objects = ExcelTool.importExcel("商户数据.xlsx", ShopDTO.class);
说明
XXL-TOOL中的Excel模块是一个简单的
快速Java对象和Excel文件互相转换工具,使用它必须要定义一个Java实体对象来映射Excel文件,不支持直接使用基础数据类型读写Excel文件,但能够同时导出多个sheet。
Excel模块代码简单,以一个ExcelTool
提供了几个静态方法调用,并提供ExcelSheet
、ExcelField
两个注解来注解Java实体对象信息。
Hutool
针对POI中Excel和Word的封装
https://hutool.cn/docs/#/poi/概述
https://mvnrepository.com/artifact/cn.hutool/hutool-all
https://mvnrepository.com/artifact/cn.hutool/hutool-poi
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.7.13</version>
</dependency>
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-poi</artifactId>
<version>5.7.13</version>
</dependency>
可以单独引入hutool-poi
或引入hutool-all
使用
使用基础数据类型
File outFile = new File(FileSystemView.getFileSystemView().getHomeDirectory(), "价格趋势.xlsx");
List<String> header = Lists.newArrayList("日期", "价格");
List<List<Object>> data = Lists.newArrayList(
Lists.newArrayList("2020-09-24", 100),
Lists.newArrayList("2020-09-25", 110),
Lists.newArrayList("2020-09-26", 120),
Lists.newArrayList("2020-09-27", 90));
ExcelWriter writer = ExcelUtil.getWriter(outFile);
writer.writeHeadRow(header);
writer.write(data);
//设置列宽自适应,必须在写出数据行后调用才有效果
writer.autoSizeColumnAll();
writer.close();
ExcelReader reader = ExcelUtil.getReader(outFile);
List<List<Object>> read = reader.read();
//[[日期, 价格], [2020-09-24, 100], [2020-09-25, 110], [2020-09-26, 120], [2020-09-27, 90]]
List<List<Object>> read1 = reader.read(1);
//[[2020-09-24, 100], [2020-09-25, 110], [2020-09-26, 120], [2020-09-27, 90]]
List<Map<String, Object>> maps = reader.readAll();
//[{日期=2020-09-24, 价格=100}, {日期=2020-09-25, 价格=110}, {日期=2020-09-26, 价格=120}, {日期=2020-09-27, 价格=90}]
使用Java实体对象
说明
Apache POI
Apache POI提供了API给Java程序对Microsoft Office(Excel、WORD、PowerPoint、Visio等)格式档案读和写的功能。
https://mvnrepository.com/artifact/org.apache.poi/poi
https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml
<dependency>
<!--基础的操作 包含HSSFWorkbook-->
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<!--包含XSSFWorkbook和SXSSFWorkbook-->
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
POI中有几个关键的对象:Workbook
、Sheet
、 Row
和Cell
。其中Workbook有3个常用实现:
HSSFWorkbook
:支持Excel2003及以前版本,扩展名是.xls
HSSFWorkbook是最常见的方式,最多支持行数为65535行,超过65535行后会报错
XSSFWorkbook
:支持Excel2007以及更高版本,扩展名是.xlsx
XSSFWorkbook可以突破65535行的限制,但是有OOM问题,原因是创建的Workbook、Sheet、 Row和Cell保存在内存中并没有持久化
SXSSFWorkbook
:支持Excel2007以及更高版本,扩展名是.xlsx
从POI 3.8开始,提供SXSSFWorkbook方式,不会发生OOM问题, 原理是会将数据缓存到硬盘中
使用
List<String> head = new ArrayList<>();
Collections.addAll(head, "日期", "价格");
List<List<Object>> data = new ArrayList<>();
for (int i = 0; i < 10; i++) {
List<Object> row = new ArrayList<>();
row.add(new Date(System.currentTimeMillis() - TimeUnit.DAYS.toMillis(i)));
row.add(new Random().nextInt(100));
data.add(row);
}
SXSSFWorkbook workbook = new SXSSFWorkbook();
SXSSFSheet sheet1 = workbook.createSheet("工作表1");
//写标题行
SXSSFRow headRow = sheet1.createRow(0);
for (int i = 0; i < head.size(); i++) {
headRow.createCell(i).setCellValue(head.get(i));
}
//写数据行
for (int i = 0; i < data.size(); i++) {
SXSSFRow row = sheet1.createRow(i + 1);
for (int j = 0; j < data.get(i).size(); j++) {
Object o = data.get(i).get(j);
if (o instanceof Date) {
row.createCell(j).setCellValue((Date) o);
}
if (o instanceof Integer) {
row.createCell(j).setCellValue((Integer) o);
}
}
}
//自定样式
Font font = workbook.createFont();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
//...
try {
File file = new File(FileSystemView.getFileSystemView().getHomeDirectory(), "价格趋势.xlsx");
workbook.write(new FileOutputStream(file));
} catch (IOException e) {
e.printStackTrace();
}
单元格格式
//设置单元格格式为日期类型
CellStyle dateCellStyle = workbook.createCellStyle();
dateCellStyle.setDataFormat(workbook.getCreationHelper().createDataFormat().getFormat("yyyy-MM-dd"));
//设置单元格格式为百分比
CellStyle percentageCellStyle = workbook.createCellStyle();
percentageCellStyle.setDataFormat(workbook.createDataFormat().getFormat("0.00%"));
列宽
//POI中Sheet列宽是通过字符个数来确定的,列宽单位为一个字符宽度的1/256
//每列可以显示的最大字符数为255
sheet.setColumnWidth(0, 12 * 256);
说明
Apache POI能够很细粒度的操作Excel,可以自定丰富的样式、图表(折线图、饼状图和柱状图)、支持公式等功能,基本上Microsoft Excel软件能做到的POI都能做到。
折线图
List<String> head = new ArrayList<>();
Collections.addAll(head, "日期", "价格", "销量");
List<List<Object>> data = new ArrayList<>();
for (int i = 0; i < 10; i++) {
List<Object> row = new ArrayList<>();
row.add(new Date(System.currentTimeMillis() - TimeUnit.DAYS.toMillis(i)));
row.add(new Random().nextInt(100));
row.add(new Random().nextInt(1000));
data.add(row);
}
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("工作表1");
//写标题行
XSSFRow headRow = sheet.createRow(0);
for (int i = 0; i < head.size(); i++) {
headRow.createCell(i).setCellValue(head.get(i));
}
Font font = workbook.createFont();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setFont(font);
//写数据行
for (int i = 0; i < data.size(); i++) {
XSSFRow row = sheet.createRow(i + 1);
for (int j = 0; j < data.get(i).size(); j++) {
Object o = data.get(i).get(j);
if (o instanceof Date) {
row.createCell(j).setCellValue((Date) o);
}
if (o instanceof Integer) {
row.createCell(j).setCellValue((Integer) o);
}
}
}
XSSFDrawing drawingPatriarch = sheet.createDrawingPatriarch();
//折线图画图区域 两个dx 不知干嘛 row1:图顶部线位置 row2:图底部线位置 col1:图左边线位置 col2:图右边线位置
//col row指的是和这些行列的偏移量 改变列宽 行高 图会保持距离
XSSFChart chart = drawingPatriarch.createChart(drawingPatriarch.createAnchor(0, 0, 0, 0, 4, 1, 14, 24));
//图例
XDDFChartLegend legend = chart.getOrAddLegend();
legend.setPosition(LegendPosition.TOP);
//底部X轴
XDDFCategoryAxis xAxis = chart.createCategoryAxis(AxisPosition.TOP);
//左侧Y轴
XDDFValueAxis yAxis = chart.createValueAxis(AxisPosition.LEFT);
yAxis.setTitle("价格");
yAxis.setCrosses(AxisCrosses.AUTO_ZERO);
//设置y轴主要网格线
XDDFShapeProperties orAddMajorGridProperties = yAxis.getOrAddMajorGridProperties();
//刻度线标记 内部
yAxis.setMajorTickMark(AxisTickMark.IN);
//折线图
XDDFLineChartData lineChartData = (XDDFLineChartData) chart.createData(ChartTypes.LINE, xAxis, yAxis);
//x轴标题 取第一列数据
XDDFDataSource<String> xTitleDataSource = XDDFDataSourcesFactory.fromStringCellRange(sheet, new CellRangeAddress(1, data.size(), 0, 0));
//折线图
for (int i = 1; i < head.size(); i++) {
XDDFNumericalDataSource<Double> yDataSource = XDDFDataSourcesFactory.fromNumericCellRange(sheet, new CellRangeAddress(1, data.size(), i, i));
XDDFLineChartData.Series series = (XDDFLineChartData.Series) lineChartData.addSeries(xTitleDataSource, yDataSource);
series.setTitle(head.get(i), null);
series.setSmooth(false);
series.setMarkerStyle(MarkerStyle.NONE);
}
chart.plot(lineChartData);
try {
File file = new File(FileSystemView.getFileSystemView().getHomeDirectory(), "价格趋势.xlsx");
workbook.write(new FileOutputStream(file));
} catch (IOException e) {
e.printStackTrace();
}
高度封装
/**
* 面向无实体的Excel处理
* 目标:封装 一个方法调用就能实现
*
* @author zhanjixun
* @date 2021-08-05 11:37:34
*/
@UtilityClass
public class FastExcel {
/**
* 并行流遍历某列的所有行
*
* @param fileName
* @param rowNo
* @param consumer
*/
public void parallelEachLineOnGivenRow(String fileName, int rowNo, Consumer<Object> consumer) {
File outFile = new File(FileSystemView.getFileSystemView().getHomeDirectory(), fileName);
List<LinkedHashMap<Integer, Object>> mapList = EasyExcel.read(outFile, new SyncReadListener()).doReadAllSync();
mapList.parallelStream().map(m -> m.get(rowNo)).forEach(consumer);
}
}