1 2 3 Previous Next 96 Replies Latest reply on Jan 26, 2011 6:32 AM by merih sakarya

    Excel for Seam preview

    Nicklas Karlsson Master

      Hi,


        Daniel Roth and myself have been tinkering with Excel for Seam in the same manner PDF docs can be created with iText.


      If you'd like to check it out, download the jboss-seam-excel.jar here, grab JExcelAPI 2.6.6 here and you should be all set!


      Documention is under construction but you can grab the docbook-draft from the source in the tracking JIRA


      Feedback and bugs can be reported to this thread or in the JIRA. Please remember we are just entering testing so the warranty is If it breaks, you get to keep the pieces ;-)


      simple use case:


      <e:workbook xmlns:e="http://jboss.com/products/seam/excel">
           <e:worksheet>
                <e:cell value="Hello World"/>
           </e:worksheet>
      </e:workbook>
      



      normal use case


      <e:workbook 
           xmlns:e="http://jboss.com/products/seam/excel"
           xmlns:f="http://java.sun.com/jsf/core">
           <e:worksheet value="#{personList.personList}" var="person">
                <e:column>
                     <f:facet name="header">
                          <e:cell value="First name"/>
                     </f:facet>
                     <e:cell value="#{person.firstName}"/>
                </e:column>
                <e:column>
                     <f:facet name="header">
                          <e:cell value="Last name"/>
                     </f:facet>
                     <e:cell value="#{person.lastName}"/>
                </e:column>
                <e:column>
                     <f:facet name="header">
                          <e:cell value="Age"/>
                     </f:facet>
                     <e:cell value="#{person.age}"/>
                </e:column>
           </e:worksheet>
      </e:workbook>
      



      use case on stereoids


      <e:workbook 
           xmlns:e="http://jboss.com/products/seam/excel"
           xmlns:f="http://java.sun.com/jsf/core">
           <e:cellTemplate name="general">
                <e:font name="Times New Roman"/>
           </e:cellTemplate>
           <e:cellTemplate name="header">
                <e:font bold="true" color="white"/>
                <e:background color="green"/>
           </e:cellTemplate>
           <e:cellTemplate name="data">
                <e:font italics="true"/>
                <e:border type="right" color="yellow" lineStyle="thin"/>
           </e:cellTemplate>
           <e:worksheetTemplate name="general">
                <e:headerFooter type="header">
                     <f:facet name="left">
                          <e:headerFooterCommands>
                               <e:headerFooterCommand command="time"/>
                          </e:headerFooterCommands>
                     </f:facet>
                </e:headerFooter>
           </e:worksheetTemplate>
           <e:worksheet value="#{personList.personList}" var="person" templates="general">
                <e:mergeCells startColumn="10" startRow="10" endColumn="12" endRow="12"/>
                <e:cell column="10" row="10" value="Hello World"/>
                <e:headerFooter type="header">
                     <f:facet name="right">
                          <e:headerFooterCommands>
                               <e:headerFooterCommand command="date"/>
                          </e:headerFooterCommands>
                     </f:facet>
                </e:headerFooter>
                <e:column autoSize="true">
                     <f:facet name="header">
                          <e:cell value="First name" templates="general,header"/>
                     </f:facet>
                     <e:cell value="#{person.firstName}" templates="general,data">
                          <e:font color="pink"/>
                          <e:background color="periwinkle" pattern="solid"/>
                     </e:cell>
                </e:column>
                <e:column>
                     <f:facet name="header">
                          <e:cell value="Last name" templates="general,header"/>
                     </f:facet>
                     <e:cell value="#{person.lastName}" templates="general,data"/>
                </e:column>
                <e:column>
                     <f:facet name="header">
                          <e:cell value="Age" templates="general,header"/>
                     </f:facet>
                     <e:cell value="#{person.age}" templates="general,data">
                          <e:numericValidation value="32" condition="greater_equal"/>
                     </e:cell>
                </e:column>
           </e:worksheet>
      </e:workbook>
      



      There is more, have a look at the supported features on the JExcelAPI homepage for hints

        • 1. Re: Excel for Seam preview
          Chris Simons Expert

          Very exciting, Daniel.


          Looking at the JExcelAPI web page mostly answers my questions, but just to post my question here for others...does the API allow a developer to take a data model, list, or other collection - that might otherwise be displayed in a JSF data table - to be converted and opened natively in Excel on the user's desktop?


          Thanks.

          • 2. Re: Excel for Seam preview
            Siarhei Dudzin Apprentice

            This is very interesting! I will definitely pay close attention to this!

            • 3. Re: Excel for Seam preview
              Nicklas Karlsson Master

              Yes, this is the basic idea.

              • 4. Re: Excel for Seam preview
                Nicklas Karlsson Master

                The JIRA indicates it has been scheduled for 2.1.0.BETA1 and hopefully Norman Richards will merge it into the svn repository in a few weeks when he gets back from his tour.


                Until then, feel free to give the pre-compiled jar a spin, there is probably plenty of bugs for everyone to discover ;-)

                • 5. Re: Excel for Seam preview
                  Chris Simons Expert

                  Niklas- thank you for the contribution.  I will most likely give it a whirl.  I'm curious though, I'd like to be able to abstract the exporting of the data to Excel rather than create a page with a hard-coded dataset. 


                  The inclusion of EL for creating the Excel file is awesome, but can you clarify whether this utility could be used in a more generic fashion?


                  Thanks.

                  • 6. Re: Excel for Seam preview
                    Nicklas Karlsson Master

                    You can use JExcelAPI directly in your backing bean. Something like (coding from my head)


                          ByteArrayOutputStream b = new ByteArrayOutputStream();
                          WritableWorkbook w = Workbook.createWorkbook(b);
                          WritableSheet s = w.createSheet("Sheet", 0);
                          for (int col = 0; col <= 10; ) {
                             for (int row = 0; col <= 10; ) {
                                s.addCell(new Label(col, row, "foo"));
                             }
                          }
                          w.write();
                          w.close();
                          byte[] d = b.toByteArray();
                    
                    


                    and then do what you want with the bytes.

                    • 7. Re: Excel for Seam preview
                      Daniel Roth Apprentice

                      Collections valid for iteration over cells are:
                      Iterable, JSF Datamodel with iterable wrapped data, Query(from the seam framework) or an array.

                      • 8. Re: Excel for Seam preview
                        Nicklas Karlsson Master

                        I have experimented with proof-of-concept support for quickdumps from the view in the form of


                        <h:form id="form">
                            <h:dataTable id="table" .../>
                            <h:commandLink value="Export" action="#{org.jboss.seam.excel.jxl.export('form:table')}"/>
                        </h:form>
                        



                        Some questions:


                        1. Flexible enough? I noticed that IE opens it in the same window (not that practical), can you execute it in a new window if you like?


                        2. Is there a way (short of writing a listener) of finding out which component executed the last action? The table id wouldn't be neccessary if I could examine if the commandLink has the dataTable as a parent.


                        3. How could I use css to make the export look the same as the datatable? The css is all client-side and just text as far as the server-side is concerned, right? Can I access the css files the view has referenced? Is there any handy library I could use to do the cascading?

                        • 9. Re: Excel for Seam preview
                          Chris Simons Expert

                          Niklas,


                          1) From my standpoint, the code above is practical and simple.  I like it.  The new window option would be important, but that be supplied as part of the h:commandLink onClick (?).


                          2) I bet that's possible but I think the table ID is an adequate solution.  For more complex requirements with code-generated tables, this could be an area of improvement.


                          3) That would be sweet to be able to provide at least a simple CSS override as the table is exported to Excel.  Wish I could help you more with your specific questions...

                          • 10. Re: Excel for Seam preview
                            Mikael Andersson Master

                            A feature request (which isn't really that important):


                            Would it be possible to use an existing Excel file as a template?


                            I know this is possible with JExcel, but not sure how doable it is within the Seam context.


                            cheers,
                            micke

                            • 11. Re: Excel for Seam preview
                              Nicklas Karlsson Master

                              Already supported through the workbook-level templateURI attribute. Although I'm not sure what URI-format is most suitable in seam/webapp-context...

                              • 12. Re: Excel for Seam preview
                                Nicklas Karlsson Master

                                Chris Simons wrote on May 26, 2008 16:45:


                                1) From my standpoint, the code above is practical and simple.  I like it.  The new window option would be important, but that be supplied as part of the h:commandLink onClick (?).


                                Hmm. It's probably possible in some way, otherwise there would be no way to execute an action in a new window, one would think...



                                Chris Simons wrote on May 26, 2008 16:45:


                                2) I bet that's possible but I think the table ID is an adequate solution.  For more complex requirements with code-generated tables, this could be an area of improvement.


                                One option is an <e:export/> tag with an overridable facet that renderes as a small xls icons with a link as default. This tag could then be placed as a child of the table or externally with a forTable-attribute specifying the ID of the table.



                                Chris Simons wrote on May 26, 2008 16:45:


                                3) That would be sweet to be able to provide at least a simple CSS override as the table is exported to Excel.  Wish I could help you more with your specific questions...


                                One hack could be placing custom css in the style attribute of the table. I think most browsers silently drop any css that isn't recognized(?) but we could still parse it ourselves. Sure, there would be some duplication but you wouldn't have to write a separate xhtml page for the export and you wouldn't have to do the css cascading yourself.

                                • 13. Re: Excel for Seam preview
                                  Nicklas Karlsson Master

                                  Tried the style attribute and it appears to be doable. Probably not worth exposing everything but fonts, background, alignments etc could be useful.


                                  There is of course the problem of the flat structure of a string, you need style=jxlHeaderFontSize : 12; jxlDataFontSize : 10 etc.


                                  Or if you want the cascading templates stuff you need a way to connect attributes to templates with names like jxl-header-FontSize or then just have a jxlTemplate:header;jxlFontSize:12;jxlTemplate:data;jxlFontSize:10 and collect everything in the latest template definition and then have template references on the output tags

                                  • 14. Re: Excel for Seam preview
                                    Nicklas Karlsson Master

                                    (trying to break the longest monologue record here)


                                    ended up with the .templatename postfix for css in the exporter. So now you can have


                                    <h:dataTable value="#{personList.personList}" var="person" id="t" 
                                         style="xlsFontName : Times New Roman; xlsFontSize.header : 12; xlsFontSize.data : 10; xlsBackgroundColor.foo : red">
                                    
                                    



                                    which defines four templates



                                    • "global" (without suffix): font Times New Roman

                                    • "header" (.header suffix): font size 12

                                    • "data" (.data suffix): font size 10

                                    • "foo" (.foo suffix): background color red



                                    all cells have the global template applied to them automagically,
                                    header cells have the additional header template and data cells the data template.


                                    extra templates are appended with the xlsTemplates attribute


                                    <h:outputText value="#{person.age}" style="xlsTemplates : foo;"/>
                                    



                                    (so the actual format applied to that cell is Times New Roman 10px on a red background)


                                    1 2 3 Previous Next