php Spreadsheet excel导出和读取
PHPExcel2019年后已经不再维护,并且php7.4后的版本都不适配,PhpSpreadsheet是PHPExcel的下一个版本。要求php7.4以上的版本使用
使用composer安装:
composer require phpoffice/phpspreadsheet
引入的类
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
创建excel
// 导出的key
$fields = ['name'];
$objExcel = new Spreadsheet();
$objWriter = IOFactory::createWriter($objExcel, 'Xlsx');
// 多标题
// $header = ['靓仔','靓女'];
// $key = ord('A'); //ord() 函数返回字符串中第一个字符的 ASCII 值。 数字0-9: 48-57 小写字母a-z:97-122 大写字母A-Z: 65-90
// foreach ($header as $k => $v) {
// //设置每列名字
// $head_name = chr($key);
// $objExcel->setActiveSheetIndex(0)->setCellValue($head_name . '1', $v);
// $key += 1;
// }
// 无标题
$objActSheet = $objExcel->getActiveSheet(0);
$title = time() . 'list';
$objActSheet->setTitle($title);//设置excel的标题
$i = 1;
// 内容
foreach ($data as $k => $v) {
foreach ($fields as $kk => $vv) {
$objActSheet->setCellValueByColumnAndRow($kk + 1, $i, $v[$vv]);
}
++$i;
}
// 保存
$objWriter->save($fileUrl . '/' . $title . '.xlsx');
读取excel
// 读取的文件地址
$file = "./{$turnDir}/{$v}";
// 读取execl
$objExcel = new Spreadsheet();
$spreadsheet = IOFactory::load($file);
$sheetData = $spreadsheet->getActiveSheet()->toArray(null, true, true, true);