django 解析上传xls文件

1.解析上传数据

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)