利用xlwings自动化操作Excel表格

利用xlwings自动化操作Excel表格

import xlwings as xw
import numpy as np
import pandas as pd
#详细内容参考网站 https://docs.xlwings.org/en/stable/course.html


"""1.建立表格连接对象"""
#1.1.新建工作簿
app = xw.App(visible=True,add_book=False)#visible=True可以看到新建的工作簿，add_book=True再新建一个表格
app.display_alerts = False    # 关闭一些提示信息，可以加快运行速度。 默认为 True。
app.screen_updating = True    # 更新显示工作表的内容。默认为 True。关闭它也可以提升运行速度。
wb = app.books.add()  #新建工作簿
wb.activate()   #激活为当前工作簿
wb.save('xlwings实验表.xlsx')  #保存工作簿为指定名字
wb.close()  #关闭工作簿，但是显示的文件并不会关闭
app.quit()  #退出应用---显示的文件也关闭


#1.2.利用已经有的工作簿
wb=xw.Book('xlwings实验表.xlsx')#将目标excel表格与wb相连接
wb.sheets#所有表格名称
sheet = wb.sheets[0]#将表格中的sheet1建立连接
sheet=wb.sheets['sheet1']#将表格中的sheet1建立连接


#1.3.操作工作簿
wb = app.books.add()                   # 新建工作簿
wb = app.books.open(r'file_path')      # 打开现有的工作簿
wb = app.books.active                  # 获取当前活动的工作簿
sheet = wb.sheets.active                 # 获取当前活动的工作表
sheet = wb.sheets.add()                 # 新建工作表，默认新建的放在最前面。
sheet = wb.sheets.add('新建工作表', after=sheet)   # 新建工作表，放在sht工作表后面。

"""2.查看表格基本信息"""
wb.fullname#查看目标excel表格所在路径
wb.name#工作簿名称
sheet.name#查看工作表名称


"""3.对表格基本操作"""

#3.1.选取单元格区域
#3.1.1.引用指定坐标单元格:通过三种方式引用单元格A1
cell=sheet.range('cell')
cell=sheet[0,0]
cell=xw.Range('cell')
cell.value#打印表格中cell位置的数据

#3.1.2.引用指定区域
range=sheet.range('a1:c10')#引用区域
range=sheet[0:9,0:3]#引用区域
value = sheet.range('a1:c10').options(ndim=2).value       # 加上 option 读取二维的数据
range.count  #返回区域中单元格的格数
range.row  # 返回区域的第一行行标
range.last_cell  #获得区域中右下角最后一个单元格
range.width#获得区域的宽度
range.address   #取得区域的绝对地址
range.shape#返回区域的行数和列数
range.column#返回单元格列表
range.row#返回单元格行表

#3.2.在表格中读写数据
#3.2.1.读写数据
sheet.range('A1').value='第一个数据'#在单元格A1中实时写入数据
sheet.range('A1').add_hyperlink(r'www.baidu.com','百度','提示：点击即链接到百度')  # 指定单元格加入超链接
sheet.range('A1').clear_contents()  # 清除该单元格内容
sheet.range('A1').clear()#将表格A1位置的数据和格式实时清除
sheet.clear()#将工作表中的内容清空
sheet.range('D4').value=np.random.rand(16,7)#从起始位置单元格输入数据
xw.Range('E21').value=34#直接从工作簿写入数据，默认写入第一个工作表
data_np=sheet.range('D4').expand().value#从起始位置单元格'D4'批量读取数据
data_pd=sheet.range('D4').options(pd.DataFrame,expand='table').value#将数据读取为数据框

#3.2.2.通过循环填充单元格B1:B50
for i in range(1,51):
    sheet.range('B'+str(i)).value=i*2

#3.2.3.查写单元格公式
sheet.range('A1').formula='=SUM(B1:B50)'#通过输入excel公式计算单元格之和
formula=sheet.range('A1').formula_array#查看单元格A1位置的公式
sheet.range('A2').formula=formula

#3.3.修改单元格格式
#3.3.1.修改行宽列高
sheet.range('A1').row_height#获取单元格的行高
sheet.range('A1').row_height=19#修改单元格的行高
sheet.range('A1').column_width#获取单元格的列宽
sheet.range('A1').column_width=19#修改单元格的列宽
sheet.range('A1').columns.autofit()#单元格列宽自动适应
sheet.range('A1').rows.autofit()#单元格列宽自动适应

#3.3.2.修改颜色
sheet.range('A1').color#获取单元格的颜色
sheet.range('A1').color=(32,14,214)#给单元格增加颜色
sheet.range('A1').color=None#清空单元格颜色
sheet.range('a1:c10').color=(32,14,214)#给区域增加颜色

#3.3.3.修改字体格式
sheet.range('A1').color = 255,200,255         # 设置单元格的填充颜色
sheet.range('A1').Font.ColorIndex = 3     # 设置字体的颜色，具体颜色索引见下方。
sheet.range('A1').Font.Size = 24          # 设置字体的大小。
sheet.range('A1').Font.Bold = True        # 设置为粗体。
sheet.range('A1').HorizontalAlignment = -4108    # -4108 水平居中。 -4131 靠左，-4152 靠右。
sheet.range('A1').VerticalAlignment = -4130      # -4108 垂直居中（默认）。 -4160 靠上，-4107 靠下， -4130 自动换行对齐。
sheet.range('A1').NumberFormat = "0.00"          # 设置单元格的数字格式。

#3.3.4.合并拆分单元格
sheet.range('D1:H1').merge()      # 合并单元格 C8 到 D8
sheet.range('D1:H1').unmerge()    # 拆分单元格。
sheet.range('D1:H1').value=8

#3.3.5.设置边框
# Borders(9) 底部边框，LineStyle = 1 直线。
sheet.range('A1').api.Borders(9).LineStyle = 1
sheet.range('A1').api.Borders(9).Weight = 3  # 设置边框粗细。

# Borders(7) 左边框，LineStyle = 2 虚线。
sheet.range('A1').api.Borders(7).LineStyle = 2
sheet.range('A1').api.Borders(7).Weight = 3

# Borders(8) 顶部框，LineStyle = 5 双点划线。
sheet.range('A1').api.Borders(8).LineStyle = 5
sheet.range('A1').api.Borders(8).Weight = 3

# Borders(10) 右边框，LineStyle = 4 点划线。
sheet.range('A1').api.Borders(10).LineStyle = 4
sheet.range('A1').api.Borders(10).Weight = 3

# Borders(5) 单元格内从左上角 到 右下角。
sheet.range('A1').api.Borders(5).LineStyle = 1
sheet.range('A1').api.Borders(5).Weight = 3

# Borders(6) 单元格内从左下角 到 右上角。
sheet.range('A1').api.Borders(6).LineStyle = 1
sheet.range('A1').api.Borders(6).Weight = 3

#如果是一个区域的单元格，内部边框设置如下
# Borders(11) 内部垂直边线。
sheet.range('A1').api.Borders(11).LineStyle = 1
sheet.range('A1').api.Borders(11).Weight = 3

# Borders(12) 内部水平边线。
sheet.range('A1').api.Borders(12).LineStyle = 1
sheet.range('A1').api.Borders(12).Weight = 3

#3.3.6.插入 、删除 一行
sheet.range('a3').api.EntireRow.Delete()  # 会删除 ’a3‘ 单元格所在的行。
sheet.api.Rows(3).Insert()  # 会在第3行插入一行，原来的第3行下移。

#插入 、删除 一列
sheet.range('c2').api.EntireColumn.Delete()  # 会删除 ’c2‘ 单元格所在的列。
sheet.api.Columns(3).Insert()  # 会在第3列插入一列，原来的第3列右移。(也可以用列的字母表示)


"""3.将matplotlib的图表写入excel"""
import matplotlib.pyplot as plt
data=np.random.rand(30)
fig=plt.figure(figsize=(10,8))
plt.plot(data)
#add(image, link_to_file=False, save_with_document=True, left=0, top=0, width=None, height=None, name=None, update=False)
sheet.pictures.add(fig,name='图片',update=True,left=40,top=30,width=40,height=40)#在表格的指定位置插入指定大小的图片