Summary of php excel file operations

Use php to generate an excel file. use php alone. just pay attention to the data input method. 1. generate excel files without using COM in php

The code is 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 ";
?>

When you run the above code in the php environment, you can see the browser asking you whether to download the excel document, click save, and an excel file is added to the hard disk, you can see the final result when using excel.
In fact, when doing real applications, you can extract the data from the database, and then add the data following each column, and echo the method after each row of data ends, use header ("Content-type: application/vnd. ms-excel ") indicates that the excel file is output, and the file name output is text.xls using header (" Content-Disposition: filename=test.xls. In this case, OK.
We can modify the header so that it can output more files in different formats. This makes php easier to process various types of files.
2. use PHP to convert a mysql data table to an excel file format

The code is as follows:

$ 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, 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 ("$ 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 {
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 excel operations (using a COM object to generate an excel file)
This is for those who just like to simply process the excel files.

The code is as follows:

// Define an excel file
$ Workbook = "C:/My Documents Ents/test.xls ";
$ Sheet = "Sheet1 ";
// Generate a com object $ ex
$ Ex = new COM ("Excel. sheet") or Die ("no connection !!! ");
// Open an excel file
$ Book = $ ex-> application-> Workbooks-> Open ($ workbook) or Die ("cannot be opened !!! ");
$ Sheets = $ book-> Worksheets ($ sheet );
$ Sheets-> activate;
// Obtain a cell
$ Cell = $ sheets-> Cells (5, 5 );
$ Cell-> activate;
// Assign a value to the cell
$ Cell-> value = 999;
// Save it as another file newtest.xls
$ Ex-> Application-> ActiveWorkbook-> SaveAs ("newtest.xls ");
// Close the excel file. if you want to see the results, comment out the following two lines. you can manually turn off the excel file.
$ Ex-> Application-> ActiveWorkbook-> Close ("False ");
Unset ($ ex );
?>

4. create EXCEL files in php
You can use PHP to generate an EXCEL file.
----------------------------
Excel Functions
----------------------------
Save the following code as excel. php and include it in the page.
Then call
1. Call xlsBOF ()
2. write some content to xlswritenunber () or xlswritelabel.
3. Call xlsEOF ()
You can also use the fwrite function to directly write data to the server, instead of simply displaying data in the browser with echo.

The code is as follows:

// ----- 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:
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
?>