PHP使用PhpSpreadsheet库的操作Excel表格

一、PhpSpreadsheet 介绍

PhpSpreadsheet是一个用纯PHP编写的库，提供了一组类，使您可以读取和写入不同的电子表格文件格式
PhpSpreadsheet提供了丰富的API接口，可以设置诸多单元格以及文档属性，包括样式、图片、日期、函数等等诸多应用，总之你想要什么样的Excel表格，PhpSpreadsheet都能做到

使用 PhpSpreadsheet 开发的PHP要求 7.1或更高版本
PhpSpreadsheet 支持链式操作

PhpSpreadsheet 官方网址：https://phpspreadsheet.readthedocs.io

PhpSpreadsheet 安装

composer require phpoffice/phpspreadsheet

二、基础使用

# 载入composer自动加载文件require 'vendor/autoload.php';# 给类文件的命名空间起个别名use PhpOffice\PhpSpreadsheet\Spreadsheet;# 实例化 Spreadsheet 对象$spreadsheet = new Spreadsheet();# 获取活动工作薄$sheet = $spreadsheet->getActiveSheet();# 获取单元格$cellA = $sheet->getCell('A1');# 设置单元格值$cellA->setValue('欧阳克');# 获取单元格$cellB = $sheet->getCellByColumnAndRow(1,2);# 设置单元格值$cellB->setValue('黄蓉');# 获取设置单元格，链式操作$sheet->getCell('A3')->setValue('郭靖');$sheet->getCellByColumnAndRow(1,4)->setValue('杨康');# Xlsx类 将电子表格保存到文件use PhpOffice\PhpSpreadsheet\Writer\Xlsx;$writer = new Xlsx($spreadsheet);$writer->save('1.xlsx');

强化表格操作

# 载入composer自动加载文件require 'vendor/autoload.php';# 给类文件的命名空间起个别名use PhpOffice\PhpSpreadsheet\Spreadsheet;# 实例化 Spreadsheet 对象$spreadsheet = new Spreadsheet();# 获取活动工作薄$sheet = $spreadsheet->getActiveSheet();//setCellValue(单元格位置,单元格的值)$sheet->setCellValue('A1','ID');$sheet->setCellValue('B1','姓名');$sheet->setCellValue('C1','年龄');$sheet->setCellValue('D1','身高');$sheet->setCellValue('E1','生日');$sheet->setCellValue('F1','网址');$sheet->setCellValue('G1','语文分数');$sheet->setCellValue('H1','数学分数');$sheet->setCellValue('I1','总分');//setCellValueByColumnAndRow(列位置,行位置,单元格的值)$sheet->setCellValueByColumnAndRow(1, 2, 1);$sheet->setCellValueByColumnAndRow(2, 2, '欧阳克');$sheet->setCellValueByColumnAndRow(3, 2, '18岁');$sheet->setCellValueByColumnAndRow(4, 2, '188cm');$sheet->setCellValueByColumnAndRow(5, 2, '188cm');$sheet->setCellValue('E2','2019-10-10 10:10:10');$sheet->setCellValue('G2',90);$sheet->setCellValue('H2',92);$sheet->setCellValue('I2','=SUM(G2:H2)'); //  总数//公式//平均数 =AVERAGE(A1:G1)//最小数 MIN(A1:G1//最大数 =MAX(A1:G1)    使用转义字符  \=MAX(A1:G1)$sheet->setCellValueByColumnAndRow(1, 3, 2);$sheet->setCellValueByColumnAndRow(2, 3, '黄蓉');$sheet->setCellValueByColumnAndRow(3, 3, '17岁');$sheet->setCellValueByColumnAndRow(4, 3, '165cm');$sheet->setCellValue('E3','2019-10-10 10:10:10');$sheet->setCellValue('G3',90);$sheet->setCellValue('H3',92);$sheet->setCellValue('I3','=SUM(G2:H2)'); //  总数//getStyle 获取单元格样式//getFont 获取单元格文字样式//setBold 设置文字粗细//setName 设置文字字体//setSize 设置文字大小//getColor() 获取坐标颜色//setRGB() 设置字体颜色//getRGB() 获取字体颜色//setARGB() 设置字体颜色//getARGB() 获取字体颜色$sheet->getStyle('B2')->getFont()->setBold(true)->setName('宋体')->setSize(20)->getColor()->setRGB('#AEEEEE');echo $sheet->getStyle('B2')->getFont()->getColor()->getRGB(),PHP_EOL;  // 000000$sheet->getStyle('B3')->getFont()->getColor()->setARGB('FFFF0000');echo $sheet->getStyle('B3')->getFont()->getColor()->getARGB(); // FFFF0000//getNumberFormat 获取格式//setFormatCode 设置格式$sheet->getStyle('E3')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_DATE_YYYYMMDD);//setWrapText 设置文本里的\n符合为：换行$sheet->setCellValue('A1',"欧阳克\n黄蓉");$sheet->getStyle('A1')->getAlignment()->setWrapText(true);//getHyperlink 获取单元格链接//setUrl 设置单元格链接$sheet->setCellValue('F2','www.php.cn');$sheet->getCell('F2')->getHyperlink()->setUrl('http://www.php.cn');//fromArray 从数组中的值填充工作表//参数1：数据(数组)//参数2：去除某个值//参数3：从哪个位置开始$sheet->fromArray(    [        [4,'小名','18岁','188cm'],        [5,'小吴','18岁','188cm'],    ],    4,    'A3');//合并单元格$sheet->mergeCells('I4:I5');// 、拆分单元格$sheet->mergeCells('I6:I8');$sheet->unmergeCells('I6:I8');//getColumnDimension 获取一列//getWidth 获取一列的宽度//setWidth 设置一列的宽度//setAutoSize 设置一列的宽度自动调整//getDefaultColumnDimension 获取一列的默认值echo $sheet->getColumnDimension('A')->getWidth().PHP_EOL;$sheet->getColumnDimension('A')->setWidth(30);$sheet->getColumnDimension('B')->setAutoSize(true);$sheet->getDefaultColumnDimension()->setWidth(20);//getRowDimension 获取一行//getRowHeight 获取一行的高度//setRowHeight 设置一行的高度echo $sheet->getRowDimension(1)->getRowHeight().PHP_EOL;$sheet->getRowDimension(1)->setRowHeight(100);$sheet->getDefaultRowDimension()->setRowHeight(1);//getHighestColumn 获取总列数//getHighestRow 获取总行数echo $sheet->getHighestColumn().PHP_EOL;echo $sheet->getHighestRow().PHP_EOL;//单元格样式  applyFromArray$styleArray = [    // use PhpOffice\PhpSpreadsheet\Style\Alignment; 文件里常量，就是参数    // Alignment::HORIZONTAL_CENTER 水平居中    // Alignment::VERTICAL_CENTER垂直居中    'alignment' => [        // 'horizontal' => Alignment::HORIZONTAL_CENTER, //水平居中        // 'vertical' => Alignment::VERTICAL_CENTER, //垂直居中        'horizontal' => 'center', //水平居中        'vertical' => 'center', //垂直居中    ],    // use PhpOffice\PhpSpreadsheet\Style\Border; 文件里常量，就是参数    // Border::BORDER_THICK 边框样式    'borders' => [        'outline' => [            // 'borderStyle' => '\PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THICK',            'borderStyle' => 'thick',            'color' => ['argb' => 'FFFF0000'],        ],    ],    'font' => [        'name' => '黑体',        'bold' => true,        'size' => 22    ]];$sheet->getStyle('B1')->applyFromArray($styleArray);$sheet->setTitle('测试'); // 设置工作簿# Xlsx类 将电子表格保存到文件use PhpOffice\PhpSpreadsheet\Writer\Xlsx;$writer = new Xlsx($spreadsheet);$writer->save('1.xlsx');

读取表格

 <?php# 载入composer自动加载文件require 'vendor/autoload.php';# 创建读操作$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');# 打开文件、载入excel表格$spreadsheet = $reader->load('1.xlsx');# 获取活动工作薄$sheet = $spreadsheet->getActiveSheet();# 获取 单元格值 和 坐标$cellC1 = $sheet->getCell('B2');echo '值: ', $cellC1->getValue(),PHP_EOL;echo  '坐标: ', $cellC1->getCoordinate(),PHP_EOL;$sheet->setCellValue('B2','欧阳锋');# 获取 单元格值 和 坐标$cellC2 = $sheet->getCell('B2');echo '值: ', $cellC2->getValue(),PHP_EOL;echo  '坐标: ', $cellC2->getCoordinate();

导入功能

$file = $_FILES['file']['tmp_name'];# 载入composer自动加载文件require 'vendor/autoload.php';# 载入方法库require 'function.php';# 创建读操作$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');# 打开文件、载入excel表格$spreadsheet = $reader->load($file);# 获取活动工作薄$sheet = $spreadsheet->getActiveSheet();# 获取总列数$highestColumn = $sheet->getHighestColumn();# 获取总行数$highestRow = $sheet->getHighestRow();# 列数 改为数字显示$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);$log = [];for($a=2;$a<$highestRow;$a++){$title = $sheet->getCellByColumnAndRow(1,$a)->getValue();$cat_fname = $sheet->getCellByColumnAndRow(2,$a)->getValue();$cat_name = $sheet->getCellByColumnAndRow(3,$a)->getValue();$price = $sheet->getCellByColumnAndRow(4,$a)->getValue();$img = $sheet->getCellByColumnAndRow(5,$a)->getValue();$cat_fid = find('shop_cat','id','name="'.$cat_fname.'"');$cat_id = find('shop_cat','id','name="'.$cat_name.'"');$data = ['title' => $title,'cat_fid' => $cat_fid['id'],'cat_id' => $cat_id['id'],'price' => $price,'img' => $img,'add_time' => time(),];// 数据处理}echo json_encode(['code'=>0,'msg'=>'成功','data'=>$log]);

xls / xlsx 文件下载

# 载入composer自动加载文件require 'vendor/autoload.php';# 给类文件的命名空间起个别名use PhpOffice\PhpSpreadsheet\Spreadsheet;# 实例化 Spreadsheet 对象$spreadsheet = new Spreadsheet();# 获取活动工作薄$sheet = $spreadsheet->getActiveSheet();$sheet->setCellValue('A1','ID');$sheet->setCellValue('B1','姓名');$sheet->setCellValue('C1','年龄');$sheet->setCellValue('D1','身高');$sheet->setCellValueByColumnAndRow(1, 2, 1);$sheet->setCellValueByColumnAndRow(2, 2, '欧阳克');$sheet->setCellValueByColumnAndRow(3, 2, '18岁');$sheet->setCellValueByColumnAndRow(4, 2, '188cm');// MIME 协议，文件的类型，不设置，会默认htmlheader('Content-Type:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');header('Content-Disposition:attachment;filename=1.xlsx');// MIME 协议的扩展header('Cache-Control:max-age=0');// 缓存控制$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');// php://output 它是一个只写数据流, 允许你以 print 和 echo一样的方式写入到输出缓冲区。 $writer->save('php://output');

导出封装函数

# 载入composer自动加载文件require 'vendor/autoload.php';function CreateExcel($Data, $Header, $Path, $FileName, $LongNumberField = null){    $SpreadSheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();    $Sheet = $SpreadSheet->getActiveSheet();    if ($LongNumberField === null) {        array_unshift($Data, $Header);        $SpreadSheet->getDefaultStyle()->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER);        $Sheet->fromArray($Data);    } else {        $HeaderCount = count($Header);        for ($i = 0; $i < $HeaderCount; $i++) {            $Sheet->setCellValueByColumnAndRow($i + 1, 1, $Header[$i]);        }        $RowIndex = 2;        $DataCount = count($Data);        for ($i = 0; $i < $DataCount; $i++) {            $ColumnIndex = 1;            foreach ($Data[$i] as $Key => $Value) {                if (in_array($Key, $LongNumberField)) {                    $Sheet->setCellValueExplicitByColumnAndRow($ColumnIndex, $RowIndex, $Value, \PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);                } else {                    $Sheet->setCellValueByColumnAndRow($ColumnIndex, $RowIndex, $Value);                }                $ColumnIndex++;            }            $RowIndex++;        }    }    $Xlsx = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($SpreadSheet);    $Xlsx->save($Path . $FileName);}$Data = [    ['id' => 1, 'name' => '张三', 'bank_card' => '123456789123456789'],    ['id' => 2, 'name' => '李四', 'bank_card' => '123456123456789789'],];$Header = ['id', '姓名', '银行卡号'];$Path = 'D:\Work\Php\test.loc\\';$FileName = 'Export.xlsx';$LongNumberField = ['bank_card'];CreateExcel($Data, $Header, $Path, $FileName, $LongNumberField);

来源地址：https://blog.csdn.net/qq_23564667/article/details/131608414