1、直接用JXL的WORKBOOK写,使用数据量较小,但是比较灵活,一万条以内效果很好。
2、写csv文件,再用EXCEL模式打开,或者修改后缀为xls文件。适用于大数据量,但是只支持单个工作表(页签),效率高,适用于大数据量。
3、写xml文件,再用EXCEL模式打开,或者修改后缀为xls文件。适用于任何模式,并且支持单个EXCEL文件多个工作表。我在工作中有遇到过这种问题,需要把几十万的数据从内存中保存为EXCEL文件,使用这种方式能有效的减少内存消耗,并提升响应速度,增强用户体验。不过无法用常规的EXCEL解析方式去解析。
/**
* 测试写XML文件为EXCEL文件
*/
public static void testWriteXml2Excel() {
StringBuffer sb = new StringBuffer("");
//组装EXCEL标准XML格式表头
sb.append("<?xml version=\"1.0\" encoding=\"GBK\" ?> ");
sb.append("\n");
sb.append("<?mso-application progid=\"Excel.Sheet\"?>");
sb.append("\n");
sb.append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
sb.append("\n");
sb.append("xmlns:o=\"urn:schemas-microsoft-com:office:office\" ");
sb.append("\n");
sb.append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");
sb.append("\n");
sb.append("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
sb.append("\n");
sb.append("xmlns:html=\"http://www.w3.org/TR/REC-html40\"> ");
sb.append("\n");
sb.append("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\"> ");
sb.append("\n");
sb.append("<Version>11.9999</Version> ");
sb.append("\n");
sb.append("</DocumentProperties> ");
sb.append("\n");
sb.append("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\"> ");
sb.append("\n");
sb.append("<WindowHeight>10005</WindowHeight> ");
sb.append("\n");
sb.append("<WindowWidth>10005</WindowWidth> ");
sb.append("\n");
sb.append("<WindowTopX>120</WindowTopX> ");
sb.append("\n");
sb.append("<WindowTopY>135</WindowTopY> ");
sb.append("\n");
sb.append("<ActiveSheet>1</ActiveSheet> ");
sb.append("\n");
sb.append("<ProtectStructure>False</ProtectStructure> ");
sb.append("\n");
sb.append("<ProtectWindows>False</ProtectWindows> ");
sb.append("\n");
sb.append("</ExcelWorkbook> ");
sb.append("\n");
sb.append("<Styles> ");
sb.append("\n");
sb.append("<Style ss:ID=\"Default\" ss:Name=\"Normal\"> ");
sb.append("\n");
sb.append("<Alignment ss:Vertical=\"Center\"/> ");
sb.append("\n");
sb.append("<Borders/> ");
sb.append("\n");
sb.append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/> ");
sb.append("\n");
sb.append("<Interior/> ");
sb.append("\n");
sb.append("<NumberFormat/> ");
sb.append("\n");
sb.append("<Protection/> ");
sb.append("\n");
sb.append("</Style> ");
sb.append("\n");
sb.append("<Style ss:ID=\"s23\"> ");
sb.append("\n");
sb.append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\" ss:Color=\"#363636\" ");
sb.append("\n");
sb.append("ss:Bold=\"1\"/> ");
sb.append("\n");
sb.append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/> ");
sb.append("\n");
sb.append("</Style> ");
sb.append("\n");
sb.append("</Styles> ");
sb.append("\n");
//封装 workSheet内容
sb.append("<Worksheet ss:Name=\"Sheet2\"> ");
sb.append("\n");
//表格属性
sb.append("<Table ss:ExpandedColumnCount=\"2\" ss:ExpandedRowCount=\"2\" x:FullColumns=\"1\" ");
sb.append("\n");
sb.append("x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\"> ");
sb.append("\n");
sb.append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"73.5\"/>");
sb.append("\n");
sb.append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"119.25\"/> ");
sb.append("\n");
//内容 第1、2、3...行
sb.append("<Row> ");
sb.append("\n");
sb.append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"String\">aaaa</Data></Cell> ");
sb.append("\n");
sb.append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"String\">bbbb</Data></Cell> ");
sb.append("\n");
sb.append("</Row> ");
sb.append("\n");
sb.append("<Row> ");
sb.append("\n");
sb.append("<Cell><Data ss:Type=\"String\">aaaa</Data></Cell> ");
sb.append("\n");
sb.append("<Cell><Data ss:Type=\"String\">bbbb</Data></Cell> ");
sb.append("\n");
sb.append("</Row> ");
sb.append("\n");
sb.append("</Table> ");
sb.append("\n");
sb.append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\"> ");
sb.append("\n");
sb.append("<Print> ");
sb.append("\n");
sb.append("<ValidPrinterInfo/> ");
sb.append("\n");
sb.append("<PaperSizeIndex>9</PaperSizeIndex> ");
sb.append("\n");
sb.append("<HorizontalResolution>600</HorizontalResolution> ");
sb.append("\n");
sb.append("<VerticalResolution>600</VerticalResolution> ");
sb.append("\n");
sb.append("</Print> ");
sb.append("\n");
sb.append("<Selected/> ");
sb.append("\n");
sb.append("<ProtectObjects>False</ProtectObjects> ");
sb.append("\n");
sb.append("<ProtectScenarios>False</ProtectScenarios>");
sb.append("\n");
sb.append("</WorksheetOptions> ");
sb.append("\n");
//内容结束
sb.append("</Worksheet> ");
sb.append("\n");
sb.append("</Workbook> ");
DataOutputStream rafs = null;
try {
rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(new File("textxml.xml"))));
rafs.write(sb.toString().getBytes());
rafs.flush();
313
rafs.close();
FileUtils.copyFile(new File("textxml.xml"), new File("textxml.xls"));
} catch (Exception e) {
System.out.println(e);
}
}
2、写csv文件,再用EXCEL模式打开,或者修改后缀为xls文件。适用于大数据量,但是只支持单个工作表(页签),效率高,适用于大数据量。
3、写xml文件,再用EXCEL模式打开,或者修改后缀为xls文件。适用于任何模式,并且支持单个EXCEL文件多个工作表。我在工作中有遇到过这种问题,需要把几十万的数据从内存中保存为EXCEL文件,使用这种方式能有效的减少内存消耗,并提升响应速度,增强用户体验。不过无法用常规的EXCEL解析方式去解析。
示例,将文件内容在代码中拼装成如下格式,再改后缀名为excel。
例如拷贝下面这串xml标准的字符串到一个记事本中,然后保存后缀为xls或xlsx格式的文件,打开时选择方式为excel即可。下面这个代码只是测试,必须要根据实际情况对代码进行提取重构。
伪代码:/**
* 测试写XML文件为EXCEL文件
*/
public static void testWriteXml2Excel() {
StringBuffer sb = new StringBuffer("");
//组装EXCEL标准XML格式表头
sb.append("<?xml version=\"1.0\" encoding=\"GBK\" ?> ");
sb.append("\n");
sb.append("<?mso-application progid=\"Excel.Sheet\"?>");
sb.append("\n");
sb.append("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
sb.append("\n");
sb.append("xmlns:o=\"urn:schemas-microsoft-com:office:office\" ");
sb.append("\n");
sb.append("xmlns:x=\"urn:schemas-microsoft-com:office:excel\" ");
sb.append("\n");
sb.append("xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\" ");
sb.append("\n");
sb.append("xmlns:html=\"http://www.w3.org/TR/REC-html40\"> ");
sb.append("\n");
sb.append("<DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\"> ");
sb.append("\n");
sb.append("<Version>11.9999</Version> ");
sb.append("\n");
sb.append("</DocumentProperties> ");
sb.append("\n");
sb.append("<ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\"> ");
sb.append("\n");
sb.append("<WindowHeight>10005</WindowHeight> ");
sb.append("\n");
sb.append("<WindowWidth>10005</WindowWidth> ");
sb.append("\n");
sb.append("<WindowTopX>120</WindowTopX> ");
sb.append("\n");
sb.append("<WindowTopY>135</WindowTopY> ");
sb.append("\n");
sb.append("<ActiveSheet>1</ActiveSheet> ");
sb.append("\n");
sb.append("<ProtectStructure>False</ProtectStructure> ");
sb.append("\n");
sb.append("<ProtectWindows>False</ProtectWindows> ");
sb.append("\n");
sb.append("</ExcelWorkbook> ");
sb.append("\n");
sb.append("<Styles> ");
sb.append("\n");
sb.append("<Style ss:ID=\"Default\" ss:Name=\"Normal\"> ");
sb.append("\n");
sb.append("<Alignment ss:Vertical=\"Center\"/> ");
sb.append("\n");
sb.append("<Borders/> ");
sb.append("\n");
sb.append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\"/> ");
sb.append("\n");
sb.append("<Interior/> ");
sb.append("\n");
sb.append("<NumberFormat/> ");
sb.append("\n");
sb.append("<Protection/> ");
sb.append("\n");
sb.append("</Style> ");
sb.append("\n");
sb.append("<Style ss:ID=\"s23\"> ");
sb.append("\n");
sb.append("<Font ss:FontName=\"宋体\" x:CharSet=\"134\" ss:Size=\"12\" ss:Color=\"#363636\" ");
sb.append("\n");
sb.append("ss:Bold=\"1\"/> ");
sb.append("\n");
sb.append("<Interior ss:Color=\"#C0C0C0\" ss:Pattern=\"Solid\"/> ");
sb.append("\n");
sb.append("</Style> ");
sb.append("\n");
sb.append("</Styles> ");
sb.append("\n");
//封装 workSheet内容
sb.append("<Worksheet ss:Name=\"Sheet2\"> ");
sb.append("\n");
//表格属性
sb.append("<Table ss:ExpandedColumnCount=\"2\" ss:ExpandedRowCount=\"2\" x:FullColumns=\"1\" ");
sb.append("\n");
sb.append("x:FullRows=\"1\" ss:DefaultColumnWidth=\"54\" ss:DefaultRowHeight=\"14.25\"> ");
sb.append("\n");
sb.append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"73.5\"/>");
sb.append("\n");
sb.append("<Column ss:AutoFitWidth=\"0\" ss:Width=\"119.25\"/> ");
sb.append("\n");
//内容 第1、2、3...行
sb.append("<Row> ");
sb.append("\n");
sb.append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"String\">aaaa</Data></Cell> ");
sb.append("\n");
sb.append("<Cell ss:StyleID=\"s23\"><Data ss:Type=\"String\">bbbb</Data></Cell> ");
sb.append("\n");
sb.append("</Row> ");
sb.append("\n");
sb.append("<Row> ");
sb.append("\n");
sb.append("<Cell><Data ss:Type=\"String\">aaaa</Data></Cell> ");
sb.append("\n");
sb.append("<Cell><Data ss:Type=\"String\">bbbb</Data></Cell> ");
sb.append("\n");
sb.append("</Row> ");
sb.append("\n");
sb.append("</Table> ");
sb.append("\n");
sb.append("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\"> ");
sb.append("\n");
sb.append("<Print> ");
sb.append("\n");
sb.append("<ValidPrinterInfo/> ");
sb.append("\n");
sb.append("<PaperSizeIndex>9</PaperSizeIndex> ");
sb.append("\n");
sb.append("<HorizontalResolution>600</HorizontalResolution> ");
sb.append("\n");
sb.append("<VerticalResolution>600</VerticalResolution> ");
sb.append("\n");
sb.append("</Print> ");
sb.append("\n");
sb.append("<Selected/> ");
sb.append("\n");
sb.append("<ProtectObjects>False</ProtectObjects> ");
sb.append("\n");
sb.append("<ProtectScenarios>False</ProtectScenarios>");
sb.append("\n");
sb.append("</WorksheetOptions> ");
sb.append("\n");
//内容结束
sb.append("</Worksheet> ");
sb.append("\n");
sb.append("</Workbook> ");
DataOutputStream rafs = null;
try {
rafs = new DataOutputStream(new BufferedOutputStream(new FileOutputStream(new File("textxml.xml"))));
rafs.write(sb.toString().getBytes());
rafs.flush();
313
rafs.close();
FileUtils.copyFile(new File("textxml.xml"), new File("textxml.xls"));
} catch (Exception e) {
System.out.println(e);
}
}