APACHE POI creates corrupted file - no matter the change I made

Question

I am Trying to create Excel files with tables that cointain certain values. The problem is that Java creates CORRUPTED Excel File (.xlsx, .xlsm) no matter what I do.

Changing name of the EXISTING table's column - File is corrupted, can't open it without repairing it. Name is changed but the file must be repaired for some reason. Creating new Tables or creating new Columns to existing Tables seems to corrupt files but I don't know why.

This is my whole code:

package pl.TiHerbatka.Ti.ExcelWriteReadTi.Main;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFTable;
import org.apache.poi.xssf.usermodel.XSSFTableColumn;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Main {

    public static void main(String[] args) throws InvalidFormatException, IOException {
        File deleteOldFile = new File("E:\\Users\\PLACEHOLDER\\ExcelTestowy#2 — kopia.xlsx");
        if (deleteOldFile.exists()) {
            deleteOldFile.delete();
            System.out.println("Deleted!");
        }
        File fT = new File("E:\\Users\\PLACEHOLDER\\ExcelTestowy#2.xlsx");
        FileInputStream fTI = new FileInputStream(fT);
        if (deleteOldFile.exists()) {
            System.out.println("File exists!\n--------------");
        } else {
            System.out.println("File doesn't existst");
        }
        /////////////////////////////////

        XSSFWorkbook wb = new XSSFWorkbook(fTI);
        XSSFSheet sh = wb.getSheetAt(0);

        XSSFTable tb = sh.getTables().get(0);
        tb.setDisplayName("ExampleTableName"); //Works
        tb.getColumns().get(0).setName("ExampleColumnName"); // Doesn't work
        tb.createColumn("ExampleAdditionalColumnINeed"); //Doesn't work too.


        File fTO = new File("E:\\Users\\PLACEHOLDER\\ExcelTestowy#2 — kopia.xlsx");
        FileOutputStream fTOO = new FileOutputStream(fTO);
        wb.write(fTOO);


        fTOO.flush();
        fTOO.close();
        wb.close();
    }

}

PS: I am beginner in Java - I had 3 years break in java. I apologise for my language.

What `apache poi` version are you using? Your code (renaming a `XSSFTable`) works properly for me using current `apache po 4.1.2`. The less size differences are because of writing the `XML` little bit different. For example no line feeds between `XML` tags or using `` instead of ``. But this do not lead to a corrupt file. — Axel Richter, Mar 29 '20 at 16:18
I am using exactly the same version of Apache. Yet even the slightest thing I change gives me corrupted file (Except when I edit value of cells that are not headers etc). — user13147599, Mar 29 '20 at 16:30
Well [Renaming headers of XSSFTable with Apache Poi leads to corrupt XLSX-file](https://stackoverflow.com/questions/55532006/renaming-headers-of-xssftable-with-apache-poi-leads-to-corrupt-xlsx-file/55539181#55539181) **had have** problems. But this is not what your provided code is doing. Please show the code which produces the problem. — Axel Richter, Mar 29 '20 at 16:44
I just edited it. With previous code everything is fine but now when I am trying to rename column of the table I get corrupted file. Also when I am trying to create new column I get the same outcome. Sorry for mistaken code. — user13147599, Mar 29 '20 at 16:49
What is `tb` in your code? Please provide complete example code. — Axel Richter, Mar 29 '20 at 16:52

score 2 · Answer 1 · answered Mar 29 '20 at 17:33

After changing the table headers in XSSFTable we also need changing the cell value in the sheet which is the representation of the table header. XSSFTableand XSSFSheet are different XSSFWorkbook document parts and apache poi fails holding both parts updated even in current apache poi 4.1.2.

The following works for me using apache poi 4.1.2:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFTable;

public class ExcelRenameTable {

    public static void main(String[] args) throws InvalidFormatException, IOException {

        File fT = new File("./ExcelTestowy.xlsm");
        FileInputStream fTI = new FileInputStream(fT);

        XSSFWorkbook wb = new XSSFWorkbook(fTI);
        XSSFSheet sh = wb.getSheetAt(0);

        XSSFTable tb = sh.getTables().get(0);
        tb.setDisplayName("ExampleTableName");

        tb.getColumns().get(0).setName("ExampleColumnName");
        //we also need changing the cell value in the sheet which is the representation of the table header
        int tbStartRow = tb.getStartRowIndex(); // start row of table = header row
        int tbStartCol = tb.getStartColIndex(); // start col of table = first col
        sh.getRow(tbStartRow).getCell(tbStartCol).setCellValue("ExampleColumnName");

        tb.createColumn("ExampleAdditionalColumnINeed");
        //we also need changing the cell value in the sheet which is the representation of the table header
        int tbEndCol = tb.getEndColIndex(); // end col of table = new created column
        XSSFCell cell = sh.getRow(tbStartRow).getCell(tbEndCol);
        //maybe we need creating the cell first since the column is new
        if (cell == null) cell = sh.getRow(tbStartRow).createCell(tbEndCol);
        cell.setCellValue("ExampleAdditionalColumnINeed");

        File fTO = new File("./ExcelTestowy — kopia.xlsm");
        FileOutputStream fTOO = new FileOutputStream(fTO);
        wb.write(fTOO);

        fTOO.close();
        wb.close();
    }

}

Yes! Now it works! Now I know how I should proceed with more stuff! Thank you very, very much! — user13147599, Mar 29 '20 at 18:23

score -2 · Answer 2 · answered Mar 29 '20 at 16:08

-2

Apache POI can't create .xlsm - as in excel files with macros.

See: write to xlsm (Excel 2007) using apache poi

answered Mar 29 '20 at 16:08

David Meersteiner

1
1

1

The question is not about new creating a `*.xlsm` file but opening, changing and rewriting the sheet content of an existing `*.xlsm` file. This is supported using `apache poi`. – Axel Richter Mar 29 '20 at 16:21
Version of the code I put here is with .xlsm beacuse I tried both in case it would work somehow. I wrote in brackets "(...)" extensions I used. – user13147599 Mar 29 '20 at 16:28

APACHE POI creates corrupted file - no matter the change I made

2 Answers2