一、介绍
在平时的业务系统开发中,少不了需要用到导出、导入excel功能,今天我们就一起来总结一下,如果你正为此需求感到困恼,那么阅读完本文,你一定有所收获!
二、poi
大概在很久很久以前,微软的电子表格软件 Excel 以操作简单、存储数据直观方便,还支持打印报表,在诞生之初,可谓深得办公室里的白领青睐,极大的提升了工作的效率,不久之后,便成了办公室里的必备工具。
随着更多的新语言的崛起,例如我们所熟悉的 java,后来便有一些团队开始开发一套能与 Excel 软件无缝切换的操作工具!
这其中就有我们所熟悉的 apache 的 poi,其前身是 Jakarta 的 POI Project项目,之后将其开源给 apache 基金会!
当然,在java生态体系里面,能与Excel无缝衔接的第三方工具还有很多,因为 apache poi 在业界使用的最广泛,因此其他的工具不做过多介绍!
话不多说,直接开撸!
2.1、首先引入apache poi的依赖
<dependencies>
<!--xls(03)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<!--xlsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<!--时间格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.6</version>
</dependency>
</dependencies>
2.2、导出excel
导出操作,即使用 Java 写出数据到 Excel 中,常见场景是将页面上的数据(可能是经过条件查询的)导出,这些数据可能是财务数据,也可能是商品数据,生成 Excel 后返回给用户下载文件。
在 poi 工具库中,导出 api 可以分三种方式
- HSSF方式:这种方式导出的文件格式为office 2003专用格式,即
.xls
,优点是导出数据速度快,但是最多65536行数据 - XSSF方式:这种方式导出的文件格式为office 2007专用格式,即
.xlsx
,优点是导出的数据不受行数限制,缺点导出速度慢 - SXSSF方式:SXSSF 是 XSSF API的兼容流式扩展,主要解决当使用 XSSF 方式导出大数据量时,内存溢出的问题,支持导出大批量的excel数据
2.2.1、HSSF方式导出
HSSF方式,最多只支持65536
条数据导出,超过这个条数会报错!
public class ExcelWrite2003Test {
public static String PATH = "/Users/hello/Desktop/";
public static void main(String[] args) throws Exception {
//时间
long begin = System.currentTimeMillis();
//创建一个工作簿
Workbook workbook = new HSSFWorkbook();
//创建表
Sheet sheet = workbook.createSheet();
//写入数据
for (int rowNumber = 0; rowNumber < 65536; rowNumber++) {
//创建行
Row row = sheet.createRow(rowNumber);
for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
//创建列
Cell cell = row.createCell(cellNumber);
cell.setCellValue(cellNumber);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "用户信息表2003BigData.xls");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin) / 1000);//4.29s
}
}
2.2.2、XSSF方式导出
XSSF方式支持大批量数据导出,所有的数据先写入内存再导出,容易出现内存溢出!
public class ExcelWrite2007Test {
public static String PATH = "/Users/hello/Desktop/";
public static void main(String[] args) throws Exception {
//时间
long begin = System.currentTimeMillis();
//创建一个工作簿
Workbook workbook = new XSSFWorkbook();
//创建表
Sheet sheet = workbook.createSheet();
//写入数据
for (int rowNumber = 0; rowNumber < 65537; rowNumber++) {
Row row = sheet.createRow(rowNumber);
for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
Cell cell = row.createCell(cellNumber);
cell.setCellValue(cellNumber);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "用户信息表2007BigData.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin) / 1000);//15.87s
}
}
2.2.3、SXSSF方式导出
SXSSF方式是XSSF方式的一种延伸,主要特性是低内存,导出的时候,先将数据写入磁盘再导出,避免报内存不足,导致程序运行异常,缺点是运行很慢!
public class ExcelWriteSXSSFTest {
public static String PATH = "/Users/hello/Desktop/";
public static void main(String[] args) throws Exception {
//时间
long begin = System.currentTimeMillis();
//创建一个工作簿
Workbook workbook = new SXSSFWorkbook();
//创建表
Sheet sheet = workbook.createSheet();
//写入数据
for (int rowNumber = 0; rowNumber < 100000; rowNumber++) {
Row row = sheet.createRow(rowNumber);
for (int cellNumber = 0; cellNumber < 10; cellNumber++) {
Cell cell = row.createCell(cellNumber);
cell.setCellValue(cellNumber);
}
}
System.out.println("over");
FileOutputStream fileOutputStream = new FileOutputStream(PATH + "用户信息表2007BigDataS.xlsx");
workbook.write(fileOutputStream);
fileOutputStream.close();
long end = System.currentTimeMillis();
System.out.println((double) (end - begin) / 1000);//6.39s
}
}
2.3、导入excel
导入操作,即将 excel 中的数据采用java工具库将其解析出来,进而将 excel 数据写入数据库!
同样,在 poi 工具库中,导入 api 也分三种方式,与上面的导出一一对应!
2.3.1、HSSF方式导入
public class ExcelRead2003Test {
public static String PATH = "/Users/hello/Desktop/";
public static void main(String[] args) throws Exception {
//获取文件流
FileInputStream inputStream = new FileInputStream(PATH + "用户信息表BigData.xls");
//1.创建工作簿,使用excel能操作的这边都看看操作
Workbook workbook = new HSSFWorkbook(inputStream);
//2.得到表
Sheet sheet = workbook.getSheetAt(0);
//3.得到行
Row row = sheet.getRow(0);
//4.得到列
Cell cell = row.getCell(0);
getValue(cell);
inputStream.close();
}
public static void getValue(Cell cell){
//匹配类型数据
if (cell != null) {
CellType cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case STRING: //字符串
System.out.print("[String类型]");
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: //布尔类型
System.out.print("[boolean类型]");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK: //空
System.out.print("[BLANK类型]");
break;
case NUMERIC: //数字(日期、普通数字)
System.out.print("[NUMERIC类型]");
if (HSSFDateUtil.isCellDateFormatted(cell)) { //日期
System.out.print("[日期]");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
//不是日期格式,防止数字过长
System.out.print("[转换为字符串输出]");
cell.setCellType(CellType.STRING);
cellValue = cell.toString();
}
break;
case ERROR:
System.out.print("[数据类型错误]");
break;
}
System.out.println(cellValue);
}
}
}
2.3.2、XSSF方式导入
public class ExcelRead2007Test {
public static String PATH = "/Users/hello/Desktop/";
public static void main(String[] args) throws Exception {
//获取文件流
FileInputStream inputStream = new FileInputStream(PATH + "用户信息表2007BigData.xlsx");
//1.创建工作簿,使用excel能操作的这边都看看操作
Workbook workbook = new XSSFWorkbook(inputStream);
//2.得到表
Sheet sheet = workbook.getSheetAt(0);
//3.得到行
Row row = sheet.getRow(0);
//4.得到列
Cell cell = row.getCell(0);
getValue(cell);
inputStream.close();
}
public static void getValue(Cell cell){
//匹配类型数据
if (cell != null) {
CellType cellType = cell.getCellType();
String cellValue = "";
switch (cellType) {
case STRING: //字符串
System.out.print("[String类型]");
cellValue = cell.getStringCellValue();
break;
case BOOLEAN: //布尔类型
System.out.print("[boolean类型]");
cellValue = String.valueOf(cell.getBooleanCellValue());
break;
case BLANK: //空
System.out.print("[BLANK类型]");
break;
case NUMERIC: //数字(日期、普通数字)
System.out.print("[NUMERIC类型]");
if (HSSFDateUtil.isCellDateFormatted(cell)) { //日期
System.out.print("[日期]");
Date date = cell.getDateCellValue();
cellValue = new DateTime(date).toString("yyyy-MM-dd");
} else {
//不是日期格式,防止数字过长
System.out.print("[转换为字符串输出]");
cell.setCellType(CellType.STRING);
cellValue = cell.toString();
}
break;
case ERROR:
System.out.print("[数据类型错误]");
break;
}
System.out.println(cellValue);
}
}
}
2.3.3、SXSSF方式导入
public class ExcelReadSXSSFTest {
public static String PATH = "/Users/hello/Desktop/";
public static void main(String[] args) throws Exception {
//获取文件流
//1.创建工作簿,使用excel能操作的这边都看看操作
OPCPackage opcPackage = OPCPackage.open(PATH + "用户信息表2007BigData.xlsx");
XSSFReader xssfReader = new XSSFReader(opcPackage);
StylesTable stylesTable = xssfReader.getStylesTable();
ReadOnlySharedStringsTable sharedStringsTable = new ReadOnlySharedStringsTable(opcPackage);
// 创建XMLReader,设置ContentHandler
XMLReader xmlReader = SAXHelper.newXMLReader();
xmlReader.setContentHandler(new XSSFSheetXMLHandler(stylesTable, sharedStringsTable, new SimpleSheetContentsHandler(), false));
// 解析每个Sheet数据
Iterator<InputStream> sheetsData = xssfReader.getSheetsData();
while (sheetsData.hasNext()) {
try (InputStream inputStream = sheetsData.next();) {
xmlReader.parse(new InputSource(inputStream));
}
}
}
/**
* 内容处理器
*/
public static class SimpleSheetContentsHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
protected List<String> row;
/**
* A row with the (zero based) row number has started
*
* @param rowNum
*/
@Override
public void startRow(int rowNum) {
row = new ArrayList<>();
}
/**
* A row with the (zero based) row number has ended
*
* @param rowNum
*/
@Override
public void endRow(int rowNum) {
if (row.isEmpty()) {
return;
}
// 处理数据
System.out.println(row.stream().collect(Collectors.joining(" ")));
}
/**
* A cell, with the given formatted value (may be null),
* and possibly a comment (may be null), was encountered
*
* @param cellReference
* @param formattedValue
* @param comment
*/
@Override
public void cell(String cellReference, String formattedValue, XSSFComment comment) {
row.add(formattedValue);
}
/**
* A header or footer has been encountered
*
* @param text
* @param isHeader
* @param tagName
*/
@Override
public void headerFooter(String text, boolean isHeader, String tagName) {
}
}
}
三、小结
apache poi 是实现 excel 导出、导入功能最流行的框架,虽然编程很复杂,但是为后续的其它框架发展做了很多的干活输出。
四、参考
写到最后
不会有人刷到这里还想白嫖吧?点赞对我真的非常重要!在线求赞。加个关注我会非常感激!
本文已整理到技术笔记中,此外,笔记内容还涵盖 Spring、Spring Boot/Cloud、Dubbo、JVM、集合、多线程、JPA、MyBatis、MySQL、微服务等技术栈。
需要的小伙伴可以点击 技术笔记 获取!