PHPExcel to operate xls files,

PHPExcel to operate 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

<? Php // read the xls header ("Content-Type: text/html; charset = UTF-8"); include 'classes/PHPExcel. php'; include 'classes/PHPExcel/IOFactory. php '; function readxls ($ file, $ type) {$ xlsReader = PHPExcel_IOFactory: createReader ($ type); $ xlsReader-> setReadDataOnly (true ); $ xlsReader-> setLoadSheetsOnly (true); $ sheets = $ xlsReader-> load ($ file); $ content = $ sheets-> getSheet (0)-> toArray (); // read the first Worksheet (Note that the number starts from 0). If you can read multiple Make a loop 0, 1, 2, 3 .... // obtain a two-dimensional array. Each small array is the data returned $ content;} // $ type = 'excel2007 'in each column of the excel table content '; // set the Excel type Excel5 (2003 or earlier) or Excel2007 $ type = 'excel5'; $ content = readxls('data.xls ', $ type) to be parsed ); echo '<pre>'; var_dump ($ content); echo '</pre>';?>

2. Write content to the xls file

 

<? Php // write the content error_reporting (E_ALL) to the xls file; ini_set ('display _ errors ', TRUE); include 'classes/PHPExcel. php'; include 'classes/PHPExcel/IOFactory. php '; // $ data: Body of the xls file content // $ title: title of the xls file content // $ filename: the exported file name // $ data and $ title must be UTF-8; otherwise, the FALSE value function write_xls ($ data = array (), $ title = array () will be written (), $ filename = 'report') {$ objPHPExcel = new PHPExcel (); // set document attributes. garbled characters are generated when Chinese characters are set... // $ objPHPExcel-> getProperties ()-> SetCreator ("Yun Shu") //-> setLastModifiedBy ("Yun Shu") //-> setTitle ("Product URL export ") //-> setSubject ("Product URL export") //-> setDescription ("Product URL export") //-> setKeywords ("Product URL export "); $ objPHPExcel-> setActiveSheetIndex (0); $ cols = 'abcdefghijklmnopqrstuvwxyz '; // set the 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' => 'ffffff'); for ($ I = 0, $ length = count ($ data ); $ I <$ length; $ I ++) {$ j = 0; foreach ($ data [$ I] as $ v) {// use foreach here, supports associating arrays and numeric index arrays $ objPHPExcel-> getActiveSheet ()-> setCellValue ($ cols {$ j }. ($ I + 2), $ v); $ j ++ ;}/// generate the xls file header ('content-Type: application/vnd. ms-e Xcel '); 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 (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');?>

 

3. Obtain the content to be written by operating the database. For example, you can use mysqli preprocessing to obtain the content:

<? 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 encoding of the connected database. Do not forget to set $ mysqli-> set_charset ("gbk"); // the encoding of Chinese characters must be consistent with that of the database, if not 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 ;} $ id = getProductIdByName ('% Chennai bathroom inner split toilet %'); var_dump ($ id);?>

OK...

References: http://blog.sina.com.cn/s/blog_44b3f96d0101cczo.htmlhttp://phpexcel.codeplex.com/