导出:Java实现大批量数据导入导出(100W以上)

阅读文本大概需要3分钟。

来源：https://www.cnblogs.com/barrywxx/p/10700283.html

使用POI或JXLS导出大数据量（百万级）Excel报表常常面临两个问题：

1. 服务器内存溢出；

2. 一次从数据库查询出这么大数据，查询缓慢。

当然也可以分页查询出数据，分别生成多个Excel打包下载，但这种生成还是很缓慢。

大数据量导入请参考：Java实现大批量数据导入导出(100W以上)　-（一）导入

那么如何解决呢？

我们可以借助XML格式利用模板替换，分页查询出数据从磁盘写入XML，最终会以Excel多sheet形式生成。亲测2400万行数据，生成Excel文件4.5G，总耗时1.5分钟。

https://www.cnblogs.com/barrywxx/p/10700283.html 

我利用StringTemplate模板解析技术对XML模板进行填充。当然也可以使用FreeMarker, Velocity等Java模板技术实现。

首先引入StringTemplate所需Jar包：

使用技术为 stringTemplate 

pom.xml：

1. <dependency>

2. <groupId>antlr</groupId>

3. <artifactId>antlr</artifactId>

4. <version>2.7.7</version>

5. </dependency>

7. <dependency>

8. <groupId>org.antlr</groupId>

9. <artifactId>stringtemplate</artifactId>

10. <version>3.2.1</version>

    </dependency>

首先准备导出Excel模板，然后打开－》另存为－》选择格式为XML，然后用文本打开XML,提取XML头模板(head.st可通用)，数据体模板(boday.st):

head.st可通用:

 

1. <?xml version="1.0"?>

2. <?mso-application progid="Excel.Sheet"?>

3. <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

4. xmlns:o="urn:schemas-microsoft-com:office:office"

5. xmlns:x="urn:schemas-microsoft-com:office:excel"

6. xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

7. xmlns:html="http://www.w3.org/TR/REC-html40">

8. <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

9. <Created>1996-12-17T01:32:42Z</Created>

10. <LastSaved>2013-08-02T09:21:24Z</LastSaved>

11. <Version>11.9999</Version>

12. </DocumentProperties>

13. <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">

14. <RemovePersonalInformation/>

15. </OfficeDocumentSettings>

16. <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

17. <WindowHeight>4530</WindowHeight>

18. <WindowWidth>8505</WindowWidth>

19. <WindowTopX>480</WindowTopX>

20. <WindowTopY>120</WindowTopY>

21. <AcceptLabelsInFormulas/>

22. <ProtectStructure>False</ProtectStructure>

23. <ProtectWindows>False</ProtectWindows>

24. </ExcelWorkbook>

25. <Styles>

26. <Style ss:ID="Default" ss:Name="Normal">

27. <Alignment ss:Vertical="Bottom"/>

28. <Borders/>

29. <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>

30. <Interior/>

31. <NumberFormat/>

32. <Protection/>

33. </Style>

34. </Styles>

生成大数据量Excel类：

ExcelGenerator:

 

1. package test.exportexcel;

3. import org.antlr.stringtemplate.StringTemplate;

4. import org.antlr.stringtemplate.StringTemplateGroup;

5. import test.exportexcel.bean.Row;

6. import test.exportexcel.bean.Worksheet;

8. import java.io.*;

9. import java.util.ArrayList;

10. import java.util.List;

11. import java.util.Random;

13. /**

14. * 类功能描述：generator big data Excel

15. *

16. * @author WangXueXing create at 19-4-13 下午10:23

17. * @version 1.0.0

18. */

19. public class ExcelGenerator {

20. public static void main(String[] args) throws FileNotFoundException{

21. ExcelGenerator template = new ExcelGenerator();

22. template.output2();

23. }

25. /**

26. * 生成数据量大的时候，该方法会出现内存溢出

27. * @throws FileNotFoundException

28. */

29. public void output1() throws FileNotFoundException{

30. StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");

31. StringTemplate st4 = stGroup.getInstanceOf("test/exportexcel/template/test");

32. List<Worksheet> worksheets = new ArrayList<>();

34. File file = new File("/home/barry/data/output.xls");

35. PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));

37. for(int i=0;i<30;i++){

38. Worksheet worksheet = new Worksheet();

39. worksheet.setSheet("第"+(i+1)+"页");

40. List<Row> rows = new ArrayList<>();

41. for(int j=0;j<6000;j++){

42. Row row = new Row();

43. row.setName1("zhangzehao");

44. row.setName2(""+j);

45. row.setName3(i+" "+j);

46. rows.add(row);

47. }

48. worksheet.setRows(rows);

49. worksheets.add(worksheet);

50. }

52. st4.setAttribute("worksheets", worksheets);

53. writer.write(st4.toString());

54. writer.flush();

55. writer.close();

56. System.out.println("生成excel完成");

57. }

59. /**

60. * 该方法不管生成多大的数据量，都不会出现内存溢出，只是时间的长短

61. * 经测试，生成2400万数据，2分钟内，4.5G大的文件，打开大文件就看内存是否足够大了

62. * 数据量小的时候，推荐用JXLS的模板技术生成excel文件，谁用谁知道，大数据量可以结合该方法使用

63. * @throws FileNotFoundException

64. */

65. public void output2() throws FileNotFoundException{

66. long startTimne = System.currentTimeMillis();

67. StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");

69. //写入excel文件头部信息

70. StringTemplate head = stGroup.getInstanceOf("test/exportexcel/template/head");

71. File file = new File("/home/barry/data/output.xls");

72. PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));

73. writer.print(head.toString());

74. writer.flush();

76. int sheets = 400;

77. //excel单表最大行数是65535

78. int maxRowNum = 60000;

80. //写入excel文件数据信息

81. for(int i=0;i<sheets;i++){

82. StringTemplate body = stGroup.getInstanceOf("test/exportexcel/template/body");

83. Worksheet worksheet = new Worksheet();

84. worksheet.setSheet(" "+(i+1)+" ");

85. worksheet.setColumnNum(3);

86. worksheet.setRowNum(maxRowNum);

87. List<Row> rows = new ArrayList<>();

88. for(int j=0;j<maxRowNum;j++){

89. Row row = new Row();

90. row.setName1(""+new Random().nextInt(100000));

91. row.setName2(""+j);

92. row.setName3(i+""+j);

93. rows.add(row);

94. }

95. worksheet.setRows(rows);

96. body.setAttribute("worksheet", worksheet);

97. writer.print(body.toString());

98. writer.flush();

99. rows.clear();

100. rows = null;

101. worksheet = null;

102. body = null;

103. Runtime.getRuntime().gc();

104. System.out.println("正在生成excel文件的 sheet"+(i+1));

105. }

107. //写入excel文件尾部

108. writer.print("</Workbook>");

109. writer.flush();

110. writer.close();

111. System.out.println("生成excel文件完成");

112. long endTime = System.currentTimeMillis();

113. System.out.println("用时="+((endTime-startTimne)/1000)+"秒");

114. }

115. }

定义JavaBean:

WorkSheet.java:

1. package test.exportexcel.bean;

3. import java.util.List;

5. /**

6. * 类功能描述：Excel sheet Bean

7. *

8. * @author WangXueXing create at 19-4-13 下午10:21

9. * @version 1.0.0

10. */

11. public class Worksheet {

12. private String sheet;

13. private int columnNum;

14. private int rowNum;

15. private List<Row> rows;

17. public String getSheet() {

18. return sheet;

19. }

20. public void setSheet(String sheet) {

21. this.sheet = sheet;

22. }

24. public List<Row> getRows() {

25. return rows;

26. }

27. public void setRows(List<Row> rows) {

28. this.rows = rows;

29. }

31. public int getColumnNum() {

32. return columnNum;

33. }

34. public void setColumnNum(int columnNum) {

35. this.columnNum = columnNum;

36. }

38. public int getRowNum() {

39. return rowNum;

40. }

41. public void setRowNum(int rowNum) {

42. this.rowNum = rowNum;

43. }

44. }

Row.java:

1. package test.exportexcel.bean;

3. /**

4. * 类功能描述：Excel row bean

5. *

6. * @author WangXueXing create at 19-4-13 下午10:22

7. * @version 1.0.0

8. */

9. public class Row {

10. private String name1;

11. private String name2;

12. private String name3;

14. public String getName1() {

15. return name1;

16. }

17. public void setName1(String name1) {

18. this.name1 = name1;

19. }

21. public String getName2() {

22. return name2;

23. }

24. public void setName2(String name2) {

25. this.name2 = name2;

26. }

28. public String getName3() {

29. return name3;

30. }

31. public void setName3(String name3) {

32. this.name3 = name3;

33. }

34. }

另附实现源码：

https://files.cnblogs.com/files/barrywxx/exportexcel.zip

☆

往期精彩

☆

01 漫谈发版哪些事，好课程推荐

02 Linux的常用最危险的命令

03 精讲Spring Boot—入门+进阶+实例

04 优秀的Java程序员必须了解的GC哪些

05 互联网支付系统整体架构详解

关注我

每天进步一点点

喜欢！在看☟