Built with Seam
You can find the full source code for this website in the Seam package in the directory /examples/wiki. It is licensed under the LGPL.
| Online: | 8 Members of 3259 |
| Forum: Seam Users |
 |
21. May 2008, 09:00 CET | Link |
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
95 Replies: | |||
|---|---|---|---|
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. |
|||
Yes, this is the basic idea. If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
Collections valid for iteration over cells are: Iterable, JSF Datamodel with iterable wrapped data, Query(from the seam framework) or an array. |
|||
This is very interesting! I will definitely pay close attention to this! Regards, Siarhei |
|||
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 ;-) If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
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 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. |
|||
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. If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
I have experimented with proof-of-concept support for
<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? If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
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... |
|||
Chris Simons wrote on May 26, 2008 16:45: 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:
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 Chris Simons wrote on May 26, 2008 16:45: 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. If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
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 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 If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
(trying to break the
ended up with the
<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
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) If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
Sweet. Looks really good - thanks for keeping everyone up to speed with what you're working on. Have you gotten any word on when this functionality might be included in the Seam distribution? Maybe 2.1GA? I've been meaning to work with your preview functionality but have since been focusing on deploying our Seam app on OC4J 10.1.3.3. Once that is figured out ( if I ever do ) I will be implementing your Excel features first thing. |
|||
The JIRA has it targetted for 2.1BETA1. Of course it may change. Or the Seam Gods might throw out the code altogether once they have a look at it ;-) If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
Hello, Sorry to add to an already long list, I've just started using SEAM and JSF and so far I'm very happy with it but can't get anywhere with this. I've downloaded the latest excel api and everything seems to be ok. I'm trying to use the simple excelExport functionality but keep getting the same exception:- Exception during request processing:
Caused by javax.servlet.ServletException with message: "#{org.jboss.seam.excel.excelExporter.export('investorSearchForm:investorAccountTable')}: org.jboss.seam.excel.ExcelWorkbookException: Could not find data table with id investorSearchForm:investorAccountTable"
This is some of the xhtml...
<h:form id="investorSearchForm">
<rich:dataTable id="investorAccountTable" value="#{investorAccounts}"
var="invAccount" rows="10">
.
.
.
.
</rich:table>
<h:commandButton value="Export" action="#{org.jboss.seam.excel.excelExporter.export('investorSearchForm:investorAccountTable')}"/>
</h:form>
Is there any obvious reason why this won't work? I've tried all obvious combination's but to no avail.. Please help!! Thanks |
|||
Have a look at the HTML source of the page, what is the id of the datatable? If it is OK then I have to look if there is anything extra with richfaces datatables... If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
I have tested it yesterday with a rich:dataTable and it works perfectly. The only difference is, i use a link:
<h:commandLink value="Export" action="#{org.jboss.seam.excel.excelExporter.export('form:taxonnameList')}"/>
And some minor notes: The list was generated with generate entitys (from jboss tools). So every content of a cell is rendered with pure el expression.
#{taxonname.taxonName}
This content does not make it into the excel file:-( So i have to change it into
<h:outputText value="#{taxonname.taxonName}" />
This works. And there are other reasons why this (with outputText) is the better style (eq. international chars etc.) so this is more or less an issue with seamgen not seam-exel:-)
Ciao, |
|||
I have tested it yesterday with a rich:dataTable and it works perfectly. The only difference is, i use a link:
Actually, I've always used commandLink too, have to see if there is an issue with button execution (not that I can think of why). The list was generated with generate entitys (from jboss tools). So every content of a cell is rendered with pure el expression. Currently I'm looking for descendants of UIOutput, perhaps we can make it more generic by accepting pure EL expressions also. If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
BTW, there is an Excel module in JIRA nowadays so you can file a feature request ;-) If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
Thanks heaps for the quick response, I did what you said and checked the generated html and sure enough the id of the table was different to what I thought it would be, sorry should of checked this before writing! However I know get this exception. 23:49:28,783 WARN [JXLTemplates] Could not find cell template data, try 23:49:29,346 ERROR [ContextualHttpServletRequest] ended request due to exception java.lang.IllegalStateException: No active event context I'm sure I've configured something wrong Will have a play tomorrow. Thanks again Chris |
|||
...and full stack trace (if there is any) and war/ear layout. The warning is dummy (I'll remove it when using the exporter)... If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
Ok I figured/guessed out what I had done wrong. I followed the instructions of how to set up a documentStore and one of the instructions was to edit the components.xml file so I did substituting excel for PDF. So I had this: - xmlns:excel="http://jboss.com/products/seam/excel" xsi:schemaLocation=".... http://jboss.com/products/seam/excel http://jboss.com/products/seam/excel-2.1.xsd" <excel:documentStore useExtensions="true"/> However this seems like it's not required? I've removed it and sure enough it all appears to be working, hurray! Thanks Chris |
|||
Yes, this seems to be a bit confusing. This is why: The pdf and excel module uses their own document store, but they are identical, except for the The document store will be generalized to work for both at the same time later on. |
|||
It would also help if I knew what I was doing!! I actually got the separate template export working as I needed to mess with the data a bit and that also works perfect. Thanks for a great add on is such a small amount of time. Chris |
|||
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 |
|||
Already supported through the workbook-level templateURI attribute. Although I'm not sure what URI-format is most suitable in seam/webapp-context... If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
Hello, I'm having trouble getting jboss and seam setup to use the excel libraries. I apologize if this is a lame question... I put jboss-seam-excel.jar into my jboss lib. I put jxl.jar into a place that causes it to get included in my WAR. I start up jboss, no problem. Then I try to go to my login page, and I can't get there. I get an underlying exception of: Caused by: java.lang.NoClassDefFoundError: org/jboss/seam/navigation/Pages at org.jboss.seam.excel.DocumentStorePhaseListener.beforePhase(DocumentStorePhaseListener.java:30) at com.sun.faces.lifecycle.LifecycleImpl.phase(LifecycleImpl.java:222) at com.sun.faces.lifecycle.LifecycleImpl.render(LifecycleImpl.java:144) I'm using jboss 4.2 and seam 2.0.1GA Any advice on how to get this running? Thanks, Tom |
|||
Hmm, sounds like a classloading issue. Tried putting jboss-seam-excel.jar, jboss-seam-ui.jar and jxl.jar into the WEB-INF\lib directory? If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
That solved it, though I now have the jboss-seam-excel jar file in both my jboss lib and my war file. I'll worry about that later though. I'm creating spreadsheets (from my java code) with basic formatting. Very easy, and very nice. The examples in Write.java are great! Tomorrow I'll try to create spreadsheets via xhtml. Thanks, Tom |
|||
Hi, this is just what i needed to code XLS output in our seam app. I tried to use data of type Short or an Enumeration
package de.bafz.aveq.types;
public enum PlotStatus {
Marker,
Project,
...
}
This won't work. The errors are i.e. java.lang.ClassCastException: java.lang.Short Now i'm using a Wrapper to convert it to Integer and it works
<e:cell value="#{projectmethodologyBacking.shortIntegerWrapper(plot.id.lane)}" />
It would be nice to use Shorts, enums and so on as simple as Strings or Integers. Have a nice day Alexander |
|||
Yes the autodetect for types is still quite crude, I'll play around with it some more... If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
It was more my mistaking of using cast to String instead of o.toString() that prevented sensible default behaviour. The new version can be downloaded here. It should support Boolean, Byte, Short, Character, String, Long, Float, Double, BigDecimal, BigInteger, enum and Object plus their primitive counterparts, where applicable. The new version also supports direct export of datatables based on component ID. Custom css supported:
<h:form id="f">
<h:dataTable value="#{personList.personList}" var="person" id="t"
style="xlsFontName : Times New Roman; xlsFontSize.header : 12; xlsFontSize.data : 10;
xlsBackgroundColor.foo : red; xlsAlignment.bar : centre; xlsColumnWidths:*,800">
<h:column>
<f:facet name="header">
<h:outputText value="First name"/>
</f:facet>
<h:outputText value="#{person.firstName}"/>
</h:column>
<h:column style="xlsWidth : 400;">
<f:facet name="header">
<h:outputText value="Last name"/>
</f:facet>
<h:outputText value="#{person.lastName}"/>
</h:column>
<h:column>
<f:facet name="header">
<h:outputText value="Age" style="xlsTemplates : bar"/>
</f:facet>
<h:outputText value="#{person.age}" style="xlsTemplates : foo;"/>
</h:column>
</h:dataTable>
<h:commandLink value="X" action="#{org.jboss.seam.excel.excelExporter.export('f:t')}"/>
</h:form>
(and currently that example covers the entire documentation on the subject) ;-) If a man speaks in the forest and there is no woman around to hear him, is he still wrong? |
|||
Now, it works fine with the newest one. Thanks a lot. Alexander Nicklas Karlsson wrote on Jun 08, 2008 21:51: |
|||
Where I can find a complete list of supported styles? I'm intrested in something like xlsBorderColor, xlsBorderStyle, etc. |
|||
Currently the style parser seem to support // Font attributes private static final String FONTNAME = "xlsFontName"; private static final String FONTSIZE = "xlsFontSize"; private static final String FONTCOLOR = "xlsFontColor"; p |
If a man speaks in the forest and there is no woman around to hear him, is he still wrong?