django导入excel文件使用pandas处理并批量插入
导入库：
import pandas as pd
import xlwt
from io import BytesIO #io数据流
django视图类
class ImportFarmerData(View):
def post(self,request):
excel_raw_data = pd.read_excel(request.FILES.get('file',''),header=None)
删除第一行的标题
获取每列
excel_raw_data.drop([0,0],inplace=True)
name_col = excel_raw_data.iloc[:,[0]]
card_id_col = excel_raw_data.iloc[:,[1]]
phone_col = excel_raw_data.iloc[:,[2]]
area_num_col = excel_raw_data.iloc[:,[3]]
对每一列数据进行处理，从DataFrame类型转换为list类型
name_list = name_col.values.tolist()
card_id_list = card_id_col.values.tolist()
phone_list = phone_col.values.tolist()
对每一列的每一行的数据进行转换，转换为str类型
for i in range(len(name_list)):
name_list_index = name_list[i]
card_id_list_index = card_id_list[i]
phone_list_index = phone_list[i]
area_num_index = area_num_list[i]
farmer_profile = FarmersProfile()
farmer_profile.name = name_list_index[0]
farmer_profile.card_id = card_id_list_index[0]
farmer_profile.phone = phone_list_index[0]
farmer_profile.area_num = area_num_index[0]
farmer_profile.address_id = address.id
farmer_profile.save()
return HttpResponse(json.dumps({'code':'200','msg':'导入成功'})
由于前端使用leiui返回格式必须为json格式
HTML：
<button type="button" class="layui-btn" id="test4" name="excel_data"><i class="layui-icon"></i>导入excel</button>
ajax:
layui.use('upload', function(){
var $ = layui.jquery,
upload = layui.upload;
//指定允许上传的文件类型
upload.render({ //允许上传的文件后缀
elem: '#test4',
type: 'post',
url: '{% url 'users:import_famer' %}',
accept: 'file', //普通文件,
exts: 'xls', //只允许上传压缩文件,
data: {'csrfmiddlewaretoken': '{{ csrf_token }}'},
done: function(res) {
if (res.code == 200 ) {
layer.msg(res.msg);
}
}
,error:function (res) {
}
});
