How to create MS Excel files using Python
MS Excel can be automated using the openpyxl library in Python, which provides various functions and methods.
Creating workbooks
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 theopenpyxllibrary to create a workbook object.Line 3: We use the
.savemethod to save the workbook under our provided name, in this casefile.xlsx.Line 4: Lastly, we close the workbook object once we’re done working with it.
Note: We save the file under the
outputfolder 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.
Creating worksheets
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
worksheetand 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 variableworksheet.Line 12: We again create a sheet and give it a custom name, assign it to
workbook.activeas well asworksheet.
We print the worksheets and can see that worksheet objects are created with varying names.
