You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by "Hayward, Ryan" <Ry...@marketstrategies.com> on 2018/05/01 21:08:08 UTC
XSSFTable with Merged Ranges
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-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
RE: XSSFTable with Merged Ranges
Posted by "Hayward, Ryan" <Ry...@marketstrategies.com>.
> -----Original Message-----
> From: Greg Woolsey [mailto:greg.woolsey@gmail.com]
> Sent: Tuesday, May 01, 2018 5:56 PM
> To: POI Users List <us...@poi.apache.org>
> 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.
Re: XSSFTable with Merged Ranges
Posted by Greg Woolsey <gr...@gmail.com>.
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 <
Ryan.Hayward@marketstrategies.com> wrote:
> 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-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>