使用 Apache Poi 重命名 XSSFTable 的标头会导致 XLSX 文件损坏 [英] Renaming headers of XSSFTable with Apache Poi leads to corrupt XLSX-file

问题描述

我正在尝试重命名现有 xlsx 文件的标题.这个想法是有一个 excel 文件将数据从 XML 导出到 excel 并在某些用户进行调整后重新导入 XML.

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.

目前我们已经用 Excel 创建了一个模板"xlsx 表格，它已经包含一个可排序的表格(poi 中的 XSSFTable)和一个到 XSD 源的映射.然后我们通过 POI 将其导入，将 XML 数据映射到其中并保存.为了根据用户调整工作表，我们希望将此现有表格的标题/列名称翻译成不同的语言.它适用于 POI 3.10-FINAL，但自从升级到 4.0.1 后，打开时会导致 xlsx 文件损坏.

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.

我已经在stackoverflow上发现了这个问题当我更改标题(列标题)中任何单元格的值时，Excel 文件会损坏但它没有回答而且很旧.但我试图弄清楚这些评论可能是关于什么的，并试图将现有的 XSSFTable 展平，将填充的数据复制到新工作表中，然后将新的 XSSFTable 放在数据上.可悲的是，这似乎非常复杂，所以我又回到纠正损坏的标题单元格上.我还尝试使用 POI 创建整个工作表并远离使用该模板"-xslx，但我无法弄清楚如何实现我们的 XSD 映射(在 Excel 中它的开发人员工具 -> 源 -> 添加然后映射动态表中某些单元格的节点)

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)

直到升级poi的代码基本是这样的:

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 是一个 org.apache.poi.ss.util.cellwalk.CellWalk，它从左上角到右下角遍历单元格范围.

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

据我所知，仅仅改变单元格的平面值是不够的，因为 xlsx 在他们的一些地图中保留了对单元格名称的引用，但我无法弄清楚如何获取它们并重命名标题.也许还有另一种翻译标题名称的方法?

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?

推荐答案

好吧，XSSFTable.updateHeaders 如果 apache poi 不会失败，则应该可以解决这个问题.

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

以下所有内容均使用 apache poi 4.0.1 完成.

All the following is done using apache poi 4.0.1.

我已经下载了您的 dummy_template.xlsx，然后尝试更改工作表中的表格列标题.但是即使在调用 XSSFTable.updateHeaders 之后，XSSFTable 中的列名也没有改变.所以我查看了 XSSFTable.java -> updateHeaders 以确定为什么不会发生这种情况.我们发现:

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
}

因此，只有根据Office Open XML 命名空间，当工作表中的相应行是有效的XML 时，才会更改列名称.但是在以后的 Excel 版本(2007 年之后)中添加了额外的命名空间.在这种情况下，行的 XML 看起来像:

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">

注意附加的 x14ac:dyDescent 属性.这就是 row.getCTRow().validate() 返回 false 的原因.

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

以下代码获取您的 dummy_template.xlsx，重命名工作表中的列标题，然后调用解除版本static void updateHeaders(XSSFTable table).之后result.xlsx就可以在Excel中打开了.

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();

 }
}

如果我使用 Excel 2007 打开 dummy_template.xlsx 然后另存为 dummy_template2007.xlsx，该行的 XML更改为

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

现在当使用这个 dummy_template2007.xlsx 时，不需要手动调用 XSSFTable.updateHeaders.XSSFTable.writeTo 由 XSSFTable.commit 调用，它会自动执行此操作.

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.

这篇关于使用 Apache Poi 重命名 XSSFTable 的标头会导致 XLSX 文件损坏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！