python导入导出excel

一 读取.xls文件中的数据

import xlrd

　　 # 读取表格内容
    file = r'0228.xls'
    book = xlrd.open_workbook(file)
    
    # 表中所存放数据对应的key
    keys = ['_id', "node.title", "node.location.desc", "node.time.startTime", "node.time.timeDesc", "node.desc",
            'status']
    sheet = book.sheet_by_name("数据")
    for i in range(1, sheet.nrows):
        value = sheet.row_values(i)
        data = dict(zip(keys, value))
        print(data)

　　

二 读取.xlsx文件中的数据

from openpyxl import load_workbook

# 读取xlsx文件
    workbook = load_workbook(filename='liveUserV2.xlsx')
    sheet = workbook.active
    for row in sheet.iter_rows(min_row=2, min_col=1, max_col=2):
        userId = row[0].value
        name = str(row[1].value)
        print(str(userId) + ", " + name)
        update(userId, name)

　　

三 导出数据到excel

import xlwt

    # 使用workbook方法，创建一个新的工作簿
    book = xlwt.Workbook(encoding='utf-8', style_compression=0)
    # 添加一个sheet，设置sheet名，参数overwrite就是说可不可以重复写入值，就是当单元格已经非空，你还要写入
    sheet = book.add_sheet('统计', cell_overwrite_ok=True)
    # 设置表头
    sheet.write(0, 0, "id")
    sheet.write(0, 1, "兑换码Id")
    sheet.write(0, 2, "兑换码名称")
    sheet.write(0, 3, "兑换码")
    sheet.write(0, 4, "兑换码状态")
    sheet.write(0, 5, "用户Id")
    sheet.write(0, 6, "用户名")
    sheet.write(0, 7, "用户手机号")
    sheet.write(0, 8, "兑换时间")
    sheet.write(0, 9, "天梯ID")
    sheet.write(0, 10, "天梯标题")
    sheet.write(0, 11, "攀登进度")
    sheet.write(0, 12, "已学知识点数量")

    # 向每个单元格中写入数据
    i = 1
    for eachData in data:
        j = 0
        for key, value in eachData.items():
            sheet.write(i, j, value)
            j = j + 1
        i = i + 1

    # 保存到excel文件
    book.save('result.xlsx')