POI SXSSF API 导出1000万数据示例
- SXSSF流式API
- SXSSF示例
- 封装后的工具类
- PoiExcelUtils类
- ExcelColumn类
- DataGenerator接口
- AbstractBatchDataGenerator抽象类
- TaskHistoryDataGenerator测试批次数据生成器
- 源代码
- Maven依赖
- PoiExcelUtils工具类源码
- ExcelColumn类源码
- PoiExcelUtilsTest测试类
SXSSF流式API
首先看一下官方文档的说明。
https://poi.apache.org/components/spreadsheet/how-to.html#sxssf
SXSSF是XSSF API的兼容流式扩展,在必须生成非常大的电子表格、并且堆空间有限时使用。 SXSSF通过限制对滑动窗口内数据的访问实现低内存占用,而XSSF允许访问文档中的所有行。 不在窗口中的数据将变得不可访问,因为它们已经被写入磁盘。
可以通过SXSSFWorkbook(int windowSize)在工作簿创建时指定窗口大小,也可以通过SXSSFSheet.setRandomAccessWindowSize(int windowSize)在每个工作表中设置。
当通过createRow()创建新行并且未刷新记录的总数超过指定的窗口大小时,将刷新具有最低索引值的行数据,并且不能再通过getRow()访问该行。
默认窗口大小为100,由SXSSFWorkbook.DEFAULT_WINDOW_SIZE定义。
windowSize为-1表示无限制访问。在这种情况下,所有未通过调用flushRows()刷新的记录都可随机访问。
请注意,SXSSF通过调用dispose方法来分配必须始终明确清理的临时文件。
请注意,根据使用的功能不同,仍然可能会消耗大量内存,例如: 合并区域、超链接、注释等仍然只存储在内存中,因此如果广泛使用可能仍需要大量内存。
SXSSF示例
下面的示例写入一个包含100行窗口的工作表。
当行计数达到101时,rownum = 0的行被刷新到磁盘并从内存中删除,当rownum达到102时,则刷新rownum = 1的行。
import junit.framework.Assert;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
public static void main(String[] args) throws Throwable {
// keep 100 rows in memory, exceeding rows will be flushed to disk
SXSSFWorkbook wb = new SXSSFWorkbook(100);
Sheet sh = wb.createSheet();
for(int rownum = 0; rownum < 1000; rownum++){
Row row = sh.createRow(rownum);
for(int cellnum = 0; cellnum < 10; cellnum++){
Cell cell = row.createCell(cellnum);
String address = new CellReference(cell).formatAsString();
cell.setCellValue(address);
}
}
// Rows with rownum < 900 are flushed and not accessible
for(int rownum = 0; rownum < 900; rownum++){
Assert.assertNull(sh.getRow(rownum));
}
// ther last 100 rows are still in memory
for(int rownum = 900; rownum < 1000; rownum++){
Assert.assertNotNull(sh.getRow(rownum));
}
FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
wb.write(out);
out.close();
// dispose of temporary files backing this workbook on disk
wb.dispose();
}
封装后的工具类
PoiExcelUtils类
PoiExcelUtils类,封装了三个方法。
static void export(List<ExcelColumn> cols, DataGenerator dataGenerator, String sheetName, OutputStream outputStream)
该方法会创建一个SXSSFWorkbook对象,使用dataGenerator生成数据,每生成一批数据会生成一个sheet工作表,然后根据cols生成表头、获取数据写入到sheet工作表,当dataGenerator没有数据后,会输出到outputStream输出流,最后释放临时资源。
static void export2Sheet(SXSSFSheet sheet, List<String> getters, List<?> data)
这个是私有方法,不对外提供。作用是把一批数据写入到sheet工作表。
static SXSSFSheet createSheet(SXSSFWorkbook workbook, List<ExcelColumn> cols, String sheetName)
这个是私有方法,不对外提供。作用是在生成一批数据后生成一个新的sheet工作表。
ExcelColumn类
封装列信息,包括列名、从数据对象中获取列值时使用的属性名、列宽度等。
DataGenerator接口
用于生成数据。有两个方法:
boolean hasNext();
判断是否还有数据
List<?> generate();
生成一批数据
AbstractBatchDataGenerator抽象类
这是一个抽象批次数据生成器。
实现类DataGenerator接口,实现了hasNext和generate两个方法。
但是子类需要实现getTotalBatch和nextBatch两个方法,以便获取到批次数量和批次数据。
如果需要编写一个批次数据生成器,可以继承该抽象类。
TaskHistoryDataGenerator测试批次数据生成器
这是一个批次数据生成器,用于生成测试数据。
源代码
Maven依赖
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.0.0</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.16.18</version>
<scope>compile</scope>
</dependency>
PoiExcelUtils工具类源码
/**
* Excel导出工具类
*/
public class PoiExcelUtils {
/**
* 默认内存缓存数据量
*/
public static final int BUFFER_SIZE = 100;
/**
* 默认每个sheet数据量
*/
@SuppressWarnings("unused")
public static final int DEFAULT_SHEET_SIZE = 50000;
/**
* 默认工作表名称
*/
public static final String DEFAULT_SHEET_NAME = "sheet";
/**
* 导出数据到excel
*
* @param cols 列信息集合
* @param dataGenerator 数据生成器
* @param sheetName sheet名称前缀
* @param outputStream 目标输出流
*/
public static void export(List<ExcelColumn> cols, DataGenerator dataGenerator, String sheetName,
OutputStream outputStream) {
SXSSFWorkbook workbook = new SXSSFWorkbook(BUFFER_SIZE);
try {
// 从数据对象中获取列值使用的getter方法名集合
List<String> methodNames = new ArrayList<>();
String propertyName;
for (ExcelColumn column : cols) {
propertyName = "get" + upperCaseHead(column.getPropertyName());
methodNames.add(propertyName);
}
List<?> objects;
int i = 0;
while (dataGenerator.hasNext()) {
objects = dataGenerator.generate();
SXSSFSheet sxssfSheet = createSheet(workbook, cols, sheetName + i);
export2Sheet(sxssfSheet, methodNames, objects);
objects.clear();
System.out.println("Current batch >> " + (i + 1));
i++;
}
// 输出
workbook.write(outputStream);
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
// dispose of temporary files backing this workbook on disk
workbook.dispose();
}
}
/**
* 把数据导出到sheet中
*
* @param sheet sheet
* @param getters 从数据对象中获取列值使用的getter方法名集合
* @param data 数据
*/
private static void export2Sheet(SXSSFSheet sheet, List<String> getters, List<?> data) {
try {
// 记录当前sheet的数据量
int sheetRowCount = sheet.getLastRowNum();
SXSSFRow dataRow;
// 遍历数据集合
for (Object datum : data) {
// 创建一行
dataRow = sheet.createRow(++sheetRowCount);
Class<?> clazz = datum.getClass();
Method readMethod;
Object o;
XSSFRichTextString text;
Cell cell;
// 遍历methodNames集合,获取每一列的值
for (int i = 0; i < getters.size(); i++) {
// 从Class对象获取getter方法
readMethod = clazz.getMethod(getters.get(i));
// 获取列值
o = readMethod.invoke(datum);
if (o == null) {
o = "";
}
text = new XSSFRichTextString(o.toString());
// 创建单元格并赋值
cell = dataRow.createCell(i);
cell.setCellValue(text);
}
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 创建一个工作表
*
* @param workbook SXSSFWorkbook对象
* @param cols Excel导出列信息
* @param sheetName 工作表名称
* @return SXSSFSheet
*/
private static SXSSFSheet createSheet(SXSSFWorkbook workbook, List<ExcelColumn> cols,
String sheetName) {
// 创建一个sheet对象
SXSSFSheet sheet = workbook.createSheet(sheetName);
// 生成表头
SXSSFRow row = sheet.createRow(0);
ExcelColumn column;
SXSSFCell cell;
XSSFRichTextString text;
for (int i = 0; i < cols.size(); i++) {
// 获取列信息
column = cols.get(i);
// 创建单元格
cell = row.createCell(i);
// 为单元格赋值
text = new XSSFRichTextString(column.getName());
cell.setCellValue(text);
// 设置列宽
int width = column.getWidth();
if (width > 0) {
sheet.setColumnWidth(i, width);
}
}
return sheet;
}
/**
* 首字母转大写
*
* @param word 单词
* @return String
*/
private static String upperCaseHead(String word) {
char[] chars = word.toCharArray();
int j = chars[0] - 32;
chars[0] = (char) j;
return new String(chars);
}
/**
* 数据生成器
*/
public interface DataGenerator {
/**
* 是否还有数据
*
* @return boolean
*/
boolean hasNext();
/**
* 生成数据
*
* @return java.util.List
*/
List<?> generate();
}
/**
* 批次数据生成器
*/
public static abstract class AbstractBatchDataGenerator implements DataGenerator {
protected int batchNumber = 1;
protected int totalBatch;
protected int batchSize;
public AbstractBatchDataGenerator(int batchSize) {
this.batchSize = batchSize;
this.totalBatch = getTotalBatch();
}
/**
* 获取一共有多少批数据
*
* @return int
*/
protected abstract int getTotalBatch();
/**
* 获取下一批数据
*
* @param batchNumber 批次
* @param batchSize 批次数据量
* @return java.util.List
*/
protected abstract List<?> nextBatch(int batchNumber, int batchSize);
/**
* 是否有下一批数据
*
* @return boolean
*/
@Override
public boolean hasNext() {
return this.batchNumber <= this.totalBatch;
}
@Override
public List<?> generate() {
if (hasNext()) {
List<?> batch = nextBatch(this.batchNumber, this.batchSize);
this.batchNumber++;
return batch;
}
return Collections.emptyList();
}
}
}
ExcelColumn类源码
/**
* 封装excel导出列信息
*/
@Data
@AllArgsConstructor
@NoArgsConstructor
public class ExcelColumn {
/**
* 列名
*/
private String name;
/**
* 从数据对象中获取列值时使用的属性名
*/
private String propertyName;
/**
* 列宽度
*/
private int width;
}
PoiExcelUtilsTest测试类
测试类导出1200万条数据,256MB内存。
运行java命令时添加-Xms256m -Xmx256m选项。
/**
* 测试excel操作工具类
*/
public class PoiExcelUtilsTest {
/**
* 文件保存目录
*/
private static final String UPLOAD_PATH = "D:/";
/**
* 测试excel导出
*/
@Test
public void testExport() {
// 打印一下运行内存
long maxMemory = Runtime.getRuntime().maxMemory();
System.out.println(maxMemory / 1024 / 1024 + "MB");
String filename = "TestPoi.xlsx";
try (OutputStream outputStream = new FileOutputStream(UPLOAD_PATH + filename)) {
int width = 10 * 512 + 500;
List<ExcelColumn> cols = new ArrayList<>();
cols.add(new ExcelColumn("vin", "vin", width));
cols.add(new ExcelColumn("设备ID", "firmwareId", width));
cols.add(new ExcelColumn("升级状态", "updateStatus", width));
cols.add(new ExcelColumn("失败原因", "failReason", width));
int size = 400000;
PoiExcelUtils.export(
cols,
new TaskHistoryDataGenerator(size),
PoiExcelUtils.DEFAULT_SHEET_NAME,
outputStream);
} catch (IOException e) {
throw new RuntimeException(e);
}
}
/**
* TaskHistory数据生成器,测试使用
*/
public static class TaskHistoryDataGenerator extends AbstractBatchDataGenerator {
public TaskHistoryDataGenerator(int batchSize) {
super(batchSize);
}
@Override
protected int getTotalBatch() {
return 30;
}
@Override
protected List<?> nextBatch(int batchNumber, int batchSize) {
List<TaskHistory> data = new ArrayList<>();
int start = (batchNumber - 1) * batchSize;
for (int i = 1; i <= batchSize; i++) {
int n = i + start;
TaskHistory taskHistory = new TaskHistory();
taskHistory.setFirmwareId(String.format("11%08d", n));
taskHistory.setFailReason("系统异常");
taskHistory.setUpdateStatus("请求成功");
taskHistory.setVin(String.format("1099728%08d", n));
data.add(taskHistory);
}
return data;
}
}
/**
* 封装测试数据
*/
@Data
public static class TaskHistory {
private String vin;
private String updateStatus;
private String firmwareId;
private String failReason;
}
}