php excel 导入导出

引入依赖包

composer require phpoffice/phpspreadsheet

导出

/**
 * @param string $title 标题 
 * @param array $header 头信息
 * @param array $data 数据 
 * @param string $filename 文件名
 * @return void
 * @throws \PhpOffice\PhpSpreadsheet\Exception
 * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
 */
function export_csv($title, $header, $data, $filename = '')
{
    $spreadsheet = new Spreadsheet();
    $worksheet = $spreadsheet->getActiveSheet();
    // 设置工作表标题名称
    $worksheet->setTitle($title);
    // 表头
    $spreadsheet->getActiveSheet()->getDefaultColumnDimension()->setWidth(20);
    // 设置单元格内容
    $worksheet->setCellValueByColumnAndRow(1, 1, $title);

    foreach ($header as $key => $value) {
        $worksheet->setCellValueByColumnAndRow($key + 1, 2, $value);
    }

    $lastCell = chr(count($header) - 1 + 65);
    // 合并单元格
    $worksheet->mergeCells('A1:'.$lastCell.'1');
    $styleArray = [
        'font' => [
            'bold' => true,
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
        'width' => [],
    ];
    // 设置单元格样式
    $worksheet->getStyle('A1')->applyFromArray($styleArray)->getFont()->setSize(28);

    $worksheet->getStyle('A1:'.$lastCell.'1')->applyFromArray($styleArray)->getFont()->setSize(14);

    // 数据起始行
    $row_num = 3;

    foreach ($data as $item) {
        foreach ($item as $key => $value) {
            $worksheet->setCellValueByColumnAndRow($key + 1, $row_num, $value);
        }
        ++$row_num;
    }

    $styleArrayBody = [
        'borders' => [
            'allBorders' => [
                'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN,
                'color' => ['argb' => '666666'],
            ],
        ],
        'alignment' => [
            'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
        ],
    ];

    $total_jzInfo = count($data) + 3;
    // 添加所有边框/居中
    $worksheet->getStyle('A1:'.$lastCell.$total_jzInfo)->applyFromArray($styleArrayBody);

    $filename = $filename.'.xlsx';
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="'.$filename.'"');
    header('Cache-Control: max-age=0');

    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
    $writer->save('php://output');
}

实例

  $header = [
            '序号',
            '订单ID',
            '订单编号',
           
        ];

        $data = [];
        foreach ($list as $key => $value) {
            $data[] = [
                $key + 1,
                $value->id,
                $value->order_no,
               
            ];
        }
        export_csv('订单', $header, $data, '订单_'.date('YmdHis'));

导入

/**
 * 读取 excel 文件数据.
 *
 * @param mixed $filepath 文件路径
 * @param mixed $starRow 开始行号
 *
 * @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
 * @throws \PhpOffice\PhpSpreadsheet\Exception
 *
 * @return array
 */
function import_csv($filepath, $starRow = 2)
{
    $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');

    $reader->setReadDataOnly(true);
    $spreadsheet = $reader->load($filepath); // 载入excel表格

    $worksheet = $spreadsheet->getActiveSheet();
    $highestRow = $worksheet->getHighestRow(); // 总行数 e.g 2
    $highestColumn = $worksheet->getHighestColumn(); // 总列数 e.g "D"
    $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5

    $lines = $highestRow - $starRow + 1;

    if ($lines <= 0) {
        return [];
    }

    $data = [];
    for ($row = $starRow; $row <= $highestRow; ++$row) {
        $cellValues = [];

        for ($column = 1; $column <= $highestColumnIndex; ++$column) {
            $cellValues[] = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
        }

        $data[] = $cellValues;
    }

    return $data;
}

实例

$excelData = import_csv($filepath);

$data = [];
foreach ($excelData as $val) {
    if ($val[0] && $val[1]) {
        $data[] = [
           
            'name' => $val[1],
            'url' => $val[2],
            
        ];
    }
}