Apache POI事件API更新现有Excel工作表

Question

我有包含多个工作表的大型excel文件。
我只想处理文件中的一个工作表...从两列读取值并更新两列。

使用此代码，我可以从Sheet读取数据。但无法确定如何将输出保存回来。

public class ExcelFunctions {

private class ExcelData implements SheetContentsHandler {       
    private Record rec ;


    public void startRow(int rowNum) {
        rec = new Record();
        output.put("R"+rowNum, rec);            
    }

    public void endRow(int rowNum) {
    }

    public void cell(String cellReference, String formattedValue,
            XSSFComment comment) {

        int thisCol = (new CellReference(cellReference)).getCol();
        if(thisCol==7){
            try {
                rec.setK1(formattedValue);
            } catch (Exception e) {
            }
        }
        if(thisCol==8){
            try {
                rec.setK2(formattedValue);
            } catch (Exception e) {
            }
        }
        if(thisCol == 27){
            String key = rec.full_key();
            System.out.println(key);
            ///////Process Matched Key...get Data
            //////Set value to column 27
        }
        if(thisCol == 28){
            String key = rec.full_key();
            System.out.println(key);
            ///////Process Matched Key...get Data
            //////Set value to column 28
        }

    }

    public void headerFooter(String text, boolean isHeader, String tagName) {
    }
}


///////////////////////////////////////

private final OPCPackage xlsxPackage;


private final Map<String, Record> output;

public ExcelFunctions(OPCPackage pkg, Map<String, Record> output) {
    this.xlsxPackage = pkg;
    this.output = output;
}

public void processSheet(
        StylesTable styles,
        ReadOnlySharedStringsTable strings,
        SheetContentsHandler sheetHandler, 
        InputStream sheetInputStream)
        throws IOException, ParserConfigurationException, SAXException {
    DataFormatter formatter = new DataFormatter();
    InputSource sheetSource = new InputSource(sheetInputStream);
    try {
        XMLReader sheetParser = SAXHelper.newXMLReader();
        ContentHandler handler = new XSSFSheetXMLHandler(
              styles, null, strings, sheetHandler, formatter, false);
        sheetParser.setContentHandler(handler);
        sheetParser.parse(sheetSource);
     } catch(ParserConfigurationException e) {
        throw new RuntimeException("SAX parser appears to be broken - " + e.getMessage());
     }
}

public void process()
        throws IOException, OpenXML4JException, ParserConfigurationException, SAXException {
    ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(this.xlsxPackage);
    XSSFReader xssfReader = new XSSFReader(this.xlsxPackage);
    StylesTable styles = xssfReader.getStylesTable();
    XSSFReader.SheetIterator iter = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
    boolean found = false;
    while (iter.hasNext() &&  !found) {
        InputStream stream = iter.next();
        String sheetName = iter.getSheetName();
        if(sheetName.equals("All Notes") ){
            processSheet(styles, strings, new ExcelData(), stream);
            found = true;
        }
        stream.close();
    }
}

@SuppressWarnings("unused")
public static void main(String[] args) throws Exception {
    File xlsxFile = new File("C:\\Users\\admin\\Downloads\\Unique Name Macro\\big.xlsm");
    if (!xlsxFile.exists()) {
        System.err.println("Not found or not a file: " + xlsxFile.getPath());
        return;
    }

    // The package open is instantaneous, as it should be.
    OPCPackage p = OPCPackage.open(xlsxFile.getPath(), PackageAccess.READ_WRITE);
    Map<String, Record> output = new HashMap<String, Record>();
    ExcelFunctions xlFunctions = new ExcelFunctions(p, output);
    xlFunctions.process();
    p.close();

    if (output != null){
        for(Record rec : output.values()){
            System.out.println(rec.full_key());
        }
    }
}
}

文件很大，我只想使用事件API。
我已成功地使用此代码进行了测试。
但这会在内存中加载整个文件（导致应用程序崩溃）...而我只需要编辑一个工作表。

    public static void saveToExcel(String ofn, Map<String, Record> data) {
    FileInputStream infile;
    try {
        infile = new FileInputStream(new File("C:\\Users\\admin\\Downloads\\Unique Name Macro\\big.xlsm"));
        XSSFWorkbook workbook = new XSSFWorkbook (infile);
        XSSFSheet sheet = workbook.getSheet("All Notes");

        for(Record rec : output.values()){
            Row dataRow = rec.getRow(rev.getRownum-1);
            setCellValue(dataRow, 26, "SomeValue");
            setCellValue(dataRow, 27, "SomeValue");
        }

        FileOutputStream out =  new FileOutputStream(new File(ofn));
        workbook.write(out);
        infile.close();
        out.close();
        workbook.close();
    }
    catch (FileNotFoundException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
}
private static void setCellValue(Row row,int col, String value){
    Cell c0 = row.getCell(col);
    if (c0 == null){
        c0 = row.createCell(col);
    }
    c0.setCellValue(value);
}

Answer 1

我不认为POI中提供了任何开箱即用的东西，允许这样做。

因此，最好解压XLSX/XLSM文件（它们实际上是一个zip中的一堆XML文件），并将XML文件作为文本文件或使用普通XML解析器读取，这样您就可以轻松地再次写出更改后的文件，从而再次生成XLSX/XLSM文件。