如何在不影响Excel VBA脚本的情况下使用Python修改Excel文件

HackerNews什么值得看
662 阅读3分钟

有时我们需要用Python源代码向包含VBA脚本代码的Excel文件写入数据，而且我还希望它不影响存在于Excel文件中的VBA代码的正常执行。

起初，我使用Python的openpyxl模块中的函数将数据写入xlsm文件中。写完数据后，我发现执行VBA代码的按钮消失了。

经过一番调查，我终于发现Python的openpyxl模块不支持VBA，只有python的xlwings模块是友好的，支持VBA脚本代码。本文将向你展示一个如何使用xlwingspython模块从/到excel工作表读写数据的例子。

1.Python xlwings模块实例

1.1 安装Python xlwings模块

  1. 检查Pythonxlwings模块是否已经安装。

    (MyPythonEnv) C:\Users\zhaosong>pip show xlwings
WARNING: Package(s) not found: xlwings

  2. 如果没有安装Pythonxlwings模块，那么用命令pip install xlwings来安装它。

    (MyPythonEnv) C:\Users\zhaosong>pip install xlwings
Collecting xlwings
  Downloading xlwings-0.25.0.tar.gz (806 kB)
     |████████████████████████████████| 806 kB 78 kB/s
Requirement already satisfied: pywin32>=224 in c:\users\zhaosong\anaconda3\envs\mypythonenv\lib\site-packages (from xlwings) (228)
Building wheels for collected packages: xlwings
  Building wheel for xlwings (setup.py) ... done
  Created wheel for xlwings: filename=xlwings-0.25.0-py3-none-any.whl size=818582 sha256=1b37a8c9354a36d762581e4dda63d6010ac1cd789969b0f223ca03e82192f749
  Stored in directory: c:\users\zhaosong\appdata\local\pip\cache\wheels\81\e4\3d\4e33a67c8dc1b10426af95ab2638cdd428e54eea1add2c1e2d
Successfully built xlwings
Installing collected packages: xlwings
Successfully installed xlwings-0.25.0

  3. 再次运行命令pip show xlwings来验证 python xlwings 模块是否已经成功安装。

    (MyPythonEnv) C:\Users\zhaosong>pip show xlwings
Name: xlwings
Version: 0.25.0
Summary: Make Excel fly: Interact with Excel from Python and vice versa.
Home-page: https://www.xlwings.org
Author: Zoomer Analytics LLC
Author-email: felix.zumstein@zoomeranalytics.com
License: BSD 3-clause
Location: c:\users\zhaosong\anaconda3\envs\mypythonenv\lib\site-packages
Requires: pywin32
Required-by:

1.2 从Excel中读取数据

  1. 默认情况下，带有数字的单元格被读取为float 类型，带有日期的单元格被读取为datetime.datetime 类型，转换空单元格为None。

  2. 数据可以按照options()方法指定的格式来读取。

  3. 下面是一个例子

    # import the xlwings python module.
import xlwings as xw
import os

# Create the App application

# visible indicates whether excel is visible when the program is running, true indicates visible, false indicates invisible.
# add_book indicates whether you want to create a new workbook.
app=xw.App(visible=True,add_book=False)

# define the excel file name.
file = "excel_file_1.xlsm"

# open the above specified file.
wb=app.books.open(file)                    

# get the reference to the first worksheet.
ws = wb.sheets["Sheet1"]  
                 
# activate the worksheet.
ws.activate()

# The value of B2 is read by default, which is a floating-point type
b2_value = ws["B2"].value
print(type(b2_value))
print(b2_value)

# Read the value of B3. The null value here should display None by default.
b3_value = ws["B3"].value
print(type(b3_value))
print(b3_value)

# Set the value of B2 to an integer.
b2_value_int = ws["B2"].options(numbers=int).value
print(type(b2_value_int))
print(b2_value_int)

  4. 另一种获取Excel单元格值的方法。

    import xlwings as xw
import os

# Create the App object.
app=xw.App(visible=True,add_book=False)

# Define the excel file name.                
file = "excel_file_1.xlsm"

# Open the above excel file.
wb=app.books.open(file)

# Get the first Excel worksheet.
ws = wb.sheets["Sheet1"]

# Get the B2 cell value by using the worksheet range() method.
b2_value = ws.range('B2').value

# Print out the B2 cell value.
print(b2_value)

1.3 写数据到Excel

  1. 下面是将数据写入excel文件的代码例子。

    import xlwings as xw
import os

# Create the xlwings App object.
app=xw.App(visible=True,add_book=False)

# Define the target excel file name.                
file = "excel_file_1.xlsm"

# Open the above target excel file.
wb=app.books.open(file)

# Reference the first worksheet.
ws = wb.sheets["Sheet1"]

# Define 3 integer value variables. 
# This value will be placed in column B.
b = 6799
# This value will be placed in column C.
c = 2345
# This value will be placed in column D.
d = 1000

# Get the excel worksheet used range.
info = ws.used_range

#print(info)

# Gets the maximum used row number in the worksheet table
nrows = info.last_cell.row
# Print the row number.
print(nrows)

# If the maximum used excel worksheet row's value is None. 
if ws['B'+str(nrows)]==None:
    # Assign the values to the current row related Excel worksheet column.
    ws['B'+str(int(nrows)-1)].value=b
    ws['C'+str(int(nrows)-1)].value=c
    ws['D'+str(int(nrows)-1)].value=d
# If the maximum used excel worksheet row's value is not None. 
else:
    # Assign the values to the next row related Excel worksheet column.
    ws['B'+str(int(nrows)+1)].value=b
    ws['C'+str(int(nrows)+1)].value=c
    ws['D'+str(int(nrows)+1)].value=d
  
# Save the above data to the Excel file.  
wb.save()

# Close the workbook.
wb.close()

# Exit the Excel file.
app.quit()

avatar
文章
阅读
粉丝