Muhammad Sadiq February 2016

Apache POI event API Update existing Excel sheet

I have large excel file with several worksheets.
I want to process just one sheet in file...Read value from two columns and update two columns.

Using this code, I am able to read data from sheet.But unable to figure out, how to save output back.

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 IO        

Answers


centic February 2016

I don't think there is anything provided in POI out of the box which allows to do that.

Therefore you might be better off doing this by unzipping the XLSX/XLSM file (they are actually a bunch of xml-files inside a zip) and reading the xml-files as text-files or with a normal XML Parser so that you can easily write out the changed file again to produce the XLSX/XLSM file again.

Post Status

Asked in February 2016
Viewed 1,802 times
Voted 10
Answered 1 times

Search




Leave an answer