Phpexcel manipulating XLS files

Source: Internet
Author: User

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/ 

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.