6 Replies Latest reply on Jul 20, 2009 5:48 PM by Nicklas Karlsson

    Embedded (Inline) Excel Workbook Generation from DataModel of a Page-Scoped Seam Component

    Sean Gurevich Newbie

      I'm trying to conditionally generate a workbook from a results section, Richfaces tab panel really, of a search page. Seam's excel library examples provide clearly documented use of generating a workbook from either a standalone page or invoking Javascript that converts an existing data table. In this case, the table of results, a Richfaces data table, contains a summarized set of data. The full data set must be exported to the spreadsheet. The same data model that is used to build the web-based table is also used to generate the workbook. The component that contains the data model is of page scope.


      Code Snippets


      Seam component:


      @Name("search")
      @Scope(ScopeType.PAGE)
      @AutoCreate
      public class searchAction {
      ...
          @DataModel
          private List<Result> results;
      
          private boolean excelExport;
      ...
          public List<Result> getResults () {
              return results;
          }
      
          public void setResults (List<Result> results) {
              this.results = results;
          }
      
          public boolean isExcelExport () {
              return excelExport;
          }
      
          public void setExcelExport (boolean excelExport) {
              this.excelExport = excelExport;
          }
      }
      



      JSF Page:


                          <rich:dataTable id="resultsTable" value="#{search.results}" var="result" width="100%">
                              <rich:column sortBy="#{result.meId}">
                                  <f:facet name="header">
                                      <h:outputText value="#{messages['results.column.me_id']}" />
                                  </f:facet>
                                  <h:outputText value="#{result.meId}" />
                              </rich:column>
      ...
                          </rich:dataTable>
      ...
                          <a4j:commandLink value="Export to Excel" action="#{search.setExcelExport(true)}" reRender="excelExportRegion"
                              ajaxSingle="true" status="doNothingStatus" />
                          <a4j:region id="excelExportRegion" renderRegionOnly="true" rendered="#{search.excelExport}">
                              <a4j:status id="doNothingStatus" />
                              <e:workbook>
                                  <e:worksheet value="#{search.results}" var="result">
                                      <e:column>
                                          <f:facet name="header">
                                              <e:cell value="#{messages['results.column.me_id']}" />
                                          </f:facet>
                                          <e:cell value="#{result.meId}" />
                                      </e:column>
                                  </e:worksheet>
                              </e:workbook>
                          </a4j:region>
                      </h:form>
                  </rich:tab>
              </rich:tabPanel>
          </ui:define>
      </ui:composition>
      



      You can imagine the hierarchy of the page from the terminating tags. The page is a part of a JSF template. The region isolates the rerender to activate the workbook construction and download. The status that does nothing is there to avoid leaving the top-level status from spinning indefinitely once the download begins. The result of this code produces an Excel file that contains only the (correct) header value. The single data row that is present in the web-based data table is not being rendered on the worksheet. I have used a debugger to verify that the list identified as a DataModel (results), which is used to satisfy the ajax call to rerender the workbook region, does contain the single result element, as expected, since we are still on the same page.


      Environment


      Jetty (Maven Plugin) 6.1.10 running on JDK 1.6.0_13


      Library Dependencies


      <dependency>
              <groupId>org.jboss.seam</groupId>
              <artifactId>jboss-el</artifactId>
              <version>2.0.0.GA</version>
      </dependency>
      <dependency>
              <groupId>org.jboss.seam</groupId>
              <artifactId>jboss-seam</artifactId>
              <version>2.1.1.GA</version>
      </dependency>
      <dependency>
              <groupId>org.jboss.seam</groupId>
              <artifactId>jboss-seam-ui</artifactId>
              <version>2.1.1.GA</version>
      </dependency>
      <dependency>
              <groupId>org.jboss.seam</groupId>
              <artifactId>jboss-seam-ioc</artifactId>
              <version>2.1.1.GA</version>
      </dependency>
      <dependency>
              <groupId>org.jboss.seam</groupId>
              <artifactId>jboss-seam-debug</artifactId>
              <version>2.1.1.GA</version>
      </dependency>
      <dependency>
              <groupId>org.jboss.seam</groupId>
              <artifactId>jboss-seam-excel</artifactId>
              <version>2.1.1.GA</version>
      </dependency>
      <dependency>
              <groupId>jboss</groupId>
              <artifactId>jboss-common</artifactId>
              <version>4.2.2.GA</version>
      </dependency>
      
      <dependency>
              <groupId>javax.faces</groupId>
              <artifactId>jsf-api</artifactId>
              <version>1.2_12</version>
      </dependency>
      <dependency>
              <groupId>javax.faces</groupId>
              <artifactId>jsf-impl</artifactId>
              <version>1.2_12</version>
      </dependency>
      <dependency>
              <groupId>com.sun.facelets</groupId>
              <artifactId>jsf-facelets</artifactId>
              <version>1.1.14</version>
      </dependency>
      <dependency>
              <groupId>org.richfaces.framework</groupId>
              <artifactId>richfaces-api</artifactId>
              <version>3.3.1.BETA3</version>
      </dependency>
      <dependency>
              <groupId>org.richfaces.framework</groupId>
              <artifactId>richfaces-impl</artifactId>
              <version>3.3.1.BETA3</version>
      </dependency>
      <dependency>
              <groupId>org.richfaces.ui</groupId>
              <artifactId>richfaces-ui</artifactId>
              <version>3.3.1.BETA3</version>
      </dependency>
      
      <dependency>
          <groupId>javax.el</groupId>
          <artifactId>el-api</artifactId>
          <version>1.0</version>
          <scope>provided</scope>
      </dependency>
      



      Side Notes



      • I can access the results DataModel directly, since results becomes a named component by virtue of the DataModel annotation, but I'd rather not have component names just popping up in the XHTML without a context. Seam's proxy still wraps the DataModelList around my list successful when called this way and I've confirmed the outcome with the workbook is the same when using just results in place of search.results.

      • I realize that once the excelExport flag turns to true, it will always be true, but that's not a problem in this case as the tab panel is client-side rendered and the excelExportRegion is only rerendered when the Export to Excel link is clicked. BTW, yes the link is just temporary and its text will come from a resource bundle as everything else on the page. ;-)



      Hopefully, I've provided enough detail while at the same time pruning and abbreviating the excess to convey my problem. I have tried upgrading to Seam version 2.1.2 for all Seam libraries, but ran into the java.lang.IllegalAccessError: tried to access class javassist.bytecode.StackMapTable$Writer from class org.jboss.seam.util.ProxyFactory problem. I have found some suggestions to avoid this problem by declaring an explicit javaassist dependency version, but decided to revert to the working version and tackle the non-trivial upgrade at a different time. I would certainly upgrade, if that is the solution to this problem; however, there is no documentation that would suggest so at this time and I would rather not get side-tracked by an upgrading discussion.


      Greatly appreciate suggestions toward a solution for the problem of generating an inline workbook by simply rerendering a portion of the page.


      Sean

        • 1. Re: Embedded (Inline) Excel Workbook Generation from DataModel of a Page-Scoped Seam Component
          Nicklas Karlsson Master

          Hmm, never tried the exporter like that... Have you tried using the debugger in the excel exporter to see what data it gets?

          • 2. Re: Embedded (Inline) Excel Workbook Generation from DataModel of a Page-Scoped Seam Component
            Sean Gurevich Newbie

            It never actually calls the ExcelExporter. I've noticed that while stepping through the UIColumn class, the UIWorksheet.getVar() method returns null. When, I have clearly defined a var attribute in the XHTML. Therefore, it ends up falling through to the No var, no iteration... case.


            Here is the section of code, inside the UIColumn.encodeBegin(FacesContext facesContext) method starting at line 63 of jboss-seam-excel-2.1.1.GA.jar, that I am referring to:


            ...
                   // Get UiCell template this column's data cells and iterate over sheet data
                  for (WorksheetItem item : getItems(getChildren()))
                  {
                     Object oldValue = null;
                     Iterator iterator = null;
                     // Store away the old value for the sheet binding var (if there is one)
                     if (sheet.getVar() != null) {
                        oldValue = FacesContext.getCurrentInstance().getExternalContext().getRequestMap().get(sheet.getVar());
                        iterator = sheet.getDataIterator();
                     } else {
                        // No var, no iteration...
                        iterator = new ArrayList().iterator();
                     }
                     while (iterator.hasNext())
                     {
                        // Store the bound data in the request map and add the cell
                        FacesContext.getCurrentInstance().getExternalContext().getRequestMap().put(sheet.getVar(), iterator.next());
                        excelWorkbook.addItem(item);
                     }
            
                     //  No iteration, nothing to restore
                     if (sheet.getVar() == null) {
                        continue;
                     }
            ...
            



            Inspecting the sheet.getVar() method, I get a return of null, which is consistent with stepping through the conditional statements in the debugger. Interestingly enough, when I inspect the sheet.getDataIterator(), it actually has the appropriate results data in the iterator.


            A Different Perspective


            I converted the workbook block to generate an h:dataTable instead, since the data iterator pattern is similar. I quickly discovered that the h:dataTable would not render upon clicking the Export to Excel a4j:commandLink. Refactoring the elements to where the h:dataTable would show up produced the following XHTML code:


            ...
                                <a4j:region id="excelExportRegion" renderRegionOnly="true">
                                    <a4j:status />
                                    <a4j:commandLink value="Export to Excel" action="#{search.setExcelExport(true)}" reRender="exportWorkbookPanel"
                                        ajaxSingle="true" />
                                    <h:panelGroup id="exportWorkbookPanel">
                                        <h:dataTable value="#{search.results}" var="result" rendered="#{search.excelExport}">
                                            <h:column>
                                                <f:facet name="header">
                                                    <h:outputText value="#{messages['results.column.account']}" />
                                                </f:facet>
                                                <h:outputText value="#{result.account}" />
                                            </h:column>
                                            <h:column>
                                                <f:facet name="header">
                                                    <h:outputText value="#{messages['results.column.me_id']}" />
                                                </f:facet>
                                                <h:outputText value="#{result.meId}" />
                                            </h:column>
                                        </h:dataTable>
                                    </h:panelGroup>
                                </a4j:region>
            ...
            



            Then, I substituted the h:dataTable specific markup with the e:workbook:


            ...
                                <a4j:region id="excelExportRegion" renderRegionOnly="true">
                                    <a4j:status />
                                    <a4j:commandLink value="Export to Excel" action="#{search.setExcelExport(true)}" reRender="exportWorkbookPanel"
                                        ajaxSingle="true" />
                                    <h:panelGroup id="exportWorkbookPanel">
                                        <e:workbook rendered="#{search.excelExport}">
                                            <e:worksheet value="#{search.results}" var="result">
                                                <e:column>
                                                    <f:facet name="header">
                                                        <e:cell value="#{messages['results.column.account']}" />
                                                    </f:facet>
                                                    <e:cell value="#{result.account}" />
                                                </e:column>
                                                <e:column>
                                                    <f:facet name="header">
                                                        <e:cell value="#{messages['results.column.me_id']}" />
                                                    </f:facet>
                                                    <e:cell value="#{result.meId}" />
                                                </e:column>
                                            </e:worksheet>
                                        </e:workbook>
                                    </h:panelGroup>
                                </a4j:region>
            ...
            



            But sadly, the sheet.getVar() still returned a null and the No var, no iteration... case was selected in UIColumn.encodeBegin(FacesContext facesContext) method.


            Have I run into some behavior of the JSF lifecycle that I fail to understand?


            Sean


            PS: Thanks for the quick response!

            • 3. Re: Embedded (Inline) Excel Workbook Generation from DataModel of a Page-Scoped Seam Component
              Nicklas Karlsson Master

              You'll have to check with some RF wizard, I only know what to do after the exporter is called ;-)

              • 5. Re: Embedded (Inline) Excel Workbook Generation from DataModel of a Page-Scoped Seam Component
                Sean Gurevich Newbie

                Turns out the workbook is generated with data, if you wrap the var attribute's value in an EL.


                Instead of:


                <e:worksheet value="#{search.results}" var="result">
                



                use:


                <e:worksheet value="#{search.results}" var="#{'result'}">
                



                Seems a bit of hack to force the var to be set in this fashion. The iterator definition in the worksheet should probably have the same behavior as a simple h:dataTable or any other dataTable implementation for that matter; though, there's nothing in the documentation that promises this. Perhaps I'm using the workbook in a strange way, but there seems to be an issue around the implementation of the UIWorksheet component. In any case, the workaround is simple enough and the library makes my life abundantly easier.


                Thanks for all the help,


                Sean

                • 6. Re: Embedded (Inline) Excel Workbook Generation from DataModel of a Page-Scoped Seam Component
                  Nicklas Karlsson Master

                  We'll see if Daniel has any comment when he sobers up, er, I mean gets back from vacation :-)