Excel与Python：将VBA宏转换成Python

精益求精

标签： Excel与Python

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

创造完整的乘法表

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

import win32com.client as win32excel = win32.gencache.EnsureDispatch('Excel.Application')excel.Visible = Truewb = 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:=3Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPrioritySelection.FormatConditions(1).ColorScaleCriteria(1).Type = _ xlConditionValueLowestValue With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor .Color = 1301146 .TintAndShade = 0 End WithSelection.FormatConditions(1).ColorScaleCriteria(2).Type = _ xlConditionValuePercentileSelection.FormatConditions(1).ColorScaleCriteria(2).Value = 50 With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor .Color = 8711167 .TintAndShade = 0 End WithSelection.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').SelectEnd Sub

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

图4

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

1.选型要添加前疏excel 。

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

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

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

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

转换为Python代码是：

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

或者使用临时变量：

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

或者使用with ：

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

创造临时改变量是为了使脚本更干净。特别是：

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

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

下面是完整的Python代码：

import win32com.client as win32excel = win32.gencache.EnsureDispatch('Excel.Application')excel.Visible = Truewb = 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 inrange(1, 4)]csc1.Type = win32.constants.xlConditionValueLowestValuecsc1.FormatColor.Color = 13011546csc1.FormatColor.TintAndShade = 0csc2.Type = win32.constants.xlConditionValuePercentilecsc2.Value = 50csc2.FormatColor.Color = 8711167csc2.FormatColor.TintAndShade = 0csc3.Type = win32.constants.xlConditionValueHighestValuecsc3.FormatColor.Color = 7039480csc3.FormatColor.TintAndShade = 0ws.Range('B:K').ColumnWidth = 4ws.Range('A1').Select()wb.SaveAs('ConditionalFormatting.xlsx')excel.Application.Quit()