In this tutorial, we will learn how to write to Excel files in Python using the popular library openpyxl. This library allows you to interact with Excel files such as reading, writing, and creating new workbooks in a simple and efficient manner.
Step 1: Installing openpyxl
To get started, we first need to install the openpyxl library. You can do this using the following command:
|
1 |
pip install openpyxl |
Step 2: Creating a Workbook
In order to write data to an Excel file, we need to create a workbook. A workbook is simply an Excel file that contains one or more sheets. With openpyxl, you can create a new workbook using the Workbook() class.
|
1 2 3 4 5 6 7 |
from openpyxl import Workbook # Create a new workbook workbook = Workbook() # Save the workbook to a file workbook.save("example.xlsx") |
When you run this code, it will create a new Excel file named example.xlsx in the same directory as your Python script.
Step 3: Adding Data to a Worksheet
To add data to an Excel sheet, we first need to access the sheet we want to write to. By default, a new workbook created with openpyxl contains a single sheet named Sheet. You can access this sheet using the active property of the workbook.
|
1 2 3 4 5 6 7 8 9 10 |
# Access the default sheet worksheet = workbook.active # Write some data to the sheet worksheet["A1"] = "Name" worksheet["B1"] = "Age" worksheet["C1"] = "Country" # Save the workbook workbook.save("example.xlsx") |
You can also add data to the sheet using the cell() method, which takes row and column numbers as arguments. For example:
|
1 2 3 4 5 6 7 |
# Write data to the second row worksheet.cell(row=2, column=1).value = "Alice" worksheet.cell(row=2, column=2).value = 30 worksheet.cell(row=2, column=3).value = "USA" # Save the workbook workbook.save("example.xlsx") |
Step 4: Adding Rows and Columns
In many cases, you will want to write multiple rows or columns of data to the sheet. You can do this easily using the append() method, which takes a list of values as input and adds a new row to the sheet.
|
1 2 3 4 5 6 |
# Add new rows to the sheet worksheet.append(["Bob", 25, "UK"]) worksheet.append(["Charlie", 22, "Canada"]) # Save the workbook workbook.save("example.xlsx") |
Step 5: Changing Worksheet Properties
Before we conclude, let’s explore how to change some basic properties of the worksheet such as its name, and column widths.
|
1 2 3 4 5 6 7 8 9 10 |
# Change the sheet name worksheet.title = "People" # Set the width of columns A, B, and C worksheet.column_dimensions['A'].width = 15 worksheet.column_dimensions['B'].width = 5 worksheet.column_dimensions['C'].width = 10 # Save the workbook workbook.save("example.xlsx") |
The Full Code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
from openpyxl import Workbook # Create a new workbook workbook = Workbook() # Access the default sheet worksheet = workbook.active # Write some data to the sheet worksheet["A1"] = "Name" worksheet["B1"] = "Age" worksheet["C1"] = "Country" # Write data to the second row worksheet.cell(row=2, column=1).value = "Alice" worksheet.cell(row=2, column=2).value = 30 worksheet.cell(row=2, column=3).value = "USA" # Add new rows to the sheet worksheet.append(["Bob", 25, "UK"]) worksheet.append(["Charlie", 22, "Canada"]) # Change the sheet name worksheet.title = "People" # Set the width of columns A, B, and C worksheet.column_dimensions['A'].width = 15 worksheet.column_dimensions['B'].width = 5 worksheet.column_dimensions['C'].width = 10 # Save the workbook workbook.save("example.xlsx") |
Conclusion
In this tutorial, we have learned how to create, write, and modify Excel files in Python using the openpyxl library. With this library, you can easily manipulate workbooks, sheets, and data in a simple and efficient way. You can even use openpyxl to apply formatting, generate charts, and more to create professional-looking Excel files programmatically. Happy coding!