CakePHP – Open an xml Formatted .xls in OpenOffice

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.

About these ads

20 thoughts on “CakePHP – Open an xml Formatted .xls in OpenOffice

      1. kmunino

        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"

        ………….

      2. ykyuen Post author

        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?

      3. ykyuen Post author

        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.

        <?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><Data ss:Type="String">MNO</Data></Cell>
        				<Cell><Data ss:Type="String">PQR</Data></Cell>
        			</Row>
        		</Table>
        	</Worksheet>
        </Workbook>
        

        could you try to create this file and try again?

  1. Pingback: CakePHP – Export data to a .xls file | Eureka!

  2. Peter

    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…..

    Reply
    1. ykyuen Post author

      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.

      Reply
      1. Peter

        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">
        
      2. Peter

        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 ?

      3. Peter

        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?”

      4. ykyuen Post author

        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;
        }
        
      5. Peter

        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 :)

      1. ykyuen Post author

        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&amp;abc=2"><Data ss:Type="String">bbb</Data></Cell>
                        <Cell><Data ss:Type="String">PQR</Data></Cell>
                    </Row>
                </Table>
            </Worksheet>
        </Workbook>
        

         

  3. Pingback: CakePHP – Export data to a .xls file | Eureka!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s