Python办公自动化之一，操作Excel

1. xlrd模块

• xlrd是第三方模块，读取Excel表格数据
• xlrd常用函数
• xlrd操作Excel列
• xlrd操作Excel行
• xlrd操作Excel单元格

示例代码

import xlrd

# 1.读取Excel
# 1.1 读取文件
data = xlrd.open_workbook(r'F:\base\elementInfo.xls')

# 1.2 工作表是否加载
print(data.sheet_loaded(0))  # 索引为0的工作表

# 1.3 卸载已经加载的工作表
# data.unload_sheet(0)

# 1.4 找到要操作的工作表
print(data.sheets())  # 得到所有的工作表
print(data.sheets()[0])  # 访问第一个工作表的对象
print(data.sheet_by_name('Sheet1'))  # 根据名字去获取工作表
print(data.sheet_by_index(0))  # 根据索引去获取工作表
print(data.sheet_names()) # 获取当前所有工作表的名字
print(data.nsheets) # 当前有多少工作表

# 1.5 操作excel行
sheet = data.sheet_by_index(0) # 获取第一个工作表
print(sheet.nrows) # 获取当前sheet下的"有效"行数
print(sheet.row(0)) # 取到第1行
print(sheet.row(1)) # 取到第2行
print(sheet.row_types(3)) # 获取第4行的数据类型(输出的结果中：0表示空，1表示字符串，2表示number, 3表示date, 4表示boolean ，5表示error)
print(sheet.row(4)[1]) # 获取第5行第2列的数据对象
print(sheet.row(4)[1].value) # 获取第5行第2列的数据值
print(sheet.row_values(4)) # 获取第5行所有列的数据值
print(sheet.row_len(4)) # 获取第5行有多少列

# 1.6 操作Excel列
sheet = data.sheet_by_index(0)  # 获取第一个工作表
print(sheet.ncols) # 获取工作表的有效列数
print(sheet.col(1)) # 拿到第二列的数据对象
print(sheet.col(1)[9].value) # 获取第2列的第10个元素的值
print(sheet.col_values(1)) # 拿到第2列中所有的数据
print(sheet.col_types(1)) # 查看第2列的每个单元格数据类型(输出的结果中：0表示空，1表示字符串，2表示number, 3表示date, 4表示boolean ，5表示error)

# 1.7 操作Excel单元格
sheet = data.sheet_by_index(0)  # 获取第一个工作表
print(sheet.cell(9, 1)) # 第10列，第2行单元格的数据对象
print(sheet.cell_type(9, 1))# 第10列，第2行单元格的数据类型(输出的结果中：0表示空，1表示字符串，2表示number, 3表示date, 4表示boolean ，5表示error)
print(sheet.cell(9, 1).ctype)# 第10列，第2行单元格的数据类型(输出的结果中：0表示空，1表示字符串，2表示number, 3表示date, 4表示boolean ，5表示error)
print(sheet.cell(9, 1).value) # 第10列，第2行单元格的数据值
print(sheet.cell_value(9, 1)) # 第10列，第2行单元格的数据值

2. xlwt模块

• xlwt是第三方模块，写入Excel表格数据
• 支持创建工作表、指定单元格、指定单元格样式等
• xlwt支持excel的03~2013版Excel。
• 保存格式只支持xls格式，不支持07版以后的xlsx格式

import xlwt

# 写入Excel步骤：创建工作簿、创建工作表、填充工作表内容、保存文件

# 指定样式
titleStyle = xlwt.XFStyle()  # 初始化样式
titleFont = xlwt.Font()
titleFont.name = '宋体'
titleFont.bold = True
titleFont.height = 11 * 20  # 20是衡量单位，11是字号
titleFont.colour_index = 0x35  # 这里的颜色不是普通的rgb，要参考XFStyle这个类里面提供的_colour_map_text属性
titleStyle.font = titleFont
# 单元格对齐方式
cellalign = xlwt.Alignment()
cellalign.horz = 0x02  # (水平方向) 这里的对齐方式参考Alignment类的具体属性的值(也可以xlwt.Alignment.xxx)
cellalign.vert = 0x01  # (垂直方向) 这里的对齐方式参考Alignment类的具体属性的值
titleStyle.alignment = cellalign
# 边框
borders = xlwt.Borders()
borders.right = xlwt.Borders.DASHED
borders.bottom = xlwt.Borders.DOTTED
titleStyle.borders = borders

# 定义第一列的背景颜色
dateStyle = xlwt.XFStyle()
bgcolor = xlwt.Pattern()
bgcolor.pattern = xlwt.Pattern.SOLID_PATTERN
bgcolor.pattern_fore_colour = 22  # 背景颜色
dateStyle.pattern = bgcolor

# 1. 创建工作簿
wb = xlwt.Workbook()

# 2. 创建工作表
ws = wb.add_sheet('工作表1')

# 3. 填充数据
ws.write_merge(0, 1, 0, 5, "2020年人民币兑换表", style=titleStyle)  # 单元格合并: 前2行合并，前6列合并

# 3.1 写入数据
data = (("日期", "拉拉", "啦啦啦", "哈哈", "哈哈哈"), ("05/27/2020", 100000, 999999, 666666, 6.3333),
        ("05/27/2020", 100000, 999999, 666666, 6.3333), ("05/27/2020", 100000, 999999, 666666, 6.3333),
        ("05/27/2020", 100000, 999999, 666666, 6.3333))
for i, item in enumerate(data):
    for j, val in enumerate(item):
        if j == 0:
            ws.write(i + 2, j, val, dateStyle)
        else:
            ws.write(i + 2, j, val)  # 需要行和列的索引(+2是为了跳过前2行，这里我们的前2行有数据了)

# 4. 创建第2个工作表，添加图片
wsimage = wb.add_sheet('兰彩云')
# 写入图片(bitmap格式)
wsimage.insert_bitmap("meinv.bmp", 0, 0)  # 从第一行第一列进行插入

# 5.保存
wb.save("test.xls")

3 xlsxwriter模块

• xlsxwriter是python的第三方模块，用于向生成的Excel表格插入数据、图表等操作
  最终生成xlsx格式的Excel文件
• 功能比xlwt模块更加强大，支持更多的Excel功能
• 100%兼容Excel xlsx文件、支持Excel 2003 2007等版本
• 处理速度更快，支持大文件写入
• 写入时可以启用内存优化模式来快速写入
• 不足：不支持读取和修改Excel文件(读取Excel文件可以使用xlrd模块)

import xlsxwriter

wb = xlsxwriter.Workbook('yinlei.xlsx')
# 设置style格式样式
cell_format = wb.add_format({'bold': True})
cell_format1 = wb.add_format()
cell_format1.set_bold()
cell_format1.set_font_color('orange')
cell_format1.set_font_size(15)
cell_format1.set_align('center')
 
cell_format2 = wb.add_format()
cell_format2.set_bg_color('#FF00FF')
 
# 创建工作表
sheet = wb.add_worksheet("yinleisheet")
 
# 写入
# sheet.write_string() # 还有很多write_xxx()
 
# 写入单个单元格
sheet.write(0, 0, "2020年", cell_format) # 会帮我们将python的数据类型映射到excel支持的类型方法中
 
# 合并单元格
sheet.merge_range(1, 0, 2, 2, '全国女朋友彩礼统计', cell_format=cell_format1) # 指定起始的行和列和结束的行、列
data = (
    ["四川", 5, 15],
    ["江西", 30, 50],
    ["乐山", 0, 10],
)
sheet.write_row(3, 0, ["地区", "最低金额(单位：万)", "最高金额(单位：万)"], cell_format=cell_format2)
for index, item  in enumerate(data):
    sheet.write_row(index+4, 0, item)
 
# 写入excel公式进行统计数据
sheet.write(7, 1, "=sum(B5:B7)")
sheet.write(7, 2, "=sum(C5:C7)")
 
# 插入超链接
sheet.write_url(9, 0, "http://yinleilei.com", string="跳转到个人网站")
 
# 插入图片
sheet.insert_image(10, 0, "plant.png")
 
# 生成图表
chart = wb.add_chart({'type': 'line'}) ## 折线图：line
chart.set_title({'name': '全国女朋友彩礼统计'})
# X轴Y轴的描述信息
chart.set_x_axis({'name': '地区'})
chart.set_y_axis({'name': '金额'})
# 图表填充数据
chart.add_series({
    'name': '最低彩礼',
    'categories': '=yinleisheet!$A$5:$A$7', # 用Excel公式取得数据A5-A7的单元格,或者通过索引的方式获取数据像下面的values一样
    'values': ['yinleisheet', 4, 1, 6, 1] # 指定工作表，选取起始行是4，起始列1到起始行6，起始列为1之间的数据
})
 
chart.add_series({
    'name': '最高彩礼',
    'categories': '=yinleisheet!$A$5:$A$7', # 用Excel公式取得数据A5-A7的单元格,或者通过索引的方式获取数据像下面的values一样
    'values': ['yinleisheet', 4, 2, 6, 2],# 指定工作表，选取起始行是4，起始列1到起始行6，起始列为1之间的数据
    'data_labels': {'value': True}, # 每个柱状图上显示数字
})
 
sheet.insert_chart('E4', chart=chart)
 
# 关闭文件
wb.close()