• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2384
  • Last Modified:

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:application/vnd.ms-excel");
header("Content-Disposition: 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>text</TD></TR>";
echo "<TR><TD>text</TD><TD>text</TD><TD>text</TD></TR>";
echo "<TR><TD>text</TD><TD>text</TD><TD>text</TD></TR>";
echo "</TABLE>";

//TABLE2
echo "<TABLE>";
echo "<TR><TD>text</TD><TD>text</TD><TD>text</TD></TR>";
echo "<TR><TD>text</TD><TD>text</TD><TD>text</TD></TR>";
echo "<TR><TD>text</TD><TD>text</TD><TD>text</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,
0
excinc
Asked:
excinc
  • 13
  • 3
  • 2
  • +1
1 Solution
 
IsisagateCommented:
0
 
JGoyerCommented:
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.
0
 
excincAuthor Commented:
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.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
excincAuthor Commented:
Warning: main(OLE/PPS/Root.php): failed to open stream: No such file or directory in E:\x\x\excel\Writer\Workbook.php on line 39

Fatal error: main(): Failed opening required 'OLE/PPS/Root.php' (include_path='.;C:\php4\includes\pear') in E:\x\x\excel\Writer\Workbook.php on line 39



Where can I find those files? Even php-4.4.2 package don't have those files!??
0
 
excincAuthor Commented:
And that opens xls file and prints only this in the data:


ÐÏࡱá;þÿ      þÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ      
            
þÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ      l      ÉBäMy first worksheet=¼%r8X"1ÈÿArial1ÈÿArial1ÈÿArial1ÈÿArial1ÈÿArial1È Arialàõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À àõÿ À à À à À “€ÿ’â8ÿÿÿÿÿÿÿÿÿÿÿÿ€€€€€€€€€ÀÀÀ€€€™™ÿ™3fÿÿÌÌÿÿffÿ€€fÌÌÌÿ€ÿÿÿÿÿÿ€€€€€ÿÌÿÌÿÿÌÿÌÿÿ™™Ìÿÿ™ÌÌ™ÿÿÌ™3fÿ3ÌÌ™ÌÿÌÿ™ÿfff™–––3f3™f333™3™3f33™333…?My first worksheet
      l      É*+€‚Áƒ„&è?'è?(ð?)ð?¡"dXXà?à?
 Name Age            
John Smith>@Johann Schmidt?@ Juan Herrera@@>            
¶            
Root Entryÿÿÿÿÿÿÿÿ      ÀFnà©¥Ænà©¥ÆÀBook      
ÿÿÿÿÿÿÿÿÿÿÿÿ      ÀF·þÿÿÿþÿÿÿþÿÿÿýÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ      
0
 
excincAuthor Commented:
So I found the missing files but that excel writer doesn't seem to be working right, why?
0
 
excincAuthor Commented:
My excel version is MS Excel 2000 (9.0.2720)
0
 
JGoyerCommented:
Can you show us the code?  Also is it still printing out that gibberish?  Are you writing to a file or to the browser?
0
 
excincAuthor Commented:
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('yellow');
$format_title->setPattern(1);
$format_title->setFgColor('blue');
// let's merge
$format_title->setAlign('merge');

$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();
?>
0
 
excincAuthor Commented:
All the manual's examples will do the 'gibberish'...
0
 
excincAuthor Commented:
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?
0
 
TeRReFCommented:
I saw your pointer. I'm sorry I cannot help you since I don't run windows. But, you could try a bit of 'reverse engineering' here. I don't know if excel lets you save multiple tab documents as CSV file. If so, you could open that saved doc. in your favorite text editor (not word, but notepad for instance) and see how that file is constructed. Then, just copy that format in PHP...
0
 
excincAuthor Commented:
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.
0
 
excincAuthor Commented:
Oracle DB -> odbc+PHP -> HTML-page -> Excel *.xls to multiple sheets.
0
 
excincAuthor Commented:
Hmm, I understood wrong your message, let's try that :)
0
 
excincAuthor Commented:
Yeah, CSV doesn't support multiple sheets :(
0
 
TeRReFCommented:
Darn... too bad. Sorry again for not being able to be of more help...
0
 
excincAuthor Commented:
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:application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=\"filename.xls\"");

// HTTP/1.1
header("Cache-Control: cache, must-revalidate");
header("Pragma: public");
?>

<HTML xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
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>
0
 
TeRReFCommented:
You're welcome. Of course you're absolutely right about the genius thingy ;)
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 13
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now