Home >php教程 >php手册 >php中使用PHPExcel读写excel(xls)文件的方法,

php中使用PHPExcel读写excel(xls)文件的方法,

WBOY
WBOYOriginal
2016-06-13 09:25:35825browse

php中使用PHPExcel读写excel(xls)文件的方法,

本文实例讲述了PHP中使用PHPExcel读写excel(xls)文件的方法,非常实用。分享给大家供大家参考之用。具体方法如下:

很多PHP类库在读取中文的xls、csv文件时会有问题,网上找了下资料,发现PHPExcel类库好用,官网地址为:http://phpexcel.codeplex.com/。现将PHPExcel读写Excel的方法分别叙述如下:

1、读取xls文件内容

<&#63;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();
    //设置文档属性,设置中文会产生乱码,需要转换成utf-8格式!!
    // $objPHPExcel->getProperties()->setCreator("云舒")
               // ->setLastModifiedBy("云舒")
               // ->setTitle("产品URL导出")
               // ->setSubject("产品URL导出")
               // ->setDescription("产品URL导出")
               // ->setKeywords("产品URL导出");
    $objPHPExcel->setActiveSheetIndex(0);
    
    $cols = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
    //设置www.jb51.net标题
    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://www.bkjia.com'),
    array(1111,'名称','品牌','商品名','http://www.bkjia.com'),
    array(1111,'名称','品牌','商品名','http://www.bkjia.com'),
    array(1111,'名称','品牌','商品名','http://www.bkjia.com'),
    array(1111,'名称','品牌','商品名','http://www.bkjia.com'),
  );
  write_xls($array,array('商品id','供应商名称','品牌','商品名','URL'),'report');
  
&#63;>

2、向xls文件写内容

<&#63;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 &#63;")) {
      $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);
&#63;>

希望本文所述对大家的PHP程序设计有所帮助

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn