1 2 Previous Next 15 Replies Latest reply on Jul 3, 2009 3:06 PM by allforjava.allforjava.aol.in

    Excel Export: xls-force-type?

    allforjava.allforjava.aol.in
      Hi,

      Even though I had given the <e:cell value="myTableValue" xls-force-type="general"> the .xls generated is with the type 'text'. Therefore the value shown in the column is as '####################################'. And after manually setting the cell format to 'general', in MS Excel, sble to view the contents.

      Even xls-wrap="true" is not working. Please assist with what I'm missing?

      Thank you in advance!
        • 1. Re: Excel Export: xls-force-type?
          nickarls

          I'll have a look at both issues early next week when I'm back from vacation...

          • 2. Re: Excel Export: xls-force-type?
            ericjung2

            I can confirm the problem. Here is an example:


            <e:worksheet name="#{messages.coverLetter}">
              <e:formula column="5" row="#{commitService.committedAmounts.size() + 22}" value="SUM(F21:F#{commitService.committedAmounts.size() + 21})" style="xls-format-mask: $##,###,###,###,###;$(##,###,###,###,###)"/>

            ...
            ...
            <e:worksheet>


            If you turn on log4j TRACE level logging for the org.jboss.seam.excel package, you'll see a note about the format mask not being recognized. I don't have the exact text in front of me, but let me know if you want it. This is with Seam 2.1.1.GA, not 2.1.2.


            Eric
            JExcelAPI Contributor, Forum Moderator, and SourceForge maintainer

            • 3. Re: Excel Export: xls-force-type?
              ericjung2

              Oops, I posted the wrong example. Although my example above demonstrates a bug, it is not the bug the original poster reported. This is what I meant to post to demonstrate the original poster's bug:




              <e:worksheet name="#{messages.coverLetter}" value="#{commitService.committedAmounts}" var="d" startRow="21">
              <e:column>
                <e:cell value="#{cams:formatNumber(d.ffpAmount, 'currencyFormatWithSymbol')}" style="xls-background: white;xls-force-type: number"/>                         
              </e:column>
              ...
              ...
              </e:worksheet>






              Perhaps the bug only surfaces when e:worksheet is iterating. I didn't try it with e:worksheet not having a value attribute. Note the cams:formatNumber() facelet function is there because of my bug report in my previous post (xls-format-mask not working for some kinds of formats)... I'm forced to do the styling in Java code.


              Eric

              • 4. Re: Excel Export: xls-force-type?
                allforjava.allforjava.aol.in
                Thank you Eric for prior reply!

                I'm unable to get about the format mask. I have not used any of them. My e:cell looks as:

                <e:cell value="#{workLog.description}" forceType="general"/> 

                Does the content from DB matter? The description field is of size varchar[1024]. User can enter any formatted/unformatted text vide textArea.

                Nicklas, Happy and Peaceful vacations! And thank you for the ack!
                • 5. Re: Excel Export: xls-force-type?
                  allforjava.allforjava.aol.in

                  Also, i'm using Seam.2.1.2.

                  • 6. Re: Excel Export: xls-force-type?
                    nickarls

                    For some strange reason


                          WritableWorkbook workbook = Workbook.createWorkbook(new File("c:/temp/foo.xls"));
                          WritableSheet worksheet = workbook.createSheet("test", 0);
                          WritableCellFormat cellFormat = new WritableCellFormat(NumberFormats.DEFAULT);
                          Number number = new Number(0, 0, 1f, cellFormat);
                          worksheet.addCell(number);
                          workbook.write();
                          workbook.close();
                    



                    Results in a general format (have to force a numeric NumberFormat to get it right.

                    • 7. Re: Excel Export: xls-force-type?
                      ericjung2

                      Can you patch the seam excel codebase?

                      • 8. Re: Excel Export: xls-force-type?
                        allforjava.allforjava.aol.in
                        Hi Nicklas,

                        1. As suggested I had modified cell to

                        <e:cell value="#{workLog.description}" forceType="number" />. However, the output remains the same. Here is the generated excelsheets a records content (with '#').

                        kalesha S     PORTQUAL-9988     PORTQUAL-9988 Topic     ###############################################################################################################################################################################################################################################################     Open     6/24/2009           5     50     PORTQUAL-9988 COMPLETED 50%

                        2. How can I provide the download and/or server-side saving option f excelsheet, programatically?

                        3. How can i give name to excel generated file name?

                        Note: Query 2 & 3 are towards the given code snippet, Nicklas.
                        • 9. Re: Excel Export: xls-force-type?
                          nickarls

                          1. I'll do a more thorough test matrix of the data type - forceType - formatMask combinations

                          2. For server-side save, try the exportKey on the workbook, do a manual render, pull out the bytearray data and store it

                          3. Try the fileName attribute on the workbook

                          • 10. Re: Excel Export: xls-force-type?
                            ericjung2

                            Nicklas Karlsson wrote on Jun 29, 2009 08:00:


                            1. I'll do a more thorough test matrix of the data type - forceType - formatMask combinations





                            Here is one that doesn't work in 2.1.1GA (untested in 2.1.2):



                            <e:formula column="5" row="#{commitService.committedAmounts.size() + 22}" value="SUM(F21:F#{commitService.committedAmounts.size() + 21})" style="xls-format-mask: $##,###,###,###,###;$(##,###,###,###,###)"/>
                            
                            WARN  [org.jboss.seam.excel.css.Parser] Style component $(##,###,###,###,###) should be of form <key>:<value>
                            
                            





                             

                            • 11. Re: Excel Export: xls-force-type?
                              nickarls

                              Did some testing


                              <e:workbook>
                                   <e:worksheet name="text2general">
                                        <e:cell value="#{data.textData}" row="0" column="0" style="xls-force-type: general"/>
                                        <e:cell value="#{data.textData}" row="1" column="0"/>
                                   </e:worksheet>
                                   <e:worksheet name="date">
                                        <e:cell value="#{data.dateData}" row="0" column="0" style="xls-format-mask: $##,###,###,###,###;$(##,###,###,###,###)"/>
                                   </e:worksheet>
                                   <e:worksheet name="date">
                                        <e:cell value="#{data.longStringData}" row="0" column="0" style="xls-wrap:true"/>
                                   </e:worksheet>
                                   <e:worksheet name="int">
                                        <e:cell value="#{data.intData}" row="0" column="0"/>
                                        <e:cell value="#{data.intData}" row="1" column="0" style="xls-force-type:number"/>
                                        <e:cell value="#{data.intData}" row="2" column="0" style="xls-force-type:number; xls-format-mask:accounting_float"/>
                                        <e:cell value="#{data.intData}" row="3" column="0" style="xls-force-type:number; xls-format-mask:0.00"/>
                                        <e:cell value="#{data.intData}" row="4" column="0" style="xls-format-mask:accounting_float"/>
                                        <e:cell value="#{data.intData}" row="5" column="0" style="xls-format-mask:0.00"/>
                                   </e:worksheet>
                              </e:workbook>
                              



                              Findings:



                              1. With String data, I was able to force the general format

                              2. Wrap works, it goes multiline on data like a\nb\nc

                              3. Using xls-force-type:number isn't enough to change from general, the xls-format-mask has to be defined also

                              4. The date parsing breaks because of the ;, we need '' for escaping (as Eric suggested in the JIRA)

                              • 12. Re: Excel Export: xls-force-type?
                                nickarls

                                Committed a new parser for the 2.2 branch, you can now use


                                style="xls-format-mask: '$##,###,###,###,###;$(##,###,###,###,###)'"
                                


                                to avoid the split at the ;

                                • 13. Re: Excel Export: xls-force-type?
                                  ericjung2

                                  Thanks, Niklas! Three questions:


                                  1. Can you post the Subversion URL for where this is committed (2.2 branch)?
                                  2. Where is the trunk for your code? I've looked at the SVN repo and it's very confusing to me, I think because of the 3.x preparation but I'm not sure.
                                  3. Does xls-format-mask still need to be specified when xls-force-type: number is used?

                                  • 14. Re: Excel Export: xls-force-type?
                                    nickarls
                                    1 2 Previous Next