Sometimes we need to use Python source code to write data to Excel files that contain VBA script code, and I also want it does not affect the normal execution of VBA code that exists in the Excel files.
At first, I used the functions in Python’s openpyxl module to write the data into the xlsm file. After writing the data, I found that the button for executing VBA code disappeared.
After some investigation, I finally found that the Python openpyxl module does not support VBA, and only the python xlwings module is friendly to support VBA script code. This article will show you an example of how to use the xlwings python module to read/write data from/to excel worksheet.
1. Python xlwings Module Example.
1.1 Install The Python xlwings Module.
- Check whether the python xlwings module has been installed or not.
(MyPythonEnv) C:\Users\zhaosong>pip show xlwings WARNING: Package(s) not found: xlwings
- If the python xlwings module is not installed, then install it with the command 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 - Run the command pip show xlwings again to verify that the python xlwings module has been installed successfully.
(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: [email protected] License: BSD 3-clause Location: c:\users\zhaosong\anaconda3\envs\mypythonenv\lib\site-packages Requires: pywin32 Required-by:
1.2 Read Data From Excel.
- By default, cells with numbers are read as the float type, cells with dates are read as datetime.datetime type, Convert empty cells to None.
- Data can be read in the format specified by the options() method.
- Below is an example.
# 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) - Another way to get excel cell values.
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 Write Data To Excel.
- Below is the code example that will write data to the excel file.
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()
