读取写入excel例子1

[python]代码库

# 处理人员信息

import xlrd, xlwt

people = []


def start():
    data = xlrd.open_workbook(r"D:/people.xlsx")
    table = data.sheet_by_name(r"Sheet1")
    title = table.row_values(0)
    nrows = table.nrows
    for h in range(1, nrows):
        values = table.row_values(h)
        people.append(dict(zip(title, values)))


def t1():
    workbook = xlwt.Workbook(encoding='utf-8')
    worksheet = workbook.add_sheet('My Worksheet')
    style = xlwt.XFStyle()  # 初始化样式
    font1 = xlwt.Font()  # 为样式创建字体
    font1.name = '黑体'
    font1.height = 20 * 20
    font1.bold = True
    font2 = xlwt.Font()  # 为样式创建字体
    font2.name = '宋体'
    font2.height = 20 * 11
    font2.bold = False
    alignment = xlwt.Alignment()  # 设置单元格对齐方式
    alignment.horz = 0x02  # 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
    alignment.vert = 0x01  # 0x00(上端对齐)、 0x01(垂直方向上居中对齐)、0x02(底端对齐)

    worksheet.col(0).width = 256 * 40
    worksheet.col(1).width = 256 * 12
    worksheet.col(2).width = 256 * 16
    worksheet.col(3).width = 256 * 16
    worksheet.col(4).width = 256 * 16

    style.font = font1
    style.alignment = alignment
    worksheet.write_merge(0, 0, 0, 4, '编制人员报表', style)  # 带格式合并单元格写入，0行0列到0行4列合并
    style.font = font2
    worksheet.write(1, 0, '单位', style)  # 带样式的写入
    worksheet.write(1, 1, '姓名', style)
    worksheet.write(1, 2, '职务', style)
    worksheet.write(1, 3, '工龄', style)
    worksheet.write(1, 4, '本年度月份', style)

    wl=2
    for peop in people:
        if peop['编制类别']=='全额拨款':
            worksheet.write(wl,0,peop['主管部门'],style)
            worksheet.write(wl, 1, peop['姓名'], style)
            worksheet.write(wl, 2, peop['领导职务层次'], style)
            worksheet.write(wl, 3, peop['参加工作时间'], style)
            worksheet.write(wl, 4, peop['进入本单位时间'], style)
            wl=wl+1

    workbook.save(r'D:/test.xls')  # 保存文件


if __name__ == '__main__':
    start()
    print(people)
    # t1()
    # print(len(people))