Export function code in PHPExcel

Source: Internet
Author: User
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

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.