如何阅读和使用POI编辑巨大的Excel文件？ [英] How do i read and edit huge excel files using POI?

问题描述

我有一个要求做到以下几点

I have a requirement to do the following

1）复制一个巨大的Excel文件1400 * 1400，并进行复印。

1)Copy a huge excel file 1400*1400 and make a copy.

2）读取复制的文件，并添加新的行和列，并在同一时间进行编辑。

2)Read the copied file and add new columns and rows and also edit at the same time.

3）将是一个独立的程序，而不是在服务器上。我有低内存占用和快速的性能的限制。

3)This is going to be a standalone program and not on a server. I have limitations of having low memory footprint and fast performance.

我已经做了一些阅读和发现以下

I have done some reading and have found the following

1）没有API复制sucg一个巨大的文件

1)There is no API to copy sucg a huge file

2）SXSSF可以使用用于写入，但不用于读

2)SXSSF can be using for writing but not for reading

3）XSSF和SAX（事件API）可以使用阅读，但不是为editing.If我试图读取和存储为对象，我再次将有一个内存问题。

3)XSSF and SAX (Event API) can be using for reading but not for editing.If i tried to read and store as objects again i will have a memory issue.

请你能帮助我如何能做到这一点？

Please can you help on how i can do this?

推荐答案

如果有很多数据，由于该内存不足或GC超限超过了'出现，如果记忆是一个问题的数据可以初步解析为一个XML文件。 Excel工作表可以用XML文件被替换，使内存的使用将是最小的。

If there is much data due to which 'Out of Memory' or 'GC overlimit exceeded' occurs and if memory is a problem the data can be initially parsed to a xml file. The excel sheet can be replaced with the xml file so that memory usage will be minimum.

在Excel中的表是psented为XML重新$ P $。使用java.util.zip.ZipFile中每个项可以被识别。为片状的XML可以用分析的XML进行更换，使我们在Excel工作表中得到预期的数据。

In excel the sheets are represented as xml. Using java.util.zip.ZipFile each entries can be identified. The xml for the sheet can be replaced with the parsed xml so that we get the expected data in excel sheet.

以下类可用于创建XML文件：

Following class can be used to create xml files:

public class XmlSpreadsheetWriter {
    private final Writer _out;
    private int _rownum;

    public XmlSpreadsheetWriter(Writer out){
        _out = out;
    }

    public void beginSheet() throws IOException {
        _out.write("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" +
                "<worksheet xmlns=\"http://schemas.openxmlformats.org/spreadsheetml/2006/main\">" );
        _out.write("<sheetData>\n");
    }

    public void endSheet() throws IOException {
        _out.write("</sheetData>");
        _out.write("</worksheet>");
    }

    public void insertRow(int rownum) throws IOException {
        _out.write("<row r=\""+(rownum+1)+"\">\n");
        this._rownum = rownum;
    }

    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\"");
     _out.write(" s=\""+styleIndex+"\"");
     _out.write(">");
     _out.write("<is><t>"+value+"</t></is>");
     _out.write("</c>");
    }

    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\"");
     _out.write(" s=\""+styleIndex+"\"");
     _out.write(">");
     _out.write("<v>"+value+"</v>");
     _out.write("</c>");
    }

    public void createEmptyCell(int columnIndex, int styleIndex)throws IOException {
     String ref = new CellReference(_rownum, columnIndex).formatAsString();
     _out.write("<c r=\""+ref+"\" t=\"n\"");
     _out.write(" s=\""+styleIndex+"\"");
     _out.write(">");
     _out.write("<v></v>");
     _out.write("</c>");
    }
} 

这篇关于如何阅读和使用POI编辑巨大的Excel文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！