`
huangsky
  • 浏览: 39514 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

poi BigGridDemo

    博客分类:
  • java
 
阅读更多
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
分享到:
评论

相关推荐

    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导出POI导出POI...

    poi全家桶ooxml-schemas/poi/poi-examples/poi-ooxml/poi-ooxml-schemas/poi-scratchpad

    标题中的"poi全家桶ooxml-schemas/poi/poi-examples/poi-ooxml/poi-ooxml-schemas/poi-scratchpad"提及的是Apache POI项目中的多个关键组件和目录结构。Apache POI是一个开源的Java库,专门用于读写Microsoft Office...

    POI poi相关所有jar包 poi jar包 poi最全jar包

    Apache POI 是一个开源项目,专门用于处理Microsoft Office格式的文件,如Excel(.xlsx、.xls)、Word(.doc、.docx)和PowerPoint(.ppt、.pptx)。这个压缩包包含了POI项目中所有必要的jar包,总计十二个,确保了...

    poi的所有jar包,poi, poi-ooxml,poi-ooxml-schemas的各个版本jar

    这个压缩包包含了POI项目的几个核心组件的jar文件,包括基础的`poi`库,以及支持Open XML格式(OOXML)的`poi-ooxml`和`poi-ooxml-schemas`。这些jar包在Java开发中非常有用,尤其是当你需要在应用程序中读取、写入...

    poi-3.17 poi-3.16

    Apache POI 是一个开源项目,专门用于处理微软的Office文档格式,如Excel、Word和PowerPoint。POI 提供了一套 Java API,使得开发者可以在Java应用程序中读写Microsoft Office格式的文件。标题中的"poi-3.17 poi-...

    poi-3.9、poi-ooxml-3.9、poi-ooxml-schemas-3.9

    Apache POI 是一个开源项目,专门用于处理微软的Office文档格式,如Excel、Word和PowerPoint。在Java编程环境中,Apache POI 提供了API,让开发者能够方便地读取、写入和修改这些文件。标题提到的"poi-3.9、poi-...

    POI中文帮助文档_POI_

    Apache POI是一个开源项目,主要用于读取和写入Microsoft Office格式的文件,如Word(.doc/.docx)、Excel(.xls/.xlsx)和PowerPoint(.ppt/.pptx)。这个“POI中文帮助文档”提供了全面的指南,帮助开发者理解和...

    poi jar包 官网下载 最新poi官网资源

    关于poi ,Apache在今年,也就是在2017年9月15日 正式发布了POI 3.17版本, Apache POI团队对于3.17版本进行了一些功能的修复。主要是几个新的功能区域和许多错误修复。 POI 3.17是支持Java 6的最后一个版本。下一个...

    百度poi,高德poi数据数据免费下载

    在IT行业中,POI(Point of Interest)是一个重要的概念,主要指地图上的兴趣点,例如商业场所、公共服务设施、交通站点等。百度POI和高德POI是两大知名的中国地图服务提供商,它们提供了丰富的地理信息数据,为...

    poi-3.10.1上传组件jar包 po poi-ooxml poi-ooxml-schemas

    Apache POI 是一个开源项目,专门用于处理微软的Office文档格式,如Excel、Word和PowerPoint。这个"poi-3.10.1上传组件jar包"包含了处理这些文档所需的Java库,使得开发者可以在Java应用中创建、读取和修改Office...

    poi excel poi excel poi excel

    ### POI Excel知识点详解 #### 一、Jakarta POI简介与Apache POI的作用 Jakarta POI 是 Apache POI 的早期项目名称,它提供了一组 API 来处理 Microsoft Office 文件格式,特别是针对 Excel(`.xls` 和 `.xlsx`)...

    poi3.17jar包下载

    poi3.17jar包下载,支持excel2003,excel2007,亲测可用。poi3.17jar包下载,支持excel2003,excel2007,亲测可用。poi3.17jar包下载,支持excel2003,excel2007,亲测可用。poi3.17jar包下载,支持excel2003,excel2007...

    java对Excel的jar包poi-3.15,poi-3.16,poi-3.17

    Java中的Apache POI库是用于处理Microsoft Office格式文件的强大工具,特别是Excel(.xls和.xlsx)文件。在Java编程环境中,POI库提供了一种高效且灵活的方式来读取、写入和修改Excel文档。在提供的压缩包中,我们有...

    高德2017poi 高德历史poi 2017科研poi

    poi|信息点|高德poiPOI是“Point of Interest”的缩写,中文可以翻译为“兴趣点”。在地理信息系统中,一个POI可以是一栋房子、 一个商铺、一个邮筒、一个公交站等。 高德POI又名高德兴趣点,来源于高德地图,...

    poi-3.16.jar,poi-ooxml-3.16.jar,poi-ooxml-schemas-3.16.jar

    Apache POI 是一个开源项目,专门用于处理Microsoft Office格式的文件,如Excel、Word和PowerPoint。在Java环境中,POI 提供了丰富的API,使得开发者可以方便地创建、读取和修改这些文件。这里提到的三个jar文件是...

    小软件POI数据下载神器

    今天分享一个能够实现零代码获取高德地图POI数据的工具。 该工具当前版本号为v1.3,主要功能特点包括: 支持通过拉框多边形以及点选省或地级市的方式输入POI数据下载范围 支持多选输入POI数据类型,例如酒店住宿、...

    poi-5.0.0.jar中文文档.zip

    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包合集

    标题中的"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" 在本文中,我们将学习如何将poi导入到eclipse中,并搭建好eclipse环境项目,以便更深一步地学习Java读取Microsoft Office的文件。 首先,需要下载poi的jar包。截至本文发表,poi最新版本是...

Global site tag (gtag.js) - Google Analytics