How To Import Data From Python To Excel

In this tutorial, we will learn how to import data from Python into Excel using various packages such as openpyxl, pandas, and xlsxwriter.

Importing data into Excel from Python can be useful when you want to share your analysis results, create reports, or visualize data using Excel’s extensive tools and features.

Step 2: Load the data in Python

Before we can import data into Excel, we must have some data in Python. Here, we will create a simple dataset using Python’s built-in csv module. If you have your own dataset, you can simply load it using pandas.

Example data file (data.csv):

Name,Age,Position
Alice,30,Manager
Bob,25,Analyst
Charlie,35,Director

Load the data using pandas:

1
2
3
4
5
6
7
import pandas as pd
 
# Load data from a CSV file
data = pd.read_csv("data.csv")
 
# Print the dataframe
print(data)

Step 3: Export the data to an Excel file

Now that we have our data, we can export it to an Excel file using pandas and xlsxwriter as the Excel engine.

1
2
# Export the dataframe to an Excel file
data.to_excel("output.xlsx", index=False, engine="xlsxwriter")

This will create a new Excel file named output.xlsx in the current directory with the data from our dataframe.

Step 4: Customize the Excel output (optional)

If you want to customize the Excel output, you can use the xlsxwriter package directly. This allows you to set column widths, format cells, add charts, and more. Here’s an example of how to change the column widths and add bold text formatting:

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
import xlsxwriter
 
# Create a new Excel workbook and worksheet
workbook = xlsxwriter.Workbook("output_custom.xlsx")
worksheet = workbook.add_worksheet()
 
# Define a bold format for header cells
bold = workbook.add_format({"bold": True})
 
# Set column widths
worksheet.set_column("A:A", 15)
worksheet.set_column("B:B", 10)
worksheet.set_column("C:C", 20)
 
# Write header row with bold text format
for col, header in enumerate(data.columns):
    worksheet.write(0, col, header, bold)
 
# Write data rows
for row, record in data.iterrows():
    for col, value in enumerate(record):
        worksheet.write(row + 1, col, value)
 
# Close the workbook
workbook.close()

Now, we have created a new Excel file named output_custom.xlsx with customized column widths and bold header text.

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
import pandas as pd
import xlsxwriter
 
# Load data from a CSV file
data = pd.read_csv("data.csv")
 
# Export the dataframe to an Excel file
data.to_excel("output.xlsx", index=False, engine="xlsxwriter")
 
# Create a new Excel workbook and worksheet
workbook = xlsxwriter.Workbook("output_custom.xlsx")
worksheet = workbook.add_worksheet()
 
# Define a bold format for header cells
bold = workbook.add_format({"bold": True})
 
# Set column widths
worksheet.set_column("A:A", 15)
worksheet.set_column("B:B", 10)
worksheet.set_column("C:C", 20)
 
# Write header row with bold text format
for col, header in enumerate(data.columns):
    worksheet.write(0, col, header, bold)
 
# Write data rows
for row, record in data.iterrows():
    for col, value in enumerate(record):
        worksheet.write(row + 1, col, value)
 
# Close the workbook
workbook.close()

Conclusion

In this tutorial, we learned how to import data from Python to Excel using the pandas, openpyxl, and xlsxwriter packages. We also demonstrated how to load data from a CSV file, export data to an Excel file, and customize the Excel output. With this knowledge, you can now easily share your data analysis results and create reports using Excel.