java导出大批量(百万以上)数据的excel文件
1.传统的导出方式会消耗大量的内存,2003每个sheet页最多65536条数据,2007每个sheet页可以达到100万条数据以上,2007会在生成Workbook时清理数据,所以2007导出量更大;
2.可以导出多个excel文件到某个目录中,然后打包下载;
3.导出excel格式的xml文件,这种方式可以分批导出数据,适用于大批量数据的导出,以下简单介绍这种方式:
代码如下:
1 package com.epay.utils; 2 3 /** 4 * 大数据量导出成EXCEL或XML 5 * @author qad 6 * 2017-04-22 7 */ 8 import java.io.BufferedOutputStream; 9 import java.io.DataOutputStream; 10 import java.io.File; 11 import java.io.FileNotFoundException; 12 import java.io.FileOutputStream; 13 import java.io.IOException; 14 15 public class Test { 16 public static void main(String[] args) { 17 StringBuffer sb = new StringBuffer(); 18 try { 19 DataOutputStream rafs = new DataOutputStream( 20 new BufferedOutputStream(new FileOutputStream(new File( 21 "d://test.xls")))); 22 sb.append("<?xml version=\"1.0\" encoding=\"GBK\" ?>"); 23 sb.append("\n"); 24 sb.append("<?mso-application progid=\"Excel.Sheet\"?>"); 25 sb.append("\n"); 26 sb.append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\""); 27 sb.append("\n"); 28 sb.append(" xmlns:o=\"urn:schemas-microsoft-com:office:office\""); 29 sb.append("\n"); 30 sb.append(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\""); 31 sb.append("\n"); 32 sb.append(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\""); 33 sb.append("\n"); 34 sb.append(" xmlns:html=\"http://www.w3.org/TR/REC-html40\">"); 35 sb.append("\n"); 36 sb.append("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\"> "); 37 sb.append("\n"); 38 sb.append(" <Styles>\n"); 39 sb.append(" <Style ss:ID=\"Default\" ss:Name=\"Normal\">\n"); 40 sb.append(" <Alignment ss:Vertical=\"Center\"/>\n"); 41 sb.append(" <Borders/>\n"); 42 sb.append(" <Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/>\n"); 43 sb.append(" <Interior/>\n"); 44 sb.append(" <NumberFormat/>\n"); 45 sb.append(" <Protection/>\n"); 46 sb.append(" </Style>\n"); 47 sb.append(" </Styles>\n"); 48 int sheetcount = 0; 49 int recordcount = 65535; 50 int currentRecord = 0; 51 int total = 100000; 52 int col = 20; 53 sb.append("<Worksheet ss:Name=\"Sheet0\">"); 54 sb.append("\n"); 55 sb.append("<Table ss:ExpandedColumnCount=\"" + col 56 + "\" ss:ExpandedRowCount=\"" + total 57 + "\" x:FullColumns=\"1\" x:FullRows=\"1\">"); 58 sb.append("\n"); 59 for (int i = 0; i < total; i++) { 60 if ((currentRecord == recordcount 61 || currentRecord > recordcount || currentRecord == 0) 62 && i != 0) {// 一个sheet写满 63 currentRecord = 0; 64 rafs.write(sb.toString().getBytes()); 65 sb.setLength(0); 66 sb.append("</Table>"); 67 sb.append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">"); 68 sb.append("\n"); 69 sb.append("<ProtectObjects>False</ProtectObjects>"); 70 sb.append("\n"); 71 sb.append("<ProtectScenarios>False</ProtectScenarios>"); 72 sb.append("\n"); 73 sb.append("</WorksheetOptions>"); 74 sb.append("\n"); 75 sb.append("</Worksheet>"); 76 sb.append("<Worksheet ss:Name=\"Sheet" + i / recordcount 77 + "\">"); 78 sb.append("\n"); 79 sb.append("<Table ss:ExpandedColumnCount=\"" + col 80 + "\" ss:ExpandedRowCount=\"" + recordcount 81 + "\" x:FullColumns=\"1\" x:FullRows=\"1\">"); 82 sb.append("\n"); 83 } 84 sb.append("<Row>"); 85 for (int j = 0; j < col; j++) { 86 System.out.println(i); 87 sb.append("<Cell><Data ss:Type=\"String\">111</Data></Cell>"); 88 sb.append("\n"); 89 } 90 sb.append("</Row>"); 91 if (i % 5000 == 0) { 92 rafs.write(sb.toString().getBytes()); 93 rafs.flush(); 94 sb.setLength(0); 95 } 96 sb.append("\n"); 97 currentRecord++; 98 } 99 rafs.write(sb.toString().getBytes()); 100 sb.setLength(0); 101 sb.append("</Table>"); 102 sb.append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">"); 103 sb.append("\n"); 104 sb.append("<ProtectObjects>False</ProtectObjects>"); 105 sb.append("\n"); 106 sb.append("<ProtectScenarios>False</ProtectScenarios>"); 107 sb.append("\n"); 108 sb.append("</WorksheetOptions>"); 109 sb.append("\n"); 110 sb.append("</Worksheet>"); 111 sb.append("</Workbook>"); 112 sb.append("\n"); 113 rafs.write(sb.toString().getBytes()); 114 rafs.flush(); 115 rafs.close(); 116 } catch (FileNotFoundException e) { 117 e.printStackTrace(); 118 } catch (IOException e) { 119 e.printStackTrace(); 120 } 121 } 122 }
导出xml文件之后直接修改后缀名为.xlsx就可以直接打开,弊端:无法导出.xls格式的excel文件.以上只是一种简单想法,留待以后具体实现.
扩展:既然可以使用xml文件导出excel,那么导出csv文件之后也可以直接改后缀名为.xls,目前只是一种想法,尚未证实可行性.
