Java 使用stringTemplate导出大批量数据excel（百万级）

转自：http://blog.csdn.net/z69183787/article/details/50737709

参考资料：http://bbs.51cto.com/thread-1074293-1-1.html

目前java框架中能够生成excel文件的的确不少，但是，能够生成大数据量的excel框架，我倒是没发现，一般数据量大了都会出现内存溢出，所以，生成大数据量的excel文件要返璞归真，用java的基础技术,IO流来实现。
   如果想用IO流来生成excel文件，必须要知道excel的文件格式内容，相当于生成html文件一样，用字符串拼接html标签保存到文本文件就可以生成一个html文件了。同理，excel文件也是可以的。怎么知道excel的文件格式呢？其实很简单，随便新建一个excel文件，双击打开，然后点击“文件”-》“另存为”，保存的类型为“xml表格”，保存之后用文本格式打开，就可以看到excel的字符串格式一览无遗了。

把下面的xml字符串复制到文本文件，然后保存为xls格式，就是一个excel文件。

[html]  view plain  copy  

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>2000-11-18T06:53:49Z</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>  
35. <Worksheet ss:Name="Sheet1">  
36.   <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"  
37.    x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">  
38.    <Column ss:AutoFitWidth="0" ss:Width="73.5"/>  
39.    <Row>  
40.     <Cell><Data ss:Type="String">zhangzehao</Data></Cell>  
41.     <Cell><Data ss:Type="String">zhangzehao</Data></Cell>  
42.    </Row>  
43.    <Row>  
44.     <Cell><Data ss:Type="String">zhangzehao</Data></Cell>  
45.    </Row>  
46.   </Table>  
47.   <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">  
48.    <Selected/>  
49.    <Panes>  
50.     <Pane>  
51.      <Number>3</Number>  
52.      <ActiveRow>5</ActiveRow>  
53.      <ActiveCol>3</ActiveCol>  
54.     </Pane>  
55.    </Panes>  
56.    <ProtectObjects>False</ProtectObjects>  
57.    <ProtectScenarios>False</ProtectScenarios>  
58.   </WorksheetOptions>  
59. </Worksheet>  
60. <Worksheet ss:Name="Sheet2">  
61.   <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"  
62.    x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>  
63.   <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">  
64.    <ProtectObjects>False</ProtectObjects>  
65.    <ProtectScenarios>False</ProtectScenarios>  
66.   </WorksheetOptions>  
67. </Worksheet>  
68. <Worksheet ss:Name="Sheet3">  
69.   <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"  
70.    x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>  
71.   <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">  
72.    <ProtectObjects>False</ProtectObjects>  
73.    <ProtectScenarios>False</ProtectScenarios>  
74.   </WorksheetOptions>  
75. </Worksheet>  
76. </Workbook>  

如果要生成千万级别以上的excel，除了这个关键点之外，还要控制IO流，如果有1000万记录，要迭代1000万次组装xml字符串，这样肯定占用相当大的内存，肯定内存溢出，所以，必须把组装的xml字符串分批用IO流刷新到硬盘里，如果是在web应用中，可以刷新到response中，web应用会自动把临时流保存到客户端的临时文件中，然后再一次性复制到你保存的路径。言归正传，分批刷新的话，可以迭代一批数据就flush进硬盘，同时把list，大对象赋值为空，显式调用垃圾回收器，表明要回收内存。这样的话，不管生成多大的数据量都不会出现内存溢出的，我曾经试过导出1亿的excel文件，都不会出现内存溢出，只是用了35分钟。
  当然，如果要把实现做的优雅一些，在组装xml字符串的时候，可以结合模板技术来实现，我个人喜好stringtemplate这个轻量级的框架，我给出的DEMO也是采用了模板技术生成的，当然velocity和freemarker都是可以，stringbuilder也行，呵呵。
   我为人比较懒，本意不是为了写个帖子的，只是想多赚点下载豆:lol1 ，这和赚钱一样谁不想？谁知道就写了那么多。同时鄙人知识寡陋，希望可以抛砖引玉。

综上：使用技术为 stringTemplate 

pom.xml：

[html]  view plain  copy  

1. <dependency>  
2.             <groupId>antlr</groupId>  
3.             <artifactId>antlr</artifactId>  
4.             <version>2.7.7</version>  
5.         </dependency>  
6.   
7.         <dependency>  
8.             <groupId>org.antlr</groupId>  
9.             <artifactId>stringtemplate</artifactId>  
10.             <version>3.2.1</version>  
11.         </dependency>  

template对象：

[java]  view plain  copy  

1. class Row{  
2.     private List<String> result;  
3.   
4.     public List<String> getResult() {  
5.         return result;  
6.     }  
7.   
8.     public void setResult(List<String> result) {  
9.         this.result = result;  
10.     }  
11. }  
12.   
13. class Worksheet{  
14.     private String sheet;  
15.       
16.     private int columnNum;  
17.       
18.     private int rowNum;  
19.   
20.     private List<String> title;  
21.       
22.     private List<Row> rows;  
23.   
24.     public String getSheet() {  
25.         return sheet;  
26.     }  
27.   
28.     public void setSheet(String sheet) {  
29.         this.sheet = sheet;  
30.     }  
31.   
32.     public List<Row> getRows() {  
33.         return rows;  
34.     }  
35.   
36.     public void setRows(List<Row> rows) {  
37.         this.rows = rows;  
38.     }  
39.   
40.     public int getColumnNum() {  
41.         return columnNum;  
42.     }  
43.   
44.     public void setColumnNum(int columnNum) {  
45.         this.columnNum = columnNum;  
46.     }  
47.   
48.     public int getRowNum() {  
49.         return rowNum;  
50.     }  
51.   
52.     public void setRowNum(int rowNum) {  
53.         this.rowNum = rowNum;  
54.     }  
55.   
56.     public List<String> getTitle() {  
57.         return title;  
58.     }  
59.   
60.     public void setTitle(List<String> title) {  
61.         this.title = title;  
62.     }  
63. }  

模版文件（通用）：

excel 头模板

[html]  view plain  copy  

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>  

body模板：

[html]  view plain  copy  

1.  $worksheet:{  
2.  <Worksheet ss:Name="$it.sheet$">  
3.   <Table ss:ExpandedColumnCount="$it.columnNum$" ss:ExpandedRowCount="$it.rowNum$" x:FullColumns="1"  
4.    x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">  
5.    <Row>  
6.    $it.title:{  
7.    <Cell><Data ss:Type="String">$it$</Data></Cell>  
8.    }$  
9.    </Row>  
10.  $it.rows:{  
11.  <Row>  
12.  $it.result:{  
13.  <Cell><Data ss:Type="String">$it$</Data></Cell>  
14.  }$  
15.    </Row>  
16.  }$  
17.   </Table>  
18.  </Worksheet>  
19. }$  

实际处理类：传入list对象，利用反射获取对象属性名及属性值

[java]  view plain  copy  

1.  long startTimne = System.currentTimeMillis();  
2.   
3.         StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");  
4.   
5.         //写入excel文件头部信息  
6.         StringTemplate head =  stGroup.getInstanceOf("head");  
7.         File file = new File("D:/output2.xls");  
8.         PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));  
9.         writer.print(head.toString());  
10.         writer.flush();  
11.   
12.         int totalRowNum = listWinningRecordDTOList.size();  
13.         int maxRowNum = 60000;  
14.         int sheets = totalRowNum % 60000 == 0 ? (totalRowNum/maxRowNum) : (totalRowNum/maxRowNum +1);  
15.         //excel单表最大行数是65535  
16.   
17.         List record = listWinningRecordDTOList;  
18.         List<String> title = new ArrayList<String>();  
19.         List<Method> getMethods = new ArrayList<Method>();  
20.         Class<?> clazz = record.get(0).getClass();  
21.   
22.         Field[] fields = clazz.getDeclaredFields();  
23.         if(fields != null && fields.length > 0){  
24.             for(Field field : fields){  
25.                 if(!"serialVersionUID".equals(field.getName())) {  
26.                     title.add(field.getName());  
27.                     getMethods.add(clazz.getDeclaredMethod("get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1)));  
28.                 }  
29.             }  
30.         }  
31. //        BeanInfo beanInfo=Introspector.getBeanInfo(clazz,Object.class);  
32. //        PropertyDescriptor[] proDescrtptors=beanInfo.getPropertyDescriptors();  
33. //        for(PropertyDescriptor propertyDescriptor : proDescrtptors){  
34. //            title.add(propertyDescriptor.getName());  
35. //            getMethods.add(propertyDescriptor.getReadMethod());  
36. //        }  
37.         int columnLength = title.size();  
38.   
39.         SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
40.   
41.         //写入excel文件数据信息  
42.         for(int i=0;i<sheets;i++){  
43.             StringTemplate body =  stGroup.getInstanceOf("body");  
44.             Worksheet worksheet = new Worksheet();  
45.             worksheet.setTitle(title);  
46.             worksheet.setSheet(" "+(i+1)+" ");  
47.             worksheet.setColumnNum(columnLength);  
48.             worksheet.setRowNum(maxRowNum+1);  
49.             List<Row> rows = new ArrayList<Row>();  
50.             int startIndex = i*maxRowNum;  
51.             int endIndex = Math.min((i+1)*maxRowNum -1,totalRowNum-1);  
52.             for(int j=startIndex;j<=endIndex;j++){  
53.                 Row row = new Row();  
54.                 List<String> result = new ArrayList<String>(columnLength);  
55.                 for(int n=0;n<columnLength;n++){  
56.                     Object value = getMethods.get(n).invoke(record.get(j));  
57.                     if(value == null){  
58.                         result.add("");  
59.                     }else{  
60.                         if(value instanceof Date){  
61.                             result.add(sdf.format((Date)value));  
62.                         }else{  
63.                             result.add(value.toString());  
64.                         }  
65.                     }  
66.   
67.                 }  
68.                 row.setResult(result);  
69.                 rows.add(row);  
70.             }  
71.             worksheet.setRows(rows);  
72.             body.setAttribute("worksheet", worksheet);  
73.             writer.print(body.toString());  
74.             writer.flush();  
75.             rows.clear();  
76.             rows = null;  
77.             worksheet = null;  
78.             body = null;  
79.             Runtime.getRuntime().gc();  
80.             System.out.println("正在生成excel文件的 sheet"+(i+1));  
81.         }  
82.   
83.         //写入excel文件尾部  
84.         writer.print("</Workbook>");  
85.         writer.flush();  
86.         writer.close();  
87.         System.out.println("生成excel文件完成");  
88.         long endTime = System.currentTimeMillis();  
89.         System.out.println("用时="+((endTime-startTimne)/1000)+"秒");  

整理后的公用类：

[java]  view plain  copy  

1. package com.dianping.emidas.activity.excel.util;  
2.   
3. import com.dianping.emidas.activity.excel.template.Row;  
4. import com.dianping.emidas.activity.excel.template.Sample;  
5. import com.dianping.emidas.activity.excel.template.Worksheet;  
6. import org.antlr.stringtemplate.StringTemplate;  
7. import org.antlr.stringtemplate.StringTemplateGroup;  
8.   
9. import java.io.*;  
10. import java.lang.reflect.Field;  
11. import java.lang.reflect.InvocationTargetException;  
12. import java.lang.reflect.Method;  
13. import java.text.SimpleDateFormat;  
14. import java.util.ArrayList;  
15. import java.util.Date;  
16. import java.util.List;  
17.   
18. /** 
19.  * Created by Administrator on 2016/2/25. 
20.  */  
21. public class ExcelStUtil {  
22.   
23.     public static void export(OutputStream outputStream,List target) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {  
24.         long startTime = System.currentTimeMillis();  
25.   
26.         StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");  
27.         //解决可能发生的中文乱码  
28.         stGroup.setFileCharEncoding("UTF-8");  
29.         //写入excel文件头部信息  
30.         StringTemplate head =  stGroup.getInstanceOf("st/head");  
31.         PrintWriter writer = new PrintWriter(new BufferedOutputStream(outputStream));  
32.         writer.print(head.toString());  
33.         writer.flush();  
34.   
35.         int totalRowNum = target.size();  
36.         int maxRowNum = 60000;  
37.         int sheets = totalRowNum % 60000 == 0 ? (totalRowNum/maxRowNum) : (totalRowNum/maxRowNum +1);  
38.         //excel单表最大行数是65535  
39.   
40.         List record = target;  
41.         List<String> title = new ArrayList<String>();  
42.         List<Method> getMethods = new ArrayList<Method>();  
43.         Class<?> clazz = record.get(0).getClass();  
44.   
45.         Field[] fields = clazz.getDeclaredFields();  
46.         if(fields != null && fields.length > 0){  
47.             for(Field field : fields){  
48.                 if(!"serialVersionUID".equals(field.getName())) {  
49.                     title.add(field.getName());  
50.                     getMethods.add(clazz.getDeclaredMethod("get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1)));  
51.                 }  
52.             }  
53.         }  
54. //        BeanInfo beanInfo=Introspector.getBeanInfo(clazz,Object.class);  
55. //        PropertyDescriptor[] proDescrtptors=beanInfo.getPropertyDescriptors();  
56. //        for(PropertyDescriptor propertyDescriptor : proDescrtptors){  
57. //            title.add(propertyDescriptor.getName());  
58. //            getMethods.add(propertyDescriptor.getReadMethod());  
59. //        }  
60.         int columnLength = title.size();  
61.   
62.         SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
63.   
64.         //写入excel文件数据信息  
65.         for(int i=0;i<sheets;i++){  
66.             StringTemplate body =  stGroup.getInstanceOf("st/body");  
67.             Worksheet worksheet = new Worksheet();  
68.             worksheet.setTitle(title);  
69.             worksheet.setSheet(" "+(i+1)+" ");  
70.             worksheet.setColumnNum(columnLength);  
71.             worksheet.setRowNum(maxRowNum+1);  
72.             List<Row> rows = new ArrayList<Row>();  
73.             int startIndex = i*maxRowNum;  
74.             int endIndex = Math.min((i+1)*maxRowNum -1,totalRowNum-1);  
75.             for(int j=startIndex;j<=endIndex;j++){  
76.                 Row row = new Row();  
77.                 List<String> result = new ArrayList<String>(columnLength);  
78.                 for(int n=0;n<columnLength;n++){  
79.                     Object value = getMethods.get(n).invoke(record.get(j));  
80.                     if(value == null){  
81.                         result.add("");  
82.                     }else{  
83.                         if(value instanceof Date){  
84.                             result.add(sdf.format((Date)value));  
85.                         }else{  
86.                             result.add(value.toString());  
87.                         }  
88.                     }  
89.   
90.                 }  
91.                 row.setResult(result);  
92.                 rows.add(row);  
93.             }  
94.             worksheet.setRows(rows);  
95.             body.setAttribute("worksheet", worksheet);  
96.             writer.print(body.toString());  
97.             writer.flush();  
98.             rows.clear();  
99.             rows = null;  
100.             worksheet = null;  
101.             body = null;  
102.             Runtime.getRuntime().gc();  
103.             System.out.println("正在生成excel文件的 sheet"+(i+1));  
104.         }  
105.   
106.         //写入excel文件尾部  
107.         writer.print("</Workbook>");  
108.         writer.flush();  
109.         writer.close();  
110.         System.out.println("生成excel文件完成");  
111.         long endTime = System.currentTimeMillis();  
112.         System.out.println("用时="+((endTime-startTime)/1000)+"秒");  
113.     }  
114.   
115.     public static void main(String[] args) throws IOException, NoSuchMethodException, IllegalAccessException, InvocationTargetException {  
116.         System.out.println(Thread.currentThread().getContextClassLoader().getResource("").getPath());  
117.         System.out.println(ExcelStUtil.class.getResource("").getPath());  
118.         System.out.println(ExcelStUtil.class.getClassLoader().getResource("").getPath());  
119.         List<Sample> result = new ArrayList<Sample>();  
120.         for(int i=0;i<100;i++){  
121.             result.add(new Sample("放大双方的"+String.valueOf(i),String.valueOf(i)));  
122.         }  
123.         //OutputStream outputStream = new FileOutputStream("D:/output2.xls");  
124.         ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();  
125.         ExcelStUtil.export(byteArrayOutputStream,result);  
126.         //ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());  
127.         //解决可能发生的中文乱码  
128.         ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(byteArrayOutputStream.toString().getBytes("UTF-8"));  
129.   
130.         File file = new File("D:/output2.xls");  
131.         OutputStream output = new FileOutputStream(file);  
132.         BufferedOutputStream bufferedOutput = new BufferedOutputStream(output);  
133.         //bufferedOutput.write(byteArrayOutputStream.toByteArray());  
134.         bufferedOutput.write(byteArrayOutputStream.toString().getBytes("UTF-8"));  
135.         bufferedOutput.flush();  
136.         bufferedOutput.close();  
137.   
138.     }  
139. }