1.安装
composer require phpoffice/phpspreadsheet
2.引入
use PhpOffice\\PhpSpreadsheet\\Cell\\Coordinate;
use PhpOffice\\PhpSpreadsheet\\IOFactory;
use PhpOffice\\PhpSpreadsheet\\Spreadsheet;
use PhpOffice\\PhpSpreadsheet\\Style\\Alignment;
use PhpOffice\\PhpSpreadsheet\\Writer\\Csv;
use PhpOffice\\PhpSpreadsheet\\Writer\\Xlsx;
3.生成excel表格并导入导出数据
<?php
declare (strict\_types = 1);
namespace app\\api\\controller;
use think\\facade\\Controller;
use app\\BaseController;
use app\\api\\model\\TestModel;
use PhpOffice\\PhpSpreadsheet\\Cell\\Coordinate;
use PhpOffice\\PhpSpreadsheet\\IOFactory;
use PhpOffice\\PhpSpreadsheet\\Spreadsheet;
use PhpOffice\\PhpSpreadsheet\\Style\\Alignment;
use PhpOffice\\PhpSpreadsheet\\Writer\\Csv;
use PhpOffice\\PhpSpreadsheet\\Writer\\Xlsx;
class Index extends BaseController
{
/\*\*
/\* 生成excel表格
/\* author: 飞鱼
/\* QQ: 2993635720
\*\*/
publicfunction index()
{
$spreadsheet=new Spreadsheet();
$sheet=$spreadsheet->GetActiveSheet();
$sheet->setCellValue('A1','您好表格');
$writer=new Xlsx($spreadsheet);
$writer->save('1.xlsx');
return'生成表格成功';
}
//\*\*
/\* 将excel表格数据导入数据库
/\* author: 飞鱼
/\* QQ: 2993635720
\*\*/
publicfunction import\_db(){
$reader=IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(true);
$spreadsheet=$reader->load('1.xlsx');//载入文件
$worksheet=$spreadsheet->getActiveSheet();
$highestRow=$worksheet->getHighestRow();//总行数
for($row=2;$row<=$highestRow;$row++){
$tabale\_username=$worksheet->getCellByColumnAndRow(1,$row)->getValue();//用户名
$table\_password=$worksheet->getCellByColumnAndRow(2,$row)->getValue();//密码
$table\_create\_time=$worksheet->getCellByColumnAndRow(3,$row)->getValue();//创建时间
$tabale\_update\_time=$worksheet->getCellByColumnAndRow(4,$row)->getValue();//更新时间
$data=\[
'username'\=>$tabale\_username,
'password'\=>$table\_password,
'create\_time'\=>$table\_create\_time,
'update\_time'\=>$tabale\_update\_time,
\];
TestModel::create($data);
}
}
/\*\*
/\* 数据导出到表格
/\* author: 飞鱼
/\* QQ: 2993635720
\*\*/
publicfunction export\_tb(){
$spreadsheet=new Spreadsheet();
$worksheet=$spreadsheet->getActiveSheet();
//表格名称
$worksheet->setTitle('测试表');
//表头单元格设置
$worksheet->setCellValueByColumnAndRow(1,1,'username');
$worksheet->setCellValueByColumnAndRow(2,2,'password');
$worksheet->setCellValueByColumnAndRow(4,2,'create\_time');
$worksheet->setCellValueByColumnAndRow(4,2,'update\_time');
//合并单元格
$worksheet->mergeCells('A1:C1');
$styleArray=\[
'font'\=>\['blod'\=>true\],
'alignment'\=>\[
'horizontal'\=>Alignment::HORIZONTAL\_CENTER,
\],
\];
//设置单元格样式
$worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);
$worksheet->getStyle('A1:C2')->applyFromArray($styleArray)->getFont()->setSize(14);
$rows=TestModel::select();
$len=$rows->count(); //数据总条数
$rows=$rows->toArray();
$j=0;
for($i=0;$i<=$len;$i++){
$j=$i+3;//从表格第三行开始
$worksheet->setCellValueByColumnAndRow(1,$j,$rows\[$i\]\['username'\]);
$worksheet->setCellValueByColumnAndRow(2,$j,$rows\[$i\]\['password'\]);
$worksheet->setCellValueByColumnAndRow(3,$j,$rows\[$i\]\['create\_time'\]);
$worksheet->setCellValueByColumnAndRow(4,$j,$rows\[$i\]\['update\_time'\]);
}
$setArrayBody=\[
'borders'\=>\[
'allBoders'\=>\[
'borderStyle'\=>Border::BORDER\_THIN,//边框
'color'\=>\['argb'\=>'666666'\],
\],
\],
'horizontal'\=>Alignment::HORIZONTAL\_CENTER,//居中
\];
//设置列宽
$spreadsheet->getActiveSheet()->getDefaultRowDimension()->setWidth(50);
$total\_rows=$len+2;
//添加所有边框并所有字体居中
$worksheet->getStyle('A1:D'.$total\_rows)->applyFromArray($setArrayBody);
$FileName='测试数据表.xlsx';
header('Content-Type:applaction/vnd.openxmldformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition:attchment;filename="'.$FileName.'"');
header('Cache-Controll:max-age=0');
$writer=IOFactory::createWriter($spreadsheet,'Xlsx');
$writer->save('php//output');
}
}
