python工具之xls解析

导入开发包

pip install xlrd==1.2.0

解析xls文件

# filename 文件路径
# file_contents 文件上传解析文件内容
def parse_xls(filename=None, file_contents=None):
    result = {"error": {}, "data_list": [], "model_name": ""}
    work_sheet = None
    try:
        if filename:
            work_sheet = xlrd.open_workbook(filename)
        elif file_contents:
            work_sheet = xlrd.open_workbook(filename=None,
                                            file_contents=file_contents)
        tb = work_sheet.sheets()[0]
        if tb.nrows > RES_LIMIT:
            raise Exception("当前xls上传的数据不能超过%d行", RES_LIMIT)
        name, datas = translate_xlsx_field_2_string(work_sheet)
        result['data_list'] = datas
        result['model_name'] = RES_MAPPING.get(name)
    except Exception as e:
        logger.exception(e)
        result['error'] = str(e)
    return result

解析内容

def translate_xlsx_field_2_string(work_sheet):
    rows_data = []
    tb = work_sheet.sheets()[0]
    n_row = tb.nrows
    # 第一条为标题，不要了
    for i in range(1, n_row):
        one_row = []
        for j in range(0, len(tb.row(i))):
            if tb.cell(i, j).ctype in [2, 4]:
                value = str(tb.cell_value(i, j)).strip()
                one_row.append(value if value else None)
            elif tb.cell(i, j).ctype == 0:
                one_row.append(None)
            elif tb.cell(i, j).ctype == 3:
                date_v = xlrd.xldate_as_tuple(tb.cell_value(i, j), work_sheet.datemode)
                one_row.append(
                    datetime.datetime(*date_v).strftime("%Y-%m-%d %H:%M:%S"))
            else:
                value = tb.cell_value(i, j).strip()
                one_row.append(value if value else None)
        # 空行没数据
        if one_row.count(None) == len(one_row):
            continue
        rows_data.append(one_row)
    return tb.name, rows_data

例子

## demo01
for single_xls in request.FILES:
    rs = parse_xls(filename=None, file_contents=request.FILES[single_xls].read())
    if rs['error']:
        errors_info.append({"xls_name": request.FILES[single_xls].name, "error": rs['error']})
    # 校验资源在对应的后台是否存在冲突
    data_json = rs['data_list']
    model_name = rs['model_name']
    model_data[model_name] = data_json

## demo02
base_dir = os.path.abspath(os.path.join(os.getcwd()))
xls_path = "/".join([base_dir, UPLOAD_PATH, str(_task_id), folder_name, file_name])
logger.info("the xls_path = %s", xls_path)
# 读取资源文件
result = parse_xls(filename=xls_path)