06
09月
2021
读取
$php_excel = new \PHPExcel();
//上传
$file = request()->file('excel');
//移动文件
$file_info = $file->validate(['size' => 1567800000,'ext' => 'xls,xlsx,xlsm',])->move(ROOT_PATH . 'public' . DS . 'uploads');
$filename = str_replace("\\", "/", $file_info->getSaveName());
$file_path = get_upload_file_root().'/'.$filename;
//读取文件
$excel_type = \PHPExcel_IOFactory::identify($file_path);
$excel_reader = \PHPExcel_IOFactory::createReader($excel_type);
//获取表单上传文件
$PHPExcel = $excel_reader->load($file_path,'utf-8');
// 读取excel文件中的第一个工作表
$sheet = $PHPExcel->getSheet(0);
// 取得最大的列号
$highestColumm = $sheet->getHighestColumn();
// 取得一共有多少行
$highestRow = $sheet->getHighestRow();
$arr = [
'A' => 'goods_name',
'B' => 'digit',
'C' => 'unit_price',
'D' => 'digit_price',
'E' => 'integer',
'F' => 'ladder_amount',
'G' => 'final_unit_price',
'H' => 'final_digit_price',
'I' => 'rank',
'J' => 'final_digit_amount',
];
$upload_date = date('Y-m-d H:i:s',time());
$upload_staff = $this->getSessionStaffId();
// 循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
for ($currentRow = 2; $currentRow <= $highestRow; $currentRow ++) {
// 从哪列开始，A表示第一列
for ($currentColumn = 'A'; $currentColumn <= 'J'; $currentColumn ++) {
// 数据坐标
$address = $currentColumn . $currentRow;
// 读取到的数据，保存到数组$arr中
$data[$currentRow][$arr[$currentColumn]] = $sheet->getCell($address)->getValue();
//新增
$data[$currentRow]['upload_date'] = $upload_date;
$data[$currentRow]['upload_staff'] = $upload_staff;
}
}写入
//写入
$sheet->setCellValue("K1", "反馈");保存
if (strstr($filename, '.xlsx')) {
$excel_writer = new \PHPExcel_Writer_Excel2007($PHPExcel);
} else {
$excel_writer = new \PHPExcel_Writer_Excel5($PHPExcel);
}
//保存文件名
$save_name = time().'.xlsx';
//保存路径
$root_path = ROOT_PATH . 'public' . DS . 'uploads';
$root_path = str_replace('\\', '/', $root_path);
$excel_src = $root_path . '/' . date('Ymd', time()) . '/'.$save_name;
//保存
$excel_writer->save($excel_src);下载
//文件路径
$arr = explode('/',$excel_src);
//文件名
$save_name = $arr[count($arr)-1];
if(file_exists($excel_src)){
//打开文件
$file1 = fopen($excel_src, "r");
//输入文件标签
Header("Content-type:application/vnd.ms-excel"); //表示输出的是excel文件
Header("Accept-Ranges: bytes");
Header("Accept-Disposition: attachment;filename=" . $save_name);
header('Content-Disposition: attachment; filename=' . $save_name);
header('Content-Type: application/octet-stream; name=' . $save_name);
ob_clean();
flush(); //清除文件中多余的路径名以及解决乱码的问题
echo fread($file1, filesize($excel_src));
fclose($file1);
unlink($excel_src);
exit();
}