PHP使用PhpSpreadsheet 导出excel文件

羊舌子瑜
2023-12-01

        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文件的使用方法 。

 类似资料: