excinc
asked on
Exporting tables to excel file with php+html
Hi !
I export html-pages to excel with php and with this code:
<?php
header("Content-type:appli cation/vnd .ms-excel" );
header("Content-Dispositio n: attachment; filename=\"filename.xls\"" );
echo "<HTML>";
echo "<HEAD>";
echo "<TITLE>TEST-PAGE</TITLE>" ;
echo "</HEAD>";
echo "<BODY>";
//TABLE1
echo "<TABLE>";
echo "<TR><TD>text</TD><TD>text </TD><TD>t ext</TD></ TR>";
echo "<TR><TD>text</TD><TD>text </TD><TD>t ext</TD></ TR>";
echo "<TR><TD>text</TD><TD>text </TD><TD>t ext</TD></ TR>";
echo "</TABLE>";
//TABLE2
echo "<TABLE>";
echo "<TR><TD>text</TD><TD>text </TD><TD>t ext</TD></ TR>";
echo "<TR><TD>text</TD><TD>text </TD><TD>t ext</TD></ TR>";
echo "<TR><TD>text</TD><TD>text </TD><TD>t ext</TD></ TR>";
echo "</TABLE>";
echo "</BODY>";
echo "</HTML>";
?>
So the question is. Is it possible to export some tables to another excel sheet in same excel file? Like table1 to sheet1 and table2 to sheet2? And then name the sheets.
How much php can modify and decide what goes to excel file?
Thanks,
I export html-pages to excel with php and with this code:
<?php
header("Content-type:appli
header("Content-Dispositio
echo "<HTML>";
echo "<HEAD>";
echo "<TITLE>TEST-PAGE</TITLE>"
echo "</HEAD>";
echo "<BODY>";
//TABLE1
echo "<TABLE>";
echo "<TR><TD>text</TD><TD>text
echo "<TR><TD>text</TD><TD>text
echo "<TR><TD>text</TD><TD>text
echo "</TABLE>";
//TABLE2
echo "<TABLE>";
echo "<TR><TD>text</TD><TD>text
echo "<TR><TD>text</TD><TD>text
echo "<TR><TD>text</TD><TD>text
echo "</TABLE>";
echo "</BODY>";
echo "</HTML>";
?>
So the question is. Is it possible to export some tables to another excel sheet in same excel file? Like table1 to sheet1 and table2 to sheet2? And then name the sheets.
How much php can modify and decide what goes to excel file?
Thanks,
I completely agree with Isisagate. I have used Excel Writer and it is awesome. It is like learning a new language though. It takes time to figure out how to use everything.
ASKER
Yeah, I checked that writer, several thousand rows of code...
I just need simple answer how to get data to multiple sheets and naming the sheets. That's all, I don't need fancy features.
I just need simple answer how to get data to multiple sheets and naming the sheets. That's all, I don't need fancy features.
ASKER
Warning: main(OLE/PPS/Root.php): failed to open stream: No such file or directory in E:\x\x\excel\Writer\Workbo ok.php on line 39
Fatal error: main(): Failed opening required 'OLE/PPS/Root.php' (include_path='.;C:\php4\i ncludes\pe ar') in E:\x\x\excel\Writer\Workbo ok.php on line 39
Where can I find those files? Even php-4.4.2 package don't have those files!??
Fatal error: main(): Failed opening required 'OLE/PPS/Root.php' (include_path='.;C:\php4\i
Where can I find those files? Even php-4.4.2 package don't have those files!??
ASKER
And that opens xls file and prints only this in the data:
ÐÏࡱá;þÿ þÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿ
þÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿ l ÉBäMy first worksheet=¼%r8X"1Èÿ Arial1 ÈÿAria l1ÈÿA rial1Èÿ Arial1È ÿArial 1ÈArialàõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À à À à À “€ÿ’â8ÿÿÿÿÿÿÿÿÿÿÿÿ€€€€€€ €€€ÀÀÀ€€€™ ™ÿ™3fÿÿÌÌÿ ÿffÿ€€fÌÌÌ ÿ€ÿÿÿÿÿÿ€€ €€€ÿÌÿÌÿÿÌ ÿÌÿÿ™™Ìÿÿ™ ÌÌ™ÿÿÌ™3fÿ 3ÌÌ™ÌÿÌÿ™ÿ fff™–––3f3 ™f333™3™3f 33™333…? My first worksheet
l É*+€‚Á ƒ„&è?' è?(ð?)ð? ¡"dXXà ?à?
NameAge
John Smith>@Johann Schmidt?@Juan Herrera@@>
¶
Root Entryÿÿÿÿÿÿÿÿ ÀFnà©¥Ænà©¥ÆÀBook
ÿÿÿÿÿÿÿÿÿÿÿÿ ÀF·þÿÿÿþÿÿÿþÿÿÿýÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿÿÿÿÿÿ ÿÿÿÿÿ
ÐÏࡱá;þÿ þÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
þÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ
l É*+€‚Á
NameAge
John Smith>@Johann Schmidt?@Juan Herrera@@>
¶
Root Entryÿÿÿÿÿÿÿÿ ÀFnà©¥Ænà©¥ÆÀBook
ÿÿÿÿÿÿÿÿÿÿÿÿ ÀF·þÿÿÿþÿÿÿþÿÿÿýÿÿÿÿÿÿ
ASKER
So I found the missing files but that excel writer doesn't seem to be working right, why?
ASKER
My excel version is MS Excel 2000 (9.0.2720)
Can you show us the code? Also is it still printing out that gibberish? Are you writing to a file or to the browser?
ASKER
I tried the manual's test code and tried to print it directly to xls file:
<?php
require_once 'excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer() ;
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$format_title =& $workbook->addFormat();
$format_title->setBold();
$format_title->setColor('y ellow');
$format_title->setPattern( 1);
$format_title->setFgColor( 'blue');
// let's merge
$format_title->setAlign('m erge');
$worksheet =& $workbook->addWorksheet();
$worksheet->write(0, 0, "Quarterly Profits for Dotcom.Com", $format_title);
// Couple of empty cells to make it look better
$worksheet->write(0, 1, "", $format_title);
$worksheet->write(0, 2, "", $format_title);
$worksheet->write(1, 0, "Quarter", $format_bold);
$worksheet->write(1, 1, "Profit", $format_bold);
$worksheet->write(2, 0, "Q1");
$worksheet->write(2, 1, 0);
$worksheet->write(3, 0, "Q2");
$worksheet->write(3, 1, 0);
$workbook->send('test.xls' );
$workbook->close();
?>
<?php
require_once 'excel/Writer.php';
$workbook = new Spreadsheet_Excel_Writer()
$format_bold =& $workbook->addFormat();
$format_bold->setBold();
$format_title =& $workbook->addFormat();
$format_title->setBold();
$format_title->setColor('y
$format_title->setPattern(
$format_title->setFgColor(
// let's merge
$format_title->setAlign('m
$worksheet =& $workbook->addWorksheet();
$worksheet->write(0, 0, "Quarterly Profits for Dotcom.Com", $format_title);
// Couple of empty cells to make it look better
$worksheet->write(0, 1, "", $format_title);
$worksheet->write(0, 2, "", $format_title);
$worksheet->write(1, 0, "Quarter", $format_bold);
$worksheet->write(1, 1, "Profit", $format_bold);
$worksheet->write(2, 0, "Q1");
$worksheet->write(2, 1, 0);
$worksheet->write(3, 0, "Q2");
$worksheet->write(3, 1, 0);
$workbook->send('test.xls'
$workbook->close();
?>
ASKER
All the manual's examples will do the 'gibberish'...
ASKER
Is there easy way to export data to multiple excel sheets in one xls file?
I think it'll take only 1-2 functions to do it? Does someone know how?
I think it'll take only 1-2 functions to do it? Does someone know how?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No no no, I print tables with PHP to HTML page, and HTML page should go to excel in xls-file. And every table to own sheet in same xls-file.
ASKER
Oracle DB -> odbc+PHP -> HTML-page -> Excel *.xls to multiple sheets.
ASKER
Hmm, I understood wrong your message, let's try that :)
ASKER
Yeah, CSV doesn't support multiple sheets :(
Darn... too bad. Sorry again for not being able to be of more help...
ASKER
TeRReF, you are genius !! Thanks man!!
I made a multisheet excel *.xls file. I saved it to format *htm. Then I looked up the source and here is what I found! So with this code you can print data to multiple sheets in Excel... Thanks man, you gave the idea for me! :)
The code:
************************** ********** ********** ********** ********** ********** *********
<?php
$url = 'http://domain/excel_writer/';
$style = 'stylesheet.css';
//HERE YOU DEFINE HOW MANY SHEETS, ONE FILE = ONE SHEET
$filename = array ('file1.php', 'file2.php');
//HERE YOU NAME THE SHEETS
$sheetname = array ('sheet1', 'sheet2');
header("Content-type:appli cation/vnd .ms-excel" );
header("Content-Dispositio n: attachment; filename=\"filename.xls\"" );
// HTTP/1.1
header("Cache-Control: cache, must-revalidate");
header("Pragma: public");
?>
<HTML xmlns:o="urn:schemas-micro soft-com:o ffice:offi ce"
xmlns:x="urn:schemas-micro soft-com:o ffice:exce l"
xmlns="http://www.w3.org/TR/REC-html40">
<HEAD>
<meta name="Excel Workbook Frameset">
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 9">
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<?php
for ($i=0; $i < count($filename); $i++) {
echo "<x:ExcelWorksheet>";
echo "<x:Name>" . $sheetname[$i] . "</x:Name>";
echo "<x:WorksheetSource HRef=\"" . $url.$filename[$i] . "\"/>";
echo "</x:ExcelWorksheet>";
}
?>
</x:ExcelWorksheets>
<x:Stylesheet HRef="<?php echo $url.$style; ?>"/>
</x:ExcelWorkbook>
</xml>
</HEAD>
</HTML>
I made a multisheet excel *.xls file. I saved it to format *htm. Then I looked up the source and here is what I found! So with this code you can print data to multiple sheets in Excel... Thanks man, you gave the idea for me! :)
The code:
**************************
<?php
$url = 'http://domain/excel_writer/';
$style = 'stylesheet.css';
//HERE YOU DEFINE HOW MANY SHEETS, ONE FILE = ONE SHEET
$filename = array ('file1.php', 'file2.php');
//HERE YOU NAME THE SHEETS
$sheetname = array ('sheet1', 'sheet2');
header("Content-type:appli
header("Content-Dispositio
// HTTP/1.1
header("Cache-Control: cache, must-revalidate");
header("Pragma: public");
?>
<HTML xmlns:o="urn:schemas-micro
xmlns:x="urn:schemas-micro
xmlns="http://www.w3.org/TR/REC-html40">
<HEAD>
<meta name="Excel Workbook Frameset">
<meta http-equiv=Content-Type content="text/html; charset=windows-1252">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 9">
<xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<?php
for ($i=0; $i < count($filename); $i++) {
echo "<x:ExcelWorksheet>";
echo "<x:Name>" . $sheetname[$i] . "</x:Name>";
echo "<x:WorksheetSource HRef=\"" . $url.$filename[$i] . "\"/>";
echo "</x:ExcelWorksheet>";
}
?>
</x:ExcelWorksheets>
<x:Stylesheet HRef="<?php echo $url.$style; ?>"/>
</x:ExcelWorkbook>
</xml>
</HEAD>
</HTML>
You're welcome. Of course you're absolutely right about the genius thingy ;)
http://pear.php.net/package/Spreadsheet_Excel_Writer