php使用phpspreadsheet实现excel导入导出

 composer引入phpspreadsheet

composer require phpoffice/phpspreadsheet

<?php

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\IOFactory;

/**
 * [数据导出]
 * @return [type] [description]
 */
function export() {
    $list = [
        [
            'id' => 1,
            'name' => '字段1'
        ],
        [
            'id' => 2,
            'name' => '字段1'
        ]
    ];

    $title = ['ID', '名称'];
    // Create new Spreadsheet object
    $spreadsheet = new Spreadsheet();
    $sheet = $spreadsheet->getActiveSheet();
    //表头
    //设置单元格内容
    $titCol = 'A';
    foreach ($title as $key => $value) {
        // 单元格内容写入
        $sheet->setCellValue($titCol . '1', $value);
        $spreadsheet->getActiveSheet()->getColumnDimension($titCol)->setAutoSize(true);
        $titCol++;
    }
    $row = 2; // 从第二行开始
    foreach ($list as $item) {
        $dataCol = 'A';
        // 单元格内容写入
        $sheet->setCellValue($dataCol . $row, $item['id']);$dataCol++;
        $sheet->setCellValue($dataCol . $row, $item['name']);$dataCol++;

        $row++;
    }

    $name = 'export-'.date('YmdHi');
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
    header('Cache-Control: max-age=0');
    $writer = new Xlsx($spreadsheet);
    $writer->save('php://output');
    //删除清空：
    $spreadsheet->disconnectWorksheets();

}

/**
 * [import 数据导入]
 * @return [type] [description]
 */
function import() {
    $file_url = './uploads/excel/import.xls';

    // 有Xls和Xlsx格式两种
    $objReader = IOFactory::createReader('Xls');
    if (!$objReader->canRead($file_url)) {
        $objReader = IOFactory::createReader('Xlsx');
        // if (!$objRead->canRead($file_url)) {
        //     return $this->error('文件读取错误, 只支持导入Excel文件！');
        // }
    }

    $objPHPExcel = $objReader->load($file_url);  //$filename可以是上传的表格，或者是指定的表格
    $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
    $highestRow = $sheet->getHighestRow();       // 取得总行数

    $list_data = [];
    //循环读取excel表格
    for ($row = 2; $row <= $highestRow; $row++) {
        $list_data[] = [
            'id' => $objPHPExcel->getActiveSheet()->getCell("A" . $row)->getValue(),
            'name' => $objPHPExcel->getActiveSheet()->getCell("B" . $row)->getValue(),
        ];
    }
    
    //针对实际业务需求对导入数据处理 
    //这里保存list_data到数据库
    DbModel::saveAll($list_data);

}