How to use Phpexcel to read and write Excel (xls) files in PHP, _php tutorial

How to use Phpexcel to read and write Excel (xls) files in PHP,

The example in this article describes how to use Phpexcel to read and write Excel (xls) files in PHP, which is very useful. Share it for everyone's reference. Here's how:

Many PHP library in the reading of Chinese XLS, CSV file will have a problem, the Internet found the following information, found Phpexcel class library useful, the official website address is: http://phpexcel.codeplex.com/. The methods of Phpexcel reading and writing Excel are described as follows:

1. read xls file contents

<?php//write content to xls file error_reporting (e_all);    Ini_set (' display_errors ', TRUE);        Include ' classes/phpexcel.php ';   Include ' classes/phpexcel/iofactory.php '; $data: XLS file content body//$title: XLS file content title//$filename: Exported filename//$data and $title must be utf-8 code, otherwise write false value function Write_xls ($    Data=array (), $title =array (), $filename = ' report ') {$objPHPExcel = new phpexcel ();    Set document properties, set Chinese will produce garbled, need to convert to UTF-8 format!! $objPHPExcel->getproperties ()->setcreator ("Yun Shu")//->setlastmodifiedby ("Yun Shu")//-&               Gt;settitle ("Product URL Export")//->setsubject ("Product URL Export")//->setdescription ("Product URL export")    ->setkeywords ("Product URL export");        $objPHPExcel->setactivesheetindex (0);    $cols = ' abcdefghijklmnopqrstuvwxyz '; Set 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 heading 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::hor Izontal_right,), ' borders ' = = Array (' top ' = = Array (' style ' = Phpexcel_sty Le_border::border_thin)), ' fill ' = = Array (' type ' = = Phpexcel_style_fill::fill_          Gradient_linear, ' rotation ' = +, ' startcolor ' = = Array (' ARGB ' = ' ffa0a0a0 '        ), ' endcolor ' = = Array (' ARGB ' = ' FFFFFFFF ')));    $i = 0;      foreach ($data as $d) {//here with foreach, supports associative arrays and numeric indexed arrays $j = 0; foreach ($d as $v) {//here with foreach, supports associative arrays and numeric indexed arrays $objPHPExcel->getactivesheet ()->setcellvalue ($cols {$j}.($i +2), $v);      $j + +;    } $i + +;    }//Generate 2003excel format of XLS file header (' Content-type:application/vnd.ms-excel '); Header (' Content-disposition:attachment;filename= '. $filename. '.    XLS "');    Header (' cache-control:max-age=0 ');    $objWriter = Phpexcel_iofactory::createwriter ($objPHPExcel, ' Excel5 ');  $objWriter->save (' php://output '); } $array = Array (Array (1111, ' name ', ' Brand ', ' Product name ', ' http://www.bkjia.com '), Array (1111, ' name ', ' Brand ', ' Product name ', ' Http://www.bkjia '). com '), array (1111, ' name ', ' Brand ', ' Product name ', ' http://www.bkjia.com '), Array (1111, ' name ', ' Brand ', ' Product name ', ' http://www.bkjia.com '), AR  Ray (1111, ' name ', ' Brand ', ' Product name ', ' http://www.bkjia.com '),);  Write_xls ($array, Array (' Product ID ', ' Supplier name ', ' Brand ', ' Product name ', ' URL '), ' report '); ?>

2. Write content to xls file

<?php//Get Database data (mysqli preprocessing learning) $config = Array (' db_type ' = ' mysql ', ' db_host ' = ' localhost ', ' db_name ' =&G t; '  Test ', ' db_user ' = ' root ', ' db_pwd ' = ' root ', ' db_port ' = ' 3306 ',);    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 < php5.2.9 OO: $mysqli->connect_error die ("Connection failed, error code:". Mysqli_connect_errn O (). "    Error message: ". Mysqli_connect_error ());    }//Set the encoding of the connection database, do not forget to set the $mysqli->set_charset ("GBK");    The encoding of Chinese characters should be consistent with 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 is 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;  Get GBK code (same as database encoding)} $id = Getproductidbyname ('% inax bathroom inax sub-toilet); Var_dump ($id);? >

I hope this article is helpful to everyone's PHP programming

http://www.bkjia.com/PHPjc/878463.html www.bkjia.com true http://www.bkjia.com/PHPjc/878463.html techarticle PHP in the use of Phpexcel read and write Excel (xls) file method, this article describes the use of PHP phpexcel read and write Excel (xls) file method, very practical. Share it for everyone's reference. ...

