python读写Excel

最近小编在处理各种.xlsx表格的数据处理和计算的工作,目前python用于操作表格的模块有很多,功能各有千秋。本文主要讲的是xlwt用于写,xlrt用于读。

表格写入

简单的写入功能可用xlwt模块,写入功能的难点在于写入合并的单元格。单元格的下标都是从0开始

xlwt官方API:https://xlwt.readthedocs.io/en/latest/api.html

安装:

pip install xlwt
  • 新建workbook:
    wk=xlwt.Workbook()
    
  • 新建sheet:
    sheet1 = wk.add_sheet("数据", cell_overwrite_ok=True)
    
  • 写入普通单元格:写入第3行,第2列
    sheet1.write(2 , 1, "liebao")
    
    # 参数一:行下标
    # 参数二:列下标
    # 参数三:写入的内容
    
  • 写入合并的单元格:
    # 列合并:写入第2行,第2~5列
    sheet1.write_merge(1, 1, 1, 4, "列合并")
    # 行合并:写入第1~3行,第3列
    sheet1.write_merge(0, 2, 2, 2, "行合并")
    
    # 参数一:开始的行下标
    # 参数二:结束的行下标(包含)
    # 参数三:开始的列下标
    # 参数四:结束的列下标(包含)
    # 参数五:写入的内容
    
  • 保存至表格文件
    wk.save(file_name)
    
    # 参数一:保存的表格文件名或者流
    

但是我们的单元格怎么设置样式呢?一般的单元格都会调整样式,如合并居中、设置字体大小、背景色等等?

如何写入公式?

如实现下面的

{
1568706718.jpg
def xlwt_excel():
    '''
    表格写入
    :return:
    '''
    # 获得可写入的workbook对象
    wk = xlwt.Workbook()
    # 增加一个sheet 并且单元格可重写
    sheet1 = wk.add_sheet(sheet_name, cell_overwrite_ok=True)

    # 合并的行:写入合并的第一、二行
    for i in range(0, len(row0_2)):
        sheet1.write_merge(0, 1, i, i, row0_2[i], get_style())

    # 写入单个最后一列的第一、二行:分开的两行消耗(元) 折前
    sheet1.write(0, len(row0_2), simple_end_col[0], get_style())
    sheet1.write(1, len(row0_2), simple_end_col[1], get_style())

    # 合并的行:写第一列
    sheet1.write_merge(2, len(liebao_accounts) + 1, 0, 0, colum0[0], get_style())
    sheet1.write_merge(2 + len(liebao_accounts), len(liebao_accounts) + len(wifi_accounts) + 1, 0, 0, colum0[1],
                       get_style())

    # 写入单个单元格:写猎豹数据:
    for index in range(0, len(liebao_accounts)):
        sheet1.write(2 + index, 1, liebao_accounts[index]['app'], get_style(True))
        sheet1.write(2 + index, 2, liebao_accounts[index]['system'], get_style(True))
        sheet1.write(2 + index, 3, liebao_accounts[index]['account'], get_style(True))
        sheet1.write(2 + index, 4, float(liebao_accounts[index]['spend']), get_style(True))

    # 写入单个单元格:写入wifi数据
    for index in range(0, len(wifi_accounts)):
        sheet1.write(2 + len(liebao_accounts) + index, 1, wifi_accounts[index]['app'], get_style(True))
        sheet1.write(2 + len(liebao_accounts) + index, 2, wifi_accounts[index]['system'], get_style(True))
        sheet1.write(2 + len(liebao_accounts) + index, 3, wifi_accounts[index]['account'], get_style(True))
        sheet1.write(2 + len(liebao_accounts) + index, 4, float(wifi_accounts[index]['spend']), get_style(True))

    # 写入数字格式化
    sheet1.write_merge(2 + len(liebao_accounts) + len(wifi_accounts), 2 + len(liebao_accounts) + len(wifi_accounts), 0,
                       1, datetime.now(), get_style(num_format=True))

    # 写入合并列:合计
    sheet1.write_merge(2 + len(liebao_accounts) + len(wifi_accounts), 2 + len(liebao_accounts) + len(wifi_accounts), 2,
                       3, "合计", get_style())
    # 写入公式:求和消耗总和
    sheet1.write(2 + len(liebao_accounts) + len(wifi_accounts), 4,
                 xlwt.Formula("SUM(E3:E%d)" % (3 + len(liebao_accounts) + len(wifi_accounts) - 1)), get_style())

    # 写入超链接
    sheet1.write_merge(3 + len(liebao_accounts) + len(wifi_accounts), 3 + len(liebao_accounts) + len(wifi_accounts), 0,
                       4, xlwt.Formula('HYPERLINK("https://sunflowercoder.com/";"更多好文 点击查看我的博客")'),
                       get_style(bold=True))

    # 修改列宽度
    for i in range(0, len(row0_2) + 1):
        sheet1.col(i).width = 150 * 30  # 定义列宽
    sheet1.col(0).width = 50 * 30  # 定义列宽
    sheet1.col(2).width = 200 * 30  # 定义列宽

    # 保存到文件
    wk.save(file_name)

def get_style(simple_ceil=False, num_format=False, bold=False):
    '''
    设置表格样式
    :param simple_ceil: 是否为 普通单元格,默认为非普通单元格
    :param num_format: 是否为需要格式化的数字单元格
    :param bold: 是否需要加粗
    :return: 
    '''
    style = xlwt.XFStyle()
    if not simple_ceil:
        # 字体
        font = xlwt.Font()
        font.name = "宋体"
        font.bold = bold
        font.underline = False
        font.italic = False
        font.colour_index = 0
        font.height = 200  # 200为10号字体
        style.font = font

        # 单元格居中
        align = xlwt.Alignment()
        align.horz = xlwt.Alignment.HORZ_CENTER  # 水平方向
        align.vert = xlwt.Alignment.VERT_CENTER  # 竖直方向
        style.alignment = align

        # 背景色
        pattern = xlwt.Pattern()
        pattern.pattern = xlwt.Pattern.SOLID_PATTERN
        pattern.pattern_fore_colour = xlwt.Style.colour_map['pale_blue']  # 设置单元格背景色为黄色
        style.pattern = pattern

    # 边框
    border = xlwt.Borders()  # 给单元格加框线
    border.left = xlwt.Borders.THIN  # 左
    border.top = xlwt.Borders.THIN  # 上
    border.right = xlwt.Borders.THIN  # 右
    border.bottom = xlwt.Borders.THIN  # 下
    border.left_colour = 0x40  # 边框线颜色
    border.right_colour = 0x40
    border.top_colour = 0x40
    border.bottom_colour = 0x40
    style.borders = border

    # 数字格式化
    if num_format:
        style.num_format_str = 'M/D/YY'  # 选项: D-MMM-YY, D-MMM-YY, D-MMM, MMM-YY, h:mm, h:mm:ss, h:mm, h:mm:ss, M/D/YY h:mm, mm:ss, [h]:mm:ss, mm:ss.0
    return style

表格读取

读取比较麻烦的是合并单元格的内容,Python读取Excel中单元格的内容返回的有5种类型,ctype分别为 : 0 empty,1 string,2 number, 3 date,4 boolean,5 error

xlrd官方API:https://xlrd.readthedocs.io/en/latest/api.html#module-xlrd

安装:pip install xlrd

读取示例:

def xlrd_excel():
    '''
    表格读取
    :return:
    '''
    # 打开文件
    wb = xlrd.open_workbook(filename=file_name, formatting_info=True)
    # 获取所有表格名字
    print("所有的表格名:", wb.sheet_names())
    # 通过索引获取表格
    sheet1 = wb.sheet_by_index(0)
    # 通过名字获取表格
    # sheet2 = wb.sheet_by_name(sheet_name)
    # 输出表格的名字,行数和列数
    print("第一个表格名:", sheet1.name, "   行数:", sheet1.nrows, "  列数:", sheet1.ncols)

    # 获取行、列的内容
    rows = sheet1.row_values(0)  # 获取第一行的内容
    cols = sheet1.col_values(0)  # 获取第一列内容
    print(rows)
    print(cols)

    # 获取单元格内容 三种方式
    print(sheet1.cell(0, 4).value)
    print(sheet1.cell_value(0, 4))
    print(sheet1.row(0)[4].value)

    # 输出合并表格的内容:注意 xlrd.open_workbook()时,必须formatting_info=True,否则merged_cells返回空
    merged_cells = sheet1.merged_cells
    print("合并的单元格:", merged_cells)
    for item in merged_cells:
        # 合并的单元格为元组形式 如(12, 13, 0, 2) 为(开始的行标,结束的行标,开始的列标,结束的列标) 取值为(开始的行标,开始的列标)即可
        print("合并的单元格", item, "值为:", sheet1.cell_value(item[0], item[2]))

    # Python读取Excel中单元格的内容返回的有5种类型,ctype分别为 :  0 empty,1 string,2 number, 3 date,4 boolean,5 error
    # 输出日期格式
    if sheet1.cell_type(12, 0) == 3:
        date_value = xlrd.xldate_as_tuple(sheet1.cell_value(12, 0), wb.datemode)
        print("日期为:", date_value, )
        print("日期为(格式为2019-09-17):", date(*date_value[:3]))
        print("日期为(格式为2019/09/17):", date(*date_value[:3]).strftime('%Y/%m/%d'))
1568707371.jpg

xlwt最大的弊端就是不能修改表格只能新增,修改的方法,小编会在后面的文章阐述。

需要示例代码,点击原文链接

💡 更多好文欢迎关注我的公众号~

公众号

推荐阅读更多精彩内容