The following introduces several PHP implementation programs for generating EXCEL documents. Friends in need can refer to them.
Original writing style
Original method: send the header, use the header of the attachment to send it to the user's browser to indicate that it is to be downloaded, then read the data in the database, parse it one by one, and write it into a file in excel format
The code is as follows | Copy code | ||||
|
Use PHPExcel library
The following is a method of using PHPExcel to implement excel with the same functions as above. getCol is a recursively implemented function used to return the corresponding column number code based on the number. Because the row number and column number need to be pointed out during the export process. Row numbers are simple numbers, while column numbers are a combination of "A-Z". In order to facilitate the import of two-dimensional arrays, the column number code is automatically obtained based on the number of columns
Instructions for use:
1. Save the following code as excel.php, and then call it on the page.
2. Then call xlsBOF(), write some content into xlswritenunber() or xlswritelabel(), and finally call xlsEOF() to end.
You can also use the fwrite function to write directly to the server instead of using echo to only display it on the browser.
The following is the PHP code:
The code is as follows | Copy code | ||||
// 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; } ?> The following is the calling code: 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 // include ("excel.php"); 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 ?> |
Complete example
The code is as follows | Copy code |
/** // uncomment //Create a processing object instance //Create file format writing object instance, uncomment //****************************************** //******************************************
//Set the name of the current active sheet //****************************************** //Explicitly specify the content type //Merge cells //Separate cells //****************************************** //Set width $objStyleA5 = $objActSheet->getStyle('A5'); //Set the number format of the cell content. //Set font //Set alignment //Set the border //Set fill color //Copy style information from the specified cell.
//Protect cell
?> |