Django处理Excel,CSV文件

1 .处理Excel文件：

• 通过前台传入Excel文件进行解析

 

import xlrd

 def create(self, request, *args, **kwargs):
        try:
            with transaction.atomic():
                files = request.data
                file = files['file']
                wb = xlrd.open_workbook(filename=None, file_contents=file.read())  # 关键点在于这里，file_contents为字符串流
                table = wb.sheets()[0]   #获取第一个sheet，可根据实际修改（下标或sheet名）
                nrows = table.nrows  # 行数
                list = []

                for i in range(1, nrows):     #第一行为表头，选择跳过
                    rowValues = table.row_values(i)  # 一行的数据
                    # 由于前台传的日期变为float型，转换成约定格式
                    t = xlrd.xldate_as_tuple(rowValues[0], 0)   
                    date = datetime.datetime(*t)
                    cell = date.strftime('%Y-%m-%d %H:%M:%S')  
                    operatorId = str(int(rowValues[3]))
                    list.append(models.ThicknessDataModel(DateTime=cell, ProductName=rowValues[1], TimeNode=rowValues[2]
                                                          , OperatorId=operatorId, SampleSN=rowValues[4], FixtureSn=rowValues[5]
                                                          , result=rowValues[6], Thickness=rowValues[7], Voltage=rowValues[8]
                                                          , Impedance=rowValues[9]))
                    # models.ThicknessDataModel.objects.create(DateTime=cell, ProductName=rowValues[1], TimeNode=rowValues[2]
                    #                                       , OperatorId=rowValues[3], SampleSN=rowValues[4], FixtureSn=rowValues[5]
                    #                                       , result=rowValues[6], Thickness=rowValues[7], Voltage=rowValues[8]
                    #                                       , Impedance=rowValues[9])
                models.ThicknessDataModel.objects.bulk_create(list)
                return ReturnData(statusCode=status.HTTP_200_OK, message=_('成功'))

        except Exception as e:
            logger_supplier.error(e)
            return ReturnData(statusCode=status.HTTP_400_BAD_REQUEST, message=_('失败'))

2. 处理CSV文件：

• 解析本地文件

 

    def create(self, request, *args, **kwargs):
        try:
            with transaction.atomic():
                with open(os.path.join(os.path.dirname(os.path.abspath(__file__)) +'/厚度.csv')) as f_csv:     #打开本地文件
                    table = csv.reader(f_csv)   #使用CSV读入文件
                    list = []
                    headings = next(table)    #跳过第一行表头栏
                    for rowValues in table:
                        print(rowValues)
                        # rowValues = table.row_values(i)  # 一行的数据
                        # 由于前台传的日期变为2019-3-22 14:21，转换成约定格式
                        cell = datetime.datetime.strptime(rowValues[0], "%Y-%m-%d %H:%M").strftime(
                            "%Y-%m-%d %H:%M:%S")
                        list.append(models.ThicknessDataModel(DateTime=cell, ProductName=rowValues[0], TimeNode='test'    #rowValues[2]
                                                              , OperatorId=rowValues[1], SampleSN=rowValues[2], FixtureSn=rowValues[3]
                                                              , result=rowValues[4], Thickness=rowValues[5], Voltage=rowValues[6]
                                                              , Impedance=rowValues[7]))
                    models.ThicknessDataModel.objects.bulk_create(list)
                return ReturnData(statusCode=status.HTTP_200_OK, message=_('成功'))

        except Exception as e:
            logger_supplier.error(e)
            return ReturnData(statusCode=status.HTTP_400_BAD_REQUEST, message=_('失败'))

• 解析前台传入的CSV文件

 

    def create(self, request, *args, **kwargs):
        try:
            with transaction.atomic():
                files = request.data
                csv_file = request.FILES['file']
                csv_file.seek(0)     #将光标聚焦到文件开头
                file_data = csv_file.read().decode('utf-8')
                lines = file_data.split('\r')
                list=[]
                for line in lines[1:]:     #从第二行开始  跳过第一行表头
                    rowValues = line.split(',')  # 一行的数据
                        # 由于前台传的日期变为2019-3-22 14:21，转换成约定格式
                    cell = datetime.datetime.strptime(rowValues[0], "%Y-%m-%d %H:%M").strftime(
                        "%Y-%m-%d %H:%M:%S")
                    list.append(models.ThicknessDataModel(DateTime=cell, ProductName=rowValues[0].strip() , TimeNode='test'    #rowValues[2]
                                                          , OperatorId=rowValues[1].strip() , SampleSN=rowValues[2].strip(), FixtureSn=rowValues[3].strip()
                                                          , result=rowValues[4].strip(), Thickness=rowValues[5].strip(), Voltage=rowValues[6].strip()
                                                          , Impedance=rowValues[7].strip()))
                    models.ThicknessDataModel.objects.bulk_create(list)
                return ReturnData(statusCode=status.HTTP_200_OK, message=_('成功'))

        except Exception as e:
            logger_supplier.error(e)
            return ReturnData(statusCode=status.HTTP_400_BAD_REQUEST, message=_('失败'))

转载自：https://www.jianshu.com/p/0d15ed85df2b