Addon: Export Table to Excel

Vaadin 6 / 7 / 8 Framework Data Binding
1

Just published an add-on that hopefully will simplify this common task for a lot of people. The information to do this is available in the forums but it still requires a lot of figuring out. The relevant forum messages I consulted were:

http://vaadin.com/forum/-/message_boards/view_message/159583

http://vaadin.com/forum/-/message_boards/view_message/124004

http://vaadin.com/forum/-/message_boards/view_message/33111

The add-on can be found at: http://vaadin.com/directory#addon/tableexport

I didn’t put Excel in the name of the Add-On, even though it only exports to Excel right now, because it is easily expandable, and POI can be used to export to a lot of other formats.

The add-on utility class takes a Table as input and exports a decent Excel file containing the data in the Container. It also handles HierarchicalContainers and the resulting Excel file will have the categories and subcategories properly grouped/outlined.

There are a number of configurable properties. The user can specify a worksheet name, a report title, and an output file name. The user can also specify if there should be a Totals row at the bottom of the export. The user can pass in custom POI CellStyles. However, if none of these are specified, the user only needs to pass in a Table.

This add-on requires the Apache POI library. I used poi-3.8-beta3-20110606.jar.

For sending the file to the browser, the add-on uses Charles Anthony’s solution from: http://vaadin.com/forum/-/message_boards/view_message/159583

The included icon comes from the fugue open source (Creative Commons) icon set.

2

Posted a new version with enhancements, the need for which became quickly apparent, as I was using the project.

The changes include:

  • Refactored to make TableExports to other formats beside Excel more logical. There is now a TableExport master abstract class, and an ExcelExport specific class.
  • Refactored the ExcelExport class to make it easier to more granularly override specific functionality. There are a number of ways for users to get the effects they want. One is to override specific methods. Another is to use the setter/getter functionality built-in. Finally, prior to export, specific parts of the Excel workbook object are made accessible.
  • Enabled having different formats for different cells as opposed to just one format per row.
  • Added documentation
  • It now handles borders for the merged report title area
  • Added handling of Row Headers which are treated just like Column Headers by default, but this can be customized.
  • Enhanced the demo application to allow users to play around with various options. Also provide an example of what an extended class might look like for providing specific export results.
3

Released an update with one fix / enhancement regarding the totals row in an Excel export sheet with hierarchical data. Summing up the column (with SUM formula) will count both categories and sub-categories, resulting in double counting. Subtotal excluding invisible cells isn’t implemented in POI yet (specifically, the Subtotal(109, range) formula). Further, the subtotal calculation will be wrong when a user expands an outline, making the sub-categories visible. It now puts the correct totals in the totals row and leaves it as a value, not a formula.

4

Hi Jonathan

Many thanks for this add-on … it certainly helped me to get Excel exports working quickly!

At the risk of having a “kneejerk” reaction (i.e. I haven’t thought about it in much detail), I have two suggestions for refinements:

  1. My tables (legitimately) include null cell values, resulting in a NullPointerException being thrown in ExcelExport.addDataRow(). My hack solution is to leave the sheet’s cell value uninitialised if “prop.getValue()” is null.
  2. I would like to be able to control which window is used to house the export result … “_self” might be an appropriate default, but for my current application I specifically want to use a different window. My hack solution is to explicitly change the “_self” constant, but a better approach would allow the user to specify a target window name.

[Update: Another thing has cropped up … collapsed columns are shown; my hack solution was to change the propIds creation mechanism, only adding those “visible” properties where table.isColumnCollapsed(propId) returns false.]

Kind regards, and thanks again

  • Martin
5

All good suggestions (and more importantly easy to implement). Just released a new version with these 3 changes:

  • Ability to exclude collapsed columns - call excludeCollapsedColumns() after construction, if you want collapsed column excluded
  • Better null handling - null property values are perfectly legitimate and should have been checked for
  • Ability to specify different windows to export to - call setExportWindow()
6

Many thanks!

Unfortunately, I still have a couple of problems with v0.1.2:

  • Use of excludeCollapsedColumns (with a collapsed column) throws a ConcurrentModificationException
  • I can call setExportWindow() ok, but the specified window name doesn’t seem to be used.

Kind regards

  • Martin
7

My bad. Poor testing on the first and sloppy coding on the second. Both are now fixed and the demo app now tests collapsed columns.

8

Another gremlin, I’m afraid :-(.

We have some users browsing with Internet Explorer, versions 7 and 8. The TableExport export mechanism doesn’t work for them, with IE reporting that it cannot download the file.

Some hunting later, I think the problem is due to strange IE handling of the Cache-Control directive (e.g. see http://drupal.org/node/163298). My workaround is to replace the “private,no-cache,no-store” value with “must-revalidate” … all currently seems to work fine for our IE and Firefox users, including no obvious problems with caching (i.e. new requests seem to return new workbooks … albeit without huge numbers of hops between server and browser, if you see what I mean).

So … what do you think? Worth implementing some such fix in TableExport, or does it break something important for other people? Perhaps compromise by allowing an override for the Cache-Control detail?

Kind regards

  • Martin

P.S. Does TableExport override the supplied MIME type?

9

It wasn’t actually using the MIME type that was passed in. It was just using the hard-coded Excel mime type. I fixed this and I added a getMimeType and setMimeType capability. The mime type should be set before calling sendConverted(). If you don’t set MIME type, it still uses correct Excel default type.

I created a google code hosting project, tableexport-for-vaadin (http://code.google.com/p/tableexport-for-vaadin/). If you send me / post your google account email address, I will add you to the project. You can then make this IE change.

Jonathan

10

I use with table have some column contain Panel Element, it show output is Panel.toString()>> "
zxczxxczxc@scczcx
"
Can I have Render Table have some column content Panel element with ad-on
or Customize colunm for render :bashful:

11

11881.jpg

12

If I understand you correctly, you probably need to create a new Field which extends CustomField (add-on) called something like ThreeItemField which shows the 3 items and overrides the toString() method to output just the value of the case ID. You then need to have a custom fieldfactory for the table which serves up a ThreeItemField whenever the property id is “Case Id”.

Jonathan

13

Thank you, now i use simple code.

[code]

public class CaseIdPanel extends Panel{
private String caseId;
public CaseIdPanel(ComponentContainer content,String caseId){
super(content);
this.caseId = caseId;
}
public String getCaseId() {
return caseId;
}
public void setCaseId(String caseId) {
this.caseId = caseId;
}
@Override
public String toString() {
return caseId;
}
}
[/code]
11883.jpg

14

Hi Jonathan,

I discovered your addon today and I like it a lot. I’m using version 1.1.4 together with Vaadin version 6.6.6.

With this version I still get NullPointerExceptions in ExcelExport#addDataRow. I can get around this problem by temporarily collapsing the generated column that causes this problem, but maybe it’s possible to avoid this at all?

Since I’m using several generated columns in my tables, I would also like to know if the Excel Export can show the content of the generated columns instead of the content of the container column.

Best regards

Andreas

15

Just an idea: could you use the
table
 cell alignment for the cell alignment of the excel cells?

Regards

Andreas

16

Just uploaded a new version with both things implemented, although minimally tested.

Exported cells now have the same cell alignment as they had in the Table. This was pretty easy to do.

Visible generated columns are now exported. However, instead of implementing Table.ColumnGenerator, they now need to implement ExportableColumnGenerator, a new class in the jar. This class just extends Table.ColumnGenerator and requires only one additional method to be written. This is required because Vaadin provides no mechanism for accessing the data in the generated column.

Let me know if you find any problems. This project is hosted on google code (http://code.google.com/p/tableexport-for-vaadin/) and you are welcome to participate if you wish. Let me know and I can add you as a member.

The issues list for the project is: http://code.google.com/p/tableexport-for-vaadin/issues/list

17

Hi Jonathan,

thanks for the quick response! Just two little things I discovered:

1.: could you please align the header cells in the same way?

2.: I have a column with Doubles that are formatted with “###,##0.00 S;###,##0.00 H”. This column is a simple one (not generated) and it’s right aligned but the Excel Export for this column is left aligned.

Thanks again for the good work

Andreas

18

Andreas - How are the doubles in the Vaadin table formatted with “###,##0.00 S;###,##0.00 H”?

19

Hi Jonathan,

I have my own FormatTable that extends com.vaadin.ui.Table. In Table.formatPropertyValue() the Doubles are formatted like this:

protected String formatPropertyValue(Object rowId, Object colId, Property property)
{
  DecimalFormat ndf = (DecimalFormat) DecimalFormat.getInstance(myLocale);
  ndf.applyPattern("###,##0.00 S;###,##0.00 H");
  Object v = property.getValue();
  if (v instanceof Double)
  {
    return ndf.format(v);
  }
  ...
}

Hope it helps.

Andreas

20

Just released an updated version 1.2.1. This fixes some outstanding and requested issues:

From Andreas’ post, headers (and also totals rows) are now aligned according to the table column alignment.
Also, fixed the issue with Doubles alignment.

Per a request by Farhan Khan on the add-on page, implemented proper handling of dates.

Also provided an easier way to set Double and Date formats for columns in the exported Excel sheet. Andreas formatted the Table with “###,##0.00 S;###,##0.00 H”, but something like this would not make it to the exported sheet, unless the user overrode the method:

[indent]

[/indent]protected CellStyle getDataStyle(final Object rootItemId, final int row, final int col)

Now the user can just use method:

[indent]

[/indent]public void setExcelFormatOfProperty(final Object propertyId, final String excelFormat)

in ExcelExport. Alternately, the user can set the default Date and Double formats using:

[indent]

[/indent]public void setDateDataFormat(final String excelDateFormat) {

[indent]

[/indent]public void setDoubleDataFormat(final String excelDoubleFormat) {

Finally, enhanced the demo application to highlight new features, which provides some sample code to see how things are intended to be used.