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');
????}
}
