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

问题描述

我需要执行以下操作

1)复制一个1400*1400的巨大excel文件并制作一份.

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)可用于读取，但不能用于编辑.如果我再次尝试读取和存储为对象，我将遇到内存问题.

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 中，工作表表示为 xml.使用 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屋！