JAVA导出数据到excel中大数据量的解决方法

web项目中需要有将数据导出excel的操作需求。曾经尝试过几种方法：

1、poi导出到excel

他是操作对象然后将结果写到excel中。

2、拼excel的html形式文件，将文件扩展名改为xls

这中方式是利用excel能够另存为html格式文件，文件包含excel的头信息。

 

以前用过的方法，没有考虑的问题

1、使用poi等导出时，没有考虑数据量的问题，大数据量无法满足，有个几千行jvm就哭了。

2、后改为拼html文件时，没有考虑excel中sheet的最大行数65536，超过这个行数就excel无法打开了。

 

综合考虑，并从网上找了些其他人的想法，最后用如下方案

继续沿用拼excel的html形式文件的方法，但在导出时，根据最大行数重新建立新的excel文件；

数据从数据库取出使用纯jdbc方式读数据，边读边向文件中写；

待所有数据写完，将各个小excel文件进行打包，输出到输出流中。

 

定义导出接口

 

[java] view plain copy
 
  1. import java.io.OutputStream;  
  2. import java.util.Collection;  
  3.   
  4. /** 
  5.  * 大数据量导出 
  6.  *  
  7.  * @author lisen 
  8.  *  
  9.  */  
  10. public interface ExportBigData {  
  11.     /** 
  12.      * 导出sql查询出的数据,输出为压缩后的格式 
  13.      *  
  14.      * @param os 
  15.      *            输出流 
  16.      * @param maxRow 
  17.      *            每个excel的最大行数 
  18.      * @param sql 
  19.      *            查询语句 
  20.      * @param sqlParams 
  21.      *            查询参数 
  22.      */  
  23.     public void exportToZip(final OutputStream os, final int maxRow,  
  24.             final String sql, final Object... sqlParams);  
  25.   
  26.     /** 
  27.      * 导出sql查询出的数据,输出为压缩后的格式 
  28.      *  
  29.      * @param titles 
  30.      *            标题 
  31.      * @param os 
  32.      *            输出流 
  33.      * @param maxRow 
  34.      *            每个excel的最大行数 
  35.      * @param sql 
  36.      *            查询语句 
  37.      * @param sqlParams 
  38.      *            查询参数 
  39.      */  
  40.     public void exportToZip(final Collection<String> titles,  
  41.             final OutputStream os, final int maxRow, final String sql,  
  42.             final Object... sqlParams);  
  43.   
  44.     public void exportToZip(final String[] titles, final OutputStream os,  
  45.             final int maxRow, final String sql, final Object... sqlParams);  
  46.   
  47.     public void exportToZip(final String[] titles, final OutputStream os,  
  48.             final String sql, final Object... sqlParams);  
  49.   
  50.     public void exportToZip(final Collection<String> titles,  
  51.             final OutputStream os, final String sql, final Object... sqlParams);  
  52.   
  53.     public void exportToZip(final OutputStream os, final String sql,  
  54.             final Object... sqlParams);  
  55. }  

 

 

大数据量导出抽象实现

 

[java] view plain copy
 
  1. import java.io.File;  
  2. import java.io.FileInputStream;  
  3. import java.io.FileOutputStream;  
  4. import java.io.IOException;  
  5. import java.io.OutputStream;  
  6. import java.sql.ResultSet;  
  7. import java.sql.SQLException;  
  8. import java.util.ArrayList;  
  9. import java.util.Arrays;  
  10. import java.util.Collection;  
  11. import java.util.Collections;  
  12. import java.util.List;  
  13. import java.util.zip.ZipEntry;  
  14. import java.util.zip.ZipOutputStream;  
  15.   
  16. import org.apache.commons.io.FileUtils;  
  17. import org.apache.commons.io.IOUtils;  
  18. import org.springframework.dao.DataAccessException;  
  19. import org.springframework.jdbc.core.JdbcTemplate;  
  20. import org.springframework.jdbc.core.ResultSetExtractor;  
  21. import org.springframework.jdbc.support.JdbcUtils;  
  22.   
  23. import com.cnaec.common.Constant;  
  24.   
  25. /** 
  26.  * 大数据量导出 抽象类 
  27.  *  
  28.  * @author lisen 
  29.  * @date 2013-10-30 上午10:40:33 
  30.  */  
  31. public abstract class AbstractExportBigData implements ExportBigData {  
  32.     /** 
  33.      * 每个文件的最大行数 超过请求按默认算 
  34.      */  
  35.     public static final int MAXROWS = 50000;  
  36.     /** 
  37.      * 用于数据查询 
  38.      */  
  39.     private JdbcTemplate jdbcTemplate;  
  40.   
  41.     /** 
  42.      * 构造函数 
  43.      *  
  44.      * @param jdbcTemplate 
  45.      */  
  46.     public AbstractExportBigData(JdbcTemplate jdbcTemplate) {  
  47.         this.jdbcTemplate = jdbcTemplate;  
  48.     }  
  49.   
  50.     /** 
  51.      * 临时文件前缀 
  52.      *  
  53.      * @return 
  54.      */  
  55.     abstract protected String getPrefix();  
  56.   
  57.     /** 
  58.      * 临时文件后缀 
  59.      *  
  60.      * @return 
  61.      */  
  62.     abstract protected String getSuffix();  
  63.   
  64.     /** 
  65.      * 删除临时文件 
  66.      *  
  67.      * @param fileList 
  68.      */  
  69.     protected void cleanTempFile(List<File> fileList) {  
  70.         for (File file : fileList) {  
  71.             System.out.println("-----删除文件," + file.getPath());  
  72.             file.delete();  
  73.         }  
  74.     }  
  75.   
  76.     /** 
  77.      * 创建临时文件 
  78.      *  
  79.      * @return 
  80.      * @throws IOException 
  81.      */  
  82.     protected File createTempFile() throws IOException {  
  83.         return File.createTempFile(getPrefix(), getSuffix());  
  84.     }  
  85.   
  86.     /** 
  87.      * 数据输出 
  88.      *  
  89.      * @param data 
  90.      * @param fos 
  91.      * @throws IOException 
  92.      */  
  93.     protected void writeToOutputStream(String data, FileOutputStream fos)  
  94.             throws IOException {  
  95.         IOUtils.write(data, fos, Constant.ENCODING);  
  96.     }  
  97.   
  98.     /** 
  99.      * 文件开头的写入 
  100.      *  
  101.      * @param fos 
  102.      * @throws IOException 
  103.      */  
  104.     abstract protected void writeHeaderToOutputStream(FileOutputStream fos)  
  105.             throws IOException;  
  106.   
  107.     /** 
  108.      * 文件结尾的写入 
  109.      *  
  110.      * @param fos 
  111.      */  
  112.     abstract protected void writeFooterToOutputStream(FileOutputStream fos)  
  113.             throws IOException;  
  114.   
  115.     /** 
  116.      * 一行数据的写入 
  117.      *  
  118.      * @param rs 
  119.      * @param fos 
  120.      * @throws SQLException 
  121.      * @throws IOException 
  122.      */  
  123.     abstract protected void writeOneRowToOutputStream(ResultSet rs,  
  124.             FileOutputStream fos) throws SQLException, IOException;  
  125.   
  126.     /** 
  127.      * 写数据标题 
  128.      *  
  129.      * @param titles 
  130.      * @param fos 
  131.      * @throws IOException 
  132.      */  
  133.     abstract protected void writeTitleToOutputStream(Collection<String> titles,  
  134.             FileOutputStream fos) throws IOException;  
  135.   
  136.     /** 
  137.      * 打包 压缩成zip 
  138.      *  
  139.      * @param os 
  140.      *            压缩输出流 
  141.      * @param fileList 
  142.      *            被压缩的文件列表 
  143.      * @throws IOException 
  144.      */  
  145.     protected void doZip(OutputStream os, List<File> fileList)  
  146.             throws IOException {  
  147.         if (fileList != null && fileList.size() > 0) {  
  148.             byte[] buf = new byte[1024];  
  149.             ZipOutputStream out = new ZipOutputStream(os);  
  150.             for (File file : fileList) {  
  151.                 FileInputStream in = new FileInputStream(file);  
  152.                 out.putNextEntry(new ZipEntry(file.getName()));  
  153.                 int len;  
  154.                 while ((len = in.read(buf)) > 0) {  
  155.                     out.write(buf, 0, len);  
  156.                 }  
  157.                 out.closeEntry();  
  158.                 in.close();  
  159.             }  
  160.             System.out.println(os == null);  
  161.             out.close();  
  162.             System.out.println(os == null);  
  163.         }  
  164.     }  
  165.   
  166.     /** 
  167.      * 获取单个文件最大行数 
  168.      *  
  169.      * @param maxRow 
  170.      * @return 
  171.      */  
  172.     protected int getMaxRows(int maxRow) {  
  173.         return maxRow < MAXROWS ? maxRow : MAXROWS;  
  174.     }  
  175.   
  176.     protected String getColumnKey(String columnName) {  
  177.         return columnName;  
  178.     }  
  179.   
  180.     protected Object getColumnValue(ResultSet rs, int index)  
  181.             throws SQLException {  
  182.         return JdbcUtils.getResultSetValue(rs, index);  
  183.     }  
  184.   
  185.     @Override  
  186.     public void exportToZip(final Collection<String> titles,  
  187.             final OutputStream os, final int maxRow, final String sql,  
  188.             final Object... sqlParams) {  
  189.         // 每个文件最大行数  
  190.         final int max = getMaxRows(maxRow);  
  191.         jdbcTemplate.query(sql, sqlParams, new ResultSetExtractor() {  
  192.             @Override  
  193.             public Object extractData(ResultSet rs) throws SQLException,  
  194.                     DataAccessException {  
  195.                 // 文件收集器  
  196.                 List<File> fileList = new ArrayList<File>();  
  197.                 // 行数记录器  
  198.                 int i = 0;  
  199.                 // 临时文件  
  200.                 File file = null;  
  201.                 FileOutputStream fos = null;  
  202.                 try {  
  203.                     while (rs.next()) {  
  204.                         // 达到最大行数 或者 新建的 创建新文件  
  205.                         if (i == max || i == 0) {  
  206.                             // 如果不是新文件 为这个文件写入文件尾  
  207.                             if (file != null) {  
  208.                                 // 写文件尾  
  209.                                 writeFooterToOutputStream(fos);  
  210.                                 // 关闭流  
  211.                                 IOUtils.closeQuietly(fos);  
  212.                             }  
  213.                             // 创建临时文件  
  214.                             file = createTempFile();  
  215.                             // 打开流  
  216.                             fos = FileUtils.openOutputStream(file);  
  217.                             // 放进收集器里  
  218.                             fileList.add(file);  
  219.                             // 写文件头  
  220.                             writeHeaderToOutputStream(fos);  
  221.                             // 数据区标题栏  
  222.                             writeTitleToOutputStream(titles, fos);  
  223.                             i = 0;  
  224.                         }  
  225.                         i++;  
  226.                         // 写实际一行数据  
  227.                         writeOneRowToOutputStream(rs, fos);  
  228.                     }  
  229.   
  230.                     if (file != null) {  
  231.                         // 写文件尾  
  232.                         writeFooterToOutputStream(fos);  
  233.                         // 关闭流  
  234.                         IOUtils.closeQuietly(fos);  
  235.                     }  
  236.                     // 打包  
  237.                     doZip(os, fileList);  
  238.                 } catch (IOException e) {  
  239.                     // io异常  
  240.                 } finally {  
  241.                     IOUtils.closeQuietly(fos);  
  242.                     // 清空临时文件  
  243.                     cleanTempFile(fileList);  
  244.                     fileList.clear();  
  245.                     fileList = null;  
  246.                 }  
  247.                 return null;  
  248.             }  
  249.         });  
  250.     }  
  251.   
  252.     @Override  
  253.     public void exportToZip(final OutputStream os, int maxRow, String sql,  
  254.             Object... sqlParams) {  
  255.         exportToZip(Collections.EMPTY_LIST, os, maxRow, sql, sqlParams);  
  256.     }  
  257.   
  258.     @Override  
  259.     public void exportToZip(final String[] titles, OutputStream os, int maxRow,  
  260.             String sql, Object... sqlParams) {  
  261.         exportToZip(Arrays.asList(titles), os, maxRow, sql, sqlParams);  
  262.     }  
  263.   
  264.     @Override  
  265.     public void exportToZip(final String[] titles, final OutputStream os,  
  266.             final String sql, final Object... sqlParams) {  
  267.         exportToZip(titles, os, MAXROWS, sql, sqlParams);  
  268.     }  
  269.   
  270.     @Override  
  271.     public void exportToZip(final OutputStream os, final String sql,  
  272.             final Object... sqlParams) {  
  273.         exportToZip(os, MAXROWS, sql, sqlParams);  
  274.     }  
  275.   
  276.     @Override  
  277.     public void exportToZip(final Collection<String> titles,  
  278.             final OutputStream os, final String sql, final Object... sqlParams) {  
  279.         exportToZip(titles, os, MAXROWS, sql, sqlParams);  
  280.     }  
  281. }  

 

 

 

EXCEL导出的实现

 

 
    1. import java.io.FileOutputStream;  
    2. import java.io.IOException;  
    3. import java.sql.ResultSet;  
    4. import java.sql.ResultSetMetaData;  
    5. import java.sql.SQLException;  
    6. import java.util.Collection;  
    7. import java.util.List;  
    8.   
    9. import org.springframework.jdbc.core.JdbcTemplate;  
    10. import org.springframework.web.util.HtmlUtils;  
    11.   
    12. import com.avicinfo.v2.common.export.bigdata.AbstractExportBigData;  
    13. import com.avicinfo.v2.common.export.bigdata.ExportBigData;  
    14.   
    15. /** 
    16.  * 用于excel大数据量导出 
    17.  *  
    18.  * @author lisen 
    19.  * @date 2013-10-30 上午10:41:36 
    20.  */  
    21. public class BigDataToExcelImpl extends AbstractExportBigData implements  
    22.         ExportBigData {  
    23.   
    24.     StringBuffer headStr = new StringBuffer(  
    25.             "<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">")  
    26.             .append("<head>")  
    27.             .append(  
    28.                     "<meta http-equiv=\"content-type\" content=\"application/ms-excel; charset=UTF-8\"/>")  
    29.             .append("<!--[if gte mso 9]><xml>").append("<x:ExcelWorkbook>")  
    30.             .append("<x:ExcelWorksheets>").append("<x:ExcelWorksheet>").append(  
    31.                     "<x:Name></x:Name>").append("<x:WorksheetOptions>").append(  
    32.                     "<x:Print>").append("<x:ValidPrinterInfo />").append(  
    33.                     "</x:Print>").append("</x:WorksheetOptions>").append(  
    34.                     "</x:ExcelWorksheet>").append("</x:ExcelWorksheets>")  
    35.             .append("</x:ExcelWorkbook>").append("</xml><![endif]-->").append(  
    36.                     "</head>").append("<body>").append("<table>");  
    37.   
    38.     StringBuffer footStr = new StringBuffer("</table></body></html>");  
    39.   
    40.     public BigDataToExcelImpl(JdbcTemplate jdbcTemplate) {  
    41.         super(jdbcTemplate);  
    42.     }  
    43.   
    44.     /** 
    45.      * 临时文件前缀 
    46.      *  
    47.      * @return 
    48.      */  
    49.     protected String getPrefix() {  
    50.         return "excelTemp";  
    51.     }  
    52.   
    53.     /** 
    54.      * 临时文件后缀 
    55.      *  
    56.      * @return 
    57.      */  
    58.     protected String getSuffix() {  
    59.         return ".xls";  
    60.     }  
    61.   
    62.     /** 
    63.      * 文件开头的写入 
    64.      *  
    65.      * @param fos 
    66.      * @throws IOException 
    67.      */  
    68.     protected void writeHeaderToOutputStream(FileOutputStream fos)  
    69.             throws IOException {  
    70.         writeToOutputStream(headStr.toString(), fos);  
    71.     }  
    72.   
    73.     /** 
    74.      * 文件结尾的写入 
    75.      *  
    76.      * @param fos 
    77.      */  
    78.     protected void writeFooterToOutputStream(FileOutputStream fos)  
    79.             throws IOException {  
    80.         writeToOutputStream(footStr.toString(), fos);  
    81.     }  
    82.   
    83.     /** 
    84.      * 一行数据的写入 
    85.      *  
    86.      * @param rs 
    87.      * @param fos 
    88.      * @throws SQLException 
    89.      * @throws IOException 
    90.      */  
    91.     protected void writeOneRowToOutputStream(ResultSet rs, FileOutputStream fos)  
    92.             throws SQLException, IOException {  
    93.         // 获取metaData;  
    94.         ResultSetMetaData rsmd = rs.getMetaData();  
    95.         int columnCount = rsmd.getColumnCount();  
    96.         writeToOutputStream("<tr>", fos);  
    97.         for (int i = 1; i <= columnCount; i++) {  
    98.             // String key = getColumnKey(rsmd.getColumnName(i));  
    99.             Object obj = getColumnValue(rs, i);  
    100.             writeToOutputStream("<td>"  
    101.                     + HtmlUtils.htmlEscape(obj == null ? "" : obj.toString())  
    102.                     + "</td>", fos);  
    103.         }  
    104.         writeToOutputStream("</tr>", fos);  
    105.     }  
    106.   
    107.     protected void fileOutputStreamStatus(List<FileOutputStream> foList)  
    108.             throws IOException {  
    109.         System.out.println("共有文件输出流：" + foList.size());  
    110.         for (FileOutputStream fo : foList) {  
    111.             System.out.println("文件输出流："  
    112.                     + (fo == null ? "已清空" : fo.toString() + " : "  
    113.                             + (fo.getFD().valid())));  
    114.         }  
    115.     }  
    116.   
    117.     @Override  
    118.     protected void writeTitleToOutputStream(Collection<String> titles,  
    119.             FileOutputStream fos) throws IOException {  
    120.         if (titles != null && titles.size() > 0) {  
    121.             writeToOutputStream("<tr>", fos);  
    122.             for (String title : titles) {  
    123.                 writeToOutputStream("<td>"  
    124.                         + HtmlUtils.htmlEscape(title == null ? "" : title)  
    125.                         + "</td>", fos);  
    126.             }  
    127.             writeToOutputStream("</tr>", fos);  
    128.         }  
    129.     }  
    130.   
    131. }  
posted @ 2017-08-04 09:55  orenal  阅读(216)  评论(0)  编辑  收藏  举报