xlwings学习

xlwings官方文档

一、如何安装xlwings

pip安装：pip Install xlwings

import xlwings as xw

注: xlwings的更新和卸载和python其他的库的操作一致，不在赘述

二、各种操作

2.1 如何选择指定的单元格

如何获得sheet使用的单元格区域

sheet.used_range

实际运用的过程中需要指定单元格进行操作该单元格，如何指定该单元格呢？

方案	详细
坐标轴	行号+列号（都是从0开始的）
切片	例如A1单元格 ”A1“

上述是两种单元格的表示方式，同样表格方式也有两种

• sheet[]

案例：切记是应为的引号

sheet['A1'] # 此时是选择了A1单元格

sheet['A1:B2']# 此时选择了A1:B2区域

sheet[0, 0] # 此时选择了A1单元格，可以看到此处是从0开始的，切片就是从0开始的

sheet[0:1, 0:4] # 此时选择了A1：D1 ,注意这个地方是不算右的，也就是右边是开的

• sheet.range()

sheet.range(1, 1) # 注意此时不是切片状态所以列和行都是从1开始的，此时表示的是A1

sheet.range((1, 1), (3, 4)) # 注意此时每个小的括号中是一个坐标，此时表示A1：D3，即（行号，列号）

sheet.range('A1')# 此时表示的是A1

sheet.range('A1:B2')# 此时表示的是A1:B2

总结：因为使用的方式不同，当切片的时候记得行号和列号是从0开始的，当使用range方式的时候是从1开始的 

2.2 向单元格中写入值和清除单元格的值

分两种情况 ：单个的值和矩阵

单个的值填写的时候，就需要先获得该单元格，然后输入需要填写的值，例如将A1的值设置为“HELLO，PYTHON”

import xlwings as xw 
app=xw.App(visible=True,add_book=False)#visible设置窗口可见性，add_book设置启动Excel程序窗口后是否新建工作簿 
#不显示Excel消息框 
app.display_alerts=False 
#关闭屏幕更新,可加快宏的执行速度 
app.screen_updating=False 
wb = xw.Book()
ws = wb.sheets[0]
ws['A1'].value="HELLO PYTHON"


# 使用公式 
ws.range('AB2').formula='=SUM(A1,A2)' 
# 另外还可以获取某一个单元格的公式 
print(ws.range('AB2').formula_array) 
# 清除工作表的所有内容但是保留原有格式 
ws.clear_contents()
# 清除工作表
ws.clear()
ws.range('A1').clear_contents()：删除数据但保留样式
ws.range('A2').clear_formats()：删除样式但保留数据
ws.range('A3').clear()：删除数据和样式
ws.range('A1').delete()：删除单元格

这里的矩阵可以是1维的也可以是n维度的，例如如果我们想将一个DataFrame的数据添加到表格中

import pandas as pd
df = pd.DataFrame([[1,2], [3,4]], columns=['A', 'B']) 
sheet1.range('A1').value = df

注意： 上述代码中将df整个添加到了sheet中，注意是全部，全部（包含index），此时跟你的真正想要输入的值其实是错列的。

如何解决呢！

# 将DataFrame数据装欢成列表的形式就可以避免这中情况的出现
df_list = df.value.tolist()
sheet1.range('A1').value = df_list 

 还有一种写法：参考使用，一直没有找到区别

import xlwings as xw
import pandas as pd

# 假设df是您要写入Excel的DataFrame
df = pd.DataFrame([[1, 2,6], [3, 4]], columns=['A', 'B',"C"])

wb = xw.Book()
ws = wb.sheets[0]
# 将DataFrame转换为二维列表并排除索引列
data_to_insert = df.values.tolist()

# 将二维列表写入Excel
ws.range('A1').expand(mode='down').value =[[1,2,3],[0,5,5]]
print(ws.range('A1:C3').value )
# 输出写入的数据区域
print(ws.range('A1').expand(mode='down').value )
print(ws.range('A1:C3').value )
ws.range('A1').expand(mode='down').value  = [[7],[1]]
print(ws.range('A1').expand(mode='down').value )
print(ws.range('A1:C3').value )
ws.range('A1').expand(mode='down').value  = [[8,11],[4,9]]
print(ws.range('A1').expand(mode='down').value )
print(ws.range('A1:C3').value )

 总结：expand，在输入信息的时候起到的作用很小，不过在读取值的时候起到了很大的作用，它会读取（从上到下）第一个空的值之前的值，如果第一个值就是空，返回None

range.offset(row_offset=5,column_offset=2) 表示偏移，row_offset行偏移量（正数表示向下偏移，负数相反），column_offset列偏移量（正数表示向右偏移，负数相反）

注意：是将选区范围进行偏移，内容不进行偏移

range.expand(mode='down') 扩展区域，参数可选取 'down' , 'right' ,'table' ，类似我们使用向下、向右或者下右方的区域扩展操作。

range.resize(row_size=4, column_size=2) 表示调整选中区域的大小，参数表示调整后区域的行、列的数量。

range.current_region 表示全选 类似Ctrl + A

2.3. 创建新的Excel文件

方法1：
创建一个新的App，并在新App中新建一个Book
wb = xw.Book()
wb.save('1.xlsx') 
wb.close() ​
方法2： 
当前App下新建一个Book
#visible参数控制创建文件时可见的属性
app=xw.App(visible=False,add_book=False)

wb=app.books.add()

sht = wb.sheets.active

wb.save('1.xlsx')

wb.close() #结束进程 
app.quit()

2.4. 打开已有的Excel文件

import xlwings as xw 
app=xw.App(visible=True,add_book=False)#visible设置窗口可见性，add_book设置启动Excel程序窗口后是否新建工作簿 
#不显示Excel消息框 
app.display_alerts=False 
#关闭屏幕更新,可加快宏的执行速度 
app.screen_updating=False 
wb=app.books.open('1.xlsx') # 输出打开的excle的绝对路径 
wb.save() 
wb.close() # 退出excel程序， 
app.quit() # 通过杀掉进程强制Excel app退出 
​# app.kill() 
# 以第一种方式创建Book时，打开文件的操作可如下 wb = xw.Book('1.xlsx')

xw.Book()打开文件传入的参数可选，具体如下：

官网中有一句提醒:

If you have the same file open in two instances of Excel, you need to fully qualify it and include the app instance. You will find your app instance key (the PID) via xw.apps.keys():xw.apps[10559].books['FileName.xlsx']

也是就是说：

（1）每个App对应一个PID值，这个PID值可以认为是一个标签，用来识别不同的App。

（2）创建工作簿之前要先创建App: app=xw.App(visible=Ture,add_book=False)

（3）通过xlwings可以创建多个App，每个App又可以创建多个工作簿，每一个工作簿中又可 以创建多个Sheet。

（4）需要注意的是这些App之间是相互独立的，也就是操作不同的工作簿的时候就要找到对 应的App。

建议使用：xw.Book('filename.xlsx') 来打开工作薄或引用工作簿，不容易出错

xw.Book 和 xw.books使用差异

xw.Book 和 xw.books 都是用于与 Excel 工作簿进行交互的方法，但它们之间有一些差异。

xw.Book 是用于打开或创建 Excel 工作簿的方法。当您使用 xw.Book 时，将打开一个新的 Excel 工作簿，并为其创建一个新的 Python 对象。您可以使用此对象来与 Excel 工作簿交互。例如，您可以使用该对象来读取或写入工作簿中的单元格。

xw.books 是用于获取当前打开的 Excel 工作簿的方法。当您使用 xw.books 时，将返回一个包含所有已打开的 Excel 工作簿的列表。您可以使用此列表来选择要与之交互的工作簿。例如，如果您已经打开了一个 Excel 工作簿，您可以使用 xw.books 来获取对该工作簿的引用，并使用该引用来读取或写入工作簿中的单元格。

因此，xw.Book 用于创建新的 Excel 工作簿，而 xw.books 用于获取对当前打开的 Excel 工作簿的引用

Tips: xlwings中的对象层次结构为：

apps->books->sheets->range

2.5 行高和列宽

# 高度和宽度 
ws.range('A1').row_height        # 获取行高
ws.range('A1').column_width      # 获取列宽
ws.range('A1').row_height = 20   # 设置行高
ws.range('A1').column_width = 20 # 设置列宽
ws.range('A1').rows.autofit()    # 自适应行高
ws.range('A1').columns.autofit() # 自适应列宽
ws.range('A1').autofit()         # 自适应行高列宽

# 批量设置行高和列宽
方法一：
ws.range('2:5').api.RowHeight = 20 # 设置行高
ws.range('B:D').api.ColumnWidth = 15  # 设置列宽

方法二：
ws.range('2:5').row_height = 20   # 设置行高
ws.range('B:D').column_width = 20 # 设置列宽

 注意：上述代码使用了两种方法设置行高和列宽，两种方案本质没有什么区别，都可以使用，但是请勿交叉使用，交叉使用会造成程序一直运行。

        这里需要注意一行或这一列中一个单元格的行高或者列宽改变之后，整个行的行高和整个列的列宽都会改变。

sheet中可以设置自动行高和列宽，跟单独或者批量设置行高列宽基本一致

ws.autofit()：自适应行高列宽
ws.autofit(axis='rows')：自适应行高
ws.autofit(axis='columns')：自适应列宽

2.6 对单元格填充颜色

ws.range('B:B').color = (255, 0, 0) # RGB通道颜色
# 或者
ws.range('B:B').color = 'FFFF00' # 这里也可以是'#FFFF00'

# 清除单元格的颜色
ws.range('B:B').color = None

当你知道如何选择指定的单元格之后其实单元格的属性就可以按照特定的函数设定，例如sheet.range('B:B').color = (255, 0, 0) # RGB通道颜色，这里就是将B列整个一列的颜色设置为红色。

你会担心如果没有值的行会被填充颜色吗？

答案是：会的，所以不建议直接进行整列的填充颜色

2.7 设置字体的格式

字体的格式参考：http://t.csdnimg.cn/fjP3o

只要掌握了基本的字体的格式的设计思路，也可以自己编造格式。

import xlwings as xw
import pandas as pd

# 假设df是您要写入Excel的DataFrame
df = pd.DataFrame([[1, 2,6], [3, 4]], columns=['A', 'B',"C"])

wb = xw.Book()
ws = wb.sheets[0]
# 将DataFrame转换为二维列表并排除索引列
data_to_insert = df.values.tolist()

# 将二维列表写入Excel
ws.range('A1').expand(mode='down').value =[["",405645,3],["1",9056456,""],[1,"",3]]

ws.range('B1').number_format = "#,###"
# 或者
ws.range('A:A').api.NumberFormat = "#,###"
ws.range('B1:B5').color = (255, 0, 0)  # 将B列的背景色设置为红色
wb.save('路径+表名称')
wb.close()

输出：

总结：如果我将ws.range('B1').number_format = "#,###"换成

ws.range('B:B').number_format = "#,###"，此时整列的值的格式都设置成了 "#,###"

2.8 设置字体的大小颜色和是否加粗、斜体

cell = ws.range('I1')
cell.font.name = 'Calibri'  # 设置字体名称
cell.font.size = 9  # 设置字体大小
cell.font.color = '000000' # 设置字体颜色（RGB格式）
cell.font.bold = False  # 设置是否加粗
cell.font.italic = False  # 设置是否倾斜
cell1.api.HorizOntalAlignment= -4108    # -4108 水平居中。 -4131 靠左，-4152 靠右。
cell1.api.VerticalAlignment = -4130      # -4108 垂直居中（默认）。 -4160 靠上，-4107 靠下， -4130 自动换行对齐。
# 一行一行的设置字体
for row_num, row in enumerate(ws.used_range.rows, start=4): # 从第4行开始，可以修改

     for cell in row:
         cell.api.Font.Name = font_name
         cell.api.Font.Size = font_size
         cell.api.Font.Color = font_color
         cell.api.Font.Bold = font_bold
         cell.api.Font.Italic = font_italic

2.9 边框的设置

在xlwings中，可以通过设置Range对象的Border属性来设置边框。具体使用方式如下：

设置Range对象边框样式：

range_obj.api.Borders(边框类型).LineStyle = 边框线型

其中，框类型是一个整数值，可选值如下：

1	左边框
2	右边框
3	上边框
4	下边框
5	斜线
6	内部横线
7	内部竖线
8	对角线
9	内部对角线

边框线型是一个整数值，可选值如下：

-4115	无边框
1	连续实线
2	虚线
3	点线
4	连续双实线
5	大虚线
6	双点划线

示例代码：

import xlwings as xw 
# 打开Excel应用程序 
app = xw.App(visible=False, add_book=False) 
# 打开Excel文件，并选择要操作的工作簿和工作表 
workbook = app.books.open('example.xlsx') 
worksheet = workbook.sheets['Sheet1'] 
# 获取A1单元格Range对象，并设置边框样式为连续实线 
range_obj = worksheet.range('A1') 
range_obj.api.Borders(1).LineStyle = 1 # 左边框 
range_obj.api.Borders(2).LineStyle = 1 # 右边框 
range_obj.api.Borders(3).LineStyle = 1 # 上边框 
range_obj.api.Borders(4).LineStyle = 1 # 下边框 
# 关闭Excel应用程序并退出 
workbook.save() 
workbook.close() 
app.quit()

设置Range对象边框颜色和粗细：

range_obj.api.Borders(边框类型).ColorIndex = 边框颜色 
range_obj.api.Borders(边框类型).Weight = 边框粗细

其中，边框颜色是一个整数值，表示颜色在调色板中的索引值，不建议这样使用；边框粗细是一个整数值，可选值为0（细）、1（中）和2（粗）。

示例代码：

import xlwings as xw # 打开Excel应用程序 
app = xw.App(visible=False, add_book=False) # 打开Excel文件，并选择要操作的工作簿和工作表 workbook = app.books.open('example.xlsx') 
worksheet = workbook.sheets['Sheet1'] # 获取A1单元格Range对象，并设置边框样式、颜色和粗细 range_obj = worksheet.range('A1') 
range_obj.api.Borders(1).LineStyle = 1 # 左边框 
range_obj.api.Borders(2).LineStyle = 1 # 右边框 
range_obj.api.Borders(3).LineStyle = 1 # 上边框 
range_obj.api.Borders(4).LineStyle = 1 # 下边框 
range_obj.api.Borders(1).ColorIndex = 3 # 红色 
range_obj.api.Borders(2).ColorIndex = 3 # 红色 
range_obj.api.Borders(3).ColorIndex = 3 # 红色 
range_obj.api.Borders(4).ColorIndex = 3 # 红色 
#或者
red_color = xw.utils.rgb_to_int((255, 0, 0))  # 将RGB颜色码转换为整数表示
ws.range('A1').api.Borders(4).Color = red_color

range_obj.api.Borders(1).Weight = 2 # 粗 
# 关闭Excel应用程序并退出 
workbook.save() 
workbook.close() 
app.quit()

总结，颜色不能使用hex只能使用整数形式，如果你有一个颜色是’#FF0000‘类型的，你可以转换成

(255, 0, 0)，然后转换成整数，使用整数进行设置。

2.10 数据验证

在xlwings中，通过range_to_validate.api.Validation.Add方法设置数据验证规则时，可以使用以下参数来定义规则的类型、条件和其他属性：

1. Type：规则的类型，表示要应用的数据验证规则类型。以下是一些常用的选项：

   • 1：整数
   • 2：小数
   • 3：文本长度
   • 4：日期
   • 5：时间
   • 6：列表
   • 7：自定义公式
   • 8：整数长度
   • 9：时间长度
   • 10：小数长度
   • 11：自定义列表

2. AlertStyle：规则触发时的警告样式，表示当数据验证规则被违反时显示的警告样式。以下是一些可选的选项：

   • 1：显示警告
   • 2：显示错误
   • 3：不显示任何警告

3. Operator：比较运算符，表示规则的比较运算符。以下是一些常用的选项：

   • 1：等于
   • 2：不等于
   • 3：大于
   • 4：大于等于
   • 5：小于
   • 6：小于等于
   • 7：介于
   • 8：不介于

4. Formula1：规则的第一个条件，根据规则类型和比较运算符的不同，该条件的含义会有所不同。例如，对于整数类型，Formula1表示最小值；对于日期类型，Formula1表示最早日期等。

5. Formula2：规则的第二个条件，仅在比较运算符为介于或不介于时需要提供。表示规则的第二个条件值。

import xlwings as xw
import datetime

# 选择要操作的工作簿和工作表
wb = xw.Book("your_workbook.xlsx")
ws = wb.sheets['Sheet1']

# 定义数据验证规则
dv_Won_date = xw.DataValidation(type="date", operator="between", 
                                formula1="2023-09-30", 
                                formula2=datetime.datetime.today().strftime('%Y-%m-%d'),
                                prompt='请填写当天的日期')

# 将数据验证应用到指定的单元格范围
col_letter3 = 'C'  # 假设您想要应用到第3列
NPSNSP_max_row = 100  # 替换为实际的最大行数
dv_range = f"{col_letter3}3:{col_letter3}{NPSNSP_max_row}"
dv_Won_date.add(ws.range(dv_range))

# 保存工作簿
wb.save()

# 关闭工作簿
wb.close()

2.11 设置筛选

import xlwings as xw

# 选择要操作的工作簿和工作表
wb = xw.Book("your_workbook.xlsx")
ws = wb.sheets['Sheet1']

# 获取列号
import string

def col_num_to_letter(n):
    result = ""
    while n > 0:
        n, remainder = divmod(n - 1, 26)
        result = string.ascii_uppercase[remainder] + result
    return result

col_num = 107
col_letters = col_num_to_letter(col_num)
print(col_letters)  # 输出结果为 'CV'

# 设置自动筛选范围
ws.api.AutoFilterMode = False  # 禁用之前可能存在的自动筛选
ws.range(f"A2:{col_letters}2").api.AutoFilter()

# 保存工作簿
wb.save()

# 关闭工作簿
wb.close()

2.12 如何获取列号

# 获取列号
import string

def col_num_to_letter(n):
    result = ""
    while n > 0:
        n, remainder = divmod(n - 1, 26)
        result = string.ascii_uppercase[remainder] + result
    return result

col_num = 107
col_letters = col_num_to_letter(col_num)
print(col_letters)  # 输出结果为 'CV'

暂时没有找到向对应的函数