Excel has become essential data processing software, almost every day in use. Xlwings is one of python’s many third-party libraries for manipulating Excel.

About xlwings

Xlwings is open source and free. It is very easy to read and write data in Excel files and modify cell formats.

Xlwings also works seamlessly with Matplotlib, NUMpy, and Pandas. It can read and write numpy and PANDAS data types, and import Matplotlib visuals into Excel.

Xlwings can call programs written by VBA in Excel files or have VBA call programs written in Python.

Xlwings installation and import

The Python version is 3.6 and the operating system is Windows. Experiments are carried out in The Jupyter Notebook.

Xlwings library with PIP installation:

pip install xlwings
Copy the code

Xlwings import:

import xlwings as xw
Copy the code

Xlwings field

,

The excel table joins

wb = xw.Book("e:\example.xlsx")
Copy the code

,

sht = wb.sheets["sheet1"]
Copy the code

,

wb.fullname
Copy the code

,

sht.range('A1').value = "xlwings"
Copy the code

,

sht.range('A1').value
Copy the code

,

sht.range('A1').clear()
Copy the code

,

sht.range('A1').column
Copy the code

,

sht.range('A1').row
Copy the code

,

sht.range('A1').row_height
Copy the code

,

sht.range('A1').row_height
Copy the code

Xlwings interacts with NUMpy, pandas, and matplotlib

,

Numpy array Data type

import numpy as np

import numpy as np
Copy the code

,

Pandas DataFrame The data type is written to excel

Import pandas as PDDF = pd.dataframe ([[1,2], [3,4]], columns=['a', 'b'])sht.range('A5').value = df

Xlwings and VBA call each other

Xlwings works perfectly with VBA. You can call VBA from Python, and you can program in Python from VBA. We won’t go into details here, but if you’re interested, you can go to xlwings.

conclusion

Xlwings is an excellent tool for manipulating data in Python and Excel. It combines python, Numpy, and Matplotlib.