使用phpoffice/phpspreadsheet,导入、导出数据
安装
composer require phpoffice/phpspreadsheet
//use \PhpOffice\PhpSpreadsheet\Spreadsheet;
//use \PhpOffice\PhpSpreadsheet\IOFactory;
//use \PHPExcel_Style_NumberFormat; //设置列的格式==>>设置文本格式
//导入数据
public function export()
{
//上传excel文件
$file = request()->file('myfile');
//获取表格的大小,限制上传表格的大小5M
$file_size = $_FILES['myfile']['size'];
if ($file_size > 50 * 1024 * 1024) {
$this->error('文件大小不能超过5M');
exit();
}
//限制上传表格类型
$fileExtendName = substr(strrchr($_FILES['myfile']["name"], '.'), 1);
//application/vnd.ms-excel 为xls文件类型
if ($fileExtendName != 'xls') {
$this->error('必须为excel表格,且必须为xls格式!');
exit();
}
header("content-type:text/html;charset=utf-8");
//将文件保存到public/uploads目录下面
$info = $file->validate(['size' => 5 * 1024 * 1024, 'ext' => 'xls,xlsx'])->move('uploads');
if ($info) {
//获取上传到后台的文件名
$fileName = $info->getSaveName();
//获取文件路径
$filePath = '/www/wwwroot/obd-api/'. 'public' . DIRECTORY_SEPARATOR . 'uploads' . DIRECTORY_SEPARATOR . $fileName;
} else {
return json(['status' => '1', 'message' => '文件过大或格式不正确导致上传失败-_-!']);
}
require dirname(dirname(dirname(__DIR__))).'/vendor/autoload.php';
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load($filePath); //载入excel表格
$worksheet = $spreadsheet->getActiveSheet();
$highestRow = $worksheet->getHighestRow(); // 总行数
$highestColumn = $worksheet->getHighestColumn(); // 总列数
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5
$lines = $highestRow - 2;
if ($lines <= 0) {
exit('Excel表格中没有数据');
}
for ($row = 3; $row <= $highestRow; ++$row) {
$temp = array(
'code' => $worksheet->getCellByColumnAndRow('1', $row)->getValue(),
'name' => $worksheet->getCellByColumnAndRow('2', $row)->getValue(),
'class' => $worksheet->getCellByColumnAndRow('3', $row)->getValue(),
'describe' => $worksheet->getCellByColumnAndRow('4', $row)->getValue(),
'symptom' => $worksheet->getCellByColumnAndRow('5', $row)->getValue(),
'cause' => $worksheet->getCellByColumnAndRow('6', $row)->getValue(),
'scheme' => $worksheet->getCellByColumnAndRow('7', $row)->getValue(),
);
$list[] = $temp;
}
}