13 Replies Latest reply on Apr 26, 2010 2:58 PM by wesleyhales

    Seam - Export datatable to Excel

      Hello,

       

      I've a portlet containing a datatable which I'm trying to export to an excel format. See the code below:

      {code}

          <h:form id="form">

       

              <h:commandLink value="Export" action="#{excelExporter.export('form:main')}"/>

       

              <rich:dataTable value="#{positionAction.flatten}" var="positionSummary" id="main" rendered="#{positionAction.flatten != null}" >

       

                  <f:facet name="header">

      etc...

      {code}

       

      When I run the application in standalone (not in a portlet), the excel document is correctly downloaded. When running it in a portlet, the server get busy for a moment (doing the job), but no files are delivered back to the browser.

      Is it an issue in portlet bridge? or do I need to add something else in my code to have it working?

       

      Thanks,

      Regards.

        • 1. Re: Seam - Export datatable to Excel
          wesleyhales

          Are you using a seam component e:workbook to export the datatable, or are you manually handling the mimetype and servlet response in your backing bean for the spreadsheet?

          • 2. Re: Seam - Export datatable to Excel

            The Seam component is provided by the package jboss-seam-excel itself, I've not developped it (this component is named excelReporter).

            The servlet response is made by this component. Please see below its implementation:

             

            Regards,

             

             

            package org.jboss.seam.excel.exporter;

             

            import java.io.IOException;

            import java.util.ArrayList;

            import java.util.HashMap;

            import java.util.Iterator;

            import java.util.List;

            import java.util.Map;

             

            import javax.faces.component.UIComponent;

            import javax.faces.component.UIData;

            import javax.faces.component.UIOutput;

            import javax.faces.context.FacesContext;

             

            import org.jboss.seam.ScopeType;

            import org.jboss.seam.annotations.Install;

            import org.jboss.seam.annotations.Name;

            import org.jboss.seam.annotations.Scope;

            import org.jboss.seam.annotations.intercept.BypassInterceptors;

            import org.jboss.seam.core.Interpolator;

            import org.jboss.seam.core.Manager;

            import org.jboss.seam.document.ByteArrayDocumentData;

            import org.jboss.seam.document.DocumentData;

            import org.jboss.seam.document.DocumentStore;

            import org.jboss.seam.excel.ExcelFactory;

            import org.jboss.seam.excel.ExcelWorkbook;

            import org.jboss.seam.excel.ExcelWorkbookException;

            import org.jboss.seam.excel.css.CSSNames;

            import org.jboss.seam.excel.css.ColumnStyle;

            import org.jboss.seam.excel.css.Parser;

            import org.jboss.seam.excel.css.StyleMap;

            import org.jboss.seam.excel.ui.ExcelComponent;

            import org.jboss.seam.excel.ui.UICell;

            import org.jboss.seam.excel.ui.UIColumn;

            import org.jboss.seam.excel.ui.UIWorkbook;

            import org.jboss.seam.excel.ui.UIWorksheet;

            import org.jboss.seam.navigation.Pages;

             

            /**

            * Excel export class that exports a UIData component to an Excel workbook

            *

            * @author Nicklas Karlsson (nickarls@gmail.com)

            * @author Daniel Roth (danielc.roth@gmail.com)

            *

            */

            @Name("org.jboss.seam.excel.exporter.excelExporter")

            @Scope(ScopeType.EVENT)

            @Install(precedence = Install.BUILT_IN)

            @BypassInterceptors

            public class ExcelExporter

            {

               // The excel workbook implementation

               private ExcelWorkbook excelWorkbook = null;

             

               // A map of known column widths

               private Map<Integer, Integer> columnWidths = new HashMap<Integer, Integer>();

             

               /**

                * Helper method to call the exporter and use the default excel workbook

                * implementation

                *

                * @param dataTableId

                */

               public void export(String dataTableId)

               {

                  export(dataTableId, "");

               }

             

               /**

                * Exports the UIData object to Excel workbook. Looks up the component, parse

                * the templates, iterates the columns and the UIOutput elements within

                *

                * @param dataTableId id of data table to export

                * @param type ExcelWorkbook implementation to use

                */

               @SuppressWarnings("unchecked")

               public void export(String dataTableId, String type)

               {

                  excelWorkbook = ExcelFactory.instance().getExcelWorkbook(type);

             

                  Parser parser = new Parser();

             

                  // Gets the datatable

                  UIData dataTable = (UIData) FacesContext.getCurrentInstance().getViewRoot().findComponent(dataTableId);

                  if (dataTable == null)

                  {

                     throw new ExcelWorkbookException(Interpolator.instance().interpolate("Could not find data table with id #0", dataTableId));

                  }

             

                  // Inits the workbook and worksheet

                  UIWorkbook uiWorkbook = new UIWorkbook();

                  excelWorkbook.createWorkbook(uiWorkbook);

                  UIWorksheet uiWorksheet = new UIWorksheet();

                  uiWorkbook.getChildren().add(uiWorksheet);

                  uiWorksheet.setStyle(Parser.getStyle(dataTable));

                  uiWorksheet.setStyleClass(Parser.getStyleClass(dataTable));

                  excelWorkbook.createOrSelectWorksheet(uiWorksheet);

             

                  // Saves the datatable var

                  String dataTableVar = dataTable.getVar();

                  Object oldValue = FacesContext.getCurrentInstance().getExternalContext().getRequestMap().get(dataTableVar);

             

                  // Processes the columns

                  List<javax.faces.component.UIColumn> columns = ExcelComponent.getChildrenOfType(dataTable.getChildren(), javax.faces.component.UIColumn.class);

                  columnWidths = parseColumnWidths(uiWorksheet);

                  int col = 0;

                  for (javax.faces.component.UIColumn column : columns)

                  {

                     ColumnStyle columnStyle = new ColumnStyle(parser.getCascadedStyleMap(column));

                     boolean cssExport = columnStyle.export == null || columnStyle.export;

                     if (column.isRendered() && cssExport)

                     {

                        uiWorksheet.getChildren().add(column);

                        Iterator iterator = UIWorksheet.unwrapIterator(dataTable.getValue());

                        processColumn(column, iterator, dataTableVar, col++);

                        excelWorkbook.nextColumn();

                     }

                  }

             

                  // Restores the data table var

                  if (oldValue == null)

                  {

                     FacesContext.getCurrentInstance().getExternalContext().getRequestMap().remove(dataTableVar);

                  }

                  else

                  {

                     FacesContext.getCurrentInstance().getExternalContext().getRequestMap().put(dataTableVar, oldValue);

                  }

             

                  // Redirects to the generated document

                  redirectExport();

             

               }

             

               /**

                * Parses column widths from a worksheet tag

                *

                * @param worksheet The worksheet to get the style from

                * @return The map of column number -> column width

                */

               private Map<Integer, Integer> parseColumnWidths(UIWorksheet worksheet)

               {

                  Map<Integer, Integer> columnWidths = new HashMap<Integer, Integer>();

                  Parser parser = new Parser();

             

                  StyleMap styleMap = parser.getCascadedStyleMap(worksheet);

                  for (Map.Entry<String, Object> entry : styleMap.entrySet())

                  {

                     String key = entry.getKey();

                     if (key.startsWith(CSSNames.COLUMN_WIDTHS))

                     {

                        String columnIndexString = key.substring(CSSNames.COLUMN_WIDTHS.length());

                        int columnIndex = Integer.parseInt(columnIndexString);

                        columnWidths.put(columnIndex, (Integer) entry.getValue());

                     }

                  }

                  return columnWidths;

               }

             

               /**

                * Puts document in store and redirects

                */

               private void redirectExport()

               {

                  String viewId = Pages.getViewId(FacesContext.getCurrentInstance());

                  String baseName = Pages.getCurrentBaseName();

                  DocumentData documentData = new ByteArrayDocumentData(baseName, excelWorkbook.getDocumentType(), excelWorkbook.getBytes());

                  String id = DocumentStore.instance().newId();

                  String url = DocumentStore.instance().preferredUrlForContent(baseName, excelWorkbook.getDocumentType().getExtension(), id);

                  url = Manager.instance().encodeConversationId(url, viewId);

                  DocumentStore.instance().saveData(id, documentData);

                  try

                  {

                     FacesContext.getCurrentInstance().getExternalContext().redirect(url);

                  }

                  catch (IOException e)

                  {

                     throw new ExcelWorkbookException(Interpolator.instance().interpolate("Could not redirect to #0", url), e);

                  }

               }

             

               /**

                * Processes a datatable column

                *

                * @param column The column to parse

                * @param iterator The iterator to the data

                * @param var The binding var

                * @param col

                */

               @SuppressWarnings("unchecked")

               private void processColumn(javax.faces.component.UIColumn column, Iterator iterator, String var, int columnIndex)

               {

                  // Process header facet

                  UIComponent headerFacet = column.getFacet(UIColumn.HEADER_FACET_NAME);

                  if (headerFacet != null && UIOutput.class.isAssignableFrom(headerFacet.getClass()))

                  {

                     List<UIOutput> headerOutputs = new ArrayList<UIOutput>();

                     headerOutputs.add((UIOutput) headerFacet);

                     processOutputs(column, headerOutputs);

                  }

             

                  // Process data

                  while (iterator.hasNext())

                  {

                     FacesContext.getCurrentInstance().getExternalContext().getRequestMap().put(var, iterator.next());

                     List<UIOutput> dataOutputs = ExcelComponent.getChildrenOfType(column.getChildren(), UIOutput.class);

                     processOutputs(column, dataOutputs);

                  }

             

                  Integer columnWidth = columnWidths.get(columnIndex);

                  if (columnWidth != null)

                  {

                     UIColumn uiColumn = new UIColumn();

                     uiColumn.setStyle(CSSNames.COLUMN_WIDTH + ":" + columnWidth);

                     excelWorkbook.applyColumnSettings(uiColumn);

                  }

             

               }

             

               /**

                * Processes all output type elements (in column)

                *

                * @param outputs The list of outputs to process

                * @param preTemplates The pre-pushed templates

                */

               private void processOutputs(javax.faces.component.UIColumn column, List<UIOutput> outputs)

               {

                  for (UIOutput output : outputs)

                  {

                     if (!output.isRendered())

                     {

                        continue;

                     }

                     UICell cell = new UICell();

                     column.getChildren().add(cell);

                     cell.setId(output.getId());

                     cell.setValue(output.getValue());

                     cell.setStyle(Parser.getStyle(output));

                     cell.setStyleClass(Parser.getStyleClass(output));

             

                     excelWorkbook.addItem(cell);

                  }

               }

             

            }

            • 3. Re: Seam - Export datatable to Excel
              wesleyhales
              Do you see any errors in the console? Otherwise, please attach a sample project war to this thread which will allow me to see what is happening.
              • 4. Re: Seam - Export datatable to Excel

                Hello,

                 

                No there is no exception raised in the console.

                In attachment, a simple (very) test case. A table with just headers.

                When accesing the application outside of portlet, an excel file can be downloaded (http://localhost:8080/TestExcel/test.seam).

                 

                The portlet is available under demo 'tab'.

                 

                Thanks and regards,

                • 5. Re: Seam - Export datatable to Excel
                  wesleyhales

                  Did you get this to work? I tried your example and got:

                  {code:xml}Caused by: java.lang.NoClassDefFoundError: jxl/write/WriteException
                  {code}

                  • 6. Re: Seam - Export datatable to Excel
                    wesleyhales
                    btw, I also added the jxl.jar and it did not work
                    • 7. Re: Seam - Export datatable to Excel

                      Sorry, I missed the jxl-2.6 lib in the war.

                      Can you please try again with the version below in attachment. I've tested against a fresh jboss portal installation.

                       

                      Regards,

                      • 8. Re: Seam - Export datatable to Excel
                        wesleyhales

                        I finally got some time to look into this. It looks like there are some issues on both sides 1) when the excel workbook url is formulated it appends docId like so:

                        {code}return baseUrlForContent(baseName, extension) + "?docId=" + contentId;{code}

                        This is not the proper way to append the parameters because our url ends up looking like:

                        /seamproject/JBossPortletBridge/actionUrl/do/nothing?_jsfBridgeViewId=%2Fseam%2Fdocstore%2Fdocument.xhtml?docId=1&cid=12 (with 2 "?" in the query params)

                         

                        So, 1 fix I know of is needed on the excel component side, as a workaround I added this to ActionRequestExternalContextImpl#redirect

                        {code}if (url.contains("?")){
                                 String tempUrl = url.substring(url.indexOf("?") + 1,url.length());
                                 url = url.substring(0,url.indexOf("?") + 1) + tempUrl.replace("?","&");
                              }{code}

                        as a simple fix, but it needs to be handled properly in the ExcelExporter.

                         

                        And then on the bridge side, we need to have the URL converted as a resource request URL. We are working on that for the next release - and I will post a maven snapshot when it is done.

                        • 9. Re: Seam - Export datatable to Excel

                          Any news on a way to write the resource urls? I am doing something similiar where I need to send a pdf back to the browser but can't do it through the portal as it doesn't handle it as a resource request. I am using Portlet Bridge 2.0CR1 with GateIn 3.0 on JBoss AS 5.1.

                           

                          Thanks,

                           

                          Matt

                          • 10. Re: Seam - Export datatable to Excel
                            wesleyhales

                            I will look into this next week, before we do the 2.0 final release.

                            • 11. Re: Seam - Export datatable to Excel

                              I found the answer in another thread. Here's the code that does it.

                               

                                          String url = getUrlToYourServlet();
                                          FacesContext fc = FacesContext.getCurrentInstance();
                                          url = fc.getApplication().getViewHandler().getResourceURL(fc, url);
                                          url = fc.getExternalContext().encodeResourceURL(url);

                               

                              I then took the result of that and used the following tag in my facelete view to render a button that opens my pdf in a new window.

                               

                                          <a4j:commandButton onclick="window.open('#{resourceEncodedUrl}')" />

                              • 12. Re: Seam - Export datatable to Excel
                                begui

                                Do you have that thread? Can you post your entire solution? Thanks.

                                • 13. Re: Seam - Export datatable to Excel
                                  wesleyhales

                                  That is part of the solution but not the entire thing for this particular problem. We are dealing with the issue of generating a resourceURL for the ExcelExporter component and using the portlet outputsream to generate a binary response.

                                  We are working on this issue now and hope to have a resolution soon.