Summary of PHP methods for manipulating excel files

One, PHP, no COM, generate excel file

 
<? 
header("Content-type:application/vnd.ms-excel"); 
header("Content-Disposition:filename=test.xls"); 
echo "test1t"; 
echo "test2tn"; 
echo "test1t"; 
echo "test2tn"; 
echo "test1t"; 
echo "test2tn"; 
echo "test1t"; 
echo "test2tn"; 
echo "test1t"; 
echo "test2tn"; 
echo "test1t"; 
echo "test2tn"; 
?> 

Running the above code in the PHP environment, you can see the browser asked the user whether to download the excel document, click save, there is an excel file on the hard disk, open with excel will see the final result, how good.
In fact, in the real application, you can take the data out of the database, and then echo out the method of adding \t at the end of each column and \n at the end of each row, using header(" content-type :application/ vrd.ms-excel ") at the beginning of PHP; The output is an excel file, with a header(" content-disposition :filename=test.xls"); The file that represents the output is called text.xls. That's ok.
We can also modify the header to output more files in more formats, which makes it easier for PHP to handle various types of files.
Ii. Convert mysql data table into excel file format with PHP

 
<?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 = " The database name :$DB_DBName, The data table :$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("$titlen"); 
$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); 
?> 

3. An example of PHP operation on excel (using COM object to generate excel)
This is for those of you who like to do a little bit of excel

 
<?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 (" I can't connect!! "); 
//Open an excel file
$book = $ex->application->Workbooks->Open($workbook) or Die (" Can't open it!! "); 
$sheets = $book->Worksheets($sheet); 
$sheets->activate; 
//Gets 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"); 
//Close excel, and if you want to see the effect, comment out the next two lines and let the user close excel manually
$ex->Application->ActiveWorkbook->Close("False"); 
unset ($ex); 
?> 

Four, PHP to generate EXCEL
You can generate EXCEL files through PHP.
----------------------------
Excel Functions provides
----------------------------
Save the following code as excel.php and include it on the page
And then call
1. Call xlsBOF ()
2. Write something to xlswritenunber() or xlswritelabel().
3. Then Call xlsEOF()
You can also write directly to the server using the fwrite function instead of just displaying it in the browser using the echo.

 
<?php 
// ----- begin of function library ----- 
// Excel begin of 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, 14, $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, 26 Jul 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 
?>