PHP Export Data to Excel

PHP Export Data to Excel

In web development, it is often necessary to import some data into Excel tables, such as the need to export eligible user data, export items including name , gender ,* * * number , contact information , and so on. So the problem is, in the exported data if there is a longer value type of items, by default by the scientific notation, such as * * * This is obviously not what we need, then what can we do to make the data in the format we want to display it?

Find some relevant information, excerpt as follows:

First, let's look at how Excel derives from Web pages. When we send this data to the client, we want to have the client program (the browser) read it in Excel format, so we set the MIME type to: application/ Vnd.ms-excel, when Excel reads a file, it renders the data in the format of each cell, and if the cell does not have a specified format, Excel renders the cell's data in the default format. This gives us the space to customize the data format, and of course we must use the format supported by Excel. Here's a list of some commonly used formats:
-Text: vnd.ms-excel.numberformat:@
-Date: Vnd.ms-excel.numberformat:yyyy/mm/dd
-Number: vnd.ms-excel.numberformat:#,# #0.00
-Currency: vnd.ms-excel.numberformat:￥#,# #0.00
-Percentage: Vnd.ms-excel.numberformat: #0.0%
These formats you can also customize, such as the year you can be defined as: yy-mm and so on. So what do you know about these formats and how do you add them to the cell? Very simply, we just need to add the style to the corresponding label pair (that is, the closing tag). Such as<td></td>, give the label to<td></td>Add a style as follows:<td style="vnd.ms-excel.numberformat:@">410522198402161833</td>Similarly, we can add a style to the div, or we can add a style to the tr,table, and when we add a style to both the parent tag pair and the child label pair, which style will the data be rendered? After testing, it is rendered in a style that is closest to the data. --Original link

The following are two sample codes for the custom format output and the default format output, respectively

Custom formats

      /**     *  User Information Export to Excel (method one)       *  Advantages: Output data column format can be customized, such as numeric, string, etc.      *  disadvantage: the need to assemble two-dimensional table data into HTML table code, Code Concise more complex      */    public function exportdo ()  {         set_time_limit (0);          $groupid  = $_request[' groupid '];         $field  =  $_request[' field '];         $head  = $_request[' head ';         //Query Data          $model  = new groupusermodel ();         $data  = $ Model->field (field )                    &nbSp;   ->where ("groupid = ". $groupid. "")                        ->order (' id ')                        ->select ();         array_unshift ($data,  $head);   //Add header           $file _name =  ' user Details ';          //excel file name         //Assemble user information into a table to export to Excel           $str  =  ";  //initialize the         //output header           $str  .=  ' <table border= "1" >                     <tr>                         <td colspan= "'. Count ($data [0]) .‘"  align= "center"  style= "Font-size:16px; height:32px; font-weight:bold" > ". $file _name. ' </td>                     </tr> ';         //loop output Line               foreach ($data  as  $v 1)  {              $str  .=  ' <tr> ';             //Loop Output Column              foreach ($v 1 as  $v 2)  {                  $str  .=  ' <td style= ' vnd.ms-excel.numberformat:@ > '. $v 2. ' </td> ';            }              $str  .=  ' </tr> ';         }         $str  .=     ' </ Table> ';         //convert to GBK encoding           $str  = iconv ("UTF-8", "GBK", $str);         //output         header (' content-length:  '. strlen ($STR));         header ("Content-type:application/vnd.ms-excel");         header ("content-disposition:filename={$file _name}.xls");         echo  $str;     } 

Default format

    /**     *  user information exported to Excel (Method II)       *  Advantages: No need to assemble two-dimensional table data into HTML table code, code concise      *  disadvantage: Output data column format is not controllable, such as * * * Number with scientific notation      */    public function exportdo ()  {         set_time_limit (0);         $ groupid = $_request[' GroupID '];         $field  = $_ request[' field '];         $head  = $_request[' head '];         header ("CONTENT-TYPE:APPLICATION/VND.MS-EXCEL;CHARSET=UTF-8;");          header ("Content-disposition:filename=". Iconv ("Utf-8",   "gb2312",  "user details. xls");         //assembly Query Header           $head _Field = array ();        foreach  ($head  as $ key =>  $row)  {            if ( Isset ($field [$key])  array_push ($head _field,  $row);         }         //Query Data          $model  = new groupusermodel ();         $data  = $ Model->field ($select _field)                        ->where ("groupid = ". $groupid. "")                        ->order (' id ')                   &Nbsp;    ->select ();         array_unshift ($data ,  $head _field)   //Add header         foreach  ($data  as  $key  =>  $row)  {              $out _string =  ';             foreach ($row  as  $h _key =>  $h _row)  {                  $out _string .= iconv ("Utf-8",  " gb2312 ",  $h _row)." \ t ";            }             echo  $out _string;             echo  "\ n";        }     }

This article is from the "July Maple" blog, please be sure to keep this source http://qiyuefeng.blog.51cto.com/7322546/1622873

PHP Export Data to Excel