XSSFTable with Merged Ranges

[Hayward, Ryan]

I've been trying to use POI 3.17 to generate files with Excel Tables (e.g. XSSFTable table = sheet.createTable()) over cells with merged ranges. The file generates fine, but when opening it in Excel 2013, it shows a "we found a problem with some content..." error. Upon trying to recover, it results in "Removed Feature: Table from /xl/tables/table1.xml part (Table)"

I've checked out the 3.17 final:

| URL: https://svn.apache.org/repos/asf/poi/tags/REL_3_17_FINAL
| Relative URL: ^/poi/tags/REL_3_17_FINAL
| Repository Root: https://svn.apache.org/repos/asf
| Repository UUID: 13f79535-47bb-0310-9956-ffa450edef68
| Revision: 1830359

And from that I modified the CreateTable.java to add a row, add cells with MergedRegions to that row, and then extended the AreaReference on the XSSFTable to encompass the cells with MergedRegions. Without the MergedRegions the example opens fine in Excel, but with them I get the error.

So, does anyone have any suggestions or examples of adding XSSFTables over MergedRegions?

Thanks,
Ryan Hayward


Here's the modifications I made to CreateTable.java in case I'm adding the MergedRegions in the wrong way, or something:

Index: src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java
===================================================================
--- src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java (revision 1830359)
+++ src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java (working copy)
@@ -21,6 +21,7 @@

import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
+import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
@@ -74,6 +75,23 @@
}
}
}
+
+ row = sheet.createRow(3);
+
+ for(int i=0; i<3; i++) {
+ cell = row.createCell(i);
+ cell.setCellValue((i+1)*4);
+
+ int additionalRows = 1;
+ int additionalColumns = 0;
+ cell.getSheet().addMergedRegion(
+ new CellRangeAddress(
+ cell.getRow().getRowNum(), cell.getRow().getRowNum()+additionalRows,
+ cell.getColumnIndex(), cell.getColumnIndex()+additionalColumns
+ )
+ );
+ }
+
// Create the columns
table.addColumn();
table.addColumn();
@@ -81,7 +99,7 @@

// Set which area the table should be placed in
AreaReference reference = wb.getCreationHelper().createAreaReference(
- new CellReference(0, 0), new CellReference(2, 2));
+ new CellReference(0, 0), new CellReference(4, 2));
table.setCellReferences(reference);

---------------------------------------------------------------------
To unsubscribe, e-mail: user-***@poi.apache.org
For additional commands, e-mail: user-***@poi.apache.org

[Greg Woolsey]

The best way I've found to work through issues like this (I also modify
tables, but don't need merged cells) is to mock up what I want it to end up
like in Excel, save it to XLSX, mock up the same (or what I want to be the
same) content via POI and save it to XLSX. I then unzip the files and
compare their contents, looking for differences. This is a bit tedious,
since XML element and attribute order will almost surely differ, but it's
the only way I know to make sure I can recreate something Excel will accept.



On Tue, May 1, 2018 at 2:08 PM Hayward, Ryan <

Post by Hayward, Ryan
I've been trying to use POI 3.17 to generate files with Excel Tables (e.g.
XSSFTable table = sheet.createTable()) over cells with merged ranges. The
file generates fine, but when opening it in Excel 2013, it shows a "we
found a problem with some content..." error. Upon trying to recover, it
results in "Removed Feature: Table from /xl/tables/table1.xml part (Table)"
| URL: https://svn.apache.org/repos/asf/poi/tags/REL_3_17_FINAL
| Relative URL: ^/poi/tags/REL_3_17_FINAL
| Repository Root: https://svn.apache.org/repos/asf
| Repository UUID: 13f79535-47bb-0310-9956-ffa450edef68
| Revision: 1830359
And from that I modified the CreateTable.java to add a row, add cells with
MergedRegions to that row, and then extended the AreaReference on the
XSSFTable to encompass the cells with MergedRegions. Without the
MergedRegions the example opens fine in Excel, but with them I get the
error.
So, does anyone have any suggestions or examples of adding XSSFTables over MergedRegions?
Thanks,
Ryan Hayward
Here's the modifications I made to CreateTable.java in case I'm adding the
src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java
===================================================================
---
src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java
(revision 1830359)
+++
src/examples/src/org/apache/poi/xssf/usermodel/examples/CreateTable.java
(working copy)
@@ -21,6 +21,7 @@
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.AreaReference;
+import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
@@ -74,6 +75,23 @@
}
}
}
+
+ row = sheet.createRow(3);
+
+ for(int i=0; i<3; i++) {
+ cell = row.createCell(i);
+ cell.setCellValue((i+1)*4);
+
+ int additionalRows = 1;
+ int additionalColumns = 0;
+ cell.getSheet().addMergedRegion(
+ new CellRangeAddress(
+ cell.getRow().getRowNum(),
cell.getRow().getRowNum()+additionalRows,
+ cell.getColumnIndex(),
cell.getColumnIndex()+additionalColumns
+ )
+ );
+ }
+
// Create the columns
table.addColumn();
table.addColumn();
@@ -81,7 +99,7 @@
// Set which area the table should be placed in
AreaReference reference =
wb.getCreationHelper().createAreaReference(
- new CellReference(0, 0), new CellReference(2, 2));
+ new CellReference(0, 0), new CellReference(4, 2));
table.setCellReferences(reference);
---------------------------------------------------------------------

[Hayward, Ryan]

-----Original Message-----
Sent: Tuesday, May 01, 2018 5:56 PM
Subject: Re: XSSFTable with Merged Ranges
The best way I've found to work through issues like this (I also modify
tables, but don't need merged cells) is to mock up what I want it to end up
like in Excel, save it to XLSX, mock up the same (or what I want to be the
same) content via POI and save it to XLSX. I then unzip the files and
compare their contents, looking for differences. This is a bit tedious,
since XML element and attribute order will almost surely differ, but it's
the only way I know to make sure I can recreate something Excel will accept.

Thanks for the advice. When I tried it out, I found that Excel itself doesn't handle Tables on merged ranges. When you try it, Excel removes the merged ranges.

Т���������������������������������������������������������������������ХF�V�7V'67&�&R�R���âW6W"�V�7V'67&�&T���6�R��&pФf�"FF�F����6����G2�R���