Use PHPExcel in php to Operate excel (xls) files,

Use PHPExcel in php to Operate excel (xls) files,

Reading Chinese xls, csv files will have problems, find the information online, found PHPExcel class library easy to use, official website address: http://phpexcel.codeplex.com/

1. Read the content of the xls file

The Code is as follows:

Copy code

<? 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:

Copy code

<? 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...

 

Related content

• 2014.05.28PHPEXCEL import an excel table to generate an array
• Example of 2014.04.16thinkphp calling phpexcel to export an excel file to a local file
• 2014.04.11thinkphp import excel using phpexcel
• 2014.04.09ThinkPHP use PHPExcel to import and export Excel files
• 2014.03.19PHP use the PHPExcel class to import and export excel
• 2013.11.24PHP use PHPExcel to export an Excel example
• 2013.08.13PHPExcel instance program for generating and reading Excel files
• Export the Excel instance code using PHPExcel in 2013.08.13Yii
• 2013.05.06PHPExcel reads excel and imports mysql database code
• 2013.05.03php excel operation class phpExcel usage Introduction