13 Replies Latest reply on Jul 25, 2011 3:43 PM by josephbeck

    Render Excel as PDF

    hurzeler
      I am using the Excel tags <e:workbook type="jxl" or type="csv" template="some_report_template.xls"> ... and I wonder if there is an easy way to render the Excel output as PDF rather. This would be an easy way to produce reports. Even business users could then design the report templates we developers just populate the data using seam and the Excel API.

      If it is not doable out of the box, could you indicate which class and methode we need to overwrite in order to add the functionality?

      Thanks for your help!
        • 1. Re: Render Excel as PDF
          nickarls

          Hmm. Perhaps the PDF generator could be modified to do a Excel -> PDF template conversion (since I assume the Excel is close to the table support in PDF)?

          • 2. Re: Render Excel as PDF
            hurzeler

            Yes perhaps.


            I would like to know where the Excel output is generated and sent to the HTTP response so that I can intercept it when the attribute type is PDF. I figure one could then use some API to convert the Excel output to PDF.

            • 3. Re: Render Excel as PDF
              hurzeler

              Can you elaborate on your idea, please? What classes are involved here?

              • 4. Re: Render Excel as PDF
                nickarls

                UIWorkBook.encodeEnd()


                You could create a new JExcelAPI workbook with the bytes and then iterate over the cells and stick them into an IText document


                Of course it would be twice the work since the Excel would be created in both cases


                or then you could implement ExcelWorkbook with a PDFWorkbook and register your own type. Actually that would be the neatest solution but I can't comment on how feasible it is. Give it a try and tell me how it went. Bonus if you can apply all formatting ;-)

                • 5. Re: Render Excel as PDF
                  nickarls

                  If you can do that we could make a general output system that would look like




                  <o:tabularData format="pdf">
                    <o:tabsheet var="#{data}">
                    </o:tabsheet>
                  </o:tabularData>




                  • 6. Re: Render Excel as PDF
                    hurzeler

                    Can you explain the implement ExcelWorkbook with a PDFWorkbook and register your own type idea a little further. How would you render the output as PDF???

                    • 7. Re: Render Excel as PDF
                      hurzeler

                      I can't see the benefits of a general output system. The attraction of the Excel API is that one could use Excel templates. Formatting and templating for a general output system would have to be newly defined.


                      XHTML or itext would be a more natural way to go then wouldn't it?

                      • 8. Re: Render Excel as PDF
                        nickarls

                        Have a look at org.jboss.seam.excel.ExcelWorkbook. The tags work against implementations of that interface. There are currently two implementations, org.jboss.seam.excel.csv.CsvExcelWorkbook and org.jboss.seam.excel.jxl.JXLExcelWorkbook


                        JXLExcelWorkbook is a JExcelAPI specific implementation. It internally holds a JExcelAPI workbook with worksheets and then when the ExcelWorkbook.addItem(WorksheetItem item) it hit, it adds a cell/whatever to the current position in the worksheet


                        A theoretical ITextWorkbook would implement the same interface but would hold a IText document internally, adding a table cell when the same ExcelWorkbook.addItem(WorksheetItem item) would be hit.


                        In the end, both implementations would return a byte[] of data, of course JXLExcelWorkbook would return XLS data while the ITextWorkbook would return PDF data.


                        The tricky part might be to implement the various formatting options, border colors etc. Of course stuff like formulas etc are Excel specific and non-portable...

                        • 9. Re: Render Excel as PDF
                          nickarls

                          Some concepts would be common border color, font size. Others would be excel specific. ICEfaces has some common renderer in 1.8.0. Haven't looked at it more closely but I assume it's more of a datatable exporter (xhtml -> foo converter) and not a XML dialect

                          • 10. Re: Render Excel as PDF
                            hurzeler
                            Well in that case wouldn't it be better to stick with JXLExcelWorkbook i.e. Excel dialect where all the formulas and formatting options are taken care of and then use XLS -> PDF converter such as in OpenOffice or other? Any suggestions?
                            • 11. Re: Render Excel as PDF
                              nickarls

                              Sure, code-wise the simplest way is doing the conversion with jxcell or something. The cost is just double work (first xls generation, then conversion) and an extra dependency. It might even be added as an attribute to e:workbook and then the extra pass would be execute if the attribute would be set to pdf.

                              • 12. Re: Render Excel as PDF
                                hurzeler

                                I guess that is quickest but perhaps not the most elegant solution for me for now.


                                There is perhaps merit in considering to implement the one stage direct rendering of the Excel output to PDF as it would make report generation rather simple and easy to understand for business users who only understand Excel and nothing else. Hence I would like to put it down as a feature request.


                                Many thanks Nicklas! Good night and greetings from Australia...

                                • 13. Re: Render Excel as PDF
                                  josephbeck

                                  I know exactly the program to help you! My work got it for us last year and since then all of the invoicing and everything has become a million times easier. It provides my business a simple way to make individualized statements for our business associates. Check it out here: http://www.nirvaha.com/excel-to-pdf.html.