MS Excel can be automated using the openpyxl
library in Python, which provides various functions and methods.
To create MS Excel files with a .xlsx
extension, we can utilize the openpyxl
library to first create an empty workbook and then proceed to save that workbook with a provided name as shown in the widget below:
import openpyxlworkbook = openpyxl.Workbook()workbook.save("output/file.xlsx")workbook.close()
Line 2: We use the Workbook()
constructor present in the openpyxl
library to create a workbook object.
Line 3: We use the .save
method to save the workbook under our provided name, in this case file.xlsx
.
Line 4: Lastly, we close the workbook object once we’re done working with it.
Note: We save the file under the
output
folder in the widget above as that lets us see the created file. Alternatively, if we were to simply save in the current folder, we would not see the file when we run the code.In real life application, we would save the created files into our directory as per our requirement.
When we create an empty workbook, an empty worksheet is added to the workbook by default and selected as the active sheet, as it is impossible to work with an excel workbook without using a worksheet.
We could access that worksheet in the following manner:
worksheet = empty_wb.active
We could also create additional worksheets as required using the create_sheet()
method provided by the workbook object. In addition, we could add an optional argument to create_sheet()
to specify the name we want to assign to our newly created worksheet. We can see the aforementioned functions in the following code snippet:
import openpyxlworkbook = openpyxl.Workbook()workbook.save("file.xlsx")worksheet = workbook.activeprint("Current worksheet:", workbook.active)worksheet = workbook.active = workbook.create_sheet()print("Current worksheet:", worksheet)worksheet = workbook.active = workbook.create_sheet('newSheet')print("Current worksheet:", worksheet)print(workbook.sheetnames)workbook.close()
Line 6: We create a variable worksheet
and assign the active sheet to it. The active sheet in this case is the worksheet created by default at the creation of a workbook.
Line 9: We create a sheet and assign it both as the active sheet using workbook.active
, and assign it to the variable worksheet
.
Line 12: We again create a sheet and give it a custom name, assign it to workbook.active
as well as worksheet
.
We print the worksheets and can see that worksheet objects are created with varying names.
Unlock your potential: MS Excel essentials series, all in one place!
To deepen your understanding of MS Excel, explore our series of Answers below:
How to create MS Excel files using Python
Learn how to generate and manipulate Excel files using Python libraries like openpyxl
and pandas
.
How to create MS Excel files using Julia
Discover how to create and modify Excel files using Julia with packages like XLSX.jl
for data analysis.
Common aggregate functions in MS Excel
Explore key aggregate functions like SUM, AVERAGE, MAX, and MIN to perform data calculations efficiently.
How to count values in MS Excel
Learn to count cells, numbers, and specific values using functions like COUNT
, COUNTA
, and COUNTIF
.
How to calculate sum in MS Excel
Use the SUM
function to add numbers across rows, columns, and custom ranges.
How to calculate average in MS Excel
Apply the AVERAGE
function to find the mean of a dataset quickly.
How to calculate maximum in MS Excel
Learn how to use the MAX
function to find the highest value in a range.
How to calculate the product in MS Excel
Use the PRODUCT
function to multiply values in a selected range.
How to calculate the median in MS Excel
Understand how the MEDIAN
function determines the middle value in a dataset.
How to calculate minimum in MS Excel
Use the MIN
function to identify the smallest value within a range.
How to calculate the mode in MS Excel
Find the most frequently occurring value using the MODE
function.
Calculate student grades from percentage in MS Excel
Learn how to automate grade calculation using conditional formulas like IF
, LOOKUP
, and VLOOKUP
.
Free Resources