How to Create Excel Files in Python

If you’ve been working with Python and handling data, you might have felt the need to create or manipulate Excel files as part of your data workflow.

Python offers several libraries for creating, reading and modifying Excel files, including xlwt, openpyxl, and pandas. In this tutorial, we’ll be looking at creating Excel files using openpyxl and pandas.

Step 1: Installing Required Libraries

Before we start with creating Excel files, ensure you have the necessary libraries installed. You can install these libraries using pip, a package installer for Python. Use the following command in your command window:

1
pip install openpyxl pandas

Step 2: Importing the Required Libraries

Import the libraries in your Python script as follows:

1
2
import openpyxl
import pandas as pd

Step 3: Creating an Excel File using Openpyxl

Let’s start by creating a workbook using openpyxl and saving it to a file:

1
2
wb = openpyxl.Workbook()
wb.save('sample.xlsx')

Step 4: Writing Data to Excel File using Openpyxl

Next, let’s write some data to the Excel file. Create a new worksheet and write data:

1
2
3
4
ws = wb.create_sheet('My Sheet')
ws['A1'] = "This is A1"
ws['B2'] = "This is B2"
wb.save("sample.xlsx")

Step 5: Creating an Excel File using Pandas

Pandas offer a simpler method to create Excel files with its DataFrame to_excel() function:

1
2
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
df.to_excel('sample_pandas.xlsx', sheet_name='sample', index=False)

Before Conclusion: Full Code

1
2
3
4
5
6
7
8
9
10
11
12
13
import openpyxl
import pandas as pd
 
wb = openpyxl.Workbook()
wb.save('sample.xlsx')
 
ws = wb.create_sheet('My Sheet')
ws['A1'] = "This is A1"
ws['B2'] = "This is B2"
wb.save("sample.xlsx")
 
df = pd.DataFrame({'Data': [10, 20, 30, 20, 15, 30, 45]})
df.to_excel('sample_pandas.xlsx', sheet_name='sample', index=False)

Conclusion

From creating basic Excel files to writing data to specific cells or even creating Excel files from pandas DataFrames, Python offers robust functionality for Excel file manipulation. As a next step, you may explore other functionalities of openpyxl and pandas for handling Excel files like reading data, modifying existing files, using formulas and more.