0

I'm trying to read and Xslx file using JAVA POI, edit it and save it. But when i change any cell value on row#0 (Header) the output file gets corrupted.

Excel can recover the file by repairing, Table from /xl/tables/table.xm .

    FileInputStream file = new FileInputStream("inventorywithbin431.xlsx");
    XSSFWorkbook workbook = (XSSFWorkbook) WorkbookFactory.create(file);
    XSSFSheet rawdata = workbook.getSheetAt(0);

    String[] headers = new String[] { "ItemCodeNoFRU","Company","ItemCode","ItemName","ItemGroup","PartNumber","Warehouse","Segment","Bin","WareHouseQty","OnOrder","IsCommited","BinQty","Cost","BinExtCost"};

    //Set Header
    for(int i=0;i<14;i++)
        rawdata.getRow(0).getCell(i).setCellValue(headers[i]);

    file.close();
    //write changes
    try ( 
        FileOutputStream output_file = new FileOutputStream("inventorywithbin431.xlsx")) {
        workbook.write(output_file);
        output_file.flush();
        output_file.close();
    }
Mooh
  • 1,237
  • 3
  • 19
  • 38
  • 1
    If there is a `/xl/tables/table[n].xml`, then the headers are part of an Excel Table object. If so, then the headers will be cell content in `/xl/worksheets/sheet[n].xml` but also column names in `/xl/tables/table[n].xml` and probably parts of [structured reference formulas](https://support.office.com/en-us/article/Using-structured-references-with-Excel-tables-f5ed2452-2337-4f71-bed3-c8ae6d2b276e). So if you wants changing a header, you have to change it all there and not only the cell content. This will be a horrible complex task. I would rethink my approach so that this will not be necessary – Axel Richter Feb 28 '17 at 06:52
  • @AxelRichter thanks ! What approach would you suggest knowing that i want to avoid any manual interaction with the excel file ? – Mooh Feb 28 '17 at 18:29
  • 1
    The question is: Why do you think it is necessary to change the headings of an existing table object? As said those headings are also column names of the table and probably parts of structured reference formulas. And all this needs also to be changed then. – Axel Richter Feb 28 '17 at 19:20
  • @AxelRichter, thanks for the hint. I'll create a new workbook based on the original one. That should solve the problem i'll keep you updated. Regards ! – Mooh Mar 02 '17 at 21:57
  • @AxelRichter It did work. Thanks ! – Mooh Mar 09 '17 at 21:10

0 Answers0