Phpexcel manipulating XLS files

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

<?PHP//Read XLS        Header("Content-type:text/html;charset=utf-8"); include' Classes/phpexcel.php '; include' Classes/phpexcel/iofactory.php '; functionReadxls ($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 number starting from 0) If reading multiple can do a loop 0,1,2,3 .... Get the two-dimensional array, where each decimal group is a row of Excel table content containing data for each column of this row        return $content; }        //$type = ' Excel2007 ';//Set the type of Excel to resolve Excel5 (2003 or below) or Excel2007    $type= ' Excel5 '; $content= Readxls (' Data.xls ',$type); Echo' <pre> '; Var_dump($content); Echo' </pre> '; ?>

2. Write content to the XLS file

<?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 filename//$data and $title must be utf-8 code, otherwise write false value    functionWrite_xls ($data=Array(),$title=Array(),$filename= ' Report '){        $objPHPExcel=NewPhpexcel (); //Set document properties, set the Chinese will produce garbled, to perfect ...//$objPHPExcel->getproperties ()->setcreator ("Yun Shu")// ->setlastmodifiedby ("Yun Shu")//->settitle ("Product URL Export")// Setsubject ("Product URL Export")//->setdescription ("Product URL Export")//->se Tkeywords ("Product URL export");        $objPHPExcel->setactivesheetindex (0); $cols= ' ABCDEFGHIJKLMNOPQRSTUVWXYZ '; //Set 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 '                    )                )            )        );  for($i=0,$length=Count($data);$i<$length;$i++) {            $j= 0; foreach($data[$i] as $v) {//here, with foreach, supports associative arrays and numeric indexed arrays                $objPHPExcel->getactivesheet ()->setcellvalue ($cols{$j}. ($i+2),$v); $j++; }        }        //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 '); ?>

3, the operation of the database to get to write the content to use mysqli preprocessing to obtain the content of an example:

<?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 ',    ); functionGetproductidbyname ($name) {        Global $config; $id=false; $mysqli=NewMysqli ($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; }        $id= Getproductidbyname ('% inax bathroom inax split toilet% '); Var_dump($id);?>

Ok...

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