Use Xlwings To Read Excel: If you want to read or write the Python data using Excel, then using the Xlwings function is the right option for you. This is because most people would like to view or understand the data from Excel rather than open the code background such as Python. Using the Xlwings function, one can easily call the Python data into Excel. In other words, the operations that are executed in Python are displayed in Excel using the Xlwings function.
In this article, let us discuss how to use the Xlwings function in Excel. Read further to find out more.
What Can You Do With Xlwings?
Xlwings is a Python module that allows you to easily invoke/call Python from Excel and vice versa. It quickly creates reading and writing to and from Excel using Python. It can also be customized to function as a Python Server for Excel, allowing Python and Excel to exchange data synchronously. Xlwings simplifies Excel automation with Python and may be used to generate an automatic report, create Excel embedded functions, manipulate Excel or CSV databases, and so on.
Installation of Xlwings:
Use the following command to install the Xlwings module:
pip install xlwings
Output:
Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels /public/simple/ Collecting xlwings Downloading xlwings-0.27.8-py3-none-any.whl (1.0 MB) |████████████████████████████████| 1.0 MB 5.1 MB/s Installing collected packages: xlwings Successfully installed xlwings-0.27.8
To begin using Xlwings, there are a few basic steps that must be followed practically every time. This includes opening an Excel file, viewing the available sheets and then selecting one.
Here we will use Sheet1(it has only 1 sheet) of the following Excel spreadsheet as an example.
Working with Excel files using Python Xlwings
- Reading Excel File and Selecting a Specific Cell value
- Performing Multiple Operations
- Inserting Data Into an Excel File
Method #1: Reading Excel File and Selecting a Specific Cell value
Approach:
- Import xlwings module using the import keyword
- Pass the excel spreadsheet file path to the Book() function of xlwings module to open an excel file and store it in a variable
- Apply sheets attribute on the above workbook object to get all the sheets present in the given excel file and store it in another variable
- Print all the available sheets present in the given excel file
- Select the first Sheet(Sheet1) of the above workbook object.
- Select any particular cell value from the selected sheet of the given Excel File.
- The Exit of the Program.
Below is the implementation:
# Import xlwings module using the import keyword
import xlwings
# Pass the excel spreadsheet file path to the Book() function of xlwings
# module to open an excel file and store it in a variable
workbookObj = xlwings.Book('SampleExcelFile.xlsx')
# Apply sheets attribute on the above workbook object to get all the
# sheets present in the given excel file and store it in another variable
all_worksheets = workbookObj.sheets
# Print all the available sheets present in the given excel file
print("The all available Sheets present in the given excel file :\n", all_worksheets)
# Select the first Sheet(Sheet1) of the above workbook object.
newWorksheet = all_worksheets[0]
# Select any particular cell value from a selected sheet of the given Excel File
A2cell_value = newWorksheet.range("A2").value
print("A2 Cell value in Sheet1 of the SampleExcelFile:", A2cell_value)
Output:
PS C:\Users\cirus\Desktop\crud> python C:\Users\cirus\Desktop\crud\sample.py The all available Sheets present in the given excel file : Sheets([<Sheet [SampleExcelFile (3).xlsx]Sheet1>]) A2 Cell value in Sheet1 of the SampleExcelFile: 2121.0
Method #2: Performing Multiple Operations
Approach:
- Import xlwings module using the import keyword
- Open some random Excel File using the Book() function of xlwings module and select a specific sheet from it and store it in a variable
- Here we select the first Sheet(Sheet1) of the given Excel File
- Select a particular cell value from selected sheet of the given Excel File using the value attribute
- Here we select the values of A1, and A4 cell values
- Select all the rows and print them.
- Select all the columns and print them.
- Select a 2-Dimensional range of data from the excel sheet and print the result.
- The Exit of the Program.
Below is the implementation:
# Import xlwings module using the import keyword
import xlwings
# Open some random Excel File using the Book() function of xlwings module
# and select a specific sheet from the it and store it in a variable
# Here we select the first Sheet(Sheet1) of the given Excel File
newWorksheet = xlwings.Book("SampleExcelFile.xlsx").sheets['Sheet1']
# Select a particular cell value from selected sheet of the given Excel File
# using the value attribute
# Here we select the values of A1, A4 cell values
A1cell_value = newWorksheet.range("A1").value
A4cell_value = newWorksheet.range("A4").value
print("A1 cell Value = ", A1cell_value)
print("A4 cell Value = ", A4cell_value)
# Select all the rows and print them
all_rows = newWorksheet.range("3:3").value
print("The all Rows:\n", all_rows)
# Select all the columns and print them
all_cols = newWorksheet.range("C:C").value
print("The all Columns:\n", all_cols)
# Select a 2-Dimensional range of data from the excel sheet and print the result
twoDim_table = newWorksheet.range("A1:C4").value
print("2-Dimensional range of data from the Excel sheet:\n", twoDim_table)
Output:
A1 cell Value = EmployeeId A4 cell Value = 2123.0
None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None, None,.......
2-Dimensional range of data from the Excel sheet: [['EmployeeId', ' EmployeeName', ' Salary'], [2121.0, 'Danny', 25000.0], [2122.0, 'Sindhu', 50000.0], [2123.0, 'Steve', 40000.0]]
Method #3: Inserting Data Into an Excel File
Approach:
- Import xlwings module using the import keyword
- Open some random Excel File using the Book() function of xlwings module and select a specific sheet from the it and store it in a variable
- Here we select the first Sheet(Sheet1) of the given Excel File
- Insert data into the A1 cell using the value attribute
- Insert multiple values into the B1 cell using the value attribute
- Insert 2-Dimensional data values into the C1 cell using the value attribute.
- The Exit of the Program.
Below is the implementation:
# Import xlwings module using the import keyword
import xlwings
# Open some random Excel File using the Book() function of xlwings module
# and select a specific sheet from the it and store it in a variable
# Here we select the first Sheet(Sheet1) of the given Excel File
newWorksheet = xlwings.Book("SampleExcelFile.xlsx").sheets['Sheet1']
# Inserting data into A1 cell using the value attribute
newWorksheet.range("A1").value = "Welcome"
# Inserting multiple values into B1 cell using the value attribute
newWorksheet.range("B1").value = "to", "the"
# Inserting 2-Dimensional data values into C1 cell using the value attribute
newWorksheet.range("C1").value = [["SheetsTips"],["Btechgeeks"]]
Output:
Now that you are provided with all the necessary information on how to use the Xlwings function in Google Spreadsheet. Xlwings allows us to create interactive apps that employ Excel spreadsheets as the GUI (graphical user interface), with Python handling the backend tasks that may be accessed by clicking a button in Excel or using a UDF (user-defined function).