当你在使用phpoffice/phpexcel 类库时候。composer 会给你提示一句话
Package phpoffice/phpexcel is abandoned, you should avoid using it. Use phpoffice/phpspreadsheet instead
phpexcel 已被废弃，建议我们用phpspreadsheet，
包地址：
https://packagist.org/packages/phpoffice/phpspreadsheet
composer：
composer require phpoffice/phpspreadsheet
使用
引入
use PhpOfficePhpSpreadsheetSpreadsheet;
use PhpOfficePhpSpreadsheetIOFactory;
use PhpOfficePhpSpreadsheetStyleNumberFormat;
导出：
//模板下载
public function template_download()
{
$spreadsheet = new Spreadsheet();
$worksheet = $spreadsheet->getActiveSheet();
$worksheet->setTitle('导入模板'); //设置当前sheet的标题
$worksheet->getStyle('A1:E1')->getFont()->setBold(true)->setName('Arial')->setSize(10);
$worksheet->getStyle('B1')->getFont()->setBold(true);
$worksheet->getDefaultColumnDimension()->setWidth(30);
//设置第一栏的标题
$worksheet->setCellValue('A1', '交易流水号');
$worksheet->setCellValue('B1', '开户名');
$worksheet->setCellValue('C1', '卡号');
$worksheet->setCellValue('D1', '交易金额');
$worksheet->setCellValue('E1', '交易时间');
//默认填充数据
$explame_data_list = array(
array(
'bank_deal_no' => '1234567890123456',
'account_name' => '小明',
'bank_card' => '4231456987436654',
'deal_money' => '100.00',
'deal_time' => date("Y-m-d H:i:s"),
),
);
//第二行起
$baseRow = 2; //数据从N-1行开始往下输出 这里是避免头信息被覆盖
foreach ($explame_data_list as $k => $val) {
$i = $k + $baseRow;
$worksheet->setCellValue('A' . $i, $val['bank_deal_no']);
$worksheet->setCellValue('B' . $i, $val['account_name']);
$worksheet->setCellValue('C' . $i, $val['bank_card']);
$worksheet->setCellValue('D' . $i, $val['deal_money']);
$worksheet->setCellValue('E' . $i, $val['deal_time']);;
}
//处理 数字过大会进行科学计数法
$worksheet->getStyle('A2')->getNumberFormat()->setFormatCode(PhpOfficePhpSpreadsheetStyleNumberFormat::FORMAT_NUMBER);
$worksheet->getStyle('C2')->getNumberFormat()->setFormatCode(PhpOfficePhpSpreadsheetStyleNumberFormat::FORMAT_NUMBER);
$this->downloadExcel($spreadsheet, '批量导入模板-合同表单选项', 'Xls');
}
/*********************************************************************************************************************/
//公共文件，用来传入xls并下载
private function downloadExcel($spreadsheet, $filename, $format)
{
// $format只能为 Xlsx 或 Xls
if ($format == 'Xlsx') {
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
} elseif ($format == 'Xls') {
header('Content-Type: application/vnd.ms-excel');
}
header("Content-Disposition: attachment;filename="
. $filename . date('Y-m-d') . '.' . strtolower($format));
header('Cache-Control: max-age=0');
$objWriter = IOFactory::createWriter($spreadsheet, $format);
$objWriter->save('php://output');
//通过php保存在本地的时候需要用到
//$objWriter->save($dir.'/demo.xlsx');
//以下为需要用到IE时候设置
// If you're serving to IE 9, then the following may be needed
//header('Cache-Control: max-age=1');
// If you're serving to IE over SSL, then the following may be needed
//header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
//header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
//header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
//header('Pragma: public'); // HTTP/1.0
exit;
}
导入
public function import(){ header("content-type:text/html;charset=utf-8"); //上传excel文件 $files = request()->file(); //将文件保存到public/uploads目录下面 try { validate(['image'=>'fileSize:1048576|fileExt:xls']) ->check($files); $savename = []; foreach($files as $file){ $savename[] = hinkfacadeFilesystem::disk('public')->putFile( 'billfile', $file,'md5'); } } catch (thinkexceptionValidateException $e) { return json(['status' => '1', 'message' => $e->getMessage()]); } //获取文件路径 $filePath = ROOT_PATH().'/public/uploads/'.$savename[0]; $spreadsheet = IOFactory::load($filePath); $sheetData = $spreadsheet->getActiveSheet()->toArray(true, true, true, true,true); $row_num = count($sheetData); $now_time = time(); $import_data = []; //数组形式获取表格数据 for ($i = 2; $i <= $row_num; $i++) { $bank_deal_no = $sheetData[$i]['A']; $account_name = $sheetData[$i]['B']; $bank_card = $sheetData[$i]['C']; $deal_money = $sheetData[$i]['D']; $deal_time = $sheetData[$i]['E']; if(!empty($bank_deal_no) && !empty($account_name) && !empty($bank_card) && !empty($deal_money) && !empty($deal_time) ){ $import_data[$i]['bank_deal_no'] = $bank_deal_no; $import_data[$i]['account_name'] = $account_name; $import_data[$i]['bank_card'] = $bank_card; $import_data[$i]['deal_money'] = $deal_money; $import_data[$i]['deal_time'] = $deal_time; $import_data[$i]['create_time'] = $now_time; $import_data[$i]['update_time'] = $now_time; } } sort($import_data); if (empty($import_data)) { return json(['status' => '1', 'message' => '数据解析失败']); } $total_num = count($import_data); if ($total_num > 100) { return json(['status' => '1', 'message' => '数据超出限制，最多100条']); } //校验是否重复:交易流水号 $data_array = array_column($import_data, 'bank_deal_no'); $data_ids = implode(',', $data_array); $result_data = Db::name('user_bank_bill') ->field('bank_deal_no') ->where('bank_deal_no', 'in', $data_ids) ->select() ->toArray(); $error_message = ''; if (!empty($result_data)) { $result_data_array = array_column($result_data, 'bank_deal_no'); $result_data_ids = implode(',', $result_data_array); $error_message = '以下流水号有重复，已筛选出: '.$result_data_ids; foreach ($import_data as $key => $value) { if(in_array($value['bank_deal_no'],$result_data_array)){ unset($import_data[$key]); } } } if(!empty($import_data)){ //将数据保存到数据库 $res = Db::name('user_bank_bill')->insertAll($import_data); if ($res) { return json(['status' => '2', 'message' => '操作成功','result'=>$error_message]); } else { return json(['status' => '1', 'message' => '提交失败，请刷新重试']); } } return json(['status' => '2', 'message' => '数据错误','result' => $error_message]); }