Solved

Exporting tables to excel file with php+html

Posted on 2006-07-11
19
2,085 Views
Last Modified: 2008-01-09
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
Comment
Question by:excinc
  • 13
  • 3
  • 2
  • +1
19 Comments
 
LVL 11

Expert Comment

by:Isisagate
ID: 17080787
0
 
LVL 2

Expert Comment

by:JGoyer
ID: 17081554
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
 

Author Comment

by:excinc
ID: 17088238
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:excinc
ID: 17088725
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
 

Author Comment

by:excinc
ID: 17088817
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
 

Author Comment

by:excinc
ID: 17088819
So I found the missing files but that excel writer doesn't seem to be working right, why?
0
 

Author Comment

by:excinc
ID: 17088826
My excel version is MS Excel 2000 (9.0.2720)
0
 
LVL 2

Expert Comment

by:JGoyer
ID: 17092565
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
 

Author Comment

by:excinc
ID: 17096835
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
 

Author Comment

by:excinc
ID: 17096840
All the manual's examples will do the 'gibberish'...
0
 

Author Comment

by:excinc
ID: 17099914
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
 
LVL 29

Accepted Solution

by:
TeRReF earned 150 total points
ID: 17103766
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
 

Author Comment

by:excinc
ID: 17104145
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
 

Author Comment

by:excinc
ID: 17104153
Oracle DB -> odbc+PHP -> HTML-page -> Excel *.xls to multiple sheets.
0
 

Author Comment

by:excinc
ID: 17104157
Hmm, I understood wrong your message, let's try that :)
0
 

Author Comment

by:excinc
ID: 17104174
Yeah, CSV doesn't support multiple sheets :(
0
 
LVL 29

Expert Comment

by:TeRReF
ID: 17104181
Darn... too bad. Sorry again for not being able to be of more help...
0
 

Author Comment

by:excinc
ID: 17106316
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
 
LVL 29

Expert Comment

by:TeRReF
ID: 17106393
You're welcome. Of course you're absolutely right about the genius thingy ;)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question