Use PHPExcel in php to Operate excel (xls) files

Use PHPExcel in php to Operate excel (xls) files

PHPExcel is a php plug-in that can read excel files, that is, xls files. Let's take a look at an example of using PHPExcel to Operate excel (xls) files, hoping to help you.

Reading Chinese xls and csv files may cause problems. I found some documents on the Internet and found that the PHPExcel class library is easy to use. 1. Reading the xls file content

The Code is as follows:

<? Php // Write content to the xls file Error_reporting (E_ALL ); Ini_set ('display _ errors ', TRUE ); Include 'classes/PHPExcel. php '; Include 'classes/PHPExcel/IOFactory. php '; // $ Data: the body of the xls file // $ Title: The content title of the xls file // $ Filename: exported file name // $ Data and $ title must be UTF-8; otherwise, FALSE values are written. Function write_xls ($ data = array (), $ title = array (), $ filename = 'report '){ $ ObjPHPExcel = new PHPExcel (); // Set document attributes. garbled characters are generated when Chinese characters are set... // $ ObjPHPExcel-> getProperties ()-> setCreator ("yunshu ") //-> SetLastModifiedBy ("yunshu ") //-> SetTitle ("Product URL export ") //-> SetSubject ("Product URL export ") //-> SetDescription ("Product URL export ") //-> SetKeywords ("Product URL export "); $ ObjPHPExcel-> setActiveSheetIndex (0 ); $ Cols = 'abcdefghijklmnopqrstuvwxy '; // Set www.111cn.net title For ($ I = 0, $ length = count ($ title); $ I <$ length; $ I ++ ){ // Echo $ cols {$ I}. '1 '; $ ObjPHPExcel-> getActiveSheet ()-> setCellValue ($ cols {$ I}. '1', $ title [$ I]); } // Set the title Style $ TitleCount = count ($ title ); $ R = $ cols {0}. '1 '; $ C = $ cols {$ titleCount}. '1 '; $ ObjPHPExcel-> getActiveSheet ()-> getStyle ("$ r: $ c")-> applyFromArray ( Array ( 'Font' => array ( 'Bold '=> true ), 'Alignment '=> array ( 'Horizontal '=> PHPExcel_Style_Alignment: HORIZONTAL_RIGHT, ), 'Borders' => array ( 'Top' => array ( 'Style' => PHPExcel_Style_Border: BORDER_THIN ) ), 'Fill' => array ( 'Type' => PHPExcel_Style_Fill: FILL_GRADIENT_LINEAR, 'Rotation' => 90, 'Startcolor' => array ( 'Arg' => 'ffa0a0' ), 'Endcolor' => array ( 'Arg' => 'ffffffff' ) ) ) ); $ I = 0; Foreach ($ data as $ d) {// use foreach here. It supports associating arrays and Numerical index arrays. $ J = 0; Foreach ($ d as $ v) {// use foreach here. It supports associating arrays and Numerical index arrays. $ ObjPHPExcel-> getActiveSheet ()-> setCellValue ($ cols {$ j}. ($ I + 2), $ v ); $ J ++; } $ I ++; } // Generate an xls file in 2003excel format Header ('content-Type: application/vnd. ms-excel '); Header ('content-Disposition: attachment; filename = "'.w.filename.'.xls "'); Header ('cache-Control: max-age = 0 '); $ ObjWriter = PHPExcel_IOFactory: createWriter ($ objPHPExcel, 'excel5 '); $ ObjWriter-> save ('php: // output '); } $ Array = array ( Array (1111, 'name', 'brand', 'commodity name', 'HTTP: // www.baidu.com '), Array (1111, 'name', 'brand', 'commodity name', 'HTTP: // www.baidu.com '), Array (1111, 'name', 'brand', 'commodity name', 'HTTP: // www.baidu.com '), Array (1111, 'name', 'brand', 'commodity name', 'HTTP: // www.baidu.com '), Array (1111, 'name', 'brand', 'commodity name', 'HTTP: // www.baidu.com '), ); Write_xls ($ array, array ('item id', 'Supplier name', 'brand', 'item name', 'url'), 'report '); ?>

2. Write content to the xls file

The Code is as follows:

<? Php // Obtain database data (mysqli preprocessing) $ Config = array ( 'Db _ type' => 'mysql ', 'Db _ host' => 'localhost ', 'Db _ name' => 'test ', 'Db _ user' => 'root ', 'Db _ pwd' => 'root ', 'Db _ port' => '123 ', ); Function getProductIdByName ($ name ){ Global $ config; $ Id = false; $ Mysqli = new mysqli ($ config ['db _ host'], $ config ['db _ user'], $ config ['db _ pwd'], $ config ['db _ name']); If (mysqli_connect_error () {// compatible with <php5.2.9 OO way: $ mysqli-> connect_error Die ("connection failed, error code:". mysqli_connect_errno (). "error message:". mysqli_connect_error ()); } // Set the database connection encoding. Do not forget to set it. $ Mysqli-> set_charset ("gbk "); // The encoding of Chinese characters must be consistent with that of the database. If no encoding is set, the result is null. $ Name = iconv ("UTF-8", "gbk // IGNORE", $ name ); If ($ mysqli_stmt = $ mysqli-> prepare ("select id from 137_product where name like? ")){ $ Mysqli_stmt-> bind_param ("s", $ name ); $ Mysqli_stmt-> execute (); $ Mysqli_stmt-> bind_result ($ id ); $ Mysqli_stmt-> fetch (); $ Mysqli_stmt-> close (); } $ Mysqli-> close (); Return $ id; // obtain the gbk code (same as the database code) } $ Id = getProductIdByName ('% Chennai bathroom inner toilet seat % '); Var_dump ($ id ); ?>

OK...