java - 当内存有问题时如何修改大型 Excel 文件

Question

正如标题所述，我有一个很大的 Excel 文件(>200 张)，需要向其中添加数据。我不想创建新的单元格，我只想修改现有的单元格。

我尝试使用 Apache Poi，但即使 Xms 和 Xmx 设置为 8g，我的应用程序也会出现内存不足的情况。低内存写入的唯一选择似乎是使用 SXSSF。问题是它只适用于创建新单元格，不允许修改现有单元格。我还尝试使用事件 API 来处理工作表的 XML，但它似乎只适用于读取操作。我一直在尝试使用 XMLEventWriter，但找不到一种方法来访问可用于写入的工作表的 XML 数据。除了使用 XSSFReader 之外，还有其他方法可以访问 Excel 文件的 XML 数据吗？

Answer 1

正如上面的评论所述，没有一种解决方案适合所有使用纯 XML 读取和写入 Office Open XML 电子表格的解决方案。每个 Excel 工作簿都需要自己的代码，具体取决于其结构以及应更改的内容。

这是因为 apache poi 的高级类提供了元级别来避免这种情况。但这需要内存才能发挥作用。对于非常大的工作簿，它需要大量内存。为了避免直接操作 XML 消耗内存，此元级别不可用。因此，必须了解工作表的 XML 结构以及所使用的 XML 元素的含义。

因此，如果我们有一个 Excel 工作簿，其第一个工作表的 A 列中包含字符串，B 列中包含数字，那么我们可以更改每隔第五行使用 StAX 直接使用以下代码操作 XML:

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackagePart;

import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTRst;

import javax.xml.stream.XMLEventFactory;
import javax.xml.stream.XMLEventReader;
import javax.xml.stream.XMLEventWriter;
import javax.xml.stream.XMLInputFactory;
import javax.xml.stream.XMLOutputFactory;
import javax.xml.stream.events.Characters;
import javax.xml.stream.events.StartElement;
import javax.xml.stream.events.XMLEvent;

import javax.xml.namespace.QName;

import java.io.File;
import java.io.InputStream;
import java.io.OutputStream;

import java.util.regex.Pattern;

class StaxReadAndChangeTest {

 public static void main(String[] args) throws Exception {
  File file = new File("ReadAndWriteTest.xlsx");
  OPCPackage opcpackage = OPCPackage.open(file);

  //since there are strings in the sheet data, we need the SharedStringsTable
  PackagePart sharedstringstablepart = opcpackage.getPartsByName(Pattern.compile("/xl/sharedStrings.xml")).get(0);
  SharedStringsTable sharedstringstable = new SharedStringsTable();
  sharedstringstable.readFrom(sharedstringstablepart.getInputStream());

  //get first worksheet
  PackagePart sheetpart = opcpackage.getPartsByName(Pattern.compile("/xl/worksheets/sheet1.xml")).get(0);

  //get XML reader and writer            
  XMLEventReader reader = XMLInputFactory.newInstance().createXMLEventReader(sheetpart.getInputStream());
  XMLEventWriter writer = XMLOutputFactory.newInstance().createXMLEventWriter(sheetpart.getOutputStream());

  XMLEventFactory eventFactory = XMLEventFactory.newInstance();

  int rowsCount = 0;
  int colsCount = 0;
  boolean cellAfound = false;
  boolean cellBfound = false;

  while(reader.hasNext()){ //loop over all XML in sheet1.xml
   XMLEvent event = (XMLEvent)reader.next();
   if(event.isStartElement()) {
    StartElement startElement = (StartElement)event;
    QName startElementName = startElement.getName();
    if(startElementName.getLocalPart().equalsIgnoreCase("row")) { //start element of row
     rowsCount++;
     colsCount = 0;
    } else if (startElementName.getLocalPart().equalsIgnoreCase("c")) { //start element of cell
     colsCount++;
     cellAfound = false;
     cellBfound = false;
     if (rowsCount % 5 == 0) { // every 5th row
      if (colsCount == 1) { // cell A
       cellAfound = true;
      } else if (colsCount == 2) { // cell B
       cellBfound = true;
      } 
     }
    } else if (startElementName.getLocalPart().equalsIgnoreCase("v")) { //start element of value
     if (cellAfound) {
      // create new rich text content for cell A
      CTRst ctstr = CTRst.Factory.newInstance();
      ctstr.setT("changed String Value A" + (rowsCount));
      //int sRef = sharedstringstable.addEntry(ctstr);
      int sRef = sharedstringstable.addSharedStringItem(new XSSFRichTextString(ctstr));
      // set the new characters for A's value in the XML
      if (reader.hasNext()) {
       writer.add(event); // write the old event
       event = (XMLEvent)reader.next(); // get next event - should be characters
       if (event.isCharacters()) {
        Characters value = eventFactory.createCharacters(Integer.toString(sRef));
        event = value;
       } 
      }        
     } else if (cellBfound) {
      // set the new characters for B's value in the XML
      if (reader.hasNext()) {
       writer.add(event); // write the old event
       event = (XMLEvent)reader.next(); // get next event - should be characters
       if(event.isCharacters()) { 
        double oldValue = Double.valueOf(((Characters)event).getData()); // old double value
        Characters value = eventFactory.createCharacters(Double.toString(oldValue * rowsCount));
        event = value;         
       }         
      }
     }
    }
   }
   writer.add(event); //by default write each read event
  }
  writer.flush();

  //write the SharedStringsTable
  OutputStream out = sharedstringstablepart.getOutputStream();
  sharedstringstable.writeTo(out);
  out.close();
  opcpackage.close();

 }
}

这将比 apache poi 的 XSSF 类消耗更少的内存。但是，如前所述，它仅适用于此类 Excel 工作簿，该工作簿的第一个工作表在 A 列中包含字符串，在 B 列中包含数字.