PhpSpreadsheet 学习和使用

全栈程序员站长

发布于 2022-09-30 11:10:45

1K00

代码可运行

文章被收录于专栏：全栈程序员必看全栈程序员必看

运行总次数：0

代码可运行

大家好，又见面了，我是你们的朋友全栈君。

1、安装

composer require phpoffice/phpspreadsheet

2、
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\IOFactory;

3、数据库导出到excel表中
//导出测试
public function daochu()
{
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
//设置工作表标题名称
$worksheet->setTitle('车型品牌');
//表头
//设置单元格内容
$worksheet->setCellValueByColumnAndRow(1, 1, '车型品牌表');
$worksheet->setCellValueByColumnAndRow(1, 2, 'ID');
$worksheet->setCellValueByColumnAndRow(2, 2, '品牌名');
$worksheet->setCellValueByColumnAndRow(3, 2, '图片');
$worksheet->setCellValueByColumnAndRow(4, 2, '状态');
//合并单元格
$worksheet->mergeCells('A1:D1');
//字体设置
$styleArray = [
'font' => [
'bold' => true,  //字体加粗
'color' => [ 'rgb' => 'FF6A6A' ], //字体颜色
'strikethrough' => true, //删除线
'italic' => true, //倾斜
'underline' => Font::UNDERLINE_DOUBLE,
],
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_CENTER,
],
];
//设置单元格样式
$worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);
$worksheet->getStyle('A2:D2')->applyFromArray($styleArray)->getFont()->setSize(14);
//设置字体颜色
//$worksheet->getStyle('B2')->getFont()->getColor()->applyFromArray(['rgb' => 'C0FF3E']);
//背景色
//$worksheet->getStyle('B2')->getFill()->applyFromArray( [ 'fillType' => Fill::FILL_GRADIENT_LINEAR, 'rotation' => 0, 'startColor' => [ 'rgb' => '000000' ], 'endColor' => [ 'argb' => 'FFFFFFFF' ] ] );
//设置单元格 自动宽度显示
$spreadsheet->getActiveSheet()->getColumnDimension('C')->setAutoSize(true);
$rows = Db::name('cartype_logo')->select();
$len = Db::name('cartype_logo')->count();
$j = 0;
for ($i=0; $i < $len; $i++) {
$j = $i + 3; //从表格第3行开始
$worksheet->setCellValueByColumnAndRow(1, $j, $rows[$i]['logo_id']);
$worksheet->setCellValueByColumnAndRow(2, $j, $rows[$i]['logo_name']);
$worksheet->setCellValueByColumnAndRow(3, $j, $rows[$i]['logo_img']);
$worksheet->setCellValueByColumnAndRow(4, $j, $rows[$i]['status']);
}
$styleArrayBody = [
'borders' => [
'allBorders' => [
'borderStyle' => Border::BORDER_THIN,
'color' => ['argb' => '666666'],
],
],
'alignment' => [
'horizontal' => Alignment::HORIZONTAL_CENTER,
],
];
$total_rows = $len + 2;
//添加所有边框/居中
$worksheet->getStyle('A1:D'.$total_rows)->applyFromArray($styleArrayBody);
$filename = '品牌表.xlsx';
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="'.$filename.'"');
header('Cache-Control: max-age=0');
$writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');
}

3、excel导入数据库表汇中
//导入测试
public function daoru()
{
$reader = IOFactory::createReader('Xls');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load('C:\Users\Administrator\Desktop\logo.xls'); //载入excel表格
$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 总行数
$lines = $highestRow - 1;
if ($lines <= 0) {
exit('Excel表格中没有数据');
}
$new = [];
for ($row = 2; $row <= $highestRow; ++$row) {
$new_logos = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //品牌名
$new_png = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //图片地址
$new[] = ['logo_name'=> $new_logos,'logo_img'=>$new_png];
}

$new = [];
for ($row = 2; $row <= $highestRow; ++$row) {
$new_logos = $worksheet->getCellByColumnAndRow(1, $row)->getValue(); //品牌名
$new_png = $worksheet->getCellByColumnAndRow(2, $row)->getValue(); //图片地址
$new[] = ['logo_name'=> $new_logos,'logo_img'=>$new_png];
}

foreach ($new as $k => $v) {
$data['logo_name'] = $v[0];
$data['logo_img'] = $v[1];
//var_dump($data); die;
Db::name('cartype_logo')->insert($data);
}