Jolly

python实现excel导入以及excel导出功能
之前在做后台管理系统时候,有导出和导入excel的需求,其实不难,很快就用xlrd和xlwt实现了。
扫描右侧二维码阅读全文
07
2020/07

python实现excel导入以及excel导出功能

之前在做后台管理系统时候,有导出和导入excel的需求,其实不难,很快就用xlrd和xlwt实现了。

首先先安装xlrd和xlwt

import xlrd
import xlwt

导入excel业务实现

def xls_import():
    """学生导入"""
    form = validators.XlsImportStudent().validate_()
    file = request.files.get('file')
    filename = f'{uuid.uuid1().hex}.{form.file_extension.data}'

    # 保存文件
    file_path = 'inxlsx/' + filename
    path = os.getcwd() + '/plugins/public/'
    file_path = os.path.join(path, file_path)

    file.save(file_path + filename)

    data = open_excel(file=file_path + filename).sheets()[0]
    nrows = data.nrows
    pass_num = 0
    success_num = 0
    for i in range(1, nrows):
        # 判断学生是否存在
        student = Student.query.filter_by(name=data.row_values(i)[0], phone=data.row_values(i)[1]).first()
        if student:
            pass_num += 1
            pass
        else:
            # 数据处理
            # 性别
            gender = data.row_values(i)[5]
            if gender == "男":
                gender_int = 1
            else:
                gender_int = 2

            # 政治面貌
            political = data.row_values(i)[10]
            if political == "群众":
                political_int = 1
            elif political == "中共共青团员":
                political_int = 2
            elif political == "中共预备党员":
                political_int = 3
            elif political == "中共党员":
                political_int = 4
            else:
                political_int = 5

            # 最高学历
            education = data.row_values(i)[15]
            if education == "小学":
                education_int = 1
            elif education == "初中":
                education_int = 2
            elif education == "高中":
                education_int = 3
            elif education == "大专":
                education_int = 4
            elif education == "本科":
                education_int = 5
            elif education == "硕士研究生":
                education_int = 6
            elif education == "博士研究生":
                education_int = 7
            else:
                education_int = 8

            # 日期处理
            birthday = data.row_values(i)[4]
            # print(birthday)
            birthday = datetime.date.fromordinal(int(birthday) + 693594)
            # dt = datetime.date(1993, 4, 27)
            # print(dt.toordinal())

            graduated_time = data.row_values(i)[17]
            graduated_time = datetime.date.fromordinal(int(graduated_time) + 693594)

            # 学校和专业
            profession_code = data.row_values(i)[3]
            profession = Profession.query.filter_by(profession_code=profession_code).first()

            if profession:
                Student(
                    bh=uuid.uuid4().hex,
                    name=data.row_values(i)[0],
                    phone=data.row_values(i)[1],
                    id_card=data.row_values(i)[2],
                    school=profession.school_id,
                    profession=profession.id,
                    birthday=birthday,
                    gender=gender_int,
                    mail=data.row_values(i)[6],
                    clan=data.row_values(i)[7],
                    hometown=data.row_values(i)[8],
                    address=data.row_values(i)[9],
                    political=political_int,
                    health=data.row_values(i)[11],
                    birth_place=data.row_values(i)[12],
                    home_address=data.row_values(i)[13],
                    work_unit=data.row_values(i)[14],
                    education=education_int,
                    graduated_school=data.row_values(i)[16],
                    graduated_time=graduated_time,
                    avatar=data.row_values(i)[18],
                    source=2
                ).direct_add_()

                success_num += 1
            else:
                return result_format(data={'fail': '第' + str(i+1) + '条数据的专业编码不存在,请检查!'})

        Student.static_commit_()

    return ''

导出excel业务实现

def xls_out_port():
    """学生导出"""
    form = validators.XlsOutportStudent(request.args).validate_()

    query = Student.query.filter_by(status=1).order_by(Student.id.desc())

    if form.profession.data:
        query = query.filter_by(profession=form.profession.data)

    paginate = query.paginate(page=form.page.data, per_page=form.limit.data, error_out=True)

    funcs = [orm_func('change_school_info')]

    data = paginate_info(paginate=paginate, items=[item.serialization(funcs=funcs) for item in paginate.items])

    # 生成excel对象
    filename = xlwt.Workbook()
    sheet = filename.add_sheet("Sheet1")

    # 写入第一行数据
    row0 = ['姓名', '手机号', '身份证号', '报名学校', '报名专业', '出生日期',
            '性别', '邮箱', '民族', '籍贯', '通信地址', '政治面貌', '身体状况',
            '出生地', '家庭地址', '工作单位', '最高学历', '毕业学校', '毕业日期', '用户头像']
    for i in range(0, len(row0)):
        sheet.write(0, i, row0[i], set_style('sky_blue'))

    for index, item in enumerate(data['items']):
        row_start = 1 + index
        sheet.write(row_start, 0, item['name'], set_style())
        sheet.write(row_start, 1, item['phone'], set_style())
        sheet.write(row_start, 2, item['id_card'], set_style())
        sheet.write(row_start, 3, item['school'], set_style())
        sheet.write(row_start, 4, item['profession'], set_style())
        sheet.write(row_start, 5, item['birthday'].split(' ')[0], set_style())
        # 性别
        if item['gender'] == 1:
            gender = "男"
        else:
            gender = "女"
        sheet.write(row_start, 6, gender, set_style())
        sheet.write(row_start, 7, item['mail'], set_style())
        sheet.write(row_start, 8, item['clan'], set_style())
        sheet.write(row_start, 9, item['hometown'], set_style())
        sheet.write(row_start, 10, item['address'], set_style())
        # 政治面貌
        if item['political'] == 1:
            political = "群众"
        elif item['political'] == 2:
            political = "中共共青团员"
        elif item['political'] == 3:
            political = "中共预备党员"
        elif item['political'] == 4:
            political = "中共党员"
        else:
            political = "其他"
        sheet.write(row_start, 11, political, set_style())
        sheet.write(row_start, 12, item['health'], set_style())
        sheet.write(row_start, 13, item['birth_place'], set_style())
        sheet.write(row_start, 14, item['home_address'], set_style())
        sheet.write(row_start, 15, item['work_unit'], set_style())
        # 学历
        if item['education'] == 1:
            education = "小学"
        elif item['education'] == 2:
            education = "初中"
        elif item['education'] == 3:
            education = "高中"
        elif item['education'] == 4:
            education = "大专"
        elif item['education'] == 5:
            education = "本科"
        elif item['education'] == 6:
            education = "硕士研究生"
        elif item['education'] == 7:
            education = "博士研究生"
        else:
            education = "其他"
        sheet.write(row_start, 16, education, set_style())
        sheet.write(row_start, 17, item['graduated_school'], set_style())
        sheet.write(row_start, 18, item['graduated_time'].split(' ')[0], set_style())
        sheet.write(row_start, 19, item['avatar'], set_style())

    # 设置单元格宽度
    sheet.col(1).width = 4500
    sheet.col(2).width = 6500
    sheet.col(3).width = 3000
    sheet.col(4).width = 3000
    sheet.col(5).width = 3700
    sheet.col(7).width = 5500
    sheet.col(10).width = 5000
    sheet.col(11).width = 3000
    sheet.col(12).width = 3000
    sheet.col(13).width = 3000
    sheet.col(14).width = 5000
    sheet.col(15).width = 5000
    sheet.col(16).width = 3000
    sheet.col(17).width = 5000
    sheet.col(18).width = 3700
    sheet.col(19).width = 15000

    file_name = '奥翔教育学生信息-' + datetime.datetime.now().strftime("%Y%m%d%H%M%S%f") + '.xlsx'

    # 保存文件
    file_path = 'outxlsx/' + file_name
    path = os.getcwd() + '/plugins/public/'
    file_path = os.path.join(path, file_path)

    filename.save(file_path)

    return ''
Last modification:July 7th, 2020 at 02:08 pm
🌓