Link to home
Start Free TrialLog in
Avatar of Shahzad Fateh Ali
Shahzad Fateh AliFlag for Pakistan

asked on

Excel Export using PHP

Hi Experts,

I want to export nested grids to excel in formatted form. Earlier, i was successful in exporting simple data.

Any idea or help will be appreciated.
ASKER CERTIFIED SOLUTION
Avatar of fraunholz
fraunholz
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Shahzad Fateh Ali

ASKER

i am already trying this. Any other script?
There is simpleXml. I am using the xmlWriter Class:

        $xml = new xmlWriter();
       
        $xml->openMemory();
        $xml->setIndent(true);
       
        $xml->startDocument('1.0', 'utf-8');
        $xml->writeRaw("<?mso-application progid=\"Excel.Sheet\"?".">\n");
       
        $xml->startElement('Workbook');
        $xml->writeAttribute('xmlns', 'urn:schemas-microsoft-com:office:spreadsheet');
        $xml->writeAttribute('xmlns:o', 'urn:schemas-microsoft-com:office:office');
        $xml->writeAttribute('xmlns:x', 'urn:schemas-microsoft-com:office:excel');
        $xml->writeAttribute('xmlns:ss', 'urn:schemas-microsoft-com:office:spreadsheet');
   
        /*********************************************/
        /* set document properties                   */
        /*********************************************/
        $xml->startElement('DocumentProperties');
        $xml->writeAttribute('xmlns', 'urn:schemas-microsoft-com:office:office');
            $xml->writeElement('Author',        'My Name');
            $xml->writeElement('LastAuthor',    'My Name');
            $xml->writeElement('Created',       date('Y-m-d\Th:i:s\Z'));
            $xml->writeElement('Company',       'My Name');
        $xml->endElement();// end DocumentProperties
       
        /*********************************************/
        /* set workbook properties                   */
        /*********************************************/
        $xml->startElement('ExcelWorkbook');
        $xml->writeAttribute('xmlns', 'urn:schemas-microsoft-com:office:excel');
            $xml->writeElement('WindowHeight',      13225);
            $xml->writeElement('WindowWidth',       19382);
            $xml->writeElement('WindowTopX',        10);
            $xml->writeElement('WindowTopY',        10);
            $xml->writeElement('ProtectStructure',  'False');
            $xml->writeElement('ProtectWindows',    'False');
        $xml->endElement();// end ExcelWorkbook
       
       
        /*********************************************/
        /* create worksheet                             */
        /*********************************************/
       
        // start Worksheet
        $xml->startElement('Worksheet');
        $xml->writeAttribute('ss:Name', 'KFZ');
           
        // start Table
        $xml->startElement('Table');
        $xml->writeAttribute('ss:ExpandedColumnCount',  (8));
        $xml->writeAttribute('ss:ExpandedRowCount',     ($rowNum));
        $xml->writeAttribute('x:FullColumns',           1);
        $xml->writeAttribute('x:FullRows',              1);
        $xml->writeAttribute('ss:DefaultColumnWidth',   106.206896551724142);
        $xml->writeAttribute('ss:DefaultRowHeight',     13.241379310344827);
                   
        // start Title Row
        $xml->startElement('Row');
       
        foreach ($fieldNames as $value) {
            $xml->startElement('Cell');
            //$xml->writeAttribute('ss:StyleID',  'Headline');
                $xml->startElement('Data');
                $xml->writeAttribute('ss:Type', 'String');
                $xml->text($value);
                $xml->endElement();// end Data
            $xml->endElement();// end Cell
        }
        $xml->endElement();// end Row
        $xml->endElement();// end Table
       
        $xml->endElement();// end Worksheet
        $xml->endElement();// end Workbook
       
        $xml->endDocument();
       
        header('Content-type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="Teilnehmerliste_'.urlencode($thisEvent[0]['TITLE']).'.xls"');
        header('Expires: 0');
        header('Cache-Control: must-revalidate, post-check=0,pre-check=0');
        header('Pragma: public');
        echo $xml->outputMemory();

Everything is possible with this method. If I don't know how which xml tags to use I save an excel file as xml, look at the code an implement it in my php script. There is no debbuging tool that tells you the line of a syntax error (like JavaScript without Firebug)
This simple class is my weapon of choice for this purpose, check out the examples:

http://bettina-attack.de/jonny/view.php/projects/php_writeexcel/
Your solution worked for me.