[PHP知识库]PHP针对EXCEL的方法处理

一、使用php代码导出表格方法PhpSpreadsheet库使用
由于PHPExcel已经不再维护，PhpSpreadsheet是PHPExcel的下一个版本。PhpSpreadsheet是一个用纯PHP编写的库，并引入了命名空间，PSR规范等。这里使用PhpSpreadsheet库来实现导出表格的方法。这里表格导出实现了 单元格合并，居中、单元格宽度等设置。

<?php
require_once __DIR__.'/vendor/autoload.php';
 
$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
   
$objActSheet = $spreadsheet->getActiveSheet();//获取当前活动sheet对象
 
//--往单元格写入数据，可以使用 setCellValue 或 setCellValueByColumnAndRow 方法，注意 参数格式不一样
 
//简单输出几个数据，真实环境中需要遍历循环输出的数据
$objActSheet->setCellValue('A1','单号')->mergeCells('A1:A2'); //合并单元格
$objActSheet->setCellValue('B1','出行人')->mergeCells('B1:D1');//合并单元格
$objActSheet->setCellValue('B2','姓名');
$objActSheet->setCellValue('C2','年龄');
$objActSheet->setCellValue('D2','性别');
$objActSheet->setCellValue('E1','时间')->mergeCells('E1:E2');//合并单元格
   
$objActSheet->setCellValue('A3','201512010001');
$objActSheet->setCellValue('B3','小张');
$objActSheet->setCellValue('C3','28');
$objActSheet->setCellValue('D3','男');
$objActSheet->setCellValue('E3','2015-12-01');
   
$objActSheet->setCellValue('A4','201512010002');
$objActSheet->setCellValue('B4','小妹');
$objActSheet->setCellValue('C4','24');
$objActSheet->setCellValue('D4','女');
$objActSheet->setCellValue('E4','2015-12-02');
 
$objActSheet->setCellValueByColumnAndRow(1,5,'201512010003');
$objActSheet->setCellValueByColumnAndRow(2,5,'张三');
$objActSheet->setCellValueByColumnAndRow(3,5,'26');
$objActSheet->setCellValueByColumnAndRow(4,5,'男');
$objActSheet->setCellValueByColumnAndRow(5,5,'2016-11-10');
 
$objStyleA1 = $objActSheet->getStyle('A1');
//设置单元格内容的数字格式，原始内容全部显示出来
$objStyleA1 ->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
//设置水平居中，垂直居中
$objStyleA1->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objStyleA1->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//从指定的单元格复制样式信息.
$objActSheet->duplicateStyle($objStyleA1, 'A1:E5');
//设置单元格的宽度
$objActSheet->getColumnDimension('A')->setWidth(25);
$objActSheet->getColumnDimension('A')->setWidth(10);
$objActSheet->getColumnDimension('A')->setWidth(10);
$objActSheet->getColumnDimension('A')->setWidth(10);
$objActSheet->getColumnDimension('A')->setWidth(16);
   
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="demo1.xlsx"');
header("Content-Transfer-Encoding: binary");
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$objWriter = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$objWriter->save('php://output');

二、使用php代码读取表格方法PHPExcel类使用

<?php
$excel_file='./demo.xlsx'; //表格文件
$ext=strtolower(pathinfo($excel_file,PATHINFO_EXTENSION));
if(!in_array($ext,array('xls','xlsx'))){
    echo '表格格式不正确，请先转换为xls或xlsx格式';
    die;
}
if(!file_exists($excel_file)){
    echo '表格文件不存在！';die;
}
 
require './vendor/phpoffice/phpexcel/Classes/PHPExcel.php';//根据实际PHPExcel路径引用
 
if($ext=='xls'){
    $reader = \PHPExcel_IOFactory::createReader('Excel5'); //设置以Excel5格式(Excel97-2003工作簿)
}else{
    $reader = new \PHPExcel_Reader_Excel2007();
}
 
$PHPExcel = $reader->load($excel_file); // 载入excel文件
 
$sheet = $PHPExcel->getSheet(0); // 读取第一個工作表
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumm = $sheet->getHighestColumn(); // 取得总列数，类似 B、AA这种格式
$colsArr=array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ','BA','BB','BC','BD','BE','BF','BG','BH','BI','BJ','BK','BL','BM','BN','BO','BP','BQ','BR','BS','BT','BU','BV','BW','BX','BY','BZ','CA','CB','CC','CD','CE','CF','CG','CH','CI','CJ','CK','CL','CM','CN','CO','CP','CQ','CR','CS','CT','CU','CV','CW','CX','CY','CZ','DA','DB','DC','DD','DE','DF','DG','DH','DI','DJ','DK','DL','DM','DN','DO','DP','DQ','DR','DS','DT','DU','DV','DW','DX','DY','DZ','EA','EB','EC','ED','EE','EF','EG','EH','EI','EJ','EK','EL','EM','EN','EO','EP','EQ','ER','ES','ET','EU','EV','EW','EX','EY','EZ','FA','FB','FC','FD','FE','FF','FG','FH','FI','FJ','FK','FL','FM','FN','FO','FP','FQ','FR','FS','FT','FU','FV','FW','FX','FY','FZ','GA','GB','GC','GD','GE','GF','GG','GH','GI','GJ','GK','GL','GM','GN','GO','GP','GQ','GR','GS','GT','GU','GV','GW','GX','GY','GZ','HA','HB','HC','HD','HE','HF','HG','HH','HI','HJ','HK','HL','HM','HN','HO','HP','HQ','HR','HS','HT','HU','HV','HW','HX','HY','HZ','IA','IB','IC','ID','IE','IF','IG','IH','II','IJ','IK','IL','IM','IN','IO','IP','IQ','IR','IS','IT','IU','IV');
$highestColumm_num=array_search($highestColumm,$colsArr);//将列数字符转换为对应的数字
 
$dataset=array();
//循环读取每个单元格的数据
for ($row = 1; $row <= $highestRow; $row++){//行数是以第1行开始
    for ($j=0; $j <= $highestColumm_num; $j++) {//列数是以A列开始
        $curval=@trim($sheet->getCell($colsArr[$j].$row)->getValue());
        $dataset[$row][$j]=$curval;
    }
}
 
var_dump($dataset);

三、读取表格方法（二）PhpSpreadsheet库使用

由于PHPExcel已经不再维护，PhpSpreadsheet是PHPExcel的下一个版本。PhpSpreadsheet是一个用纯PHP编写的库，并引入了命名空间，PSR规范等。这里简单介绍下PhpSpreadsheet库的表格读取数据功能。
<?php
$excel_file='./demo.xlsx'; //表格文件
$ext=strtolower(pathinfo($excel_file,PATHINFO_EXTENSION));
if(!in_array($ext,array('xls','xlsx'))){
    echo '表格格式不正确，请先转换为xls或xlsx格式';
    die;
}
if(!file_exists($excel_file)){
    echo '表格文件不存在！';die;
}
 
require_once __DIR__ . '/vendor/autoload.php';
 
$spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($excel_file);
 
$sheet = $spreadsheet->getSheet(0); // 读取第一個工作表
$highestRow = $sheet->getHighestRow(); // 取得总行数
$highestColumm = $sheet->getHighestColumn(); // 取得总列数，类似 B、AA这种格式
 
$highestColumm_num=\PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumm);//将列数字符转换为对应的数字
 
$dataset=array();
//循环读取每个单元格的数据
for ($row = 1; $row <= $highestRow; $row++){//行数是以第1行开始
    for ($j=0; $j <= $highestColumm_num; $j++) {//列数是以A列开始
        $curval=@trim($sheet->getCellByColumnAndRow($j,$row)->getValue());
        //$curval=$sheet->getCellByColumnAndRow($j,$row)->getCalculatedValue();
        //获取公式计算之后的值
        $dataset[$row][$j]=$curval;
    }
}
 
var_dump($dataset);

四、导出表格方法（一）csv方式

在实际开发中，经常遇到帮数据导出表格操作。今天我们来讲下导出csv格式方式，这种是最快捷的、占用资源少的方法。这里简单介绍下csv格式，每一行数据间使用 换行符分割，每一列值之间通过逗号分割。这里要注意下 值里面可能含有特殊字符，如果不进行相关转义操作，会造成解析错误。下面也会提供解决方法
function quoteCsvField($str,$force=false){
    if($force || strpos($str,',')!==false || strpos($str,'"') !==false || strpos($str,"\n") !==false || strpos($str,"\n") !==false){
        $str='"'.str_replace('"','""',$str).'"';
    }
    return $str;
}
//--数据，这里简单表示下
$result=array(
    array('brands'=>'游乐园','dename'=>'旅游部','appdate'=>'2020-05','remark'=>"游乐园项目\n详情：3月签约,4月回款1000"),
    array('brands'=>'房产中介','dename'=>'房产','appdate'=>'2020-04','remark'=>"已回款"),
);
  
header("Content-type:text/csv");
$filename='上线广告';
header("Content-Disposition:attachment;filename=".$filename.".csv");
header('Cache-Control:must-revalidate,post-check=0,pre-check=0');
header('Expires:0');
header('Pragma:public');
  
$html='品牌,部门,时间,备注说明'."\n";
foreach($result as $k=>$v) {
    $html.=$v['brands'].','.$v['dename'].','.$v['appdate'].','.quoteCsvField($v['remark'])."\n";
}
echo $html;
die;

**五、使用php代码导出表格方法（二）PHPExcel类使用

之前使用csv方式来导出xls文档。现在需求改了，要导出待有合并单元格形式展示的文档。这次使用 phpexcel来生成，并设置单元格的格式，如宽度、居中、数字格式等。

基本步骤为：加载PHPExcel.php文件，并实例化PHPExcel类，写入数据并设置单元格的格式。
include './Classes/PHPExcel.class.php'; //引入PHPExcel类所在的文件，根据具体路径修改
$phpexcel=new PHPExcel();
$phpexcel->setActiveSheetIndex(0);//设置活动sheet
  
$objActSheet = $phpexcel->getActiveSheet();//获取当前活动sheet对象
  
//简单输出几个数据，真实环境中需要遍历循环输出的数据
$objActSheet->setCellValue('A1','单号')->mergeCells('A1:A2'); //合并单元格
$objActSheet->setCellValue('B1','出行人')->mergeCells('B1:D1');//合并单元格
$objActSheet->setCellValue('B2','姓名');
$objActSheet->setCellValue('C2','年龄');
$objActSheet->setCellValue('D2','性别');
$objActSheet->setCellValue('E1','时间')->mergeCells('E1:E2');//合并单元格
  
$objActSheet->setCellValue('A3','201512010001');
$objActSheet->setCellValue('B3','小张');
$objActSheet->setCellValue('C3','28');
$objActSheet->setCellValue('D3','男');
$objActSheet->setCellValue('E3','2015-12-01');
  
$objActSheet->setCellValue('A4','201512010002');
$objActSheet->setCellValue('B4','小妹');
$objActSheet->setCellValue('C4','24');
$objActSheet->setCellValue('D4','女');
$objActSheet->setCellValue('E4','2015-12-02');
  
$objStyleA1 = $objActSheet->getStyle('A1');
//设置单元格内容的数字格式，原始内容全部显示出来
$objStyleA1 ->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER);
//设置水平居中，垂直居中
$objStyleA1->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objStyleA1->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//从指定的单元格复制样式信息.
$objActSheet->duplicateStyle($objStyleA1, 'A1:E4');
//设置单元格的宽度
$objActSheet->getColumnDimension('A')->setWidth(25);
$objActSheet->getColumnDimension('A')->setWidth(10);
$objActSheet->getColumnDimension('A')->setWidth(10);
$objActSheet->getColumnDimension('A')->setWidth(10);
$objActSheet->getColumnDimension('A')->setWidth(16);
  
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition:inline;filename="demo.xls"');
header("Content-Transfer-Encoding: binary");
header("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
header("Last-Modified: " . gmdate("D, d M Y H:i:s") . " GMT");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Pragma: no-cache");
$objWriter = PHPExcel_IOFactory::createWriter($phpexcel, 'Excel5');
$objWriter->save('php://output');