- composer安装包
composer require phpoffice/phpspreadsheet
一段读取上传表格的文件的代码实例
/**
* 导入表格数据
* User: zmq3821@163.com
* Date: 2021/5/15 0:07
* @param $files
* @return bool
* @throws \PhpOffice\PhpSpreadsheet\Exception
* @throws \PhpOffice\PhpSpreadsheet\Reader\Exception
*/
public function import($files): bool
{
foreach($files as $file) {
$inputFileName = "xxx.xls";
# 检测文件类型
$inputFileType = \PhpOffice\PhpSpreadsheet\IOFactory::identify($inputFileName);
# 创建读操作
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader($inputFileType);
# 打开文件、载入excel表格
$spreadsheet = $reader->load($inputFileName);
# 获取活动工作薄
$sheet = $spreadsheet->getActiveSheet();
# 获取总列数
$highestColumn = $sheet->getHighestColumn();
# 获取总行数
$highestRow = $sheet->getHighestRow();
# 列数 改为数字显示
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
if ($highestRow-1 > 500) {
$this->error = '导入最大条数不能超过500条';
return false;
}
//序号 申请单号* 设备编号 设备名称* 外委单位* 预计金额(元) 工期要求* 外委理由 申请时间 申请人
$fields = [ 'number', 'sn', 'dev_sn', 'dev_name', 'department', 'plan_amount', 'time_request', 'reason', 'apply_time', 'creator' ];
$data = [];
for($a=2; $a<=$highestRow; $a++){
$rowData = [];
foreach ($fields as $k => $field) {
if ($k == 'number') continue;
$_value = trim($sheet->getCellByColumnAndRow($k+1, $a)->getValue());
switch ($field)
{
case 'number':
$_value = intval($_value);
break;
case 'plan_amount':
$_value = intval(floatval($_value) * 100);
break;
case 'apply_time':
$_value = $this->excelTime($_value);
break;
}
$rowData[$field] = $_value;
}
//检查数据
$re = $this->checkImportData($rowData);
if (true !== $re) {
$this->error = "第{$a}行{$re}";
return false;
}
$rowData['process_status'] = 4;
$rowData['create_time'] = time();
$data[] = $rowData;
}
if (empty($data)) {
$this->error = '导入数据为空,请检查';
return false;
}
//插入数据
$insert_number = Db::name('var_device_repair_outer')->insertAll($data);
if (!$insert_number) {
$this->error = '数据导入失败';
return false;
}
$this->result = $insert_number;
return true;
}
}
/**
* 检查导入表格数据
* User: zmq3821@163.com
* Date: 2021/5/15 1:15
* @param array $row
* @return string
*/
public function checkImportData(array $row)
{
$requireFields = ['sn'=>'设备编号', 'dev_name'=>'设备名称', 'department'=>'外委单位', 'time_request'=>'工期要求', 'apply_time'=>'申请时间', 'creator'=>'申请人'];
foreach ($row as $f => $datum) {
if (array_key_exists($f, $requireFields) && empty($datum)) {
return $requireFields[$f].'不能为空';
}
}
return true;
}
/**
* 转换excel的日期为时间戳
* User: zmq3821@163.com
* Date: 2021/5/15 10:25
* @param string $value
* @return int
*/
public function excelTime(string $value)
{
//如果是数字则转化,如果是有 - 或者 /,视作文本格式不作处理
$type1 = strpos($value, '/');
$type2 = strpos($value, '-');
if ($type1 || $type2) {
$toTimestamp = intval(strtotime($value));
} else {
$toTimestamp = \PhpOffice\PhpSpreadsheet\Shared\Date::excelToTimestamp($value, 'PRC');
}
return $toTimestamp;
}
/**
* @todo 下载文件
*/
public function downlaodImportTemplate(){
header("Content-type:text/html;charset=utf-8");
$root_path = app()->getRootPath();
$file_name = 'BwDeviceManage/static/assets/data/excel/DeviceRepairOuterImportTmeplate.xlsx';
$file_name = iconv("utf-8", "gb2312", $file_name);
$file_path = $root_path . $file_name;
if (!file_exists($file_path)) {
$view = View::instance();
$view->assign('msg', '下载文件不存在');
$data = $view->fetch(config('view.dispatch_error_tmpl'));
$response = Response::create($data, 'html');
throw new HttpResponseException($response);
}
$fp = fopen($file_path, "r");
$file_size = filesize($file_path);
//下载文件需要用到的头
Header("Content-type: application/octet-stream");
Header("Accept-Ranges: bytes");
Header("Accept-Length:".$file_size);
Header("Content-Disposition: attachment; filename=".$file_name);
$buffer = 1024;
$file_count = 0;
while (!feof($fp) && $file_count < $file_size) {
$file_con = fread($fp, $buffer);
$file_count += $buffer;
echo $file_con;
}
fclose($fp);
}