一、安装
composer require phpoffice/phpexcel
二、引入
use PHPExcel_IOFactory;
use PHPExcel;
三、使用
导出示例:
public function export($data){
//$data参数是要循环输出的数据数组
$filename = iconv('utf-8', 'gb2312', '导出数据测试');//设置导出文件名,转码,否则可能出现文件名乱码
$excel = new PHPExcel();//创建phpexcel类的实例
$excel->setactivesheetindex(0);//设置当前活动的sheet,0表示第一个
$excel->getActiveSheet()->setTitle('201904考核排名');//设置当前活动的sheet名,默认就是第一个sheet
$excel->getactivesheet()->setcellvalue('A1', '工号');//设置第一列名
$excel->getactivesheet()->setcellvalue('B1', '姓名');//设置第二列名
$excel->getactivesheet()->setcellvalue('C1', '得分');//设置第三列名
//循环数组,设置第一列、第二列、第三列的数据
foreach($data as $key => $value)
{
$key = $key+2;
$excel->getactivesheet()->setcellvalue('A'.$key, $value['number']);
$excel->getactivesheet()->setcellvalue('B'.$key, $value['name']);
$excel->getactivesheet()->setcellvalue('C'.$key, $value['score']);
}
header('pragma:public');
header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$filename.'.xls"');
header("Content-Disposition:attachment;filename=$filename.xls");
$objWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
$objWriter->save('php://output');
exit;
}
导入示例:
public function import($file){
//$file是上传到服务器后的excel文件路径
//判断excel文件格式
$type = pathinfo($file);
$type = strtolower($type["extension"]);
if ($type=='xlsx') {
$type='Excel2007';
}elseif($type=='xls') {
$type = 'Excel5';
}
$objReader =\PHPExcel_IOFactory::createReader($type);
$objPHPExcel = $objReader->load($file, $encode = 'utf-8');
$excel_array = $objPHPExcel->getsheet(0)->toArray();
$res = [];
array_shift($excel_array);//去掉第一行,因为我上传的excel文件第一行为列名
if(!is_array($excel_array) || empty($excel_array)){
return $res;
}else{
//循环读取数据
foreach ($excel_array as $key => $value) {
$res[$key]['number'] = (string)$value[0];
$res[$key]['name'] = (string)$value[1];
$res[$key]['score'] = (string)$value[2];
}
}
return $res;
}