一个通用的导入导出excel的思路
本文中的代码示例都使用的Yii2,其他框架可能不适用,不过可以参考下思路。
本人经常给企业做项目,excel导入导出的需求实在是不少,最近又收到了excel导出的一个需求,看项目组的小伙又是焦头烂额的,决定优化一下实现一个通用的导入导出的方法。由于精力有限,本文仅提供一个大概的思路,有些功能还未能完全实现。
当前导入导出的一些问题
下面列出了多年了,系统中的导入导出功能的痛点和解决的方案:
问题 | 解决思路 |
---|---|
导出的excel如果客户要调整字段,非常麻烦,因为要依赖于硬编码,而且要同时改导入模板 | 可以根据字段动态导出excel模板 |
具备导出功能的接口遍布系统各处,难以统一管理 | 所有导出的地方应该一个地方统一管理,动态开关 |
导入的模板不是动态生成的,每次字段修改都需要重新上传模板 | 空白模板应该由程序动态生成 |
用户无法确定导入的内容范围,同时无法定位具体的错误行数,无法实现部分导入功能(即只导入数据验证正确的数据,不导入错误的) | 提供导入预览功能 |
研发经验无法积累,每做一个导出功能都是重新开发 | 提供通用方法给前端,长期维护一套导出代码 |
用户导入失败时数据不入库,只能用户本人发excel过来发现问题 | excel导入时,数据校验失败的也应存储,方便查证问题 |
数据量大时,导出时间太久 | 导出与下载应该分离开 |
模版更新后,用户使用旧版模版导入时报错 | 应提供模版版本检测 |
而其中还需要注意的就是导入的本质其实就是表单,应该跟页面上的表单验证走同一个方法同等的校验,而不是单独校验,导致两边的校验规则不一(这个实际中遇到过,导入时直接操作数据库,不做同等导致比页面上的表单权限大,产生错误),导出的本质就是从数据库显示数据而已。
表设计
为了解决上面的问题,我想到的思路就是利用数据库记录导入导出的字段和导入的日志。
这里面我用三个表实现这个需求:
1 导入日志表
这个表格比较简单,phase记录一个uuid,一次导入动作记录一次,field对应excel中的字母列,row对应行数,value就是值,category用于区分不同的导入模版:
2 活动记录表
这个表也非常简单,excel_name这里记录的就是excel文件的名称,parent_id sheet_name字段这里是考虑到了导出多个sheet到一个excel中的需求。重点关注model字段,这个是记录Yii active record表的命名空间的路径字段。
3 字段规则表
这个表比较关键,这里记录了导入导出的规则。考虑导出的话,我们使用field字段作为字母列,original_field
是模型的动态属性,同时因为会有关联数据所以我们应该支持gmm->project_id
这种写法,意思就是取关联对象中的某一属性。
代码实现
下面是一些代码实现的示例,重点提供一个思路,有些考虑还不到位。,
导出空白模板方法
这个是读取数据库中设置的规则,导出一个空白模板供用户填入信息,同时设置了一些简单的样式,例如合并单元格显示表格标题,有数据的列都是具备边框。
public function actionTemplate()
{
// 查到规则表 是否存在
$params = Yii::$app->request->get();
$model = GmmImportField::find()->where(['category'=>$params['category'], 'type' => 1])->all();
$table = GmmImport::find()->where(['category'=>$params['category']])->one();
if(!$table){
throw new ServerErrorHttpException('未设定导入模版');
}
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->getRowDimension(1)->setRowHeight(40);
// 设置大标题的样式
$ATitle = $sheet->getCell('A1'); /
$ATitle->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER); // 内容水平居中
$ATitle->getStyle('A1')->getFont()->setSize(22)->setBold(true); // 字体大小,加粗
$ATitle->setValue($table->excel_name);
// 设置有数据的列都增加边框,同时设置prompt字段
$col_start = 'A';
foreach($model as $index=>$item){
$sheet->setCellValue($item->field.'2', $item->label);
$validation = ($sheet->getCell($item->field.'2'))->getDataValidation();
$validation->setShowInputMessage(true);
$validation->setPrompt($item->prompt);
$column = $sheet->getColumnDimension($item->field);
$column->setWidth(20);
if($index==0){
$col_start = 'A';
}else{
$col_start++;
}
}
// 设置边框
$sheet->getStyle('A1:'.$col_start.'22')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
$sheet->mergeCells('A1:'.$col_start.'1'); // 合并单元格
// 操作完成导出excel下载到浏览器
$writer = new Xlsx($spreadsheet);
$filename = $table->excel_name.".xlsx";
header('Content-Type:application/vnd.ms-excel');
header('Content-Disposition:attachment;filename=' . $filename);
header('Cache-Control:max-age=0');
$writer->save('php://output');
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
exit;
}
导入预览功能的实现
这部分代码中,我们将实现两个功能,一个是将前端传来的excel先暂存到导入记录表中,先不论数据验证通过与否全部入库,同时将这些数据按照返回给前端,我们这边里商定的是使用and design的表格的数据格式。
/**
* 导入预览
* 存入导入记录表中,读取出数据
*/
public function actionImportPreSheet()
{
$params = Yii::$app->request->get();
$reader = new \PhpOffice\PhpSpreadsheet\Reader\Xlsx();
$spreadsheet = $reader->load($_FILES['file']['tmp_name']);
$field_group = GmmImport::find()->where(['category'=>$params['category']])->one();
if($field_group){
$field_group_child = GmmImport::find()->where(['parent_id'=>$field_group->id])->orWhere(['id'=>$field_group->id])->all();
}else{
$field_group_child = $field_group;
}
$batchArray = [];
# 期数
$phase = Uuid::uuid4()->toString();
foreach ($field_group_child as $k=>$v) {
$fields=GmmImportField::findAll(['category'=>$v->category, 'type' => 1]);
$last_letter= 'A';
foreach($fields as $field){
$last_letter ++;
}
$spreadsheet->setActiveSheetIndex($k);
$sheetDataCount = $spreadsheet->getActiveSheet()->toArray();
$last_row =count($sheetDataCount);
$sheetData = $spreadsheet->getActiveSheet()->rangeToArray(
'A3:'.$last_letter.$last_row,
null, // Value that should be returned for empty cells
true, // Should formulas be calculated (the equivalent of getCalculatedValue() for each cell)
true, // Should values be formatted (the equivalent of getFormattedValue() for each cell)
true
);
$fields=[];
foreach (array_values($sheetData)[0] as $key=>$item) {
$fields[]=$key;
}
foreach ($sheetData as $key =>$item) {
$item_arr = array_values($item);
if (empty(array_filter($item_arr))) {
continue;
}
$num =0;
foreach ($fields as $field) {
$batchArray[] =[
'field'=>$field,
'value'=>$item_arr[$num],
'row'=>$key,
'phase'=>$phase,
'operator_id'=>Yii::$app->user->id,
'category'=>$v->category,
];
$num +=1;
}
}
}
Yii::$app->db->createCommand()->batchInsert('gmm_import_model_log', ['field', 'value','row','phase','operator_id','category'], $batchArray)->execute();
$rows = GmmImportLog::findAll(['phase'=>$phase]);
$rows_arr = ArrayHelper::index(ArrayHelper::toArray($rows),null,'category');
$dataSourceArr = [];
foreach ($rows_arr as $category => $sheet_item) {
$sheet_model = GmmImport::findOne(['category'=>$category]);
$row = ArrayHelper::index(ArrayHelper::toArray($sheet_item), null, 'row');
$dataSourceArrOne = [];
foreach ($row as $item) {
$dataSource = [];
foreach ($item as $v) {
$dataSource[$v['field']] =$v['value'];
}
$multi_rows_arr[] = $dataSource;
}
$dataSourceArrOne['dataSource']=$multi_rows_arr;
$dataSourceArrOne['category'] = $sheet_item[0]['category'];
$dataSourceArrOne['sheet_name'] = $sheet_model->sheet_name;
$dataSourceArr[] = $dataSourceArrOne;
}
$columns=GmmImportFieldIndex::find()->where(['like','category',$params['category'],'type'=>1])->orderBy(['field'=>SORT_ASC])->all();
$columns_arr = ArrayHelper::index(ArrayHelper::toArray($columns),null,'category');
foreach($dataSourceArr as $key =>&$item){
$dataSourceArr[$key]['columns'] = $columns_arr[$item['category']];
unset($item['category']);
}
return $dataSourceArr;
return [
'dataSource'=>$dataSourceArr,
'columns'=>$columns,
'phase'=>$phase
];
}
实际导入功能
由于上面我们将导入预览和真正的导入分开了,这里还需要实现一个真正往业务数据库中写入数据的方法
public function actionImportConfirm()
{
$params = Yii::$app->request->post();
$rows = GmmImportLog::findAll(['phase'=>$params['phase']]);
if($rows == null){
throw new BadRequestHttpException('找不到导入实体');
}
$rows_arr = ArrayHelper::index(ArrayHelper::toArray($rows),null,'row');
$dataSourceArr = [];
foreach($rows_arr as $item){
$dataSource = [];
foreach($item as $v){
$dataSource[$v['target_field']] =$v['value'];
}
$dataSourceArr[] = $dataSource;
}
$target_model = GmmImport::findOne(['category'=>$rows[0]->category]);
$target_model_class = $target_model->model;
foreach($dataSourceArr as $item){
if ($item['UUID']) {
$model = $target_model_class::find()->where(['UUID' => $item['UUID']])->one();
if(!$model){
throw new BadRequestHttpException('未找到对应UUID');
}
} else {
$model = new $target_model_class;
$item['UUID'] = Uuid::uuid4()->toString();
}
$model->scen = 'import';
$model->load($item,'');
if(!$model->save()){
throw new BadRequestHttpException(implode(' ', $model->getFirstErrors()));
}
}
return true;
}
这里是excel的导出功能,目前实现的是单个sheet的导出,多个sheet导出功能也类似,但是还不是非常完善就不贴了。注意这里导出时,仍需要增加上导出空白模板时设置的必填项规则和下拉选项等,因为有时用户需要导出一组数据,然后在这组数据上面改一下,再导入回去,这也要支持。
public function actionExportExcel()
{
$params = Yii::$app->request->get();
$model = GmmImportField::find()->where(['category'=>$params['category'],'type'=>1])->all();
$table = GmmImport::find()->where(['category'=>$params['category']])->one();
if(!$table){
throw new ServerErrorHttpException('未设定导出模版');
}
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->getRowDimension(1)->setRowHeight(40);
// 设置标题
$ATitle = $sheet->getCell('A1'); // 获取单元格
$ATitle->getStyle('A1')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER); // 内容水平居中
$ATitle->getStyle('A1')->getFont()->setSize(22)->setBold(true); // 字体大小,加粗
$ATitle->setValue($table->excel_name);
$col_start = 'A';
foreach($model as $index=>$item){
$sheet->setCellValue($item->field.'2', $item->label??'');
$validation = ($sheet->getCell($item->field.'2'))->getDataValidation();
$validation->setShowInputMessage(true);
if(!empty($item->prompt)){
$validation->setPrompt($item->prompt);
}
$column = $sheet->getColumnDimension($item->field);
$column->setWidth(20);
if($index==0){
$col_start = 'A';
}else{
$col_start++;
}
}
$field_list = ArrayHelper::toArray($model);
// $field_arr = array_column($field_list,'original_field');
$field_arr =[];
foreach($field_list as $k=>$item){
$field_arr = array_merge($field_arr,[trim($item['original_field'])=>trim($item['field'])]);
}
$rows = $table->model::findItems();
$count = count($rows);
$count = $count+2;
$start_row = 3;
foreach($rows as $index => $item_obj){
$items= $item_obj->toArray(array_keys($field_arr));
foreach($items as $k=>$v){
$sheet->setCellValue($field_arr[$k].$start_row, $v);
$sheet->setCellValue($field_arr['row_number'].$start_row, $index + 1);
}
$start_row ++;
}
// 设置边框
$sheet->getStyle('A1:'.$col_start.$count)->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);
$sheet->mergeCells('A1:'.$col_start.'1'); // 合并单元格
$this->outputFile($spreadsheet,$table->excel_name.".xlsx");
$writer = new Xlsx($spreadsheet);
header('Content-Type:application/vnd.ms-excel');
header('Content-Disposition:attachment;filename=' . $filename);
header('Cache-Control:max-age=0');
$writer->save('php://output');
$spreadsheet->disconnectWorksheets();
unset($spreadsheet);
exit;
}
模型的实现
这里面我为了简洁,删减了大部分字段,其中需要给外注意的是这里面实现了一个接口,这个接口里面要求每个模型都需要具备两个方法getId()
和findItems()
,getId()
是获取原始表的主键ID,findItems
可以设置查询条件,例如要查询那些特定的数据等等。
<?php
namespace app\models;
class GmmCrawlProject extends AreaProject implements GmmPushTemplate
{
public function fields()
{
return [
'STAGE_ID',
];
}
public function getSTAGE_ID()
{
return $this->statusCode->code;
}
public function getId()
{
return $this->id;
}
public static function findItems($ids)
{
return self::find()->where(['lev'=>4,'status'=>1,'project_type'=>[1,2],'id'=>$ids]);
}
}
本作品采用《CC 协议》,转载必须注明作者和本文链接
推荐文章: