使用PHPExcel操作.xls文件

读取中文的xls、csv文件会有问题，网上找了下资料，发现PHPExcel类库好用，官网地址： http://phpexcel.codeplex测试数据/  

1、读取xls文件内容

<? php //向xls文件写入内容 error_reporting ( E_ALL ); ini_set ( 'display_errors' , TRUE ); include 'Classes/PHPExcel.php' ; include 'Classes/PHPExcel/IOFactory.php' ; //$data:xls文件内容正文 //$title:xls文件内容标题 //$filename:导出的文件名 //$data和$title必须为utf-8码，否则会写入FALSE值 function write_xls ( $data = array (), $title = array (), $filename = 'report' ){ $objPHPExcel = new PHPExcel (); //设置文档属性，设置中文会产生乱码，待完善... // $objPHPExcel->getProperties()->setCreator("云舒") // ->setLastModifiedBy("云舒") // ->setTitle("产品URL导出") // ->setSubject("产品URL导出") // ->setDescription("产品URL导出") // ->setKeywords("产品URL导出"); $objPHPExcel -> setActiveSheetIndex ( 0 ); $cols = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' ; //设置标题 for ( $i = 0 , $length = count ( $title ); $i < $length ; $i ++) { //echo $cols{$i}.'1'; $objPHPExcel -> getActiveSheet ()-> setCellValue ( $cols { $i }. '1' , $title [ $i ]); } //设置标题样式 $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 ) { //这里用foreach,支持关联数组和数字索引数组 $j = 0 ; foreach ( $d as $v ) { //这里用foreach,支持关联数组和数字索引数组 $objPHPExcel -> getActiveSheet ()-> setCellValue ( $cols { $j }.( $i + 2 ), $v ); $j ++; } 　　　　　　　$i ++; } // 生成2003excel格式的xls文件 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 , '名称' , '品牌' , '商品名' , 'http://HdhCmsTestbaidu测试数据' ), array ( 1111 , '名称' , '品牌' , '商品名' , 'http://HdhCmsTestbaidu测试数据' ), array ( 1111 , '名称' , '品牌' , '商品名' , 'http://HdhCmsTestbaidu测试数据' ), array ( 1111 , '名称' , '品牌' , '商品名' , 'http://HdhCmsTestbaidu测试数据' ), array ( 1111 , '名称' , '品牌' , '商品名' , 'http://HdhCmsTestbaidu测试数据' ), ); write_xls ( $array , array ( '商品id' , '供应商名称' , '品牌' , '商品名' , 'URL' ), 'report' ); ?>

2、向xls文件写内容

<? php //获取数据库数据(mysqli预处理学习) $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 ()) { //兼容 < php5.2.9 OO way:$mysqli->connect_error die ( "连接失败，错误码：" . mysqli_connect_errno (). "错误信息：" . mysqli_connect_error ()); } //设置连接数据库的编码，不要忘了设置 $mysqli -> set_charset ( "gbk" ); //中文字符的编码要与数据库一致，若没设置，结果为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 ; //得到的是gbk码（同数据库编码） } $id = getProductIdByName ( '%伊奈卫浴伊奈分体座便器%' ); var_dump ( $id ); ?>

 

 

查看更多关于使用PHPExcel操作.xls文件的详细内容...