php, without COM, generate Excel files
Copy Code code as follows:
?
Header ("Content-type:application/vnd.ms-excel");
Header ("Content-disposition:filename=test.xls");
echo "Test1\t";
echo "test2\t\n";
echo "Test1\t";
echo "test2\t\n";
echo "Test1\t";
echo "test2\t\n";
echo "Test1\t";
echo "test2\t\n";
echo "Test1\t";
echo "test2\t\n";
echo "Test1\t";
echo "test2\t\n";
?>
In the PHP environment to run the above code, you can see the browser to ask the user whether to download the Excel document, click Save, the hard drive on the more than an Excel file, use Excel to open will see the final results, how good it.
In fact, when doing real application, you can take the data out of the database, and then after each column of data plus \ t, after each row of data added \ n method echo out, in the beginning of PHP with header ("content-type:application/ Vnd.ms-excel "); indicates that the output is an Excel file, with the header (" Content-disposition:filename=test.xls "); The file name for the output is Text.xls. That's OK.
We can also modify the header to let him output more format files, so PHP is more convenient for processing various types of files.
second, use PHP to convert MySQL datasheet to excel file format
Copy Code code as follows:
<?php
$DB _server = "localhost";
$DB _username = "mydowns";
$DB _password = "";
$DB _dbname = "mydowns";
$DB _tblname = "user";
$Connect = @mysql_connect ($DB _server, $DB _username, $DB _password)
Or Die ("couldn@ #t Connect.");
$Db = @mysql_select_db ($DB _dbname, $Connect)
Or Die ("couldn@ #t Select Database.");
$file _type = "Vnd.ms-excel";
$file _ending = "xls";
Header ("content-type:application/$file _type");
Header ("content-disposition:attachment; filename=mydowns. $file _ending ");
Header ("Pragma:no-cache");
Header ("expires:0");
$now _date = Date (@ #Y-m-d h:i@#);
$title = "Database name: $DB _dbname, Datasheet: $DB _tblname, Backup date: $now _date";
$sql = "SELECT * from $DB _tblname";
$ALT _db = @mysql_select_db ($DB _dbname, $Connect)
Or Die ("couldn@ #t Select Database");
$result = @mysql_query ($sql, $Connect)
Or Die (Mysql_error ());
Echo ("$title \ n");
$sep = "T";
for ($i = 0; $i < Mysql_num_fields ($result); $i + +) {
Echo Mysql_field_name ($result, $i). "T";
}
Print ("\ n");
$i = 0;
while ($row = Mysql_fetch_row ($result))
{
$schema _insert = "";
for ($j =0; $j <mysql_num_fields ($result); $j + +)
{
if (!isset ($row [$j]))
$schema _insert. = "NULL". $sep;
ElseIf ($row [$j]!= "")
$schema _insert. = "$row [$j]". $sep;
Else
$schema _insert. = "" $SEP;
}
$schema _insert = Str_replace ($sep.) $ "," ", $schema _insert);
$schema _insert. = "T";
Print (Trim ($schema _insert));
print "\ n";
$i + +;
}
return (true);
?>
An example of PHP operating Excel (using COM objects to generate Excel)
This is for those who only like to simply deal with Excel friends
Copy Code code as follows:
<?php
Define an Excel file
$workbook = "C:/My Documents/test.xls";
$sheet = "Sheet1";
Generate a COM object $ex
$ex = new COM ("Excel.Sheet") or Die ("Not even!!! ");
Open an Excel file
$book = $ex->application->workbooks->open ($workbook) or Die ("Not open!!! ");
$sheets = $book->worksheets ($sheet);
$sheets->activate;
Get a cell
$cell = $sheets->cells (5,5);
$cell->activate;
Assign a value to the cell
$cell->value = 999;
Save as another file Newtest.xls
$ex->application->activeworkbook->saveas ("Newtest.xls");
Turn off Excel and, if you want to see the effect, comment out the following two lines and manually turn off Excel by the user
$ex->application->activeworkbook->close ("False");
Unset ($EX);
?>
Four, PHP to generate Excel Dongdong
You can use PHP to generate Excel files.
----------------------------
Excel functions
----------------------------
Save the following code as excel.php and include it in the page
and then call
1. Call Xlsbof ()
2. Write some content to Xlswritenunber () or Xlswritelabel ().
3. Then invoke call Xlseof ()
You can also use the Fwrite function to write directly to the server, rather than using echo only to display on the browser.
Copy Code code as follows:
<?php
-----The begin of Function library-----
Excel begin with File header
function Xlsbof () {
Echo Pack ("Ssssss", 0x809, 0x8, 0x0, 0x10, 0x0, 0x0);
Return
}
Excel End of File footer
function xlseof () {
Echo Pack ("ss", 0x0A, 0x00);
Return
}
Function to write a number (double) into Row, Col
function Xlswritenumber ($Row, $Col, $Value) {
Echo Pack ("Sssss", 0x203, $Row, $Col, 0x0);
Echo Pack ("D", $Value);
Return
}
Function to write a label (text) into Row, Col
function Xlswritelabel ($Row, $Col, $Value) {
$L = strlen ($Value);
Echo Pack ("Ssssss", 0x204, 8 + $L, $Row, $Col, 0x0, $L);
Echo $Value;
Return
}
-----End of Function library-----
?>
//
To display the contents directly in a MIME compatible browser
Add the following lines on top of your PHP file:
<?php
Header ("Expires:mon, June June 1997 05:00:00 GMT");
Header ("last-modified:"). Gmdate ("D,d M yh:i:s"). "GMT");
Header ("Cache-control:no-cache, must-revalidate");
Header ("Pragma:no-cache");
Header (@ #Content-type:application/x-msexcel@#);
Header ("content-disposition:attachment; Filename=empllist.xls ");
Header ("Content-description:php/interbase generated Data");
//
The next lines demonstrate the generation of the Excel stream
//
Xlsbof (); Begin Excel Stream
Xlswritelabel (0,0, "This is a label"); Write a label in A1, use for dates too
Xlswritenumber (0,1,9999); Write a number B1
Xlseof (); Close the stream
?>
