Shahzad Fateh Ali
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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-appl ication progid=\"Excel.Sheet\"?"." >\n");
$xml->startElement('Workbo ok');
$xml->writeAttribute('xmln s', 'urn:schemas-microsoft-com :office:sp readsheet' );
$xml->writeAttribute('xmln s:o', 'urn:schemas-microsoft-com :office:of fice');
$xml->writeAttribute('xmln s:x', 'urn:schemas-microsoft-com :office:ex cel');
$xml->writeAttribute('xmln s:ss', 'urn:schemas-microsoft-com :office:sp readsheet' );
/************************* ********** ********** /
/* set document properties */
/************************* ********** ********** /
$xml->startElement('Docume ntProperti es');
$xml->writeAttribute('xmln s', 'urn:schemas-microsoft-com :office:of fice');
$xml->writeElement('Author ', 'My Name');
$xml->writeElement('LastAu thor', 'My Name');
$xml->writeElement('Create d', date('Y-m-d\Th:i:s\Z'));
$xml->writeElement('Compan y', 'My Name');
$xml->endElement();// end DocumentProperties
/************************* ********** ********** /
/* set workbook properties */
/************************* ********** ********** /
$xml->startElement('ExcelW orkbook');
$xml->writeAttribute('xmln s', 'urn:schemas-microsoft-com :office:ex cel');
$xml->writeElement('Window Height', 13225);
$xml->writeElement('Window Width', 19382);
$xml->writeElement('Window TopX', 10);
$xml->writeElement('Window TopY', 10);
$xml->writeElement('Protec tStructure ', 'False');
$xml->writeElement('Protec tWindows', 'False');
$xml->endElement();// end ExcelWorkbook
/************************* ********** ********** /
/* create worksheet */
/************************* ********** ********** /
// start Worksheet
$xml->startElement('Worksh eet');
$xml->writeAttribute('ss:N ame', 'KFZ');
// start Table
$xml->startElement('Table' );
$xml->writeAttribute('ss:E xpandedCol umnCount', (8));
$xml->writeAttribute('ss:E xpandedRow Count', ($rowNum));
$xml->writeAttribute('x:Fu llColumns' , 1);
$xml->writeAttribute('x:Fu llRows', 1);
$xml->writeAttribute('ss:D efaultColu mnWidth', 106.206896551724142);
$xml->writeAttribute('ss:D efaultRowH eight', 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:T ype', '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-Dispositio n: attachment; filename="Teilnehmerliste_ '.urlencod e($thisEve nt[0]['TIT LE']).'.xl s"');
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)
$xml = new xmlWriter();
$xml->openMemory();
$xml->setIndent(true);
$xml->startDocument('1.0',
$xml->writeRaw("<?mso-appl
$xml->startElement('Workbo
$xml->writeAttribute('xmln
$xml->writeAttribute('xmln
$xml->writeAttribute('xmln
$xml->writeAttribute('xmln
/*************************
/* set document properties */
/*************************
$xml->startElement('Docume
$xml->writeAttribute('xmln
$xml->writeElement('Author
$xml->writeElement('LastAu
$xml->writeElement('Create
$xml->writeElement('Compan
$xml->endElement();// end DocumentProperties
/*************************
/* set workbook properties */
/*************************
$xml->startElement('ExcelW
$xml->writeAttribute('xmln
$xml->writeElement('Window
$xml->writeElement('Window
$xml->writeElement('Window
$xml->writeElement('Window
$xml->writeElement('Protec
$xml->writeElement('Protec
$xml->endElement();// end ExcelWorkbook
/*************************
/* create worksheet */
/*************************
// start Worksheet
$xml->startElement('Worksh
$xml->writeAttribute('ss:N
// start Table
$xml->startElement('Table'
$xml->writeAttribute('ss:E
$xml->writeAttribute('ss:E
$xml->writeAttribute('x:Fu
$xml->writeAttribute('x:Fu
$xml->writeAttribute('ss:D
$xml->writeAttribute('ss:D
// start Title Row
$xml->startElement('Row');
foreach ($fieldNames as $value) {
$xml->startElement('Cell')
//$xml->writeAttribute('ss
$xml->startElement('Data')
$xml->writeAttribute('ss:T
$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-Dispositio
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/
http://bettina-attack.de/jonny/view.php/projects/php_writeexcel/
ASKER
Your solution worked for me.
ASKER