Using Phpexcel to manipulate Excel (xls) files in PHP

Read the Chinese XLS, CSV file will be a problem, the Internet to find the next information, found Phpexcel class library useful, official address: http://phpexcel.codeplex.com/

1. read xls file contents

The code is as follows

Copy Code

<?php Write content to an 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 file name The $data and $title must be utf-8 codes, or false values will be written function Write_xls ($data =array (), $title =array (), $filename = ' report ') { $objPHPExcel = new Phpexcel (); Set document properties, set Chinese will be garbled, to perfect ... $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 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 heading styles $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 ( ' 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 XLS files in 2003excel format 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.baidu.com '), Array (1111, ' name ', ' Brand ', ' Product name ', ' http://www.baidu.com '), Array (1111, ' name ', ' Brand ', ' Product name ', ' http://www.baidu.com '), Array (1111, ' name ', ' Brand ', ' Product name ', ' http://www.baidu.com '), Array (1111, ' name ', ' Brand ', ' Product name ', ' http://www.baidu.com '), ); Write_xls ($array, Array (' Product ID ', ' Supplier name ', ' Brand ', ' Product name ', ' URL '), ' report '); ?>

2. Write content to the XLS file

The code is as follows

Copy Code

<?php Get Database data (mysqli preprocessing learning) $config = Array ( ' Db_type ' = ' mysql ', ' db_host ' = ' localhost ', ' db_name ' = ' 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_errno (). " Error message: ". Mysqli_connect_error ()); } Set the encoding of the connection database, and do not forget to set the $mysqli->set_charset ("GBK"); The encoding of Chinese characters should be the same as 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 the GBK code (same as the database code) } $id = getproductidbyname ('% inax bathroom inax split toilet% '); Var_dump ($id); ?>

Ok...

Related content

• 2014.05.28PHPEXCEL Import Excel Table build array
• 2014.04.16thinkphp call Phpexcel export Excel file to local example
• 2014.04.11thinkphp using Phpexcel for importing Excel
• Import and export Excel files in 2014.04.09ThinkPHP with Phpexcel
• 2014.03.19PHP exporting Excel via Phpexcel class import
• Export Excel samples using Phpexcel in 2013.11.24PHP
• 2013.08.13PHPExcel Generating and reading an Excel file instance program
• Use Phpexcel to export Excel instance code in 2013.08.13YII
• 2013.05.06PHPExcel read Excel and import MySQL database code
• 2013.05.03php Excel Operation class Phpexcel usage Introduction

Using Phpexcel to manipulate Excel (xls) files in PHP