PhpSpreadsheet 学习和使用

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺

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

echo ‘ok’;

}