POI SXSSF API 导出1000万数据示例

SXSSF是XSSF API的兼容流式扩展，在必须生成非常大的电子表格、并且堆空间有限时使用。

SXSSF通过限制对滑动窗口内数据的访问实现低内存占用，而XSSF允许访问文档中的所有行。

不在窗口中的数据将变得不可访问，因为它们已经被写入磁盘。

一、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的行。

 1 import junit.framework.Assert;
 2 import org.apache.poi.ss.usermodel.Cell;
 3 import org.apache.poi.ss.usermodel.Row;
 4 import org.apache.poi.ss.usermodel.Sheet;
 5 import org.apache.poi.ss.usermodel.Workbook;
 6 import org.apache.poi.ss.util.CellReference;
 7 import org.apache.poi.xssf.streaming.SXSSFWorkbook;
 8 
 9     public static void main(String[] args) throws Throwable {
10 
11         // keep 100 rows in memory, exceeding rows will be flushed to disk
12         SXSSFWorkbook wb = new SXSSFWorkbook(100);
13         Sheet sh = wb.createSheet();
14         for(int rownum = 0; rownum < 1000; rownum++){
15             Row row = sh.createRow(rownum);
16             for(int cellnum = 0; cellnum < 10; cellnum++){
17                 Cell cell = row.createCell(cellnum);
18                 String address = new CellReference(cell).formatAsString();
19                 cell.setCellValue(address);
20             }
21         }
22 
23         // Rows with rownum < 900 are flushed and not accessible
24         for(int rownum = 0; rownum < 900; rownum++){
25           Assert.assertNull(sh.getRow(rownum));
26         }
27 
28         // ther last 100 rows are still in memory
29         for(int rownum = 900; rownum < 1000; rownum++){
30             Assert.assertNotNull(sh.getRow(rownum));
31         }
32         
33         FileOutputStream out = new FileOutputStream("/temp/sxssf.xlsx");
34         wb.write(out);
35         out.close();
36 
37         // dispose of temporary files backing this workbook on disk
38         wb.dispose();
39     }

三、封装后的工具类

1、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工作表。

2、ExcelColumn类

封装列信息，包括列名、从数据对象中获取列值时使用的属性名、列宽度等。

3、DataGenerator接口

用于生成数据。有两个方法：

boolean hasNext();

判断是否还有数据

List<?> generate();

生成一批数据

4、AbstractBatchDataGenerator抽象类

这是一个抽象批次数据生成器。

实现类DataGenerator接口，实现了hasNext和generate两个方法。

但是子类需要实现getTotalBatch和nextBatch两个方法，以便获取到批次数量和批次数据。

如果需要编写一个批次数据生成器，可以继承该抽象类。

5、TaskHistoryDataGenerator测试批次数据生成器

这是一个批次数据生成器，用于生成测试数据。

四、源代码

1、依赖

 1 <dependency>
 2   <groupId>junit</groupId>
 3   <artifactId>junit</artifactId>
 4   <version>4.11</version>
 5   <scope>test</scope>
 6 </dependency>
 7 <dependency>
 8   <groupId>org.apache.poi</groupId>
 9   <artifactId>poi</artifactId>
10   <version>4.0.0</version>
11 </dependency>
12 <dependency>
13   <groupId>org.apache.poi</groupId>
14   <artifactId>poi-ooxml</artifactId>
15   <version>4.0.0</version>
16 </dependency>
17 <dependency>
18   <groupId>org.projectlombok</groupId>
19   <artifactId>lombok</artifactId>
20   <version>1.16.18</version>
21   <scope>compile</scope>
22 </dependency>

2、PoiExcelUtils工具类源码

  1 /**
  2  * Excel导出工具类
  3  */
  4 public class PoiExcelUtils {
  5 
  6   /**
  7    * 默认内存缓存数据量
  8    */
  9   public static final int BUFFER_SIZE = 100;
 10 
 11   /**
 12    * 默认每个sheet数据量
 13    */
 14   @SuppressWarnings("unused")
 15   public static final int DEFAULT_SHEET_SIZE = 50000;
 16 
 17   /**
 18    * 默认工作表名称
 19    */
 20   public static final String DEFAULT_SHEET_NAME = "sheet";
 21 
 22   /**
 23    * 导出数据到excel
 24    *
 25    * @param cols 列信息集合
 26    * @param dataGenerator 数据生成器
 27    * @param sheetName sheet名称前缀
 28    * @param outputStream 目标输出流
 29    */
 30   public static void export(List<ExcelColumn> cols, DataGenerator dataGenerator, String sheetName,
 31       OutputStream outputStream) {
 32 
 33     SXSSFWorkbook workbook = new SXSSFWorkbook(BUFFER_SIZE);
 34 
 35     try {
 36 
 37       // 从数据对象中获取列值使用的getter方法名集合
 38       List<String> methodNames = new ArrayList<>();
 39       String propertyName;
 40 
 41       for (ExcelColumn column : cols) {
 42         propertyName = "get" + upperCaseHead(column.getPropertyName());
 43         methodNames.add(propertyName);
 44       }
 45 
 46       List<?> objects;
 47 
 48       int i = 0;
 49 
 50       while (dataGenerator.hasNext()) {
 51 
 52         objects = dataGenerator.generate();
 53 
 54         SXSSFSheet sxssfSheet = createSheet(workbook, cols, sheetName + i);
 55         export2Sheet(sxssfSheet, methodNames, objects);
 56 
 57         objects.clear();
 58 
 59         System.out.println("Current batch >> " + (i + 1));
 60 
 61         i++;
 62       }
 63 
 64       // 输出
 65       workbook.write(outputStream);
 66 
 67     } catch (IOException e) {
 68       throw new RuntimeException(e);
 69     } finally {
 70       // dispose of temporary files backing this workbook on disk
 71       workbook.dispose();
 72     }
 73   }
 74 
 75   /**
 76    * 把数据导出到sheet中
 77    *
 78    * @param sheet sheet
 79    * @param getters 从数据对象中获取列值使用的getter方法名集合
 80    * @param data 数据
 81    */
 82   private static void export2Sheet(SXSSFSheet sheet, List<String> getters, List<?> data) {
 83 
 84     try {
 85 
 86       // 记录当前sheet的数据量
 87       int sheetRowCount = sheet.getLastRowNum();
 88 
 89       SXSSFRow dataRow;
 90 
 91       // 遍历数据集合
 92       for (Object datum : data) {
 93 
 94         // 创建一行
 95         dataRow = sheet.createRow(++sheetRowCount);
 96 
 97         Class<?> clazz = datum.getClass();
 98         Method readMethod;
 99         Object o;
100         XSSFRichTextString text;
101         Cell cell;
102 
103         // 遍历methodNames集合，获取每一列的值
104         for (int i = 0; i < getters.size(); i++) {
105           // 从Class对象获取getter方法
106           readMethod = clazz.getMethod(getters.get(i));
107           // 获取列值
108           o = readMethod.invoke(datum);
109           if (o == null) {
110             o = "";
111           }
112           text = new XSSFRichTextString(o.toString());
113           // 创建单元格并赋值
114           cell = dataRow.createCell(i);
115           cell.setCellValue(text);
116         }
117       }
118 
119     } catch (Exception e) {
120       throw new RuntimeException(e);
121     }
122   }
123 
124   /**
125    * 创建一个工作表
126    *
127    * @param workbook SXSSFWorkbook对象
128    * @param cols Excel导出列信息
129    * @param sheetName 工作表名称
130    * @return SXSSFSheet
131    */
132   private static SXSSFSheet createSheet(SXSSFWorkbook workbook, List<ExcelColumn> cols,
133       String sheetName) {
134 
135     // 创建一个sheet对象
136     SXSSFSheet sheet = workbook.createSheet(sheetName);
137 
138     // 生成表头
139     SXSSFRow row = sheet.createRow(0);
140 
141     ExcelColumn column;
142     SXSSFCell cell;
143     XSSFRichTextString text;
144 
145     for (int i = 0; i < cols.size(); i++) {
146 
147       // 获取列信息
148       column = cols.get(i);
149 
150       // 创建单元格
151       cell = row.createCell(i);
152 
153       // 为单元格赋值
154       text = new XSSFRichTextString(column.getName());
155       cell.setCellValue(text);
156 
157       // 设置列宽
158       int width = column.getWidth();
159 
160       if (width > 0) {
161         sheet.setColumnWidth(i, width);
162       }
163     }
164 
165     return sheet;
166   }
167 
168   /**
169    * 首字母转大写
170    *
171    * @param word 单词
172    * @return String
173    */
174   private static String upperCaseHead(String word) {
175     char[] chars = word.toCharArray();
176     int j = chars[0] - 32;
177     chars[0] = (char) j;
178     return new String(chars);
179   }
180 
181   /**
182    * 数据生成器
183    */
184   public interface DataGenerator {
185 
186     /**
187      * 是否还有数据
188      *
189      * @return boolean
190      */
191     boolean hasNext();
192 
193     /**
194      * 生成数据
195      *
196      * @return java.util.List
197      */
198     List<?> generate();
199   }
200 
201   /**
202    * 批次数据生成器
203    */
204   public static abstract class AbstractBatchDataGenerator implements DataGenerator {
205 
206     protected int batchNumber = 1;
207 
208     protected int totalBatch;
209 
210     protected int batchSize;
211 
212     public AbstractBatchDataGenerator(int batchSize) {
213       this.batchSize = batchSize;
214       this.totalBatch = getTotalBatch();
215     }
216 
217     /**
218      * 获取一共有多少批数据
219      *
220      * @return int
221      */
222     protected abstract int getTotalBatch();
223 
224     /**
225      * 获取下一批数据
226      *
227      * @param batchNumber 批次
228      * @param batchSize 批次数据量
229      * @return java.util.List
230      */
231     protected abstract List<?> nextBatch(int batchNumber, int batchSize);
232 
233     /**
234      * 是否有下一批数据
235      *
236      * @return boolean
237      */
238     @Override
239     public boolean hasNext() {
240       return this.batchNumber <= this.totalBatch;
241     }
242 
243     @Override
244     public List<?> generate() {
245 
246       if (hasNext()) {
247         List<?> batch = nextBatch(this.batchNumber, this.batchSize);
248         this.batchNumber++;
249         return batch;
250       }
251       return Collections.emptyList();
252     }
253   }
254 }

View Code

3、ExcelColumn类源码

 1 /**
 2  * 封装excel导出列信息
 3  */
 4 @Data
 5 @AllArgsConstructor
 6 @NoArgsConstructor
 7 public class ExcelColumn {
 8 
 9   /**
10    * 列名
11    */
12   private String name;
13 
14   /**
15    * 从数据对象中获取列值时使用的属性名
16    */
17   private String propertyName;
18 
19   /**
20    * 列宽度
21    */
22   private int width;
23 }

4、PoiExcelUtilsTest测试类

测试类导出1200万条数据，256MB内存。

运行java命令时添加-Xms256m -Xmx256m选项。

 1 /**
 2  * 测试excel操作工具类
 3  */
 4 public class PoiExcelUtilsTest {
 5 
 6   /**
 7    * 文件保存目录
 8    */
 9   private static final String UPLOAD_PATH = "D:/";
10 
11   /**
12    * 测试excel导出
13    */
14   @Test
15   public void testExport() {
16 
17     // 打印一下运行内存
18     long maxMemory = Runtime.getRuntime().maxMemory();
19     System.out.println(maxMemory / 1024 / 1024 + "MB");
20 
21     String filename = "TestPoi.xlsx";
22 
23     try (OutputStream outputStream = new FileOutputStream(UPLOAD_PATH + filename)) {
24 
25       int width = 10 * 512 + 500;
26 
27       List<ExcelColumn> cols = new ArrayList<>();
28       cols.add(new ExcelColumn("vin", "vin", width));
29       cols.add(new ExcelColumn("设备ID", "firmwareId", width));
30       cols.add(new ExcelColumn("升级状态", "updateStatus", width));
31       cols.add(new ExcelColumn("失败原因", "failReason", width));
32 
33       int size = 400000;
34 
35       PoiExcelUtils.export(
36           cols,
37           new TaskHistoryDataGenerator(size),
38           PoiExcelUtils.DEFAULT_SHEET_NAME,
39           outputStream);
40 
41     } catch (IOException e) {
42       throw new RuntimeException(e);
43     }
44   }
45 
46   /**
47    * TaskHistory数据生成器，测试使用
48    */
49   public static class TaskHistoryDataGenerator extends AbstractBatchDataGenerator {
50 
51     public TaskHistoryDataGenerator(int batchSize) {
52       super(batchSize);
53     }
54 
55     @Override
56     protected int getTotalBatch() {
57       return 30;
58     }
59 
60     @Override
61     protected List<?> nextBatch(int batchNumber, int batchSize) {
62 
63       List<TaskHistory> data = new ArrayList<>();
64 
65       int start = (batchNumber - 1) * batchSize;
66 
67       for (int i = 1; i <= batchSize; i++) {
68         int n = i + start;
69         TaskHistory taskHistory = new TaskHistory();
70         taskHistory.setFirmwareId(String.format("11%08d", n));
71         taskHistory.setFailReason("系统异常");
72         taskHistory.setUpdateStatus("请求成功");
73         taskHistory.setVin(String.format("1099728%08d", n));
74         data.add(taskHistory);
75       }
76 
77       return data;
78     }
79   }
80 
81   /**
82    * 封装测试数据
83    */
84   @Data
85   public static class TaskHistory {
86 
87     private String vin;
88 
89     private String updateStatus;
90 
91     private String firmwareId;
92 
93     private String failReason;
94   }
95 }

View Code