Provides various official and user-released code examples. For code reference, you are welcome to exchange and learn how to export excel function code.
Common functions:
/**
* Export data tables in excel
*
* @ Author rainfer <81818832@qq.com>
*
* @ Param string $ table, excluding the prefix table name, required
* @ Param string $ file: The saved excel file name. The default table name is the file name.
* @ Param string $ fields: names of fields to be exported. All fields are separated by commas by default.
* @ Param string $ field_titles: the title of the field to be exported. It must correspond to $ field one by one. If it is null, the field name is used as the title and separated by commas.
* @ Param stting $ tag. The filter conditions are passed in as strings, for example, "limit:; order: post_date desc, listorder desc; where: id> 0 ;"
* Limit: the number of data entries. It can be specified to start from the number of entries, for example, 3 or 8 (8 calls in total, starting from 3rd)
* Order: sorting method, for example, post_date desc
* Where: the query condition in the string format. It is the same as the SQL statement.
*/
Function export2excel ($ table, $ file = '', $ fields ='', $ field_titles = '', $ tag = ''){
// Process passed parameters
If (stripos ($ table, C ('db _ prefix') = 0 ){
// Remove the table prefix from a table with a prefix
$ Table = substr ($ table, strlen (C ('db _ prefix ')));
}
$ File = empty ($ file )? C ('db _ prefix'). $ table: $ file;
$ Fieldsall = M ($ table)-> getDbFields ();
$ Field_titles = empty ($ field_titles )? Array (): explode (",", $ field_titles );
If (empty ($ fields )){
$ Fields = $ fieldsall;
// If the number of members is inconsistent, the field name is used as the title.
If (count ($ fields )! = Count ($ field_titles )){
$ Field_titles = $ fields;
}
} Else {
$ Fields = explode (",", $ fields );
$ Rst = array ();
$ Rsttitle = array ();
$ Title_y_n = (count ($ fields) = count ($ field_titles ))? True: false;
Foreach ($ fields as $ k => $ v ){
If (in_array ($ v, $ fieldsall )){
$ Rst [] = $ v;
// Take the specified title for one-to-one correspondence; otherwise, use the field name.
$ Rsttitle [] = $ title_y_n? $ Field_titles [$ k]: $ v;
}
}
$ Fields = $ rst;
$ Field_titles = $ rsttitle;
}
// Process tag tags
$ Tag = param2array ($ tag );
$ Limit =! Empty ($ tag ['limit'])? $ Tag ['limit']: '';
$ Order =! Empty ($ tag ['order'])? $ Tag ['order']: '';
$ Where = array ();
If (! Empty ($ tag ['where']) {
$ Where ['_ string'] = $ tag ['where'];
}
// Process data
$ Data = M ($ table)-> field (join (",", $ fields)-> where ($ where)-> order ($ order) -> limit ($ limit)-> select ();
Import ("Org. Util. PHPExcel ");
Error_reporting (E_ALL );
Date_default_timezone_set ('Europe/London ');
$ ObjPHPExcel = new \ PHPExcel ();
Import ("Org. Util. PHPExcel. Reader. Excel5 ");
/* Set excel attributes */
$ ObjPHPExcel-> getProperties ()-> setCreator ("rainfer") // creator
-> SetLastModifiedBy ("rainfer") // The Last modifier.
-> SetKeywords ("excel") // keyword
-> SetCategory ("result file"); // type
// The first row of data
$ ObjPHPExcel-> setActiveSheetIndex (0 );
$ Active = $ objPHPExcel-> getActiveSheet ();
Foreach ($ field_titles as $ I => $ name ){
$ Ck = num2alpha ($ I ++). '1 ';
$ Active-> setCellValue ($ ck, $ name );
}
// Fill in data
Foreach ($ data as $ k => $ v ){
$ K = $ k + 1;
$ Num = $ k + 1; // data is input from the second row
$ ObjPHPExcel-> setActiveSheetIndex (0 );
Foreach ($ fields as $ I => $ name ){
$ Ck = num2alpha ($ I ++). $ num;
$ Active-> setCellValue ($ ck, $ v [$ name]);
}
}
$ ObjPHPExcel-> getActiveSheet ()-> setTitle ($ table );
$ ObjPHPExcel-> setActiveSheetIndex (0 );
Header ('content-Type: application/vnd. ms-excel ');
Header ('content-Disposition: attachment; filename = "'.w.file.'.xls "');
Header ('cache-Control: max-age = 0 ');
$ ObjWriter = \ PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5 ');
$ ObjWriter-> save ('php: // output ');
Exit;
}Two functions used:
/**
* Generate a parameter list and return it as an array
* @ Author rainfer <81818832@qq.com>
*/
Function param2array ($ tag = ''){
$ Param = array ();
$ Array = explode (';', $ tag );
Foreach ($ array as $ v ){
$ V = trim ($ v );
If (! Empty ($ v )){
List ($ key, $ val) = explode (':', $ v );
$ Param [trim ($ key)] = trim ($ val );
}
}
Return $ param;
}
/**
* Numbers to letter columns
* @ Author rainfer <81818832@qq.com>
*/
Function num2alpha ($ intNum, $ isLower = false)
{
$ Num26 = base_convert ($ intNum, 10, 26 );
$ Addcode = $ isLower? 49: 17;
$ Result = '';
For ($ I = 0; $ I <strlen ($ num26); $ I ++ ){
$ Code = ord ($ num26 {$ I });
If ($ code <58 ){
$ Result. = chr ($ code + $ addcode );
} Else {
$ Result. = chr ($ code + $ addcode-39 );
}
}
Return $ result;
}For PHPExcel, download it by yourself
Instance reference YFCMF: http://www.topthink.com/topic/23584.html
AD: truly free, domain name + VM + enterprise mailbox = 0 RMB