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

Java 使用stringTemplate导出大批量数据excel（百万级）
目前java框架中能够生成excel文件的的确不少，但是，能够生成大数据量的excel框架，我倒是没发现，一般数据量大了都会出现内存溢出，所以，生成大数据量的excel文件要返璞归真，用java的基础技术,IO流来实现。如果想用IO流来生成excel文件，必须要知道excel的文件格式内容，相当于生成html文件一样，用字符串拼接html标签保存到文本文件就可以生成一个html文件了。同理，excel文件也是可以的。怎么知道excel的文件格式呢？其实很简单，随便新建一个excel文件，双击打开，然后点击“文件”-》“另存为”，保存的类型为“xml表格”，保存之后用文本格式打开，就可以看到excel的字符串格式一览无遗了。
把下面的xml字符串复制到文本文件，然后保存为xls格式，就是一个excel文件。
[html] view plain copy print ?
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>
```
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Created>1996-12-17T01:32:42Z</Created>
  <LastSaved>2000-11-18T06:53:49Z</LastSaved>
  <Version>11.9999</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <RemovePersonalInformation/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>4530</WindowHeight>
  <WindowWidth>8505</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <AcceptLabelsInFormulas/>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
</Styles>
<Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="2" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
   <Column ss:AutoFitWidth="0" ss:Width="73.5"/>
   <Row>
    <Cell><Data ss:Type="String">zhangzehao</Data></Cell>
    <Cell><Data ss:Type="String">zhangzehao</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">zhangzehao</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>5</ActiveRow>
     <ActiveCol>3</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
  <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
  <Table ss:ExpandedColumnCount="0" ss:ExpandedRowCount="0" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25"/>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
</Worksheet>
</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 print ?
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>
11. </dependency>
```
<dependency>
            <groupId>antlr</groupId>
            <artifactId>antlr</artifactId>
            <version>2.7.7</version>
        </dependency>

        <dependency>
            <groupId>org.antlr</groupId>
            <artifactId>stringtemplate</artifactId>
            <version>3.2.1</version>
        </dependency>
```
template对象：
[java] view plain copy print ?
1. class Row{
2. private List<String> result;
4. public List<String> getResult() {
5. return result;
6. }
8. public void setResult(List<String> result) {
9. this.result = result;
10. }
11. }
13. class Worksheet{
14. private String sheet;
16. private int columnNum;
18. private int rowNum;
20. private List<String> title;
22. private List<Row> rows;
24. public String getSheet() {
25. return sheet;
26. }
28. public void setSheet(String sheet) {
29. this.sheet = sheet;
30. }
32. public List<Row> getRows() {
33. return rows;
34. }
36. public void setRows(List<Row> rows) {
37. this.rows = rows;
38. }
40. public int getColumnNum() {
41. return columnNum;
42. }
44. public void setColumnNum(int columnNum) {
45. this.columnNum = columnNum;
46. }
48. public int getRowNum() {
49. return rowNum;
50. }
52. public void setRowNum(int rowNum) {
53. this.rowNum = rowNum;
54. }
56. public List<String> getTitle() {
57. return title;
58. }
60. public void setTitle(List<String> title) {
61. this.title = title;
62. }
63. }
```
class Row{
    private List<String> result;

    public List<String> getResult() {
        return result;
    }

    public void setResult(List<String> result) {
        this.result = result;
    }
}

class Worksheet{
	private String sheet;
	
	private int columnNum;
	
	private int rowNum;

    private List<String> title;
	
	private List<Row> rows;

	public String getSheet() {
		return sheet;
	}

	public void setSheet(String sheet) {
		this.sheet = sheet;
	}

	public List<Row> getRows() {
		return rows;
	}

	public void setRows(List<Row> rows) {
		this.rows = rows;
	}

	public int getColumnNum() {
		return columnNum;
	}

	public void setColumnNum(int columnNum) {
		this.columnNum = columnNum;
	}

	public int getRowNum() {
		return rowNum;
	}

	public void setRowNum(int rowNum) {
		this.rowNum = rowNum;
	}

    public List<String> getTitle() {
        return title;
    }

    public void setTitle(List<String> title) {
        this.title = title;
    }
}
```
模版文件（通用）：

excel 头模板
[html] view plain copy print ?
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>
```
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Created>1996-12-17T01:32:42Z</Created>
  <LastSaved>2013-08-02T09:21:24Z</LastSaved>
  <Version>11.9999</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <RemovePersonalInformation/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>4530</WindowHeight>
  <WindowWidth>8505</WindowWidth>
  <WindowTopX>480</WindowTopX>
  <WindowTopY>120</WindowTopY>
  <AcceptLabelsInFormulas/>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="宋体" x:CharSet="134" ss:Size="12"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
```
body模板：
[html] view plain copy print ?
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. }$
```
 $worksheet:{
 <Worksheet ss:Name="$it.sheet$">
  <Table ss:ExpandedColumnCount="$it.columnNum$" ss:ExpandedRowCount="$it.rowNum$" x:FullColumns="1"
   x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="14.25">
   <Row>
   $it.title:{
   <Cell><Data ss:Type="String">$it$</Data></Cell>
   }$
   </Row>
 $it.rows:{
 <Row>
 $it.result:{
 <Cell><Data ss:Type="String">$it$</Data></Cell>
 }$
   </Row>
 }$
  </Table>
 </Worksheet>
}$
```
实际处理类：传入list对象，利用反射获取对象属性名及属性值
[java] view plain copy print ?
1. long startTimne = System.currentTimeMillis();
3. StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");
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();
12. int totalRowNum = listWinningRecordDTOList.size();
13. int maxRowNum = 60000;
14. int sheets = totalRowNum % 60000 == 0 ? (totalRowNum/maxRowNum) : (totalRowNum/maxRowNum +1);
15. //excel单表最大行数是65535
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();
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();
39. SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
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. }
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. }
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)+"秒");
```
 long startTimne = System.currentTimeMillis();

        StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");

        //写入excel文件头部信息
        StringTemplate head =  stGroup.getInstanceOf("head");
        File file = new File("D:/output2.xls");
        PrintWriter writer = new PrintWriter(new BufferedOutputStream(new FileOutputStream(file)));
        writer.print(head.toString());
        writer.flush();

        int totalRowNum = listWinningRecordDTOList.size();
        int maxRowNum = 60000;
        int sheets = totalRowNum % 60000 == 0 ? (totalRowNum/maxRowNum) : (totalRowNum/maxRowNum +1);
        //excel单表最大行数是65535

        List record = listWinningRecordDTOList;
        List<String> title = new ArrayList<String>();
        List<Method> getMethods = new ArrayList<Method>();
        Class<?> clazz = record.get(0).getClass();

        Field[] fields = clazz.getDeclaredFields();
        if(fields != null && fields.length > 0){
            for(Field field : fields){
                if(!"serialVersionUID".equals(field.getName())) {
                    title.add(field.getName());
                    getMethods.add(clazz.getDeclaredMethod("get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1)));
                }
            }
        }
//        BeanInfo beanInfo=Introspector.getBeanInfo(clazz,Object.class);
//        PropertyDescriptor[] proDescrtptors=beanInfo.getPropertyDescriptors();
//        for(PropertyDescriptor propertyDescriptor : proDescrtptors){
//            title.add(propertyDescriptor.getName());
//            getMethods.add(propertyDescriptor.getReadMethod());
//        }
        int columnLength = title.size();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        //写入excel文件数据信息
        for(int i=0;i<sheets;i++){
            StringTemplate body =  stGroup.getInstanceOf("body");
            Worksheet worksheet = new Worksheet();
            worksheet.setTitle(title);
            worksheet.setSheet(" "+(i+1)+" ");
            worksheet.setColumnNum(columnLength);
            worksheet.setRowNum(maxRowNum+1);
            List<Row> rows = new ArrayList<Row>();
            int startIndex = i*maxRowNum;
            int endIndex = Math.min((i+1)*maxRowNum -1,totalRowNum-1);
            for(int j=startIndex;j<=endIndex;j++){
                Row row = new Row();
                List<String> result = new ArrayList<String>(columnLength);
                for(int n=0;n<columnLength;n++){
                    Object value = getMethods.get(n).invoke(record.get(j));
                    if(value == null){
                        result.add("");
                    }else{
                        if(value instanceof Date){
                            result.add(sdf.format((Date)value));
                        }else{
                            result.add(value.toString());
                        }
                    }

                }
                row.setResult(result);
                rows.add(row);
            }
            worksheet.setRows(rows);
            body.setAttribute("worksheet", worksheet);
            writer.print(body.toString());
            writer.flush();
            rows.clear();
            rows = null;
            worksheet = null;
            body = null;
            Runtime.getRuntime().gc();
            System.out.println("正在生成excel文件的 sheet"+(i+1));
        }

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

import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by Administrator on 2016/2/25.
 */
public class ExcelStUtil {

    public static void export(OutputStream outputStream,List target) throws NoSuchMethodException, InvocationTargetException, IllegalAccessException {
        long startTime = System.currentTimeMillis();

        StringTemplateGroup stGroup = new StringTemplateGroup("stringTemplate");
        //解决可能发生的中文乱码
        stGroup.setFileCharEncoding("UTF-8");
        //写入excel文件头部信息
        StringTemplate head =  stGroup.getInstanceOf("st/head");
        PrintWriter writer = new PrintWriter(new BufferedOutputStream(outputStream));
        writer.print(head.toString());
        writer.flush();

        int totalRowNum = target.size();
        int maxRowNum = 60000;
        int sheets = totalRowNum % 60000 == 0 ? (totalRowNum/maxRowNum) : (totalRowNum/maxRowNum +1);
        //excel单表最大行数是65535

        List record = target;
        List<String> title = new ArrayList<String>();
        List<Method> getMethods = new ArrayList<Method>();
        Class<?> clazz = record.get(0).getClass();

        Field[] fields = clazz.getDeclaredFields();
        if(fields != null && fields.length > 0){
            for(Field field : fields){
                if(!"serialVersionUID".equals(field.getName())) {
                    title.add(field.getName());
                    getMethods.add(clazz.getDeclaredMethod("get" + field.getName().substring(0, 1).toUpperCase() + field.getName().substring(1)));
                }
            }
        }
//        BeanInfo beanInfo=Introspector.getBeanInfo(clazz,Object.class);
//        PropertyDescriptor[] proDescrtptors=beanInfo.getPropertyDescriptors();
//        for(PropertyDescriptor propertyDescriptor : proDescrtptors){
//            title.add(propertyDescriptor.getName());
//            getMethods.add(propertyDescriptor.getReadMethod());
//        }
        int columnLength = title.size();

        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        //写入excel文件数据信息
        for(int i=0;i<sheets;i++){
            StringTemplate body =  stGroup.getInstanceOf("st/body");
            Worksheet worksheet = new Worksheet();
            worksheet.setTitle(title);
            worksheet.setSheet(" "+(i+1)+" ");
            worksheet.setColumnNum(columnLength);
            worksheet.setRowNum(maxRowNum+1);
            List<Row> rows = new ArrayList<Row>();
            int startIndex = i*maxRowNum;
            int endIndex = Math.min((i+1)*maxRowNum -1,totalRowNum-1);
            for(int j=startIndex;j<=endIndex;j++){
                Row row = new Row();
                List<String> result = new ArrayList<String>(columnLength);
                for(int n=0;n<columnLength;n++){
                    Object value = getMethods.get(n).invoke(record.get(j));
                    if(value == null){
                        result.add("");
                    }else{
                        if(value instanceof Date){
                            result.add(sdf.format((Date)value));
                        }else{
                            result.add(value.toString());
                        }
                    }

                }
                row.setResult(result);
                rows.add(row);
            }
            worksheet.setRows(rows);
            body.setAttribute("worksheet", worksheet);
            writer.print(body.toString());
            writer.flush();
            rows.clear();
            rows = null;
            worksheet = null;
            body = null;
            Runtime.getRuntime().gc();
            System.out.println("正在生成excel文件的 sheet"+(i+1));
        }

        //写入excel文件尾部
        writer.print("</Workbook>");
        writer.flush();
        writer.close();
        System.out.println("生成excel文件完成");
        long endTime = System.currentTimeMillis();
        System.out.println("用时="+((endTime-startTime)/1000)+"秒");
    }

    public static void main(String[] args) throws IOException, NoSuchMethodException, IllegalAccessException, InvocationTargetException {
        System.out.println(Thread.currentThread().getContextClassLoader().getResource("").getPath());
        System.out.println(ExcelStUtil.class.getResource("").getPath());
        System.out.println(ExcelStUtil.class.getClassLoader().getResource("").getPath());
        List<Sample> result = new ArrayList<Sample>();
        for(int i=0;i<100;i++){
            result.add(new Sample("放大双方的"+String.valueOf(i),String.valueOf(i)));
        }
        //OutputStream outputStream = new FileOutputStream("D:/output2.xls");
        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        ExcelStUtil.export(byteArrayOutputStream,result);
        //ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(byteArrayOutputStream.toByteArray());
        //解决可能发生的中文乱码
        ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(byteArrayOutputStream.toString().getBytes("UTF-8"));

        File file = new File("D:/output2.xls");
        OutputStream output = new FileOutputStream(file);
        BufferedOutputStream bufferedOutput = new BufferedOutputStream(output);
        //bufferedOutput.write(byteArrayOutputStream.toByteArray());
        bufferedOutput.write(byteArrayOutputStream.toString().getBytes("UTF-8"));
        bufferedOutput.flush();
        bufferedOutput.close();

    }
}
```
相关阅读:
url中特殊字符被转义成编码后如何处理
 解决：扰人的报错 Input length must be multiple of 8 when decrypting with padded cipher
使用fastjson 进行jsonObject转实体类对象
 Mybatis之foreach遍历Map
Map对象中的keyset()、entryset()和Map.Entry
jdk的keytool生成jks和获取jks的信息,公匙
 postgres如何不插入重复的值
 docker查看日志记录
 Git Bash输错账号密码如何重新输入
 IntellIJ IDEA 配置 Maven 以及修改默认 Repository
原文地址：https://www.cnblogs.com/firstdream/p/8961032.html