django导入excel文件使用pandas处理并批量插入

导入库：

1
2
3

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：

1	<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) {
                  
              }
          });