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.
On Tue, May 1, 2018 at 2:08 PM Hayward, Ryan <
ryan.hayw...@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-unsubscr...@poi.apache.org
> For additional commands, e-mail: user-h...@poi.apache.org
>
>
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-unsubscr...@poi.apache.org
For additional commands, e-mail: user-h...@poi.apache.org
ApacheCon North America 2018 schedule is now live.
Dear Apache Enthusiast, We are pleased to announce our schedule for ApacheCon North America 2018. ApacheCon will be held September 23-27 at the Montreal Marriott Chateau Champlain in Montreal, Canada. Registration is open! The early bird rate of $575 lasts until July 21, at which time it goes up to $800. And the room block at the Marriott ($225 CAD per night, including wifi) closes on August 24th. We will be featuring more than 100 sessions on Apache projects. The schedule is now online at https://apachecon.com/acna18/ The schedule includes full tracks of content from Cloudstack[1], Tomcat[2], and our GeoSpatial community[3]. We will have 4 keynote speakers, two of whom are Apache members, and two from the wider community. On Tuesday, Apache member and former board member Cliff Schmidt will be speaking about how Amplio uses technology to educate and improve the quality of life of people living in very difficult parts of the world[4]. And Apache Fineract VP Myrle Krantz will speak about how Open Source banking is helping the global fight against poverty[5]. Then, on Wednesday, we’ll hear from Bridget Kromhout, Principal Cloud Developer Advocate from Microsoft, about the really hard problem in software - the people[6]. And Euan McLeod, VP VIPER at Comcast will show us the many ways that Apache software delivers your favorite shows to your living room[7]. ApacheCon will also feature old favorites like the Lightning Talks, the Hackathon (running the duration of the event), PGP key signing, and lots of hallway-track time to get to know your project community better. Follow us on Twitter, @ApacheCon, and join the disc...@apachecon.com mailing list (send email to discuss-subscr...@apachecon.com) to stay up to date with developments. And if your company wants to sponsor this event, get in touch at h...@apachecon.com for opportunities that are still available. See you in Montreal! Rich Bowen VP Conferences, The Apache Software Foundation h...@apachecon.com @ApacheCon [1] http://cloudstackcollab.org/ [2] http://tomcat.apache.org/conference.html [3] http://apachecon.dukecon.org/acna/2018/#/schedule?search=geospatial [4] http://apachecon.dukecon.org/acna/2018/#/scheduledEvent/df977fd305a31b903 [5] http://apachecon.dukecon.org/acna/2018/#/scheduledEvent/22c6c30412a3828d6 [6] http://apachecon.dukecon.org/acna/2018/#/scheduledEvent/fbbb2384fa91ebc6b [7] http://apachecon.dukecon.org/acna/2018/#/scheduledEvent/88d50c3613852c2de - To unsubscribe, e-mail: user-unsubscr...@poi.apache.org For additional commands, e-mail: user-h...@poi.apache.org
