概述
业务开发过程中,经常会有导出报表的需求,一般情况下以Excel文本形式。Java里有很多工具支持这一功能。
POI
Apache POI,提供对Microsoft Office格式文档的读和写功能。不过实际工作中,大多数场景只是利用POI来操作Excel,甚至只用xls一种格式:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
</dependency>
扩展——其他 artifactId:
Maven artifactId | Prerequisites |
---|---|
poi | |
poi-scratchpad | poi |
poi-ooxml | poi, poi-ooxml-schemas |
poi-ooxml-schemas | xmlbeans |
poi-examples | poi, poi-scratchpad, poi-ooxml |
ooxml-schemas | xmlbeans |
maven artifactId的功能与定位,方便只引入必要的GAV: | |
Component | Application type |
– | – |
POIFS | OLE2 Filesystem |
HPSF | OLE2 Property Sets |
HSSF | Excel XLS |
HSLF | PowerPoint PPT |
HWPF | Word DOC |
HDGF | Visio VSD |
HPBF | Publisher PUB |
HSMF | Outlook MSG |
OpenXML4J | OOXML |
XSSF | Excel XLSX |
XSLF | PowerPoint PPTX |
XWPF | Word DOCX |
Common SS | Excel XLS and XLSX |
当只要使用xls格式时、只要导入poi即可;
当还要使用xlsx格式、还要导入poi-ooxml;
当需要操作word、ppt、viso、outlook等时需要用到poi-scratchpad。
简介
API:
- HSSF - 提供读写Microsoft Excel格式档案的功能。
- XSSF - 提供读写Microsoft Excel OOXML格式档案的功能。
- HWPF - 提供读写Microsoft Word格式档案的功能。
- HSLF - 提供读写Microsoft PowerPoint格式档案的功能。
- HDGF - 提供读写Microsoft Visio格式档案的功能。
HSSF
用来操作Office 2007版本前excel.xls文件,XSSF用来操作Office 2007版本后的excel.xlsx文件。HSSF在org.apache.poi.hssf.usermodel包中,实现Workbook 接口,常用组件:
HSSFWorkbook excel的文档对象
HSSFSheet excel的表单
HSSFRow excel的行
HSSFCell excel的格子单元
HSSFFont excel字体
HSSFDataFormat 日期格式
HSSFHeader sheet头
HSSFFooter sheet尾(只有打印的时候才能看到效果)
HSSFCellStyle cell样式
HSSFDateUtil 日期
HSSFPrintSetup 打印
HSSFErrorConstants 错误信息表
XSSF
在org.apache.xssf.usemodel包,实现Workbook接口,常用组件:
XSSFWorkbook excel的文档对象
XSSFSheet excel的表单
XSSFRow excel的行
XSSFCell excel的格子单元
XSSFFont excel字体
XSSFDataFormat 日期格式
和HSSF类似;
入门
导出Excel
一般来说,导出Excel可以分为如下七个步骤,其他设置单元格数据格式、单元格背景、单元格宽度等略去:
// 1.创建Excel文档
HSSFWorkbook workbook = new HSSFWorkbook();
// 2.设置文档的基本信息(可选)
// 获取文档信息,并配置
DocumentSummaryInformation dsi = workbook.getDocumentSummaryInformation();
// 文档类别
dsi.setCategory("员工信息");
// 设置文档管理员
dsi.setManager("win10");
// 设置组织机构
dsi.setCompany("XXX集团");
// 获取摘要信息并配置
SummaryInformation si = workbook.getSummaryInformation();
// 设置文档主题
si.setSubject("员工信息表");
// 设置文档标题
si.setTitle("员工信息");
// 设置文档作者
si.setAuthor("XXX集团");
// 设置文档备注
si.setComments("备注信息暂无");
// 3.创建一个Excel表单,参数为sheet的名字
HSSFSheet sheet = workbook.createSheet("XXX集团员工信息表");
// 4.创建一行
HSSFRow headerRow = sheet.createRow(0);//0表示第一行
// 5.在第一行中创建第一个单元格,并设置数据
HSSFCell cell0 = headerRow.createCell(0);
cell0.setCellValue("编号");
// 6.将Excel写到ByteArrayOutputStream中
baos = new ByteArrayOutputStream();
workbook.write(baos);
// 7.创建ResponseEntity并返回
return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.CREATED);
导入Excel
主要涉及三个步骤:
- 文件上传
- Excel解析
- 数据插入
文件上传可以采用ElementUI中的Upload控件;正在上传时,文件上传控件不可用,上传成功或者失败之后才可用,上传过程中,上传按钮会有loading显示。然后在SpringMVC中接收上传文件即可:
@RequestMapping(value = "/importEmp", method = RequestMethod.POST)
public RespBean importEmp(MultipartFile file) {
}
Excel解析
将上传到的MultipartFile转为输入流,然后交给POI去解析即可。可以分为如下四个步骤:
// 1.创建HSSFWorkbook对象
HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
// 2.获取一共有多少sheet,遍历
int numberOfSheets = workbook.getNumberOfSheets();
for (int i = 0; i < numberOfSheets; i++) {
HSSFSheet sheet = workbook.getSheetAt(i);
}
// 3.获取sheet中一共有多少行,遍历行(第一行是标题)
int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
Employee employee;
for (int j = 0; j < physicalNumberOfRows; j++) {
if (j == 0) {
continue;//标题行
}
}
// 4.获取每一行有多少单元格,遍历单元格
int physicalNumberOfCells = row.getPhysicalNumberOfCells();
employee = new Employee();
for (int k = 0; k < physicalNumberOfCells; k++) {
HSSFCell cell = row.getCell(k);
}
其他补充
合并单元格:HSSFSheet.addMergedRegion()
public int addMergedRegion(CellRangeAddress region)
参数CellRangeAddress 表示合并的区域,构造方法如下(起始行,截至行,起始列, 截至列):
CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
EasyExcel
EasyExcel 是一个用来对 Java 进行解析、生成 Excel 的框架,重写 poi 对 07 版 Excel 的解析,原本一个 3M 的 Excel 用 POI sax 需要 100M 左右内存,EasyExcel 可降低到 MB 级别,再大的 excel 也不会出现内存溢出的情况。03 版依赖 POI 的 sax 模式。在上层做模型转换的封装,让使用者更加简单方便。
easyexcel-GitHub,阿里开源,专注于Excel,不考虑其他office文档类型。
实战
@Data
public class User {
@ExcelProperty("姓名")
private String name;
@ExcelProperty(index = 1)
private Integer age;
}
@ExcelProperty 注解,使用 index 属性 (从0开始,代表第一列),同时支持以「列名」name 的方式匹配,上面是示例,不建议index和name混用。两者有何区别:
- 如果读取的 Excel 模板信息列固定,这里建议以 index 的形式使用,因为如果用名字去匹配,名字重复,会导致只有一个字段读取到数据,所以 index 是更稳妥的方式
- 如果 Excel 模板的列 index 经常有变化,那还是选择 name 方式比较好,不用经常性修改实体的注解 index 数值
自定义数据映射
EasyExcel支持自定义 converter,将excel内容转换为程序需要的信息:
public class GenderConverter implements Converter<Integer> {
public static final String MALE = "男";
public static final String FEMALE = "女";
@Override
public Class supportJavaTypeKey() {
return Integer.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public Integer convertToJavaData(CellData cellData, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
String stringValue = cellData.getStringValue();
if (MALE.equals(stringValue)){
return 1;
}else {
return 2;
}
}
@Override
public CellData convertToExcelData(Integer integer, ExcelContentProperty excelContentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return null;
}
}
通过注解ExcelProperty关联自定义converter:
@ExcelProperty(index = 2, converter = GenderConverter.class)
private Integer gender;
还是太麻烦,对比EasyPoi,简直弱爆。
日期信息转换
通过@DateTimeFormat注解进行日期格式化
@ExcelProperty(index = 3)
@DateTimeFormat("yyyy-MM-dd HH:mm:ss")
private String birth;
读写
@Test
public void testReadExcel() {
// sheet方法可指定sheetNo,默认第一个sheet的信息
EasyExcel.read("aa.xlsx", User.class, new UserExcelListener()).sheet().read();
}
@Slf4j
public class UserExcelListener extends AnalysisEventListener<User> {
/**
* 批处理阈值
*/
private static final int BATCH_COUNT = 2;
List<User> list = new ArrayList<User>(BATCH_COUNT);
@Override
public void invoke(User user, AnalysisContext analysisContext) {
log.info("解析到一条数据:{}", JSON.toJSONString(user));
list.add(user);
if (list.size() >= BATCH_COUNT) {
saveData();
list.clear();
}
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
saveData();
log.info("所有数据解析完成!");
}
private void saveData(){
log.info("{}条数据,开始存储数据库!", list.size());
log.info("存储数据库成功!");
}
}
EasyPoi
由于内容太多,另起一篇:EasyPoi使用记录
hutool
hutool简介
hutool是国人开源的工具类库,在其他开源类库的基础之上的二次封装,使开发专注于业务,最大限度的避免封装不完善带来的bug。
官网
GitHub
<dependency>
<groupId>cn.hutool</groupId>
<artifactId>hutool-all</artifactId>
<version>5.0.7</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.1</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>3.17</version>
</dependency>
Controller:
@RequestMapping("/export")
@ResponseBody
public void export(HttpServletResponse response) {
List<User> list = new ArrayList<>();
list.add(new User("excel", "16"));
// 通过工具类创建writer,默认创建xls格式
ExcelWriter writer = ExcelUtil.getWriter();
//自定义标题别名
writer.addHeaderAlias("name", "姓名");
writer.addHeaderAlias("age", "年龄");
// 合并单元格后的标题行,使用默认标题样式
writer.merge(2, "人员信息");
// 一次性写出内容,使用默认样式,强制输出标题
writer.write(list, true);
//out为OutputStream,需要写出到的目标流
response.setContentType("application/vnd.ms-excel;charset=utf-8");
//test.xls是弹出下载对话框的文件名,不能为中文,中文请自行编码
String name = StringUtils.toUtf8String("导出excel");
response.setHeader("Content-Disposition", "attachment;filename=" + name + ".xls");
ServletOutputStream out = null;
try {
out = response.getOutputStream();
writer.flush(out, true);
} catch (IOException e) {
} finally {
writer.close();
}
IoUtil.close(out);
}
选择
选择哪一个工具,常规的Excel(如数据量并不多),poi即可满足需求。如果是几十上百M的文本。则需要慎重选择。或者从业务场景里解决,把数据拆分一下,导出为多个Excel文件。
easyexcel官方宣称性能无敌(64M内存1分钟内读取75M(46W行25列)的Excel),暂未对比测试。
todo