使用xlrd模块读取excel数据

简介

xlrd是读取excel表格数据；
支持 xlsx和xls 格式的excel表格；

小知识

xls ：xls为Excel2003版本的扩展名，支持最大行数为65536，在Excel中按下Ctrl + ↓可到达最大行的位置
xlsx：xlsx为Excel2007版本之后的拓展名，Excel2013支持最大行数为1048576

0.导入xlrd模块

import xlrd

1.创建 Workbook

wb = xlwt.Workbook()

2.创建 worksheet

ws = wb.add_sheet(‘test_sheet’)

3.写入第一行内容 ws.write(a, b, c) a：行，b：列，c：内容

ws.write(0, 0, ‘球队’)
ws.write(0, 1, ‘号码’)
ws.write(0, 2, ‘姓名’)
ws.write(0, 3, ‘位置’)

保存文件

wb.save(’./myExcel.xls’)

安装

pip install xlrd

示例

import os
import xlrd
from rest_framework.views import APIView

from common.returnFormat import info_format
from common.errors import *
from inspectionModule.models import *
from publicModule.models import Supplier, Component, ComponentModel


class BatchImportAPI(APIView):

    def post(self, request):
        file = request.data.get('file', '')
        state = request.data.get('state')
        if not file or not state:
            return info_format(code=PARAMS_NOT_COMPLETE, msg='参数不全！')
        if state not in ['1', '2']:
            return info_format(code=PARAMS_IS_INVALID, msg='参数错误！')
        warehouse_excel_path = os.path.join(BASE_DIR,
                                            'static/inspectionModule/warehouse_account_excel/warehouse_account.xls')
        with open(warehouse_excel_path, 'wb') as f:
            for chunk in file.chunks():
                f.write(chunk)
        warehouse_excel = xlrd.open_workbook(warehouse_excel_path)
        warehouse_sheet = warehouse_excel.sheet_by_name('warehouse_account')
        rows = warehouse_sheet.nrows
        if rows < 2:
            return info_format(code=PARAMS_IS_INVALID, msg='导入的excel数据为空！')
        warehouse_account_list = []
        for i in range(1, rows):
            row_data = warehouse_sheet.row_values(i)
            for j in range(len(row_data)):
                if not row_data[j]:
                    return info_format(code=PARAMS_IS_INVALID, msg=f'第{i}行第{j+1}列数据不能为空！')
            try:
                arrival_of_goods_date = xlrd.xldate_as_datetime(row_data[0], 0).strftime('%Y-%m-%d')
            except:
                return info_format(DATA_IS_WRONG, msg=f'第{i}行到货日期数据错误！')
            supplier = Supplier.objects.filter(name=row_data[1]).first()
            if not supplier:
                return info_format(code=DATA_NOT_FOUND, msg=f'第{i}行供应商不存在！')
            component_model = ComponentModel.objects.filter(name=row_data[2]).first()
            if not component_model:
                return info_format(code=DATA_NOT_FOUND, msg=f'第{i}行零件型号不存在！')
            component = Component.objects.filter(component_material=row_data[3]).first()
            if not component:
                return info_format(code=DATA_NOT_FOUND, msg=f'第{i}行零件不存在！')
            if state == '1':
                warehouse_accounts = RoughcastAccount.objects.filter(arrival_of_goods_date=arrival_of_goods_date,
                                                                     supplier_id=supplier.id,
                                                                     component_model_id=component_model.id,
                                                                     component_id=component.id,
                                                                     steel_furnace_number=row_data[4],
                                                                     batch_number=row_data[5],
                                                                     heat_treatment_batch_number=row_data[6],
                                                                     batch_count=int(row_data[7]),
                                                                     unit_price=row_data[8])
                if warehouse_accounts:
                    return info_format(code=DATA_ALREADY_EXISTED, msg=f'第{i}行数据已存在！')
                warehouse_account = RoughcastAccount(arrival_of_goods_date=arrival_of_goods_date,
                                                     supplier_id=supplier.id,
                                                     component_model_id=component_model.id,
                                                     component_id=component.id,
                                                     steel_furnace_number=row_data[4],
                                                     batch_number=row_data[5],
                                                     heat_treatment_batch_number=row_data[6],
                                                     batch_count=int(row_data[7]),
                                                     unit_price=row_data[8])
            else:
                warehouse_accounts = AccessoriesAccount.objects.filter(arrival_of_goods_date=arrival_of_goods_date,
                                                                       supplier_id=supplier.id,
                                                                       component_model_id=component_model.id,
                                                                       component_id=component.id,
                                                                       material_manufacturer=row_data[4],
                                                                       batch_number=row_data[5],
                                                                       value=row_data[6],
                                                                       batch_count=int(row_data[7]),
                                                                       unit_price=row_data[8])
                if warehouse_accounts:
                    return info_format(code=DATA_ALREADY_EXISTED, msg=f'第{i}行数据已存在！')
                warehouse_account = AccessoriesAccount(arrival_of_goods_date=arrival_of_goods_date,
                                                       supplier_id=supplier.id,
                                                       component_model_id=component_model.id,
                                                       component_id=component.id,
                                                       material_manufacturer=row_data[4],
                                                       batch_number=row_data[5],
                                                       value=row_data[6],
                                                       batch_count=int(row_data[7]),
                                                       unit_price=row_data[8])
            warehouse_account_list.append(warehouse_account)
        if state == '1':
            RoughcastAccount.objects.bulk_create(warehouse_account_list)
        else:
            AccessoriesAccount.objects.bulk_create(warehouse_account_list)
        return info_format(msg='导入成功！')