导出excel

问题描述：              

最近遇到一个很棘手的问题，信用卡网上支付的交易越来越多了，日平均交易数量很大，并且这个数据还处在一个上升的阶段，但这仅仅是个开始。我们这边的系统负责给柜员提供一系列报表，数据量比较大，时不时的会time out,或者会om掉。

原因分析：

Time out超时现象：这个是我们系统跨另外一个系统获取数据，我们所用技术的框架有个超时设置，即一般不能超过60秒。这样设置也合情合理，换作我是客户，我也不愿意在页面提交个请求后，要等很长时间才给我个结果。

这个问题瓶颈在于我们访问后台的时间瓶颈，即我们不能耗时太长，也就是说根本不可能要求修改框架设定的超时时间。

那么怎么解决呢？我们只有要求后台分批返回这些数据。

Outof Memory内存溢出现象：这个问题是当数据量比较大的时候，下载时这些大数据占用太多内存，JAVA虚拟机报出的错误。有个直接的但不长久的解决方法，就是增大JAVA虚拟机的内存设置，但随着数据量的增长，这个解决方法就会被淘汰。

解决方案分析：

Time out问题可以和提供接口方商讨，按照分页的方式即可解决。

我们重点关注一下OM的问题吧。这里有几种解决方案：

1.      用POI或JXL

POI是apache公司的一个子项目，主要提供一组windows文档的Java API；

JXL是Java Excel的简称，一个开放源码的项目，通过它Java开发人员可以操作Excel        文件的内容，可能它更专注些，比如名字就叫做java(不包含其他编程语言)的excel（不处理windows的其他文件），所以数据量稍大时效率以及在消耗内存方面，略胜前者一筹，这里我就不再赘述两者之间的差别了,在网络上大家可以搜索到一大箩筐关于此信息。

数据量	POI/JXL	加载时间（ms）	总耗时(ms)
1千条：746K	POI	499	515
	JXL	203	359
1万条：7.3M	POI	1716	1731
	JXL	390	1108
5万条：36.8M	POI	7878	7925
	JXL	2309	5711

 

我们来说说稍微好些的插件JXL的处理方式，JXL是将每个单元格生成一个Cell对象，每一个对象都要消耗一定的内存空间，每个对象的大小不同，也会影响内存的使用，所以很容易导致内存溢出。

2.用数据流的方法

从消耗内存的原因出发，既然上述表现较好的JXL由于生成大量的对象导致，我们就采用避免java创建太多的对象，就不用jxl了。用数据流的方法解决。

可以直接写txt文本文件，可以用”/t”来分割内容的话，用户就可以直接把txt生成的内        容直接复制到excel中去。

也可以用流的方式写成.csv格式，写入方式和写txt文件类似，可以用流的方式追加，只要在每列之间以固定标示符进行间隔，不存在内存和格式问题。这种格式可以让用户直接以exl方式打开，而不用复制进去了。

3.HTML方式

其实我们就是用页面元素的属性设置，来达到和xls相互转换，所以可以先写HTML，这样内存不会溢出，写好后，用xls方式打开即可。

不妨你可以试一试，打开一个xls，选择另存为网页，可以查看下这个经过转换后的html的源码，直接把这个HTML文件后缀改为xls，打开后效果和刚刚的那个xls一样。  

 

这种方式正是我们现在项目所采用的方式，但我们采用的是jsp默认的输出数据到页面，并没有通过java的io程序来实时控制，当数据量比较大的时候，进行适当的flush。所以目前的项目中这种方式，也存在着潜在的OM的可能。

 

实践验证：

数据流方式csv生成：

public class CSVUtils {

    /** 

     * 导出为CVS文件    

     * @param exportData 

     */ 

    public static File createCSVFile(List exportData, LinkedHashMap rowMapper, String outPutPath) {  

        File csvFile = null;  

        BufferedWriter csvFileOutputStream = null;  

        try {  

            csvFile = File.createTempFile("temp", ".csv", new File(outPutPath));  

            // GB2312使正确读取分隔符","  

            csvFileOutputStream = new BufferedWriter(new OutputStreamWriter(newFileOutputStream(csvFile), "GB2312"),   

                    1024);  

            // 写入文件头部  

            for (Iterator propertyIterator = rowMapper.entrySet().iterator(); propertyIterator.hasNext();) {  

                java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator.next();  

                csvFileOutputStream.write("/"" + propertyEntry.getValue().toString() + "/"");  

                if (propertyIterator.hasNext()) {  

                    csvFileOutputStream.write(",");  

                }  

            }   

            csvFileOutputStream.newLine();  

            // 写入文件内容  

            for (Iterator iterator = exportData.iterator(); iterator.hasNext();) {  

                Object row = (Object) iterator.next();  

                for (Iterator propertyIterator = rowMapper.entrySet().iterator(); propertyIterator.hasNext();) {  

                    java.util.Map.Entry propertyEntry = (java.util.Map.Entry) propertyIterator.next();  

                    csvFileOutputStream.write("/"" 

                            + BeanUtils.getProperty(row, propertyEntry.getKey().toString()).toString() + "/"");  

                    if (propertyIterator.hasNext()) {  

                        csvFileOutputStream.write(",");  

                    }  

                }  

                if (iterator.hasNext()) {  

                    csvFileOutputStream.newLine();  

                }  

            }  

            csvFileOutputStream.flush();  

        } catch (Exception e) {  

            e.printStackTrace();  

        } finally {  

            try {  

                csvFileOutputStream.close();  

            } catch (IOException e) {  

                e.printStackTrace();  

            }  

        }  

        return csvFile;  

    }      

}

 

用html方式：

 

/**

 *html方式导出数据

 *

 *这个格式怎样得到呢？方法是这样： 

 *你先建一个excel文件，如a.xls。填上两条伪数据。然后另存为网页，即htm格式，如a.htm。  

 *然后，用记事本打开htm格式的a.htm，这样excel文件格式代码就暴露在你面前。     

 *剩下的事，就是把a.htm源代码的伪数据部分，替成数据库里的数据，然后把替换后的整个a.htm源代码，用java的io写成一个后缀为xls的文件。就打完收工了。   

 *

 *注意：

 *为了不给内存增加压力，要把a.htm源代码分成三部分：头（伪数据部分 前的代码） + 伪数据部分 + 尾（伪数据部分 后的代码）。   

 *先把 头 写到文件，并flush。

 *然后是 伪数据部分 ,替一条数据库里的记录就写到文件里，并flush。

 *最后把 尾 写到文件，并flush。  

 */

public class htmlAndExcel {

   

    public static void exportToExcel(OutputStream out) throws Exception{   

 

        String str = "<html xmlns:o='urn:schemas-microsoft-com:office:office'"+  

            "xmlns:x='urn:schemas-microsoft-com:office:excel'"+  

            "xmlns='http://www.w3.org/TR/REC-html40'>"+  

            "<head>"+  

            "<meta http-equiv=Content-Type content='text/html; charset=gb2312'>"+  

            "<meta name=ProgId content=Excel.Sheet>"+  

            "<meta name=Generator content='Microsoft Excel 11'>"+  

            "<link rel=File-List href='1111.files/filelist.xml'>"+  

            "<link rel=Edit-Time-Data href='1111.files/editdata.mso'>"+  

            "<link rel=OLE-Object-Data href='1111.files/oledata.mso'>"+  

            "<!--[if gte mso 9]><xml>"+  

             "<o:DocumentProperties>"+  

              "<o:Created>1996-12-17T01:32:42Z</o:Created>"+  

              "<o:LastSaved>2010-03-17T06:50:36Z</o:LastSaved>"+  

              "<o:Version>11.5606</o:Version>"+  

             "</o:DocumentProperties>"+  

             "<o:OfficeDocumentSettings>"+  

              "<o:RemovePersonalInformation/>"+  

             "</o:OfficeDocumentSettings>"+  

            "</xml><![endif]-->"+  

            "<style>"+  

            "<!--table"+  

                "{mso-displayed-decimal-separator:'//.';"+  

                "so-displayed-thousand-separator:'//,';}"+  

            "@page"+  

                "{margin:1.0in .75in 1.0in .75in;"+  

                "mso-header-margin:.5in;"+  

                "mso-footer-margin:.5in;}"+  

            "tr"+  

                "{mso-height-source:auto;"+  

                "mso-ruby-visibility:none;}"+  

            "col"+  

                "{mso-width-source:auto;"+  

                "mso-ruby-visibility:none;}"+  

            "br"+  

                "{mso-data-placement:same-cell;}"+  

            ".style0"+  

                "{mso-number-format:General;"+  

                "text-align:general;"+  

                "vertical-align:bottom;"+  

                "white-space:nowrap;"+  

                "mso-rotate:0;"+  

                "mso-background-source:auto;"+  

                "mso-pattern:auto;"+  

                "color:windowtext;"+  

                "font-size:12.0pt;"+  

                "font-weight:400;"+  

                "font-style:normal;"+  

                "text-decoration:none;"+  

                "font-family:宋体;"+  

                "mso-generic-font-family:auto;"+  

                "mso-font-charset:134;"+  

                "border:none;"+  

                "mso-protection:locked visible;"+  

                "mso-style-name:常规;"+  

                "mso-style-id:0;}"+  

            "td"+  

                "{mso-style-parent:style0;"+  

                "padding-top:1px;"+  

                "padding-right:1px;"+  

                "padding-left:1px;"+  

                "mso-ignore:padding;"+  

                "color:windowtext;"+  

                "font-size:12.0pt;"+  

                "font-weight:400;"+  

                "font-style:normal;"+  

                "text-decoration:none;"+  

                "font-family:宋体;"+  

                "mso-generic-font-family:auto;"+  

                "mso-font-charset:134;"+  

                "mso-number-format:General;"+  

                "text-align:general;"+  

                "vertical-align:bottom;"+  

                "border:none;"+  

                "mso-background-source:auto;"+  

                "mso-pattern:auto;"+  

                "mso-protection:locked visible;"+  

                "white-space:nowrap;"+  

                "mso-rotate:0;}"+  

            "ruby"+  

                "{ruby-align:left;}"+  

            "rt"+  

                "{color:windowtext;"+  

                "font-size:9.0pt;"+  

                "font-weight:400;"+  

                "font-style:normal;"+  

                "text-decoration:none;"+  

                "font-family:宋体;"+  

                "mso-generic-font-family:auto;"+  

                "mso-font-charset:134;"+  

                "mso-char-type:none;"+  

                "display:none;}"+  

            "-->"+  

            "</style>"+  

            "<!--[if gte mso 9]><xml>"+  

             "<x:ExcelWorkbook>"+  

              "<x:ExcelWorksheets>"+  

               "<x:ExcelWorksheet>"+  

                "<x:Name>Sheet1</x:Name>"+  

                "<x:WorksheetOptions>"+  

                 "<x:DefaultRowHeight>285</x:DefaultRowHeight>"+  

                 "<x:CodeName>Sheet1</x:CodeName>"+  

                 "<x:Selected/>"+  

                 "<x:Panes>"+  

                  "<x:Pane>"+  

                   "<x:Number>3</x:Number>"+  

                   "<x:ActiveRow>4</x:ActiveRow>"+  

                   "<x:ActiveCol>4</x:ActiveCol>"+  

                  "</x:Pane>"+  

                 "</x:Panes>"+  

                 "<x:ProtectContents>False</x:ProtectContents>"+  

                 "<x:ProtectObjects>False</x:ProtectObjects>"+  

                 "<x:ProtectScenarios>False</x:ProtectScenarios>"+  

                "</x:WorksheetOptions>"+  

               "</x:ExcelWorksheet>"+  

               "<x:ExcelWorksheet>"+  

                "<x:Name>Sheet2</x:Name>"+  

                "<x:WorksheetOptions>"+  

                 "<x:DefaultRowHeight>285</x:DefaultRowHeight>"+  

                 "<x:CodeName>Sheet2</x:CodeName>"+  

                 "<x:ProtectContents>False</x:ProtectContents>"+  

                 "<x:ProtectObjects>False</x:ProtectObjects>"+  

                 "<x:ProtectScenarios>False</x:ProtectScenarios>"+  

                "</x:WorksheetOptions>"+  

               "</x:ExcelWorksheet>"+  

               "<x:ExcelWorksheet>"+  

                "<x:Name>Sheet3</x:Name>"+  

                "<x:WorksheetOptions>"+  

                 "<x:DefaultRowHeight>285</x:DefaultRowHeight>"+  

                 "<x:CodeName>Sheet3</x:CodeName>"+  

                 "<x:ProtectContents>False</x:ProtectContents>"+  

                 "<x:ProtectObjects>False</x:ProtectObjects>"+  

                 "<x:ProtectScenarios>False</x:ProtectScenarios>"+  

                "</x:WorksheetOptions>"+  

               "</x:ExcelWorksheet>"+  

              "</x:ExcelWorksheets>"+  

              "<x:WindowHeight>4530</x:WindowHeight>"+  

              "<x:WindowWidth>8505</x:WindowWidth>"+  

              "<x:WindowTopX>480</x:WindowTopX>"+  

              "<x:WindowTopY>120</x:WindowTopY>"+  

              "<x:AcceptLabelsInFormulas/>"+  

              "<x:ProtectStructure>False</x:ProtectStructure>"+  

              "<x:ProtectWindows>False</x:ProtectWindows>"+  

             "</x:ExcelWorkbook>"+  

            "</xml><![endif]-->"+  

            "</head>"+  

            "<body link=blue vlink=purple>";  

        str += "<table x:str border=0 cellpadding=0 cellspacing=0 width=144 style='border-collapse:collapse;table-layout:fixed;width:108pt'>"+  

             "<col width=72 span=2 style='width:54pt'>";  

        int count = 1000;  

        while (count>0) {  

            str += "<tr height=19 style='height:14.25pt'>"+  

              "<td height=19 align=right width=72 style='height:14.25pt;width:54pt' x:num>"+count+"</td>"+  

              "<td width=72 style='width:54pt'>"+"仅仅为了测试，^_^"+"</td>"+  

             "</tr>";  

            count--;

        }  

        str += "<![endif]>"+  

            "</table>"+  

            "</body>"+  

            "</html";  

        System.out.println(str);  

        out.write(str.getBytes());  

        out.close();    

    } 

}

 

结论：

综合上述几种分析，由于项目的特点，暂时采用读取内容到csv文件中，用户可以用xls方式打开。2003版本的excel单sheet是只能65535行,

我们在程序中判断，当达到10000条数据时，就要new sheet.