Php excel file operations method summary _ PHP Tutorial-php Tutorial

Source: Internet
Author: User
Tags php excel
A summary of php operations on excel files. I. php. the code for generating an excel file without using COM is as follows :? Header (Content-type: applicationvnd. ms-excel); header (Content-Disposition: filenametest.xls); echotest1 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 \ t after each column of data ends, after each row ends, add the \ n method to echo it, and 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
?>

The pipeline code is as follows :? Header ("Content-type: application/vnd. ms-excel"); header ("Content-Disposition: filename=test.xls"); echo "test1...

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.