phpexcel读写xls文件实现程序

★安装

 代码如下

1.安装composer 

<?php
include_once('PHPExcel.php');
//read excel file;
$PHPExcel = new PHPExcel();    
$PHPReader = new PHPExcel_Reader_Excel5();
$PHPExcel = $PHPReader->load('/home/yuanjianjun/taobao_cat.xls');
$currentSheet = $PHPExcel->getSheet(0);
$allColumn = $currentSheet->getHighestColumn();
$allRow = $currentSheet->getHighestRow();
for($currentRow = 1; $currentRow<=$allRow; $currentRow++){
   for($currentColumn='A'; $currentColumn<=$allColumn; $currentColumn++){  
    $address = $currentColumn.$currentRow;  
    echo $currentSheet->getCell($address)->getValue()."t";  
   }
   echo "n";
}

  安装方法: 

//write excel file
$objExcel = new PHPExcel();
$objWriter = new PHPExcel_Writer_Excel5($objExcel);
$objProps = $objExcel->getProperties();  
$objProps->setCreator("yuan");
$objProps->setLastModifiedBy("yuan");
$objProps->setTitle("excel test");  
$objProps->setSubject("my excel test");
$objProps->setDescription("hello world.");  
$objProps->setKeywords("PHPExcel");
$objProps->setCategory("EXCEL");
$objExcel->setActiveSheetIndex(0);
$objActSheet = $objExcel->getActiveSheet();
$objActSheet->setTitle('TEST1');
$objActSheet->setCellValue('A1', '字符串内容');
$objActSheet->setCellValue('A2', 26);
$objActSheet->setCellValue('A3', true);
$objActSheet->setCellValue('A4', '=A2+A2');
$objWriter->save('/home/yuanjianjun/helloworld.xls');  

  cmd  =》 composer -v  检查是否安装成功

//copy excel format
$objReader = PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel = $objReader->load('/home/yuanjianjun/20100301.xls');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->_phpExcel->setActiveSheetIndex(0);
$objWriter->_phpExcel->getActiveSheet()->setCellValue('A1', 'FESDF');
$objWriter->_phpExcel->getActiveSheet()->setCellValue('B1', 'S');
$objWriter->_phpExcel->getActiveSheet()->setCellValue('C1', 'FEFSD');
$objWriter->_phpExcel->getActiveSheet()->setCellValue('D1', 'SDFD');
$objWriter->_phpExcel->getActiveSheet()->setCellValue('E1', '淘宝CPS');
$objWriter->save('/home/yuanjianjun/copy.xls');

2.cmd =》切换到项目目录,执行composer require phpoffice/phpexcel。之后等待安装完成,会自动在vendor目录生成文件

★应用

图片 1

1.导出 

<a href="{:url('***/***/out')}" class="btn btn-primary">导出</a>

图片 2

private function getExcel($fileName,$headArr,$data){
  //对数据进行检验
  if(empty($data) || !is_array($data)){
    die("data must be a array");
  }
  //检查文件名
  if(empty($fileName)){
    exit;
  }

  $date = date("Y_m_d",time());
  $fileName .= "_{$date}.xlsx";
  $objPHPExcel = new PHPExcel();
  $objProps = $objPHPExcel->getProperties();

  foreach($headArr as $k=>$v){
    if($k<26){
      $colum[$k]=chr(65+$k);
      $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum[$k].'1', $v);
    }else if($k<702){
      $colum[$k]=chr(64+($k/26)).chr(65+$k%26);
      $objPHPExcel->setActiveSheetIndex(0) ->setCellValue($colum[$k].'1', $v);
    }
  }

  $column=2;
  $objActSheet = $objPHPExcel->getActiveSheet();
  foreach($data as $key => $rows){ //行写入
  $n = 0;
    foreach($rows as $keyName=>$value){// 列写入
      $j = $colum[$n];
      $objActSheet->setCellValueExplicit($j.$column,$value,PHPExcel_Cell_DataType::TYPE_STRING);
      $n++;
    }
    $column++;
  }
  $fileName = iconv("utf-8", "gb2312", $fileName);
  //重命名表
  // $objPHPExcel->getActiveSheet()->setTitle('test');
  //设置活动单指数到第一个表,所以Excel打开这是第一个表
  $objPHPExcel->setActiveSheetIndex(0);
  ob_end_clean(); //清空缓存
  header('Content-Type: application/vnd.ms-excel');
  header("Content-Disposition: attachment;filename="$fileName"");
  header('Cache-Control: max-age=0');
  $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
  //文件通过浏览器下载
  $objWriter->save('php://output');exit;
}

2.导入

  A.上传文件,这步略;

  B. 获取文件地址和名字,即是 $filename = "./1.xlsx";  

  C.执行 下面方法即可,$arr为从excel中获取到的数据

图片 3图片 4

 

图片 5

 

private function excelToArray($fliepath,$table){
  $phpexce = new PHPExcel();
  $phpReader = new PHPExcel_Reader_Excel2007();
  if(!$phpReader->canRead($fliepath)){
    $phpReader = new PHPExcel_Reader_Excel5();
    if(!$phpReader->canRead($fliepath)){
      echo 'no excel';
      return;
    }
  }
  $phpexcel = $phpReader->load($fliepath);
  $currentSheet = $phpexcel->getSheet(0); //读取excel文件中的第一个工作表
  $allColumn = $currentSheet->getHighestColumn(); //取得最大的列号
  $allRow = $currentSheet->getHighestRow(); //取得一共有多少行  
  $erp_orders_id = array(); //声明数组
  $val=[];
  for($currentRow = 1;$currentRow <= $allRow;$currentRow++){
    /**从第A列开始输出*/
    for($currentColumn= 0;$currentColumn< (ord($allColumn)-64); $currentColumn++){
      if($currentColumn<26){
        $colum=chr(65+$currentColumn);
      }else if($currentColumn<702){
        $colum=chr(64+($currentColumn/26)).chr(65+$currentColumn%26);
      }
      $v = $currentSheet->getCell($colum.$currentRow)->getValue();/**ord()将字符转为十进制数*/
      $val[$table[$currentColumn]] = rtrim(ltrim((String)$v,'="'),'"');
      /**如果输出汉字有乱码,则需将输出内容用iconv函数进行编码转换,如下将gb2312编码转为utf-8编码输出*/
      //echo iconv('utf-8','gb2312', $val)."t";
    }
    if($val!=''){
      $erp_orders_id[$currentRow] = $val;
    }
  }
  return $erp_orders_id;
}

★错误总结:

1.ERR_INVALID_RESPONSE  这个是由于PHP版本过高导致的。在1.8.1版本中已经解决

当前解决方法: 删除PHPExcel / Classes / PHPExcel / Calculation / Functions.php中 581行左右删除break即可.

图片 6

 

  1. 可能导出的数据混乱

这个可能是缓存导致的,清空就好 代码中添加:ob_end_clean();

本文由365bet体育在线官网发布于关于计算机,转载请注明出处:phpexcel读写xls文件实现程序

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。