-
30. Re: Excel for Seam preview
tjey Jun 14, 2008 11:45 AM (in response to nickarls)Hi! I am trying to specify cell type to be
number
and column width.
It seems to not work.<e:column width="30"> <e:cell value="#{r.currencyRates}" formatMask="# ##0,00" templates="data,number" type="number"/> </e:column>
-
31. Re: Excel for Seam preview
nickarls Jun 16, 2008 10:14 PM (in response to nickarls)I need some more information here, could you elaborate on
not working
. The column width? The format mask? Something else?What type is r.currencyRates? Tried forceType=
number
? What does thedata
andnumber
templates look like? -
32. Re: Excel for Seam preview
aconn7 Jun 20, 2008 8:51 PM (in response to nickarls)Hi. Fantastic! I've already bundled it into our production code on a limited basis.
I did find a fairly significant
glitch
however with a work-around, but the glitch should probably be addressed.When iterating over a list, or using a the org.jboss.seam.excel.excelExporter.export feature, the resulting spreadsheet will not reflect cell elements that are null. If you have a null cell on col2 row5 for instance, when row6 is filled in the value for col2 is placed in row5 instead. The final result has the rows mis-aligned and looks like everything collapses upward.
A simple but annoying fix is to make sure each cell being processed is != null by seting it equal to an empty string (== '';)
I did this in the xhtml. Previously my code read as:
<e:workbook xmlns:e="http://jboss.com/products/seam/excel" xmlns:f="http://java.sun.com/jsf/core"> <e:worksheet value="#{newPart.partList}" var="part"> <e:column> <f:facet name="header"> <e:cell value="Part#"/> </f:facet> <e:cell value="#{part.partNumberDisplay }"/> </e:column> <e:column> <f:facet name="header"> <e:cell value="Proposed#"/> </f:facet> <e:cell value="#{part.partNumberProposed}"/> </e:column> <e:column> <f:facet name="header"> <e:cell value="PCB Footprint"/> </f:facet> <e:cell value="#{part.pcbFootPrint}"/> </e:column> <e:column> <f:facet name="header"> <e:cell value="sizeXnom"/> </f:facet> <e:cell value="#{part.sizeXnom}"/> </e:column> <e:column> <f:facet name="header"> <e:cell value="sizeYnom"/> </f:facet> <e:cell value="#{part.sizeYnom}"/> </e:column> <e:column> <f:facet name="header"> <e:cell value="sizeZmax"/> </f:facet> <e:cell value="#{part.sizeZmax}"/> </e:column> </e:worksheet> </e:workbook>
which didn't work. So I changed it successfully to:
<e:workbook xmlns:e="http://jboss.com/products/seam/excel" xmlns:f="http://java.sun.com/jsf/core"> <e:worksheet value="#{newPart.partList}" var="part"> <e:column> <f:facet name="header"> <e:cell value="Part#"/> </f:facet> <e:cell value="#{empty part.partNumberDisplay ? '' : part.partNumberDisplay }"/> </e:column> <e:column> <f:facet name="header"> <e:cell value="Proposed#"/> </f:facet> <e:cell value="#{empty part.partNumberProposed ? '' : part.partNumberProposed}"/> </e:column> <e:column> <f:facet name="header"> <e:cell value="PCB Footprint"/> </f:facet> <e:cell value="#{empty part.pcbFootPrint ? '' : part.pcbFootPrint}"/> </e:column> <e:column> <f:facet name="header"> <e:cell value="sizeXnom"/> </f:facet> <e:cell value="#{empty part.sizeXnom ? '' : part.sizeXnom}"/> </e:column> <e:column> <f:facet name="header"> <e:cell value="sizeYnom"/> </f:facet> <e:cell value="#{empty part.sizeYnom ? '' : part.sizeYnom}"/> </e:column> <e:column> <f:facet name="header"> <e:cell value="sizeZmax"/> </f:facet> <e:cell value="#{empty part.sizeZmax ? '' : part.sizeZmax}"/> </e:column> </e:worksheet> </e:workbook>
-
33. Re: Excel for Seam preview
aconn7 Jun 20, 2008 11:55 PM (in response to nickarls)I saw on the JIRA that this may have been fixed on the latest post. I downloaded what appeared to be the latest and it seems to work. Awsome.
-
34. Re: Excel for Seam preview
tjey Jun 22, 2008 5:39 PM (in response to nickarls)Thnx Nicklas for reply! Yeah it seems what attributes
width
,mask
,type
does not applies.For more info i made simple demo.
import java.util.ArrayList; import java.util.List; import kz.bee.ramstore.entity.MyItem; import org.jboss.seam.annotations.Name; import org.jboss.seam.annotations.datamodel.DataModel; @Name("myTest") public class MyTest { @DataModel List<MyItem> list = new ArrayList<MyItem>(); public MyTest(){ list.add(new MyItem(10.0)); list.add(new MyItem(20.0)); list.add(new MyItem(30.0)); list.add(new MyItem(40.0)); list.add(new MyItem(50.0)); list.add(new MyItem(60.0)); list.add(new MyItem(70.0)); list.add(new MyItem(80.0)); list.add(new MyItem(90.0)); list.add(new MyItem(100.0)); } public List<MyItem> getList() { return list; } public void setList(List<MyItem> list) { this.list = list; } } public class MyItem { Double value; public MyItem(Double value){ this.value = value; } public Double getValue() { return value; } public void setValue(Double value) { this.value = value; } }
and template is
<e:workbook type="jxl" rationalization="false" xmlns="http://www.w3.org/1999/xhtml" xmlns:s="http://jboss.com/products/seam/taglib" xmlns:ui="http://java.sun.com/jsf/facelets" xmlns:e="http://jboss.com/products/seam/excel" xmlns:f="http://java.sun.com/jsf/core" xmlns:c="http://java.sun.com/jstl/core"> <e:cellTemplate name="header" alignment="centre"> <e:font name="Arial" pointSize="8" bold="true"/> <e:background color="gold" /> <e:border border="all" lineStyle="thin" color="black"/> </e:cellTemplate> <e:cellTemplate name="data" alignment="centre"> <e:font name="Arial" pointSize="8"/> <e:border border="all" lineStyle="thin" color="black"/> </e:cellTemplate> <e:cellTemplate name="number" alignment="right" /> <e:worksheet name="MySheet" value="#{myTest.list}" var="r" startColumn="0" startRow="2"> <e:cell column="0" row="1" value="MyHeader" templates="header" /> <e:column width="100"> <e:cell value="#{r.value}" formatMask="# ##0,00" templates="data,number" type="number"/> <!-- e:cell value="#{r.value}" formatMask="# ##0,00" templates="data,number" forceType="number"/--> </e:column> </e:worksheet> </e:workbook>
here is attachments
http : // bee.office-on-the.net / distr/ _TJey/ Seam/ DemoSeamExcelSupport.zipAny help would be welcome.
P.S. Looking forward for
- Data Grouping
- Freeze support
-
35. Re: Excel for Seam preview
nickarls Jun 22, 2008 9:39 PM (in response to nickarls)I'm on vacation right now so I'll try some long shots:
- mask-issue: does using
mask
instead offormatMask
work? forceType shouldn't be neccessary for doubles - data grouping: does the currently slightly undocumented features <e:groupColumns startColumn=
1
endColumn=3
collapse=true
/> and the similar groupRows do anything for you at the worksheet level? - freezing: do the worksheet attributes horizontalFreeze and verticalFreeze do anything useful?
- mask-issue: does using
-
36. Re: Excel for Seam preview
nickarls Jun 22, 2008 11:34 PM (in response to nickarls)As a side note, 30 is a small parameter for a column width since it is an JExcelAPI measure so you might want to try 256*30...
-
37. Re: Excel for Seam preview
tjey Jun 23, 2008 5:46 PM (in response to nickarls)Vacations :) Nice. One problem solved!
Width-issue, value 36 aprox equals to 1px ( may be it should be documented ? )
But there still remains
- 1) mask-issue, i tried formatMask and just mask, no effect
may be i'm applying wrong format mask # ##0,00 ? ( Excel formating style )
- 2) type, forceType and without both, did not work even with Double,
I tried all types Int, Double, Date and opened it in Excel clicking on cell property showed me what it has General type format. may be there is issue only with mask ? ( may be with mask i can modify date and numerics )
Thnx for a hint on new features. But there are also troubles
- 3) tried to freeze 2nd row
<e:worksheet horizontalFreeze="1"...
but it throws NullPointerException :(
i look at source code, may be here something wrong ( misprinting )if (template.getHorizontalFreeze() != null) { oldSettings.setHorizontalCentre(template.getHorizontalCentre()); } if (template.getHorizontalFreeze() != null) { oldSettings.setHorizontalFreeze(template.getHorizontalFreeze()); }
may be in first
if
should be smth likeif (template.getHorizontalCentre() != null)
- 4) groupColumns and groupRows works!!! But with abnormal cell height ( 469 px ) and i cannot override this :(.
specifying <e:cell height="360"... didnt help.
-
38. Re: Excel for Seam preview
nickarls Jun 23, 2008 8:26 PM (in response to nickarls)
Timur Junussov wrote on Jun 23, 2008 17:46:
Width-issue, value 36 aprox equals to 1px ( may be it should be documented ? )Yep. There is some documentation but it's in docbook so it's sort of write-only docs for me while I wait for the code to be integrated into the trunk so it gets built with the rest of the docs ;-)
Timur Junussov wrote on Jun 23, 2008 17:46:
may be i'm applying wrong format mask # ##0,00 ? ( Excel formating style )Use Java number formatting masks
Timur Junussov wrote on Jun 23, 2008 17:46:
I tried all types Int, Double, Date and opened it in Excel clicking on cell property showed me what it has General type format. may be there is issue only with mask ? ( may be with mask i can modify date and numerics )I'll have to look into what it is supposed to show as...
Timur Junussov wrote on Jun 23, 2008 17:46:
may be in firstif
should be smth likeif (template.getHorizontalCentre() != null)
You are probably right. There are lots of bulk-code that has been opened up against the library without really being used yet. Thanks for the report.
Timur Junussov wrote on Jun 23, 2008 17:46:
specifying <e:cell height="360"... didnt help.At one time there was a problem with the JExcelAPI grouping, have to see if this issue stems from that...
-
39. Re: Excel for Seam preview
harpritt Jun 24, 2008 1:12 PM (in response to nickarls)Dude(s) thats fantastic!
My boss just gets happier and happier when i tell him about all the neat new seam features that you guys develop
Cheers muchly
-
40. Re: Excel for Seam preview
danielc.roth Jun 24, 2008 4:30 PM (in response to nickarls)Thanks, hopefully it just wont be thrashed when the Seam folks actually looks at the code. he he he ;-)
-
41. Re: Excel for Seam preview
m.a.g Jul 4, 2008 7:43 AM (in response to nickarls)Where I can find a complete list of supported styles?
I'm intrested in something like xlsBorderColor, xlsBorderStyle, etc. -
42. Re: Excel for Seam preview
m.a.g Jul 4, 2008 8:03 AM (in response to nickarls)I've found the same issue with jboss-seam-excel-2008-06-06.jar - rows are shifted if there is a column with null data in the middle
I've tried the same with the lastest and got an exception:
Caused by: java.lang.NumberFormatException: empty String
at sun.misc.FloatingDecimal.readJavaFormatString(FloatingDecimal.java:994)
at java.lang.Double.parseDouble(Double.java:482)
at org.jboss.seam.excel.jxl.JXLExcelFactory.createCell(JXLExcelFactory.java:600)
at org.jboss.seam.excel.jxl.JXLExcelWorkbook.addCell(JXLExcelWorkbook.java:260)
at org.jboss.seam.excel.jxl.JXLExcelWorkbook.addItem(JXLExcelWorkbook.java:501)
at org.jboss.seam.excel.jxl.exporter.ExcelExporter.processOutputs(ExcelExporter.java:202)
at org.jboss.seam.excel.jxl.exporter.ExcelExporter.processColumn(ExcelExporter.java:157)
at org.jboss.seam.excel.jxl.exporter.ExcelExporter.export(ExcelExporter.java:91)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:585)
at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:329)
at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:274)
at org.jboss.el.parser.AstMethodSuffix.getValue(AstMethodSuffix.java:59)
at org.jboss.el.parser.AstMethodSuffix.invoke(AstMethodSuffix.java:65)
at org.jboss.el.parser.AstValue.invoke(AstValue.java:96)
at org.jboss.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
at com.sun.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:68)
at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:88)
... 50 more -
43. Re: Excel for Seam preview
nickarls Jul 4, 2008 12:14 PM (in response to nickarls)On vacation. Using mobile. No source available. Post code :-)
-
44. Re: Excel for Seam preview
brachie Jul 16, 2008 10:51 PM (in response to nickarls)Hi,
first of all, thanks for this implementation of excel templates in seam! :-)
Now my question: Is it possible to use formulas and reference cells of another worksheet? If so, could anybody give an example template?
Thanks!
Alexander