Excel与Python：将VBA宏转换成Python

fanjy

发布于 2023-08-29 21:11:31

7590

发布于 2023-08-29 21:11:31

文章被收录于专栏：完美Excel

标签：Excel与Python

在本文的示例中，将从头开始创建两个表：乘法表和随机数表，然后对这些数字应用条件格式。

创建完整的乘法表

首先，使用下面的代码初始化工作表：

import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = True
wb = excel.Workbooks.Add()
ws = wb.Worksheets('Sheet1')

然后，使用下面两条代码创建行列标题：

ws.Range("B2:K2").Value = [i for i in range(1, 11)]
ws.Range("B2:B11").Value = list(zip([i for i in range(1, 11)]))

结果如下图1所示。

图1

乘法表中，单元格中的值等于对应的行列标题数字相乘，在Excel中使用混合引用的公式实现。实现自动填充的Python代码如下：

ws.Range("C3").Formula = "=$B3*C$2"
ws.Range("C3:C3").Select()
excel.Selection.AutoFill(ws.Range("C3:K3"),win32.constants.xlFillDefault)
ws.Range("C3:K3").Select()
excel.Selection.AutoFill(ws.Range("C3:K11"),win32.constants.xlFillDefault)

结果如下图2所示。

图2

创建随机数表

我们创建包含1至100的随机数表，代码如下：

ws.Range("B13:K22").Formula = "=INT(RAND()*100)"

结果如下图3所示。

图3

添加条件格式

添加基于单元格值应用颜色的条件格式。

我们先录制宏。单击“录制宏”按钮开始录制。

选择单元格区域B2:K22，单击功能区“开始”选项卡“样式”组中的“条件格式——色阶——红黄蓝色阶”，然后选择列B至列K，将列宽设置为“4”，最后选择单元格A1，停止录制。

录制的代码如下：

Sub 宏1()
'
' 宏1 宏
'
'
 Range("B2:K22").Select
 Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
 xlConditionValueLowestValue
 With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
 .Color = 1301146
 .TintAndShade = 0
 End With
Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
 xlConditionValuePercentile
Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
 With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
 .Color = 8711167
 .TintAndShade = 0
 End With
Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
 xlConditionValueHighestValue
 With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
 .Color = 7039480
 .TintAndShade = 0
 End With
 Columns("B:K").Select
 Selection.ColumnWidth = 4
 Range("A1").Select
End Sub

设置条件格式后的工作表如下图4所示，接近数字100是红色、50是黄色、1是蓝色。

图4

下面是将宏代码转换为Python的一些规则。

1.Selection要添加前缀excel。

2.Range要添加前缀ws，这是前面的代码已经定义过的。

3.Python中的函数调用需添加括号()。

4.With语句必须被展开。例如，VBA中的With块：

With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
 .Color = 13011546
 .TintAndShade = 0
End With

转换为Python代码是：

excel.Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor.Color
= 13011546
excel.Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor.TintAndShade
= 0

或者使用临时变量：

x = excel.Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
x.Color = 13011546
x.FormatColor.TintAndShade = 0

或者使用with：

with excel.Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
as fc:
 fc.Color = 13011546
 fc.FormatColor.TintAndShade = 0

创建临时变量是为了使脚本更加简洁。特别是：

[csc1, csc2, csc3] = [excel.Selection.FormatConditions(1).ColorScaleCriteria(n) for n in
range(1, 4)]

对3个ColorScaleCriteria方法使用了3个临时变量。

下面是完整的Python代码：

import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
excel.Visible = True
wb = excel.Workbooks.Add()
ws = wb.Worksheets('Sheet1')
ws.Range("B2:K2").Value = [i for i in range(1, 11)]
ws.Range("B2:B11").Value = list(zip([i for i in range(1, 11)]))
ws.Range("C3").Formula = "=$B3*C$2"
ws.Range("C3:C3").Select()
excel.Selection.AutoFill(ws.Range("C3:K3"), win32.constants.xlFillDefault)
ws.Range("C3:K3").Select()
excel.Selection.AutoFill(ws.Range("C3:K11"), win32.constants.xlFillDefault)
ws.Range("B13:K22").Formula = "=INT(RAND()*100)"
ws.Range("B2:K22").Select()
excel.Selection.FormatConditions.AddColorScale(ColorScaleType=3)
excel.Selection.FormatConditions(excel.Selection.FormatConditions.Count).SetFirstPriority()
[csc1, csc2, csc3] = [excel.Selection.FormatConditions(1).ColorScaleCriteria(n) for n in
range(1, 4)]
csc1.Type = win32.constants.xlConditionValueLowestValue
csc1.FormatColor.Color = 13011546
csc1.FormatColor.TintAndShade = 0
csc2.Type = win32.constants.xlConditionValuePercentile
csc2.Value = 50
csc2.FormatColor.Color = 8711167
csc2.FormatColor.TintAndShade = 0
csc3.Type = win32.constants.xlConditionValueHighestValue
csc3.FormatColor.Color = 7039480
csc3.FormatColor.TintAndShade = 0
ws.Range("B:K").ColumnWidth = 4
ws.Range("A1").Select()
wb.SaveAs('ConditionalFormatting.xlsx')
excel.Application.Quit()

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-05-17，如有侵权请联系 cloudcommunity@tencent.com 删除

python