-
1. Re: Excel Export: xls-force-type?
nickarls Jun 26, 2009 12:31 PM (in response to allforjava.allforjava.aol.in)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 Jun 26, 2009 3:33 PM (in response to allforjava.allforjava.aol.in)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 Jun 26, 2009 3:39 PM (in response to allforjava.allforjava.aol.in)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 Jun 26, 2009 4:33 PM (in response to 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 Jun 26, 2009 4:34 PM (in response to allforjava.allforjava.aol.in)Also, i'm using Seam.2.1.2.
-
6. Re: Excel Export: xls-force-type?
nickarls Jun 27, 2009 12:24 AM (in response to allforjava.allforjava.aol.in)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 Jun 27, 2009 7:34 AM (in response to allforjava.allforjava.aol.in)Can you patch the seam excel codebase?
-
8. Re: Excel Export: xls-force-type?
allforjava.allforjava.aol.in Jun 29, 2009 7:42 AM (in response to 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 Jun 29, 2009 8:00 AM (in response to allforjava.allforjava.aol.in)- I'll do a more thorough test matrix of the data type - forceType - formatMask combinations
- For server-side save, try the exportKey on the workbook, do a manual render, pull out the bytearray data and store it
- Try the fileName attribute on the workbook
-
10. Re: Excel Export: xls-force-type?
ericjung2 Jun 29, 2009 5:19 PM (in response to allforjava.allforjava.aol.in)
Nicklas Karlsson wrote on Jun 29, 2009 08:00:- 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 Jun 30, 2009 10:02 AM (in response to allforjava.allforjava.aol.in)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:
- With String data, I was able to force the general format
- Wrap works, it goes multiline on data like a\nb\nc
- Using xls-force-type:number isn't enough to change from general, the xls-format-mask has to be defined also
- 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 Jul 2, 2009 9:45 AM (in response to allforjava.allforjava.aol.in)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 Jul 2, 2009 6:08 PM (in response to allforjava.allforjava.aol.in)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 whenxls-force-type: number
is used?