About a half year ago, i posted an article about exporting data to an .xls file in CakePHP.
CakePHP – Export data to a .xls file
It is found that the exported .xls cannot be opened in OpenOffice. Today, Stebu finds the solution. Here is the modified helper class.
xls.php
<?php
/**
* This xls helper is based on the one at
* http://bakery.cakephp.org/articles/view/excel-xls-helper
*
* The difference compared with the original one is this helper
* actually creates an xml which is openable in Microsoft Excel.
*
* Written by Yuen Ying Kit @ ykyuen.wordpress.com
*
*/
class XlsHelper extends AppHelper {
/**
* set the header of the http response.
*
* @param unknown_type $filename
*/
function setHeader($filename) {
header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/vnd.ms-excel; charset=UTF-8");
header("Content-Type: application/force-download");
header("Content-Type: application/download");;
//header("Content-Disposition: inline; filename=\"".$filename.".xml\"");
// Name the file to .xlsx to solve the excel/openoffice file opening problem
header("Content-Disposition: inline; filename=\"".$filename.".xlsx\"");
}
/**
* add the xml header for the .xls file.
*
*/
function addXmlHeader() {
echo "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n";
echo "<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\n";
echo " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\n";
echo " xmlns:x=\"urn:schemas-microsoft-com:office:excel\"\n";
echo " xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"\n";
echo " xmlns:html=\"http://www.w3.org/TR/REC-html40\">\n";
return;
}
/**
* add the worksheet name for the .xls.
* it has to be added otherwise the xml format is incomplete.
*
* @param unknown_type $workSheetName
*/
function setWorkSheetName($workSheetName) {
echo "\t<Worksheet ss:Name=\"".$workSheetName."\">\n";
echo "\t\t<Table>\n";
return;
}
/**
* add the footer to the end of xml.
* it has to be added otherwise the xml format is incomplete.
*
*/
function addXmlFooter() {
echo "\t\t</Table>\n";
echo "\t</Worksheet>\n";
echo "</Workbook>\n";
return;
}
/**
* move to the next row in the .xls.
* must be used with closeRow() in pair.
*
*/
function openRow() {
echo "\t\t\t<Row>\n";
return;
}
/**
* end the row in the .xls.
* must be used with openRow() in pair.
*
*/
function closeRow() {
echo "\t\t\t</Row>\n";
return;
}
/**
* Write the content of a cell in number format
*
* @param unknown_type $Value
*/
function writeNumber($Value) {
if (is_null($Value)) {
echo "\t\t\t\t<Cell><Data ss:Type=\"String\"> </Data></Cell>\n";
} else {
echo "\t\t\t\t<Cell><Data ss:Type=\"Number\">".$Value."</Data></Cell>\n";
}
return;
}
/**
* Write the content of a cell in string format
*
* @param unknown_type $Value
*/
function writeString($Value) {
echo "\t\t\t\t<Cell><Data ss:Type=\"String\">".$Value."</Data></Cell>\n";
return;
}
}
?>
Thanks Stebu again. =)
* Update @ 2011-10-14: Named the file to .xlsx instead of .xls in order to fixed the excel/openoffice problem.Thanks Erwan.
Eureka! 
Great job,
Thank you very much 🙂
Just for easiness I’ve changed xml to xls in 25th line.
All best!
LikeLike
glad to know that the article could help =)
LikeLike
Thanks!
But doesn’t work yet. Only in MS Office
Could you help me?
Best regards!
LikeLike
What is the problem about?
LikeLike
I changed the extension file at line 25 .xml to .xls and I’ve done all like the article “CakePHP – Export data to a .xls file” but in open office always seems something like this:
<Workboo xmlns="urn:schemas-microsoft-com:office:spreadsheet"
………….
LikeLike
i dun have an openoffice at home. let me try it out when i back to office tmr.
hope i can bring a good news.
by the way, what version of openoffice are u using?
LikeLike
Hi Kmunino,
I just tried to open an xml in OpenOffice 2.4 and i didn’t come across your problem.
the generated .xml should have similar structure as follow.
could you try to create this file and try again?
LikeLike
Hello
I am trying to open the documents using my Open Office Calc and I there is no content in the file. But if I open the file with notepad++ or any other program the data is there…..
LikeLike
did u try to open it in microsoft excel?
or you can copy the .xml in the above comment and see if it could be opened in open office.
LikeLike
Hello,
I was using Microsoft Excel for the whole time, but now once my trial version has ended I’ve tried solution for open office and it doesn’t work.
I can open the above example (in the comment) but I can’t open the file generated by my site.
This is the code I get:
<?xml version="1.0" encoding="UTF-8"?> <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">LikeLike
Ok I figured out what’s the problem. In my export data there are links. These links sometimes contain special characters like “&” which cause open office to not display content of a document. Once I’ve deleted all the links which had special characters it all worked.
Is there any way to go through this ?
LikeLike
It does contain a lot of data. I just skipped the etc. things cause they are fine (checked carefully).
LikeLike
maybe you could try the PHP rawurldecode() to wrap the string.
PHP – URL Encode and Decode
LikeLike
Solution to this problem lays not in the data but in the form the data is saved. The links are causing the program to fail and there is no way to go pass this. So the question is: “Is there a way to ‘tell’ OpenOffice that the data in the cell is a data and not part of a code so it would treat it as a string?”
LikeLike
I couldn’t find any OpenOffice reference about how to let it know it should parse the value as string only.
You can find more info @ XML Spreadsheet Reference
but an alternative way is to make use of the cell ss:HREF attribute. so maybe you need to add the following function in the helper
/** * Write the content of a cell in string format * * @param unknown_type $Value */ function writeUrl($Value) { echo "\t\t\t\t<Cell><Data ss:HREF=\"".$Value."\" ss:Type=\"String\">link</Data></Cell>\n"; return; }LikeLike
The ss:HREF doesn’t work.
Even if I deleted the “bad” links then in the OpenOffice “link” word wasn’t eve hyperlinked to the actual url.
Thanks for your help anyway, I really appreciate it 🙂
LikeLike
Ok will test that now and let you know about the solution.
LikeLike
Hi Peter,
i think you can try replace the “&” with “&”. This could solve the problem.
i tried the following .xml in LibreOffice and it works.
<?xml version="1.0" encoding="UTF-8"?> <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"> <Worksheet ss:Name="Sheet 1"> <Table> <Row> <Cell><Data ss:Type="String">Column 1</Data></Cell> <Cell><Data ss:Type="String">Column 2</Data></Cell> <Cell><Data ss:Type="String">Column 3</Data></Cell> </Row> <Row> <Cell><Data ss:Type="Number">123</Data></Cell> <Cell><Data ss:Type="String">ABC</Data></Cell> <Cell><Data ss:Type="String">DEF</Data></Cell> </Row> <Row> <Cell><Data ss:Type="Number">456</Data></Cell> <Cell><Data ss:Type="String">GHI</Data></Cell> <Cell><Data ss:Type="String">JKL</Data></Cell> </Row> <Row> <Cell><Data ss:Type="Number">789</Data></Cell> <Cell ss:HRef="http://www.abc.com?name=1&abc=2"><Data ss:Type="String">bbb</Data></Cell> <Cell><Data ss:Type="String">PQR</Data></Cell> </Row> </Table> </Worksheet> </Workbook>LikeLike
Hello
I am trying to set rows as Text formate.
LikeLike
so does it work?
LikeLike