Renaming headers of XSSFTable with Apache Poi leads to corrupt XLSX-file

Question

I am trying to rename headers of an existing xlsx-file. The idea is to have an excel-file to export data from XML to excel and reimport the XML once some user has made adjustments.

At the moment we have created a "template" xlsx-sheet with Excel which already contains a sortable table (XSSFTable in poi) and a mapping to a XSD-source. Then we import it via POI, map XML data into it and save it. To adjust the sheet to the users we want to translate the headers/column-names of this existing table into different languages. It worked with POI 3.10-FINAL but since an upgrade to 4.0.1 it leads to a corrupt xlsx-file when opening.

I found this question on stackoverflow already Excel file gets corrupted when i change the value of any cell in the header (Columns Title) but it is not answered and pretty old. But I tried to figure out what the comments may were about and tried to flatten the existing XSSFTable, copy the filled data to a new sheet and put on a new XSSFTable to the data. Sadly this seems to be pretty complicated so I am back to correcting the broken header-cells. I also tried to create the whole sheet with POI and step away from using that "template"-xslx, but I cannot figure out how to implement our XSD-Mapping (in Excel its Developer-Tools -> Source -> Add and then mapping the nodes to some cells in a dynamic table)

The code that worked until the upgrade of poi is basically this:

//Sheet is the current XSSFSheet
//header is a Map with the original header-name from the template mapped to a the new translated name
//headerrownumber is the row containing the tableheader to be translated

 public static void translateHeaders(Sheet sheet,final Map<String,String> header,int headerrownumber) {
  CellRangeAddress address = new CellRangeAddress(headerrownumber,headerrownumber,0,sheet.getRow(headerrownumber).getLastCellNum());  //Cellrange is the header-row

        MyCellWalk cellWalk = new MyCellWalk (sheet,address);
        cellWalk.traverse(new CellHandler() {
            public void onCell(Cell cell, CellWalkContext ctx) {
                String val = cell.getStringCellValue();
                if (header.containsKey(val)) {
                    cell.setCellValue(header.get(val));
                }
            }
        });
}

MyCellWalk is a org.apache.poi.ss.util.cellwalk.CellWalk which traverses the cell range from top left to the bottom right cell.

As far as I could figure out its not enough to simply change the flat value of the cell because xlsx keeps references to the cellname in some of their maps, but I cannot figure out how to grab them all and rename the header. Maybe there is also another approach in translating the headernames?

Answer 1

Well, the XSSFTable.updateHeaders should do the trick if apache poi would not fail doing it.

All the following is done using apache poi 4.0.1.

I have downloaded your dummy_template.xlsx and then tried changing the table column headers in the sheet. But even after calling XSSFTable.updateHeaders the column names in the XSSFTable has not changed. So I had a look into XSSFTable.java -> updateHeaders to determine why this not happens. There we find:

if (row != null && row.getCTRow().validate()) {
 //do changing the column names
}

So the column names only will be changed if the corresponding row in the sheet is valid XML according to Office Open XML name spaces. But in later Excel versions (after 2007) additional name spaces were added. In this case the row's XML looks like:

<row r="4" spans="1:3" x14ac:dyDescent="0.25">

Note the additional x14ac:dyDescent attribute. That's why row.getCTRow().validate() returns false.

The following code gets your dummy_template.xlsx, renames the column headers in the sheet and then calls a disarmed version static void updateHeaders(XSSFTable table). After that the result.xlsx is valid for opening in Excel.

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.util.cellwalk.*;

import org.apache.poi.xssf.usermodel.*;

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

import java.io.*;
import java.util.*;

class ExcelRenameTableColumns {

 static void translateHeaders(Sheet sheet, final Map<String,String> header, int headerrownumber) {
  CellRangeAddress address = new CellRangeAddress(
   headerrownumber, headerrownumber, 
   0, sheet.getRow(headerrownumber).getLastCellNum());

  CellWalk cellWalk = new CellWalk (sheet, address);
  cellWalk.traverse(new CellHandler() {
   public void onCell(Cell cell, CellWalkContext ctx) {
    String val = cell.getStringCellValue();
    if (header.containsKey(val)) {
     cell.setCellValue(header.get(val));
    }
   }
  });
 }

 static void updateHeaders(XSSFTable table) {
  XSSFSheet sheet = (XSSFSheet)table.getParent();
  CellReference ref = table.getStartCellReference();

  if (ref == null) return;

  int headerRow = ref.getRow();
  int firstHeaderColumn = ref.getCol();
  XSSFRow row = sheet.getRow(headerRow);
  DataFormatter formatter = new DataFormatter();

System.out.println(row.getCTRow().validate()); // false!

  if (row != null /*&& row.getCTRow().validate()*/) {
   int cellnum = firstHeaderColumn;
   CTTableColumns ctTableColumns = table.getCTTable().getTableColumns();
   if(ctTableColumns != null) {
    for (CTTableColumn col : ctTableColumns.getTableColumnList()) {
     XSSFCell cell = row.getCell(cellnum);
     if (cell != null) {
      col.setName(formatter.formatCellValue(cell));
     }
     cellnum++;
    }
   }
  }
 }

 public static void main(String[] args) throws Exception {

  String templatePath = "dummy_template.xlsx";
  String outputPath = "result.xlsx";

  FileInputStream inputStream = new FileInputStream(templatePath);
  Workbook workbook = WorkbookFactory.create(inputStream);
  Sheet sheet = workbook.getSheetAt(0);

  Map<String, String> header = new HashMap<String, String>();
  header.put("textone", "Spalte eins");
  header.put("texttwo", "Spalte zwei");
  header.put("textthree", "Spalte drei");

  translateHeaders(sheet, header, 3);

  XSSFTable table = ((XSSFSheet)sheet).getTables().get(0);

  updateHeaders(table);

  FileOutputStream outputStream = new FileOutputStream(outputPath);
  workbook.write(outputStream);
  outputStream.close();
  workbook.close();

 }
}

If I open the dummy_template.xlsx using Excel 2007 and then save as dummy_template2007.xlsx, the row's XML changes to

<row r="4" spans="1:3">

Now when using this dummy_template2007.xlsx no manually calling the XSSFTable.updateHeaders is necessary. The XSSFTable.writeTo which is called by XSSFTable.commit does this automatically.