django 解析上传xls文件

django 解析上传xls文件

class DataUploadAPIView(APIView):
    # authentication_classes = (JSONWebTokenAuthentication, SessionAuthentication)
    # permission_classes = (IsAuthenticated,)
 
    @transaction.atomic
    def post(self, request, *arge, **kwargs):
        '''上传数据'''
        data = request.data
        # {'type': ['0'], 'table_name': ['16'], 'new_table_name': ['11'], 'field_name': ['id'], 'versions': ['0'], 'versions_name': ['222'], 'Fdata': [<InMemoryUploadedFile: drug_base_copy2.xls (application/vnd.ms-excel)>]}
        filed_name = request.data['Fdata']
        if not filed_name:
            raise Forbidden('没有获取到文件')
        destination = open(
            os.path.join(os.path.dirname(os.path.abspath(__file__)) + '\\excel_upload\\', filed_name.name),
            'wb+')  # 打开特定的文件进行二进制的写操作
        for chunk in filed_name.chunks():  # 分块写入文件
            destination.write(chunk)
        destination.close()
        type_name = data['type']
        table_name_id = data['table_name']
        new_table_name = data['new_table_name']
        field_name = data['field_name']
        versions = data['versions']
        versions_name = data['versions_name']
        table_name = 'database_data_' + DrugTables.objects.filter(pk=table_name_id)[0].table_name
        if int(type_name) == int(2):
            cursor = connection.cursor()
            cursor.execute(f'CREATE TABLE database_data_{new_table_name} LIKE {table_name};')
            table_datas = DrugTables.objects.filter(table_name=table_name.replace('database_data_', ''))[0]
            DrugTables.objects.create(table_name=new_table_name, table_comment=table_datas.table_comment,
                                      # drug_cls=table_datas.drug_cls, user_cls=table_datas.user_cls)
                                      drug_cls=table_datas.drug_cls, user_cls=request.user)
        if str(versions) == str(0):
            versions = versions_name
        # 读取文件数据
        workbook = xlrd.open_workbook(
            os.path.join(os.path.dirname(os.path.abspath(__file__)) + '\\excel_upload\\', filed_name.name))
        table = workbook.sheets()[0]
        # 获取总行数
        nrows = table.nrows
        head_data = []
        queryset = get_query_dawnload(connection, table_name, DATABASES['default']['NAME'])
        for sql_info in queryset:
            if sql_info['COLUMN_NAME'] != 'id' and sql_info['COLUMN_NAME'] != 'version':
                head_data.append(sql_info['COLUMN_NAME'])  # 设置标题字段
 
        # 从第三行开始
        cursor = connection.cursor()
        for x in range(2, nrows):
            row = table.row_values(x)
            table_data = {}
            # 获取数据字段
            for i, info in enumerate(head_data):
                ctype = table.cell(x, i).ctype
                if ctype == 2 and row[i] % 1 == 0:
                    table_data[info] = int(row[i])
                elif ctype == 3:
                    table_data[info] = xlrd.xldate_as_datetime(row[i], 0)
                else:
                    table_data[info] = str(row[i])
            if int(type_name) == int(0):
                list_key = []
                list_lalues = []
                for key, lalues in table_data.items():
                    list_key.append(key)
                    list_lalues.append("'" + str(lalues) + "'")
                # 拼接sql语句
                sql_insert = 'insert into {}({}, version) values({}, "{}")'.format(table_name,
                                                                                   ', '.join(list_key),
                                                                                   ', '.join(list_lalues), versions)
            elif int(type_name) == int(1):
                list_lalues = []
                field_value = ''
                for key, lalues in table_data.items():
                    list_lalues.append(key + '=' + "'" + str(lalues) + "'")
                    if key == field_name:
                        field_value = lalues
                lalues = ','.join(list_lalues)
                sql_insert = f'update {table_name} set {lalues} where {field_name}="{field_value}" and version="{versions}"'
 
            elif int(type_name) == int(2):
                list_key = []
                list_lalues = []
                for key, lalues in table_data.items():
                    list_key.append(key)
                    list_lalues.append("'" + str(lalues) + "'")
                sql_insert = 'insert into database_data_{}({}, version) values({}, "{}")'.format(new_table_name,
                                                                                                 ', '.join(list_key),
                                                                                                 ', '.join(list_lalues),
                                                                                                 versions)
 
            try:
                cursor.execute(sql_insert)
            except Exception as e:
                raise Forbidden('上传解析失败,第{}指端长度超索引, 错误类型:{}'.format(x + 1, e))
        return HttpResponse(json.dumps({"status": "上传数据成功", 'data': filed_name.name}), status=status.HTTP_200_OK)