PhpSpreadsheet中⽂⽂档Spreadsheet操作教程实例Spreadsheet ⽀持excel 函数 公式使⽤
<?php
namespace app
# 给类⽂件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
# Xlsx类将电⼦表格保存到⽂件
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
# 实例化 Spreadsheet 对象
$spreadsheet=new Spreadsheet();
# 获取活动⼯作薄
$sheet=$spreadsheet->getActiveSheet();
$sheet->setCellValue('A1','10');
$sheet->setCellValue('B1','15');
$sheet->setCellValue('C1','20');
$sheet->setCellValue('D1','25');
$sheet->setCellValue('E1','30');
$sheet->setCellValue('G1','35');
$sheet->setCellValue('A2','总数：');
$sheet->setCellValue('B2','=SUM(A1:G1)');
$sheet->setCellValue('A3','平均数：');
$sheet->setCellValue('B3','=AVERAGE(A1:G1)');
$sheet->setCellValue('A4','最⼩数：');
$sheet->setCellValue('B4','=MIN(A1:G1)');
$sheet->setCellValue('A5','最⼤数：');
$sheet->setCellValue('B5','=MAX(A1:G1)');
$sheet->setCellValue('A6','最⼤数：');
$sheet->setCellValue('B6','\=MAX(A1:G1)');// 使⽤转义字符
// 批量赋值
$sheet->setCellValue('A1','ID');
$sheet->setCellValue('B1','姓名');
$sheet->setCellValue('C1','年龄');
$sheet->setCellValue('D1','⾝⾼');
$sheet->fromArray(
[
[1,'欧阳克','18岁','188cm'],
[2,'黄蓉','17岁','165cm'],
[3,'郭靖','21岁','180cm']
],
3,
'A2'
);
// 合并单元格合并后，赋值只能给A1，开始的坐标。
$sheet->mergeCells('A1:B5');
$sheet->getCell('A1')->setValue('欧阳克');
# Xlsx类将电⼦表格保存到⽂件
$writer=new Xlsx($spreadsheet);
$writer->save('1.xlsx');
// 客户端⽂件下载
header('Content-Type:application/vnd.ms-excel');
header('Content-Disposition:attachment;filename=1.xls');
header('Cache-Control:max-age=0');
$writer= \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet,'Xls');
$writer->save('php://output');
读取表格⽂件
namespace app;
# 创建读操作
$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,
php手册官方中文版'add_time'=>time(),
];
$ins=insert('shop_list',$data);
if($ins){
$log[]='第'.$a.'条，插⼊成功';
}else{
$log[]='第'.$a.'条，插⼊失败';
}
}
echo json_encode(['code'=>0,'msg'=>'成功','data'=>$log]);

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。