[Learn More-PHP] How to Use phpexcel to export Excel files

How to Use phpexcel to export Excel files

Autor: xiaoqingtime: 2011-04-22

To sum up the problems encountered recently when using the phpexcel class to export Excel files, there are three main categories:

1. What should I do if the system prompts that the file cannot be found during export?

2. Chinese characters are garbled during Excel export.

3. When the exported data volume is large, the system prompts that the memory exceeds the maximum value or the running time times out.

Let's take a look at the common methods of phpexcel Summary (extracted from zeal_blog: http://www.zeali.net/entry/556)

<? /*** If excel5 is used, the output content should be GBK encoded. */Require_once 'phpexcel. PHP '; // require_once 'phpexcel/Writer/excel5.php'; // used for other earlier versions of XLS // require_once 'phpexcel/Writer/excel2007.php '; // In the Excel-2007 format $ table = new phpexcel (); // you can specify the current sheet index for subsequent content operations. // Display the call only when multiple sheets are used. // By default, phpexcel will automatically create the first sheet and set sheetindex = 0 $ table-> setactivesheetindex (0 ); // set the name of the current active sheet $ table-> setactivesheetindex (0)-> settitle ('test _ title'); $ ohjactsheet = $ table-> setactivesheetindex (0 ); // set the cell content // phpexcel automatically determines the cell content type based on the input content $ objactsheet-> setcellvalue ('a1', 'string content '); // string content $ objactsheet-> setcellvalue ('a2 ', 26); // value $ objactsheet-> setcellvalue ('a3', true ); // Boolean value $ objactsheet-> setcellva Lue ('a4 ',' = sum (A2: A2) '); // formula // explicitly specify the content type $ objactsheet-> setcellvalueexplicit ('a5 ', '200', phpexcel_cell_datatype: type_string); // merge cells $ objactsheet-> mergecells ('b1: c22'); // separate cells $ objactsheet-> unmergecells ('b1: c22 '); // set the cell style $ objactsheet-> getcolumndimension (' B ')-> setautosize (true); $ objactsheet-> getcolumndimension ('A ') -> setwidth (30); $ objstylea5 = $ objactsheet-> getstyle ('a5 ');// Set the font $ objfonta5 = $ objstylea5-> getfont (); $ objfonta5-> setname ('courier new'); $ objfonta5-> setsize (10 ); $ objfonta5-> setbold (true); $ objfonta5-> setunderline (phpexcel_style_font: underline_single); $ objfonta5-> getcolor ()-> setargb ('ff999999 '); // set the aligment mode $ objaligna5 = $ objstylea5-> getalignment (); $ region-> sethorizontal (Region: horizontal_right); // horizontally $ objaligna5-> setvertical Vertex: vertical_center); // vertical // set the border $ objbordera5 = $ objstylea5-> getborders (); $ objbordera5-> gettop ()-> setborderstyle (phpexcel_style_border :: border_thin); $ objbordera5-> gettop ()-> getcolor ()-> setargb ('ffff00000000'); // color $ objbordera5-> getbottom ()-> setborderstyle (Border:: border_thin); $ objbordera5-> getleft ()-> setborderstyle (phpexcel_style_border: border_thin); $ objbordera 5-> getright ()-> setborderstyle (phpexcel_style_border: border_thin); // Add a new worksheet $ table-> createsheet (); $ table-> getsheet (1) -> settitle ('test 2'); // protect cells $ table-> getsheet (1)-> getprotection ()-> setsheet (true ); $ table-> getsheet (1)-> protectcells ('a1: c22', 'phpexcel '); // $ objwriter-> Save ($ outputfilename ); // header ("Content-Type: Application/octet-stream"); // header ('content-Disposition: inline; filename = "'. $ Outputfilename. '"'); // header (" cache-control: Must-revalidate, post-check = 0, pre-check = 0 "); // header (" Pragma: no-Cache "); // $ objwriter-> Save ('php: // output');?>

Summary of frequently used problems:

1. Questions about the file not found:

Possible causes are: the buffer is not opened or the mb_overlod_func function is not closed. The best way to solve this problem is to open display_errors in PHP. ini, download the file, and prompt the corresponding error message. If it is the latter, open the php. ini file and find mbsrtring. func_overload = 7 to hide it.

If the Excel file can be exported normally but there is no data, it is generally a program error (no data is obtained or the data is not assigned a value)

2. Garbled text occurs during Excel export. There are many answers to this question on the Internet. The main solutions include:

(1 ). cause of garbled characters: the Chinese version of the Windows system platform used by the customer, while the file name encoding of the Windows platform is gb2312 (GBK). In order to follow up the existing trend, we generally use UTF-8 (international) encoding, in this case, when we: Header ("content-Disposition: inline; filename = \"". $ filename. ". when XLS-: Special: 1:-"), garbled characters may occur. If your webpage code is gb2312, you do not need to consider the encoding problem (otherwise, garbled characters may occur after transcoding ).

Solution: transcode $ filename and execute: iconv ('utf-8 "," gb2312 ", $ filename ). If your environment does not support iconv functions, you only need to convert $ filename encoding to GBK.

(2) Add the corresponding header information to the output file:

Header ('content-type: Application/vnd. MS-Excel; charset = UTF-8 ');

(3) If the above method is garbled, it may be that there are other redundant content in the output buffer. In the PHP file, set the Excel content to clean up the output buffer: <? PHP ob_clean ();?>

The above three steps can basically cover most Excel garbled issues.

3. An error is prompted when exporting big data.

Open display_errors in the PHP. ini file and view the specific error information. Generally, there are two types:

(1). the runtime exceeds maxium time, which may be caused by an endless or large loop in the file. Check the source code to see if there is an endless loop. We recommend that you do not set the Excel format in the loop body. It takes more time. After preliminary tests, if 3-4 formats are set in the loop body, the running timeout may occur when about 1500 data entries exist. Therefore, try to place the Code with the specified format in vitro. If there is still a problem, there are two methods: Modify the INI file and set time_limit = 0; if the INI file cannot be modified remotely, add set_time_limit (0) to the program );

(2). Memory size of... bytes exhausted this is a typical case of insufficient memory. If you can modify PHP. in the INI file, replace memory_limit = 32 m with memory_limit = 64 m or M larger. PHP cannot be modified remotely. INI file, you need to modify it in the Code: @ ini_set ('memory _ limit ', '128m ');