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