PhpSpreadsheet 是使用PHP语言编写的用户操作excel的类库,提供了一系列丰富的接口,可很方便的对文档属性、单元格进行操作,包括设置样式、图片、日期、函数等等。
PhpSpreadsheet用方法也很简单,示例如下:
一、使用PhpSpreadsheet进行excel操作,首先需要安装
composer require phpoffice/phpspreadsheet
二、引入PhpSpreadsheet类
use \PhpOffice\PhpSpreadsheet\Spreadsheet;use \PhpOffice\PhpSpreadsheet\IOFactory;use \PhpOffice\PhpSpreadsheet\Cell\Coordinate;use PhpOffice\PhpSpreadsheet\Style\Border;use PhpOffice\PhpSpreadsheet\Style\Alignment;
三、实例化PhpSpreadsheet
//实例化一个新的excel文档$newExcel = new Spreadsheet();//获取当前操作sheet的对象$objSheet = $newExcel->getActiveSheet();$date = date('Ymd',time());$name = '订单信息表';$objSheet->setTitle($name);//设置当前sheet的标题//设置表头$objSheet->setCellValue('A1', '单号')->setCellValue('B1', '时间')->setCellValue('C1', '联系人')->setCellValue('D1', '手机号')->setCellValue('E1', '支付金额');//设置表头样式$font = ['font' => ['bold' => true,'size' => 13,],];$objSheet->getStyle('A1:E1')->applyFromArray($font);
四、设置表格数据
$info = [数据数组];$dataCount = count($info);$k = 1;if($dataCount == 0){exit;}else{for ($i=0;$i<$dataCount;$i++){$k = $k + 1;$objSheet->setCellValue('A' . $k, $info[$i]['order_type'])->setCellValue('B' . $k, $info[$i]['addtime'])->setCellValue('C' . $k, $info[$i]['name'])->setCellValue('D' . $k, $info[$i]['faren'])->setCellValue('E' . $k, " ".$info[$i]['shenfenzheng']);$objSheet->getStyle('A'.$k)->applyFromArray($styleArray);}$k = $k + 1; $extends = $lists['extend'];$objSheet->setCellValue('A' . $k, "这里是个合并单元格的示例");//合并单元格$objSheet->mergeCells('A' . $k.':E'.$k); //最后一行样式$objSheet->getStyle('A'.$k.':'.'A'.$k)->applyFromArray(['font' => ['bold' => true,'size' => 13,'color'=>['argb' => '000000']],]);}
五、设置表格样式
//默认行高$objSheet->getDefaultRowDimension()->setRowHeight(22);//设置宽度$objSheet->getColumnDimension('A')->setWidth(15); $objSheet->getColumnDimension('B')->setWidth(20); $objSheet->getColumnDimension('C')->setWidth(40); $objSheet->getColumnDimension('D')->setWidth(15); $objSheet->getColumnDimension('E')->setWidth(25);
六、执行保存或下载
ob_end_clean();ob_start();//设置文件扩展名, $format只能为 Xlsx 或 Xls$format='Xlsx';if ($format == 'Xlsx') {header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');} elseif ($format == 'Xls') {header('Content-Type: application/vnd.ms-excel');}header("Content-Disposition: attachment;filename=" . $name . '.' . strtolower($format));header('Cache-Control: max-age=0');$objWriter = IOFactory::createWriter($newExcel, $format);$objWriter->save('php://output');//通过php保存在服务器的时候需要用到//$objWriter->save($dir.'/demo.xlsx');exit;
以上就是Thinkphp6使用PhpSpreadsheet保存为Excel文件的使用方法 。