Excel Creation – Multiple worksheets in php

An article by admin    No Comments

After spending few days i find a format to generate excel with multiple work sheets.

Based on that format , I created my own functions for creating excel with multiple work sheets with out using any third party tool.

Creating Header for excel

function header_excel(){
$header = <<<EOH
<?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:html=”http://www.w3.org/TR/REC-html40″>
<DocumentProperties xmlns=”urn:schemas-microsoft-com:office:office”>
<Author>Author Name</Author>
<LastAuthor>Author Name</LastAuthor>
<Created>Created Date</Created>
<Company>Company Name</Company>
<Version>Version</Version>
</DocumentProperties>
<ExcelWorkbook xmlns=”urn:schemas-microsoft-com:office:excel”>
<WindowHeight>12240</WindowHeight>
<WindowWidth>24855</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>105</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID=”Default” ss:Name=”Normal”>
<Alignment ss:Vertical=”Bottom”/>
<Borders/>
<Font ss:FontName=”Calibri” x:Family=”Swiss” ss:Size=”11″ ss:Color=”#000000″/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID=”s18″ ss:Name=”Currency”>
<NumberFormat
ss:Format=”_(”$”* #,##0.00_);_(”$”* \(#,##0.00\);_(”$”* “-”??_);_(@_)”/>
</Style>
<Style ss:ID=”s66″>
<Font ss:FontName=”Calibri” x:Family=”Swiss” ss:Size=”11″ ss:Color=”#000000″/>
</Style>
<Style ss:ID=”s68″ ss:Parent=”s18″>
<Font ss:FontName=”Calibri” x:Family=”Swiss” ss:Size=”11″ ss:Color=”#000000″/>
</Style>
<Style ss:ID=”s69″>
<Alignment ss:Horizontal=”Center” ss:Vertical=”Bottom”/>
<Font ss:FontName=”Calibri” x:Family=”Swiss” ss:Size=”11″ ss:Color=”#000000″
ss:Bold=”1″/>
</Style>
<Style ss:ID=”s70″ ss:Parent=”s18″>
<Alignment ss:Horizontal=”Center” ss:Vertical=”Bottom”/>
<Font ss:FontName=”Calibri” x:Family=”Swiss” ss:Size=”11″ ss:Color=”#000000″
ss:Bold=”1″/>
</Style>
<Style ss:ID=”s73″>
<Alignment ss:Horizontal=”Center” ss:Vertical=”Bottom”/>
<Font ss:FontName=”Calibri” x:Family=”Swiss” ss:Size=”11″ ss:Color=”#000000″/>
</Style>
</Styles>
EOH;
return $header;
}

2. By using the below method we can able to add work sheets

$sheetname  – Name of the worksheet
function add_excel_worksheet($sheetname) {
$worksheet_name = ‘<Worksheet ss:Name=”‘.$sheetname.’”>’;
return $worksheet_name;
}
3. This Method is used to Add the content for the particular work sheet

$cnt,$cnt1 – total number of row and column.

function formatDataHeader($cnt,$cnt1) {

$table_header = ‘<Table ss:ExpandedColumnCount=”‘.$cnt1.’” ss:ExpandedRowCount=”‘.$cnt.’” x:FullColumns=”1″
x:FullRows=”1″ ss:StyleID=”s66″ ss:DefaultRowHeight=”15″>
<Column ss:StyleID=”s73″ ss:AutoFitWidth=”1″ ss:Width=”200″/>
<Column ss:StyleID=”s66″ ss:AutoFitWidth=”1″ ss:Width=”135″/>
<Column ss:StyleID=”s68″ ss:AutoFitWidth=”1″ ss:Width=”82.5″/>’;

/*$table_row = ‘<Row ss:StyleID=”s69″>’;
$column_cnt = count($headers);
$column_cont = “”;
for($i=0;$i<$column_cnt;$i++) {
$column_cont .= ‘<Cell><Data ss:Type=”String”>’.$headers[$i].’</Data></Cell>’;
}
$table_row_end =”</Row>”;

$table = $table_header.$table_row.$column_cnt.$table_row_end;*/
return $table_header;
}
function formatDataContent($contents) {

$table_row = ‘<Row ss:StyleID=”s69″>’;
$column_cnt = count($contents);
$column_cont = “”;
for($i=0;$i<$column_cnt;$i++) {
$column_cont .= ‘<Cell><Data ss:Type=”String”>’.$contents[$i].’</Data></Cell>’;
}
$table_row_end =”</Row>”;

$table_cont= $table_row.$column_cont.$table_row_end;
return $table_cont;
}

4. This is used to close content tag for particular worksheet.

function closetableNode() {
return “</Table>”;
}
5. The below method is used to close the worksheet.

function close_excel_worksheet() {
$worksheet_close = ‘<WorksheetOptions xmlns=”urn:schemas-microsoft-com:office:excel”>
<PageSetup>
<Header x:Margin=”0.3″/>
<Footer x:Margin=”0.3″/>
<PageMargins x:Bottom=”0.75″ x:Left=”0.7″ x:Right=”0.7″ x:Top=”0.75″/>
</PageSetup>
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>0</VerticalResolution>
</Print>
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>’;
return $worksheet_close;
}
5. The below method is used to close the workbook.

function close_workbook() {
$workbook_close = “</Workbook>”;
return $workbook_close;
}