Skip to main content

How to read and write Excel files in Python

How to read and write Excel files in Python.

Here's a detailed step-by-step tutorial on how to read and write Excel files in Python using the pandas library.

Prerequisites​

Before we begin, make sure you have the following:

  • Python installed on your machine
  • pandas library installed (pip install pandas)

Reading Excel Files​

To read an Excel file in Python, follow these steps:

  1. Import the pandas library:
import pandas as pd
  1. Use the read_excel() function to read the Excel file into a DataFrame:
df = pd.read_excel('path/to/file.xlsx')

Replace 'path/to/file.xlsx' with the actual path to your Excel file.

  1. Access and manipulate the data in the DataFrame as needed:
# Print the first few rows of the DataFrame
print(df.head())

# Access a specific column
column_data = df['Column Name']

# Access a specific cell
cell_data = df.at[row_index, 'Column Name']

Writing Excel Files​

To write data to an Excel file in Python, follow these steps:

  1. Create a DataFrame with the data you want to write:
data = {'Column 1': [1, 2, 3],
        'Column 2': ['A', 'B', 'C']}
df = pd.DataFrame(data)

Replace 'Column 1', 'Column 2', and the corresponding data with your own.

  1. Use the to_excel() function to write the DataFrame to an Excel file:
df.to_excel('path/to/output.xlsx', index=False)

Replace 'path/to/output.xlsx' with the desired path and filename for your output file.

By default, the to_excel() function includes row numbers in the output file. Setting index=False removes the row numbers.

  1. Verify that the file was created and contains the expected data.

Additional Operations​

Here are a few additional operations you can perform when working with Excel files in Python:

Specifying Sheet Names​

By default, read_excel() reads the first sheet of the Excel file. You can specify a different sheet by providing the sheet name or index:

df = pd.read_excel('path/to/file.xlsx', sheet_name='Sheet2')

Replace 'Sheet2' with the desired sheet name or index.

Writing Multiple DataFrames to Different Sheets​

To write multiple DataFrames to different sheets in the same Excel file, you can use the ExcelWriter class from pandas:

with pd.ExcelWriter('path/to/output.xlsx') as writer:
    df1.to_excel(writer, sheet_name='Sheet1', index=False)
    df2.to_excel(writer, sheet_name='Sheet2', index=False)

Replace df1 and df2 with your DataFrames. The ExcelWriter class allows you to write multiple DataFrames to the same Excel file.

Formatting Excel Output​

You can apply various formatting options to the Excel output, such as setting column width, formatting cells, adding borders, etc. For advanced formatting, you may need to use additional libraries like openpyxl or xlrd.

Conclusion​

In this tutorial, we covered the basics of reading and writing Excel files in Python using the pandas library. You learned how to read an Excel file into a DataFrame, access and manipulate the data, write data to an Excel file, and perform additional operations like specifying sheet names and formatting the output. Now you can efficiently work with Excel files in your Python projects!