- 浏览: 39514 次
- 性别:
- 来自: 深圳
文章分类
最新评论
import java.io.*; import java.util.*; import java.util.zip.ZipEntry; import java.util.zip.ZipFile; import java.util.zip.ZipOutputStream; import org.apache.poi.ss.usermodel.DateUtil; import org.apache.poi.ss.usermodel.IndexedColors; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.usermodel.*; /** * Demonstrates a workaround you can use to generate large workbooks and avoid OutOfMemory exception. * * The trick is as follows: * 1. create a template workbook, create sheets and global objects such as cell styles, number formats, etc. * 2. create an application that streams data in a text file * 3. Substitute the sheet in the template with the generated data * * <p> * Since 3.8-beta3 POI provides a low-memory footprint SXSSF API which implementing the "BigGridDemo" strategy. * XSSF is an API-compatible streaming extension of XSSF to be used when * very large spreadsheets have to be produced, and heap space is limited. * SXSSF achieves its low memory footprint by limiting access to the rows that * are within a sliding window, while XSSF gives access to all rows in the * document. Older rows that are no longer in the window become inaccessible, * as they are written to the disk. * </p> * See <a "http://poi.apache.org/spreadsheet/how-to.html#sxssf"> * http://poi.apache.org/spreadsheet/how-to.html#sxssf</a>. * * @author Yegor Kozlov */ public class BigGridDemo { private static final String XML_ENCODING = "UTF-8"; public static void main(String[] args) throws Exception { // 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("big-grid.xlsx"); substitute(new File("template.xlsx"), tmp, sheetRef.substring(1), out); out.close(); } /** * 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(XSSFCellStyle.ALIGN_RIGHT); style1.setDataFormat(fmt.getFormat("0.0%")); styles.put("percent", style1); XSSFCellStyle style2 = wb.createCellStyle(); style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); style2.setDataFormat(fmt.getFormat("0.0X")); styles.put("coeff", style2); XSSFCellStyle style3 = wb.createCellStyle(); style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style3.setDataFormat(fmt.getFormat("$#,##0.00")); styles.put("currency", style3); XSSFCellStyle style4 = wb.createCellStyle(); style4.setAlignment(XSSFCellStyle.ALIGN_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(XSSFCellStyle.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 < 100000; 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 = new ZipFile(zipfile); ZipOutputStream zos = new ZipOutputStream(out); @SuppressWarnings("unchecked") Enumeration<ZipEntry> en = (Enumeration<ZipEntry>) 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(); } 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); } } }
摘自http://blog.csdn.net/xiaowenjie/article/details/8846982
发表评论
-
mysql去除重复
2012-11-27 18:09 938今天在做个联系人功能时,要做导入去重复的功能 假设有10万个联 ... -
定时器
2012-01-30 11:40 973现在常用到两种定时器,做下备忘 有Spring的 < ... -
Struts标签之<s:optiontransferselect/>
2011-05-16 12:27 1728<s:optiontransferselect n ... -
test
2011-04-28 14:39 0testtest -
ant 找不到符号
2011-04-27 11:39 4241第一次用ANT打包,报找不到符号,找不到类,大部分是 ja ... -
Base64Coding
2010-12-17 10:08 979由于环境原因,经常使用HTTP或WebService接口,BA ... -
WebService备忘
2010-11-26 14:42 7831,new webservice project 2,new ... -
ibatis的模糊查询 like
2010-11-25 14:00 937<isNotNull prepend=" ... -
java解析XML
2010-11-15 17:47 897package resource; import jav ... -
一个LIST中的重复与不重复
2010-10-21 16:37 2065去掉一个LIST的重复 Set someSet = new H ... -
各种集合笔记
2010-10-21 16:27 812public class JAVAContainer { ... -
Struts2标签 增加序号列
2010-09-28 10:36 2109有的时候由于特殊需要,得在列前再多一列序号列,用Struts2 ... -
MAP传参需仔细
2010-09-19 18:04 1201在PO中 private Date birthday; ... -
struts2 格式化数字,可扩展四舍五入
2010-08-06 08:38 1917struts.properties中添加 FormatNume ... -
HTTP Status 404 - No result defined for action
2010-08-02 17:42 1791No result defined for action an ...
相关推荐
POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI导出POI...
标题中的"poi全家桶ooxml-schemas/poi/poi-examples/poi-ooxml/poi-ooxml-schemas/poi-scratchpad"提及的是Apache POI项目中的多个关键组件和目录结构。Apache POI是一个开源的Java库,专门用于读写Microsoft Office...
Apache POI 是一个开源项目,专门用于处理Microsoft Office格式的文件,如Excel(.xlsx、.xls)、Word(.doc、.docx)和PowerPoint(.ppt、.pptx)。这个压缩包包含了POI项目中所有必要的jar包,总计十二个,确保了...
这个压缩包包含了POI项目的几个核心组件的jar文件,包括基础的`poi`库,以及支持Open XML格式(OOXML)的`poi-ooxml`和`poi-ooxml-schemas`。这些jar包在Java开发中非常有用,尤其是当你需要在应用程序中读取、写入...
Apache POI 是一个开源项目,专门用于处理微软的Office文档格式,如Excel、Word和PowerPoint。POI 提供了一套 Java API,使得开发者可以在Java应用程序中读写Microsoft Office格式的文件。标题中的"poi-3.17 poi-...
Apache POI 是一个开源项目,专门用于处理微软的Office文档格式,如Excel、Word和PowerPoint。在Java编程环境中,Apache POI 提供了API,让开发者能够方便地读取、写入和修改这些文件。标题提到的"poi-3.9、poi-...
Apache POI是一个开源项目,主要用于读取和写入Microsoft Office格式的文件,如Word(.doc/.docx)、Excel(.xls/.xlsx)和PowerPoint(.ppt/.pptx)。这个“POI中文帮助文档”提供了全面的指南,帮助开发者理解和...
关于poi ,Apache在今年,也就是在2017年9月15日 正式发布了POI 3.17版本, Apache POI团队对于3.17版本进行了一些功能的修复。主要是几个新的功能区域和许多错误修复。 POI 3.17是支持Java 6的最后一个版本。下一个...
在IT行业中,POI(Point of Interest)是一个重要的概念,主要指地图上的兴趣点,例如商业场所、公共服务设施、交通站点等。百度POI和高德POI是两大知名的中国地图服务提供商,它们提供了丰富的地理信息数据,为...
Apache POI 是一个开源项目,专门用于处理微软的Office文档格式,如Excel、Word和PowerPoint。这个"poi-3.10.1上传组件jar包"包含了处理这些文档所需的Java库,使得开发者可以在Java应用中创建、读取和修改Office...
### POI Excel知识点详解 #### 一、Jakarta POI简介与Apache POI的作用 Jakarta POI 是 Apache POI 的早期项目名称,它提供了一组 API 来处理 Microsoft Office 文件格式,特别是针对 Excel(`.xls` 和 `.xlsx`)...
poi3.17jar包下载,支持excel2003,excel2007,亲测可用。poi3.17jar包下载,支持excel2003,excel2007,亲测可用。poi3.17jar包下载,支持excel2003,excel2007,亲测可用。poi3.17jar包下载,支持excel2003,excel2007...
Java中的Apache POI库是用于处理Microsoft Office格式文件的强大工具,特别是Excel(.xls和.xlsx)文件。在Java编程环境中,POI库提供了一种高效且灵活的方式来读取、写入和修改Excel文档。在提供的压缩包中,我们有...
poi|信息点|高德poiPOI是“Point of Interest”的缩写,中文可以翻译为“兴趣点”。在地理信息系统中,一个POI可以是一栋房子、 一个商铺、一个邮筒、一个公交站等。 高德POI又名高德兴趣点,来源于高德地图,...
Apache POI 是一个开源项目,专门用于处理Microsoft Office格式的文件,如Excel、Word和PowerPoint。在Java环境中,POI 提供了丰富的API,使得开发者可以方便地创建、读取和修改这些文件。这里提到的三个jar文件是...
今天分享一个能够实现零代码获取高德地图POI数据的工具。 该工具当前版本号为v1.3,主要功能特点包括: 支持通过拉框多边形以及点选省或地级市的方式输入POI数据下载范围 支持多选输入POI数据类型,例如酒店住宿、...
poi-***.jar中文文档.zip,java,poi-***.jar,org.apache.poi,poi,***,jar包,Maven,第三方jar包,组件,开源组件,第三方组件,Gradle,apache,中文API文档,手册,开发手册,使用手册,参考手册 # 使用方法: 解压 【poi-***...
标题中的"poi-ooxml-schemas-3.8,poi-3.8,poi-ooxml jar包合集"指的是Apache POI项目中用于处理Microsoft Office格式文件的Java库,特别是针对Excel(XLS和XLSX)文档的处理。Apache POI是一个流行的开源库,允许...
"poi导入到eclipse" 在本文中,我们将学习如何将poi导入到eclipse中,并搭建好eclipse环境项目,以便更深一步地学习Java读取Microsoft Office的文件。 首先,需要下载poi的jar包。截至本文发表,poi最新版本是...