Django设计批量导入Excel数据接口(包含图片)

示例xlsx文件

接口详情

前端上传FormData

随便弄个上传按钮即可

这里上传我用的是ElementUI自带的action(懒)

<el-uploadclass="upload-Excel-demo"name="Excel_File":action=$config.BASEURL+$config.ExcelUploadURL:with-credentials=true:on-success="handleSuccess"multiple:file-list="ExcelFileList"><el-button size="small">Excel批量导入</el-button>
</el-upload>

后端APIView

class UploadExcelApiView(APIView):def post(self, request, *args, **kwargs):# 上传Excel文件excel_file = request.FILES.get('Excel_File', None)print(excel_file)# 验证文件格式file_extension = os.path.splitext(excel_file.name)[1]assert file_extension.lower() in ['.xlsx', '.xls'], "不支持的文件格式，请上传 .xlsx 或 .xls 文件"# 定义一个允许的字段字典allowed_fields = {'男/女': '', '款号': '', '产品类型': '', '针型': '', '克重': '', '材质': '', '针法': '','织机时间': '', '描述': '', '工艺师': '', '生产日期': '', '产品图片': ''}# 读取Excel文件并将每个sheet表格放入列表中wb = openpyxl.load_workbook(excel_file)sheet_list = [wb[sheet] for sheet in wb.sheetnames]# 定义临时变量 存储数据对象garment_knowledge_objects = []garment_image_objects = []try:for sheet in sheet_list:# 验证表头是否正确first_row = next(sheet.iter_rows(values_only=True))print(f'first_row: {first_row}')allowed_fields = xlsx_first_row_validator(allowed_fields, first_row)print(f'allowed_fields: {allowed_fields}')# 提取图片for img in sheet._images:# 图片转base64 以便上传到服务器img_file = xlsx_img_to_base64(img)img_row = img.anchor._from.row  # 图片所在行img_col = img.anchor._from.col  # 图片所在列# 格式化数据formatted_data = extract_and_format_date(sheet.cell(row=img_row + 1, column=(first_row.index(allowed_fields['生产日期'])) + 1).value)formatted_gender = format_gender_handler(sheet.cell(row=img_row + 1, column=(first_row.index(allowed_fields['男/女'])) + 1).value)product_id = sheet.cell(row=img_row + 1, column=(first_row.index(allowed_fields['款号'])) + 1).valueprint(f'正在上传{product_id}')# GarmentKnowledge就是自己创建的模型类g_obj, created = GarmentKnowledge.objects.update_or_create(product_id=product_id,defaults={'user': request.user,'product_type': sheet.cell(row=img_row + 1,column=(first_row.index(allowed_fields['产品类型'])) + 1).value,'needle_type': sheet.cell(row=img_row + 1,column=(first_row.index(allowed_fields['针型'])) + 1).value,'knitting_time': sheet.cell(row=img_row + 1,column=(first_row.index(allowed_fields['织机时间'])) + 1).value,'gram_weight': sheet.cell(row=img_row + 1,column=(first_row.index(allowed_fields['克重'])) + 1).value,'composition': sheet.cell(row=img_row + 1,column=(first_row.index(allowed_fields['材质'])) + 1).value,'technologist': sheet.cell(row=img_row + 1,column=(first_row.index(allowed_fields['工艺师'])) + 1).value,'description': sheet.cell(row=img_row + 1,column=(first_row.index(allowed_fields['描述'])) + 1).value,'manufactured_at': formatted_data,'pattern': sheet.cell(row=img_row + 1,column=(first_row.index(allowed_fields['针法'])) + 1).value,'gender_type': formatted_gender,})img_obj = GarmentImagesFile(garment_knowledge=g_obj, image=img_file)garment_knowledge_objects.append(g_obj)garment_image_objects.append(img_obj)# 批量创建对象GarmentKnowledge.objects.bulk_create(garment_knowledge_objects)GarmentImagesFile.objects.bulk_create(garment_image_objects)print('上传成功')except Exception as e:print(f'错误信息：{e}')return APIResponse(code_msg='error', detail=e, data=None, status=status.HTTP_400_BAD_REQUEST)return APIResponse(code_msg='success', detail='上传成功', data=None, status=status.HTTP_200_OK)

调用函数

import io
import refrom django.core.files.uploadedfile import InMemoryUploadedFiledef format_gender_handler(gender_str):if gender_str.lower() == '男':return '男装'elif gender_str.lower() == '女':return '女装'else:return Nonedef extract_and_format_date(date_str):date_str = str(date_str)# 使用正则表达式提取字符串中的第一个四位数年份、月份和日期match = re.search(r'(\d{4}).*?(\d{1,2}).*?(\d{1,2})', date_str)if match:year = match.group(1)  # 提取年份month = match.group(2).zfill(2)  # 提取月份，补全两位数day = match.group(3).zfill(2)  # 提取日期，补全两位数return f"{year}-{month}-{day}"else:raise ValueError("无法从输入中提取日期")def xlsx_img_to_base64(img_obj):"""img_obj必须是一个xlsx文件中的图片对象例：sheet._images"""img_bytes = io.BytesIO(img_obj._data())img_bytes.seek(0)img_file = InMemoryUploadedFile(file=img_bytes,  # 图像字节流field_name=None,  # 字段名，保持为 Nonename='image.png',  # 使用图像对象的名字或默认名content_type='image/png',  # 根据实际图像格式设置内容类型size=img_bytes.getbuffer().nbytes,  # 获取图像字节大小charset=None  # 字符编码，通常不需要)return img_filedef xlsx_first_row_validator(allowed_fields, first_row):"""first_row必须是一个由表头字段组成的可遍历对象例：('款号', '产品类型', '针型', '克重(克)', '材质', '针法')返回值allowed_fields将会更新为由规定字段和接收字段组成的字典例：{'款号': '款号', '产品类型': '产品类型', '针型': '针型', '克重': '克重'}"""for first_cell in first_row:for key, value in allowed_fields.items():if key in first_cell or first_cell in key:allowed_fields[key] = first_cellreturn allowed_fieldsfor key, value in allowed_fields.items():if key in first_cell or first_cell in key:allowed_fields[key] = first_cellreturn allowed_fields