基于大量数据的Excel生成方案
以往我们在基于POI生成Excel文件时,都是利用官方提供的HSSF或XSSF对应的系列API,它们操作简便,上手比较快。但是对于大数据量的Excel文件生成往往会比较耗时,这是我们利用标准的API进行开发的一个痛点。对于性能更高一点的API,POI官方会建议我们使用SXSSF系列API,虽然它的性能比起HSSF和XSSF会有很大的提高,但是面对大量数据的时候还是会比较慢,为此官方还给我们提供了一种基于XML的方案。
其实对于一个Excel文件来说,最核心的是它的数据。Excel文件中的数据和样式文件是分开存储的,它们都对应于它自己体系中的一个XML文件。有兴趣的朋友可以把Excel文件的后缀名改成“.zip
”,然后用压缩文件把它解压缩,可以看到它里面的结构是由一堆的XML文件组成的。如果我们把解压缩后的文件再压缩成一个压缩文件,并把它的后缀名改为Excel文件对应的后缀名“.xlsx
”或“.xls
”,然后再用Excel程序把它打开。这个时候你会发现它也是可以打开的。笔者本文所要讲述的基于大量的数据生成Excel的方案就是基于这种XML文件的方案,它依赖于一个现有的Excel文件(这个Excel文件可以在运行时生成好),然后把我们的数据生成对应的XML表示,再把我们的XML替换原来的XML文件,再进行打包后就变成了一个Excel文件了。基于这种方式,笔者做了一个测试,生成了一个拥有3500万行,5列的Excel文件,该文件大小为1GB,耗时412秒。这种效率比起我们应用传统的API来说是指数倍的。
细节的实现详情,请读者自己参考以下示例代码,该示例代码是笔者从Apache官方下载的,原地址是https://svn.apache.org/repos/asf/poi/trunk/src/examples/src/org/apache/poi/xssf/usermodel/examples/BigGridDemo.java。需要注意的是生成的XML中需要应用到的样式需要事先生成,需要应用函数、合并单元格等逻辑的时候,可以先拿一个Excel文件应用对应的函数、合并逻辑,再把它解压缩后查看里面的XML文件的展现形式,然后自己拼接的时候也拼接成对应的形式,这样自己生成的Excel文件也会有对应的效果。
public class BigDataTest {
private static final String XML_ENCODING = "UTF-8";
public static void main(String[] args) throws Exception {
long start = System.currentTimeMillis();
// Step 1. Create a template file. Setup sheets and workbook-level objects such as
// cell styles, number formats, etc.
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("Big Grid");
Map<String, XSSFCellStyle> styles = createStyles(wb);
//name of the zip entry holding sheet data, e.g. /xl/worksheets/sheet1.xml
String sheetRef = sheet.getPackagePart().getPartName().getName();
//save the template
FileOutputStream os = new FileOutputStream("template.xlsx");
wb.write(os);
os.close();
//Step 2. Generate XML file.
File tmp = File.createTempFile("sheet", ".xml");
Writer fw = new OutputStreamWriter(new FileOutputStream(tmp), XML_ENCODING);
generate(fw, styles);
fw.close();
//Step 3. Substitute the template entry with the generated data
FileOutputStream out = new FileOutputStream("D:/big-grid2.xlsx");
//用心拼接生成的XML文件替换原来模板Excel文件中对应的XML文件,再压缩打包为一个Excel文件。
substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out);
out.close();
wb.close();
long end = System.currentTimeMillis();
System.out.println("耗时: " + (end - start));
}
/**
* Create a library of cell styles.
*/
private static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb){
Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>();
XSSFDataFormat fmt = wb.createDataFormat();
XSSFCellStyle style1 = wb.createCellStyle();
style1.setAlignment(HorizontalAlignment.RIGHT);
style1.setDataFormat(fmt.getFormat("0.0%"));
styles.put("percent", style1);
XSSFCellStyle style2 = wb.createCellStyle();
style2.setAlignment(HorizontalAlignment.CENTER);
style2.setDataFormat(fmt.getFormat("0.0X"));
styles.put("coeff", style2);
XSSFCellStyle style3 = wb.createCellStyle();
style3.setAlignment(HorizontalAlignment.RIGHT);
style3.setDataFormat(fmt.getFormat("$#,##0.00"));
styles.put("currency", style3);
XSSFCellStyle style4 = wb.createCellStyle();
style4.setAlignment(HorizontalAlignment.RIGHT);
style4.setDataFormat(fmt.getFormat("mmm dd"));
styles.put("date", style4);
XSSFCellStyle style5 = wb.createCellStyle();
XSSFFont headerFont = wb.createFont();
headerFont.setBold(true);
style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style5.setFillPattern(FillPatternType.SOLID_FOREGROUND);
style5.setFont(headerFont);
styles.put("header", style5);
return styles;
}
private static void generate(Writer out, Map<String, XSSFCellStyle> styles) throws Exception {
Random rnd = new Random();
Calendar calendar = Calendar.getInstance();
SpreadsheetWriter sw = new SpreadsheetWriter(out);
sw.beginSheet();
//insert header row
sw.insertRow(0);
int styleIndex = styles.get("header").getIndex();
sw.createCell(0, "Title", styleIndex);
sw.createCell(1, "% Change", styleIndex);
sw.createCell(2, "Ratio", styleIndex);
sw.createCell(3, "Expenses", styleIndex);
sw.createCell(4, "Date", styleIndex);
sw.endRow();
//write data rows
for (int rownum = 1; rownum < 100; rownum++) {
sw.insertRow(rownum);
sw.createCell(0, "Hello, " + rownum + "!");
sw.createCell(1, (double)rnd.nextInt(100)/100, styles.get("percent").getIndex());
sw.createCell(2, (double)rnd.nextInt(10)/10, styles.get("coeff").getIndex());
sw.createCell(3, rnd.nextInt(10000), styles.get("currency").getIndex());
sw.createCell(4, calendar, styles.get("date").getIndex());
sw.endRow();
calendar.roll(Calendar.DAY_OF_YEAR, 1);
}
sw.endSheet();
}
/**
*
* @param zipfile the template file
* @param tmpfile the XML file with the sheet data
* @param entry the name of the sheet entry to substitute, e.g. xl/worksheets/sheet1.xml
* @param out the stream to write the result to
*/
private static void substitute(File zipfile, File tmpfile, String entry, OutputStream out) throws IOException {
ZipFile zip = ZipHelper.openZipFile(zipfile);
try {
ZipOutputStream zos = new ZipOutputStream(out);
Enumeration<? extends ZipEntry> en = zip.entries();
while (en.hasMoreElements()) {
ZipEntry ze = en.nextElement();
if(!ze.getName().equals(entry)){
zos.putNextEntry(new ZipEntry(ze.getName()));
InputStream is = zip.getInputStream(ze);
copyStream(is, zos);
is.close();
}
}
zos.putNextEntry(new ZipEntry(entry));
InputStream is = new FileInputStream(tmpfile);
copyStream(is, zos);
is.close();
zos.close();
} finally {
zip.close();
}
}
private static void copyStream(InputStream in, OutputStream out) throws IOException {
byte[] chunk = new byte[1024];
int count;
while ((count = in.read(chunk)) >=0 ) {
out.write(chunk,0,count);
}
}
/**
* Writes spreadsheet data in a Writer.
* (YK: in future it may evolve in a full-featured API for streaming data in Excel)
*/
public static class SpreadsheetWriter {
private final Writer _out;
private int _rownum;
public SpreadsheetWriter(Writer out){
_out = out;
}
public void beginSheet() throws IOException {
_out.write("<?xml version=\"1.0\" encoding=\""+XML_ENCODING+"\"?>" +
"<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
_out.write("<sheetData>\n");
}
public void endSheet() throws IOException {
_out.write("</sheetData>");
_out.write("</worksheet>");
}
/**
* Insert a new row
*
* @param rownum 0-based row number
*/
public void insertRow(int rownum) throws IOException {
_out.write("<row r=\""+(rownum+1)+"\">\n");
this._rownum = rownum;
}
/**
* Insert row end marker
*/
public void endRow() throws IOException {
_out.write("</row>\n");
}
public void createCell(int columnIndex, String value, int styleIndex) throws IOException {
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\""+ref+"\" t=\"inlineStr\"");
if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
_out.write(">");
_out.write("<is><t>"+value+"</t></is>");
_out.write("</c>");
}
public void createCell(int columnIndex, String value) throws IOException {
createCell(columnIndex, value, -1);
}
public void createCell(int columnIndex, double value, int styleIndex) throws IOException {
String ref = new CellReference(_rownum, columnIndex).formatAsString();
_out.write("<c r=\""+ref+"\" t=\"n\"");
if(styleIndex != -1) _out.write(" s=\""+styleIndex+"\"");
_out.write(">");
_out.write("<v>"+value+"</v>");
_out.write("</c>");
}
public void createCell(int columnIndex, double value) throws IOException {
createCell(columnIndex, value, -1);
}
public void createCell(int columnIndex, Calendar value, int styleIndex) throws IOException {
createCell(columnIndex, DateUtil.getExcelDate(value, false), styleIndex);
}
}
}
(注:本文由Elim写于2017年6月5日)
相关推荐
这一工具对于需要处理大量数据和报告的业务分析师、财务人员以及任何需要批量创建工作簿的专业人士来说,都是一个极具价值的解决方案。该脚本利用了Python的几个关键库,如openpyxl来操作Excel文件,zipfile用于压缩...
在“Order”窗体生成订制信息,将订单和明细输出到“Excel”表。 将硬盘信息文件(HddInfo.xls)复制到将来保存数据库文件的计算机,采集该计算机的硬盘信息。 用户将上述保存订购信息的文件发送到开发者的信箱:...
而e表因为可以在一个类Excel的静态网格报表上定义无限多的扩展公式,运算报表时这些扩展公式同时扩展,每个扩展公式可以基于不同的数据集。它相当于有无限多个循环运算点。因此报表的表现力大大增强,可以说是复杂...
对存储在 varbinary(max) 或 image 列中的数据编制索引时,筛选器(实现了 IFilter 接口)将基于为该数据指定的文件格式(例如,Microsoft Word)来提取文本。在某些情况下,筛选器组件会要求将 varbinary(max) 或...
人工很难生成那些来自多个系统数据的大量电子邮件。同时,如果企业又涉及很多这方面的邮件,那么就需要考虑自动化了。 商业职能活动 市场营销 10-销售线索培养 潜在客户通过各种渠道获得,比如来自...
试验结果基于Excel文件输出,并可通过设定不同的迭代结束方式选择试验数据的输出方式,包括: 1. 输出随迭代次数变化的平均达优率数据(设定终止条件区间大于0)。 2. 输出随迭代次数变化的平均最优值数据(设定...
生成各类统计报表,可定制方案(人力资源软件) 查询非常方便,可以自定义方案 提供了演示数据库(人力资源软件) 支持数据导入,启用非常方便(见dll目录下导入模板) 支持自定义信息扩展 人力资源管理软件工资功能...
技术点 ADO.Net技术应用、SQLServer、MD5安全算法、基于NPOI的Excel文件处理、树状结构数据处理、递归、CodeSmith、代码生成器、三层架构。 项目说明 这是一个用WinForm技术实现的系统,传智播客在开课的半个月就...
为了帮助可视化此数据,已创建了一个用MATLAB编写的合作伙伴应用程序,该应用程序可生成此数据的图形。 此应用程序也包含在此存储库中,并以相同的许可证分发。 设置并忘记它 可以动态设置一系列试验,每个试验都...
学完本节后,你对基于建模技术的集如何引入模型会有一个基本的理解。 2.1 为什么使用集 集是LINGO建模语言的基础,是程序设计最强有力的基本构件。借助于集,能够用一个单一的、长的、简明的复合公式表示一系列相似...
10 <br>0023 如何添加引用第3方控件 11 <br>0024 如何生成DLL文件 11 <br>0025 如何使用不安全代码 11 <br>第2章 语言基础 13 <br>2.1 注释 14 <br>0026 如何对代码进行注释 14 ...
4:基于thinkphp MVC框架开发, 内置大量函数方便前台模板调用,大米拥有自己的万能标签与分类标签等方便调用数据 5:作站灵活,可以将该系统做成任何类型网站,内置新闻类型站、企业站、手机3g站模型,通过http://***安装...
注意:静态编译后常量数据位于PE文件的.rdata段中,只可读不可写,编程时请避免修改它们。譬如以下的代码,静态编译后就可能会出现问题: a = " " GetWindowTextA(hWnd, a, 20) 正确的代码为: a = 取空白文本 ...
Microsoft Project 2007是流行的基于网络的项目管理软件,在各类项目管理中发挥着巨大的作用。本书全面介绍了Microsoft Project 2007的强大功能和使用方法。首先从项目管理基础入手,然后建立具体的项目,介绍如何...
Microsoft Project 2007是流行的基于网络的项目管理软件,在各类项目管理中发挥着巨大的作用。本书全面介绍了Microsoft Project 2007的强大功能和使用方法。首先从项目管理基础入手,然后建立具体的项目,介绍如何...
Microsoft Project 2007是流行的基于网络的项目管理软件,在各类项目管理中发挥着巨大的作用。本书全面介绍了Microsoft Project 2007的强大功能和使用方法。首先从项目管理基础入手,然后建立具体的项目,介绍如何...
Microsoft Project 2007是流行的基于网络的项目管理软件,在各类项目管理中发挥着巨大的作用。本书全面介绍了Microsoft Project 2007的强大功能和使用方法。首先从项目管理基础入手,然后建立具体的项目,介绍如何...
Microsoft Project 2007是流行的基于网络的项目管理软件,在各类项目管理中发挥着巨大的作用。本书全面介绍了Microsoft Project 2007的强大功能和使用方法。首先从项目管理基础入手,然后建立具体的项目,介绍如何...
Microsoft Project 2007是流行的基于网络的项目管理软件,在各类项目管理中发挥着巨大的作用。本书全面介绍了Microsoft Project 2007的强大功能和使用方法。首先从项目管理基础入手,然后建立具体的项目,介绍如何...
Microsoft Project 2007是流行的基于网络的项目管理软件,在各类项目管理中发挥着巨大的作用。本书全面介绍了Microsoft Project 2007的强大功能和使用方法。首先从项目管理基础入手,然后建立具体的项目,介绍如何...