Report generating based on templates

Topics: Developer Forum, User Forum
Jul 7, 2008 at 12:45 PM
I have to genarate table reports based on Excel template.

My solution:

1) Load template (Excel2007) with one table header, one row-pattern and table footer
$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$this->objPHPExcel->addExternalSheet($objReader->load($fileName)->getSheetByName($sheetName));

Main problem: long time!
      
2) Expand rows:     
$sheet->insertNewRowBefore($curRow,$nRows);

Problem: Formulas in SUM() are not adjusted.

3)  Fill data

4) Output (only Excel2007, Excel 5 and HTML useful)

Is there better solution?

Jul 8, 2008 at 12:06 PM
Edited Jul 8, 2008 at 12:11 PM
1
Jul 8, 2008 at 12:09 PM
Hello. I' m trying to do the same thing. And it will be wonderfull to have something like temlate FIELDS.

For example report1.xlst:

[REPORTTITLE]  (the value of the field will be inserted from script)

Column1          column2                column3
FFF                GGG                      HHH
(other row inserted by script)
(other row inserted by script)
(other row inserted by script)
                       total                        =sum(C5:C6)            (the range of sum should resize automaticaly depending on count of inserted rows)


                                                      Posted by:   [REPORTAUTHOR]
                                                      Date:           [REPORTDATE]

This is the best way to design report in Excel and fill it from script. Can PHPExcel does something like this?
Jul 8, 2008 at 2:09 PM
Edited Jul 8, 2008 at 2:29 PM
2 alexey_baranov: Hello to Russians from Russians!

And reports like 1C: Enterprise - that would be great!

For example:
sheet.outputSection (<Row number from source table>)

PS. setCellValueByName(<Name of range in SOURCE table>) -very good  feature but can be used only with sections!

For example:

sheet.sourceTable(templateTable)
sheet.setCellValueByName('Report header variable', $CompanyName);
sheet.outputSection('1:6') // report header
while (!end of file)
{
sheet.setCellValueByName('Name', $Name);
sheet.setCellValueByName('Surname', '$Surname');
...
sheet.outputSection ('7') //
}
sheet.outputSection('9:16') // report footer
2 developers:

you can see 1C: Enterprise reports viewer (freeware) here :
International version:
http://www.v8.1c.ru/metod/fileworkshop/fileworkshopint/FileWorkshopINT.exe (38 Mb)

Sample files:
http://www.v8.1c.ru/metod/fileworkshop/Demo.exe
Jul 9, 2008 at 4:10 AM
Edited Jul 9, 2008 at 4:38 AM
Hello everybody! pbond otdelniy salut zemlyakam!

2 developers:
What you thinking about REPORT FRAMEWORK based on PHPExcel? You have to do a little step to complete this. It will be very expected first PHP template engine.
The most necessary things are:
1. designing and cell formatting (already provided by readers and writers)
2. fields (waiting for)

It should be developed in this direction, because there are reach enought printing and formatting features, but they are very heavy without templates.
Jul 25, 2008 at 8:18 AM
hi folks,

this idea about template-system is already exists in other librarys.
here is a nice lib-example: http://www.tmssoftware.com/site/flexcelnet.asp
and here a screencast: http://www.tmssoftware.com/flexcel/tutorial.htm
would be very nice to see this idea in phpexcel!

@martanbaa: is it a nice idea and worth a issue?


Coordinator
Jul 25, 2008 at 9:21 AM
This is not really the intention of PHPExcel. It could be useful and nice to have a reporting engine on top of PHPExcel, but this will not be the core functionality of PHPExcel.
Jul 25, 2008 at 10:33 AM
Edited Jul 25, 2008 at 10:35 AM
Well, after watching demonstration movie I have only one question. Does anybody here want to try this shit? I’m not!
I have my own template framework. It is good enough for me. Is provides only 2 features, but the simplicity of using is amazing! The only features are:
1.    Styling(ExcelReader_Excel2007 reads cell format very nice and Excel_Sheet::insertRowBefore()  copies all applied formats  from previous row to new)
2.    Fields (simple function eregi_replaceCellValue() based on sequence fetch cells)
3.    Varying size formulas (based on nice feature of Excel_Sheet::insertRowBefore() to resize formulas)
With this framework I can redirect designing to other people. Each department designs their own templates now. People like this. I’m to. I need 30 min to design middle heavy report form in PHP- code. With this template any entry level employee can prepare much nicer form in Excel on few minutes! After this I load prepared file and fill it using my framework.

Jul 25, 2008 at 2:26 PM
Reporting system on PHP:  HTML for preview, Excel (2000-2007), PDF, one code, cell , based on templates, fast and precise. Is it possible? ;-)
Aug 22, 2008 at 11:53 AM
Edited Aug 22, 2008 at 12:25 PM
I think I found the best solution for creating template-based report. It exists since Office 2002:
1) Design template in MSO (>=2002) or OO (>=2)
##Name## {$rec.name}
2) Save as XML Spreadsheet
3) Download Help and Instellisense files
http://www.microsoft.com/downloads/details.aspx?familyid=fe118952-3547-420a-a412-00a2662442d9&displaylang=en
3) Open in Eclipse, change header to activate Intellisense:
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="urn:schemas-microsoft-com:office:spreadsheet excelss.xsd">

4)Create Smarty template using for example
http://smarty.incutio.com/?page=BandedReportGenerator

PS.
Limitations:
http://office.microsoft.com/en-us/excel/HA010346391033.aspx

TODO: Convert to HTML and PDF
Aug 22, 2008 at 12:21 PM
Links:
http://support.microsoft.com/kb/288215/en-us

http://support.microsoft.com/kb/278976/
http://support.microsoft.com/kb/285891/
Aug 25, 2008 at 4:14 AM
I really think that idea with “simple” templates will be very popular. The main point is template edited by users!! Not by programmer. No special programs, no special skills. Just open Excel and do it.

I have tested this way on my work and it had great success. Easy to create and easy to change! People like it.

It is very simple to realize it because 99% is already provided by PHPExcel.

I had just two little problems now:
1.    Work with non default locale on FreeBSD.
2.    Mechanism to initialize extended spreadsheet class instead of regular in PHPExcel class.
Coordinator
Aug 25, 2008 at 6:10 AM
Also check this one: http://blog.maartenballiauw.be/post/2008/03/Reuse-Excel-business-logic-with-PHPExcel.aspx
Aug 25, 2008 at 6:55 AM
2 maartenba

Great, but useful only with Print One Record.

For reports with tables (one or many, with one or many grouping levels, simple or pivot tables) - SpreadsheetML (XML with Intellisense) + Smarty may be better.
Aug 25, 2008 at 10:16 AM
This is exactly I’m talking about! The only difference is you print out result as HTML. I send result as .xlsx file.
I agree with your idea absolutely. I have used it. It is very success!

There is a way to extend your method to multi- row reports. This is very easy with PHPExcel because of nice method insertRowBefore(), which copies formatting and resizes formulas.
The basic multi- row report are showed in my post form Jul 8 at 6:09 PM
Aug 25, 2008 at 1:02 PM
I think, that PHPExcel with templates is too slow - I have tested but 3 seconds - too much!

There are 2 schemes:

1) SpreadsheetML template ->Smarty->Excel report - I have realized - very small time and templates are
Excel report in SpeadsheetML -> XSL(T) transformation to  HTML -> HTML report

I recommend to create your own Style Definitions for all reports and include into main XML template (3 tpl.xml files: Header, Main template, Footer)

2) XML data -> XSL(T) transformation to SpreadsheetML->Excel report
XML data -> XSL(T) transformation to HTML->HTML report

Is not so easy.
Aug 29, 2008 at 11:33 AM
I'm going on my vocation. Hope we will continue our discussion after 3 weeks...
Oct 20, 2008 at 11:01 AM
Hello, again!

After a few months of our teamwork I can say that I’m absolutely satisfied with PHPExcel. A few critical for me bugs was successfully released and I enjoy to work with this library.

Hope, you gays will find time to realize templated reports (The main idea is template should be created in pure Excel, as I tried to explain you before) and national encodings support. I will wait it very much! I think fixing all bugs in PHPExcel will populate it in a little much. But simple report template engine will populate it twice, twice and twice!

Best regards,
alexey_baranov