Convert HTML to Excel - page-break-before:always; not working

**bennylam** · 01-18-2012, 12:12 PM

Hi all,

I am trying to generate excel file from HTML page by MIME streaming of a php script
Content-Type: application/vnd.ms-excel
Content-Disposition: attachment; filename='testexcel.xls'
......

It is working good and can be opened by Excel 2007 as expected.

However, there is one problem that I cannot fix so far.

I want to insert page break after some rows.
I try to use the following HTML code at the line that I want to have a page-break in Excel.
<p style="page-break-before:always">

After opening the generated file in Excel 2007, the page-break line is not found and no page-break happen during printing.

I had tried different ways of page-break tag e.g. <br style="page-break-before:always"> ....etc.
Never have the page-break effect in all generated excel file.

Can some one help me to fix this problem?

Thanks

Benny

**RockHydra** · 01-24-2012, 04:05 PM

I was just looking into this issue this morning. I am writing an HTML to Excel export and I have been working on formatting the Excel so that the users do not need to fiddle with the settings before printing. My example below contains code to set the page orientation to landscape, set the scale to 70%, and to add in some page breaks. I have to admit that the documentation on the page breaks was very lacking. I ended up using information from several different sites and then playing with it a bit to get everything working.

The part for the page breaks is contained in the "x:PageBreaks" tags. All you need to do is add an

<x:RowBreak><x:Row>yourRowNumberHere</x:Row></x:RowBreak>

entry for each break. I hope this helps!

<html xmlns:v='urn:schemas-microsoft-com:vml'
xmlns:o='urn:schemas-microsoft-com:office:office'
xmlns:x='urn:schemas-microsoft-com:office:excel'
xmlns='http://www.w3.org/TR/REC-html40'>
<head>
<style type="text/css">
@page{mso-page-orientation:landscape;}
td{text-align:center;}
td.code{mso-number-format:\@}
td.cost{mso-number-format:0\.00000}
td.date{mso-number-format:mm\/dd\/yy \[$-F400\]h\:mm\:ss\\ AM\/PM}
</style>

</head>

**bennylam** · 01-30-2012, 10:51 PM

Hi RockHydra,

Thanks for your help.
Your sample code seems working for page break formatting.
I've manually cut-and-paste <head>...</head> section of your code into a MIME generated .xls text file and the page breaks are shown at rows as defined in the <head> section.

I will try to incorporate the <head> section into my php report generation program and generate the xls report by MIME streaming.

Will keep it posted on the outcome.

Best Regards,

Benny

**bennylam** · 01-30-2012, 11:50 PM

Hi RockHydra,

I've used your sample code and turned it into a php function with no. of report page and page height as parameters.
With this function, the Excel report generation php program is working without fault and the pages are breaking in the generated Excel file as required.

Your help and advice are much appreciated and I believe this solution will be very helpful for those who are looking for a similar solution.

The php function is shown below
//*****************************************************
// Header for generating Excel file by MIME streaming
// $nPage = No. of Page in the report
// $hPage = Height of each Page
//*****************************************************
function ExcelHeader($nPage, $hPage)
{
?>
<html xmlns:v='urn:schemas-microsoft-com:vml'
xmlns:o='urn:schemas-microsoft-com:office:office'
xmlns:x='urn:schemas-microsoft-com:office:excel'
xmlns='http://www.w3.org/TR/REC-html40'>
<head>
<style type="text/css">
@page{mso-page-orientation:landscape;}
td{text-align:center;}
td.code{mso-number-format:\@}
td.cost{mso-number-format:0\.00000}
td.date{mso-number-format:mm\/dd\/yy \[$-F400\]h\:mm\:ss\\ AM\/PM}
</style>

</head>

<?php
}

Benny

www.rightstation.com
www.shippingatom.com
Right Station
Web Solution
The first Cloud Logistics Solution in Asia.

**bennylam** · 01-31-2012, 05:48 AM

Hi RockHydra,

Your solution is good for generating Excel file with known page (row) break position.

However, do you have any idea or sample code on how to create a page break if page break position is not known in advance.
For example, how to insert a page break after a report footer so that next report header can be printed in a new page.
Such page break will be useful for printing multiple section report headers in new pages when the no. of report row within each section is not known in advance?

Thanks

Benny

www.rightstation.com
www.shippingatom.com
Right Station
Web Solution
The first Cloud Logistics Solution in Asia.