In php, use PHPExcel to read and write excel (xls) files. _ PHP Tutorial

In php, you can use PHPExcel to read and write excel (xls) files ,. Php uses PHPExcel to read and write excel (xls) files. This article describes how to use PHPExcel to read and write excel (xls) files in PHP, which is very practical. Share it with you for your reference. In php, you can use PHPExcel to read and write excel (xls) files,

This example describes how to use PHPExcel to read and write excel (xls) files in PHP, which is very practical. Share it with you for your reference. The specific method is as follows:

Many PHP class libraries may encounter problems when reading Chinese xls and csv files. I found some information on the internet and found that the PHP Excel class library is easy to use. the official website address is http://phpexcel.codeplex.com /. The methods for reading and writing PHPExcel files are described as follows:

1. read the content of 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. UTF-8 format is required !! // $ 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 www.jb51.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, supports associating arrays and numeric index arrays $ j = 0; foreach ($ d as $ v) {// use foreach here, supports associating arrays and numeric index arrays $ objPHPExcel-> getActiveSheet ()-> setCellValue ($ cols {$ j }. ($ I + 2), $ v); $ j ++;} $ I ++;} // Generate the xls file 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 (1111, 'name', 'brand', 'commodity name ',' http://www.bkjia.com '), Array (1111, 'name', 'brand', 'commodity name ',' http://www.bkjia.com '), Array (1111, 'name', 'brand', 'commodity name ',' http://www.bkjia.com '), Array (1111, 'name', 'brand', 'commodity name ',' http://www.bkjia.com '), Array (1111, 'name', 'brand', 'commodity name ',' http://www.bkjia.com '),); Write_xls ($ array, array ('item ID', 'Supplier name', 'brand', 'item name', 'URL '), 'report');?>

2. write content to the xls file

<? 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; // Obtain the gbk code (same as the database code)} $ id = getProductIdByName ('% Nai Wei Yi Nai split toilet %'); var_dump ($ id);?>

I hope this article will help you with PHP programming.

This document describes how to use PHPExcel to read and write excel (xls) files in PHP. I will share it with you for your reference ....