Using Python Pandas to Read and Write Excel Files

This article explains how to set up a Python development environment, install Pandas and its dependencies, and perform basic Excel read/write operations using Pandas.

Installation : Download Python 3.6.5 from the official website, configure environment variables, and use VS Code with the Python extension. Install Pandas and required packages with:

pip install pandas
pip install xlrd
pip install openpyxl

Official documentation can be found at pandas.pydata.org and the Chinese site www.pypandas.cn .

Basic usage : After installing, you can start an interactive Python session and import Pandas:

import pandas as pd
from pandas import DataFrame

Creating a simple DataFrame and writing it to an Excel file:

import pandas as pd
from pandas import DataFrame

dic = {'标题列1': ['malena', 'morgan'], '标题列2': [36, 34]}
df = pd.DataFrame(dic)
df.to_excel('write_test.xlsx', index=False)

Reading an Excel file (the default read_excel uses xlrd, which only supports .xls in recent versions). To read .xlsx files you can either install an older xlrd version or use openpyxl as the engine:

# Install older xlrd version
pip uninstall xlrd
pip install xlrd==1.2.0

# Or use openpyxl
df = pandas.read_excel('data.xlsx', engine='openpyxl')

Example of reading a specific sheet and printing the data:

data = pd.read_excel('zmy-weekly.xlsx', sheet_name='3月', engine='openpyxl')
print(data)

Modifying cell values can be done via data.loc, which works like a two‑dimensional array. After changes, write the updated DataFrame back to a new Excel file:

DataFrame(data).to_excel('new.xlsx', sheet_name='3月', index=False, header=True)

The article also provides a complete script that reads a weekly report Excel file, updates several cells, and saves the result:

import pandas as pd
from pandas import DataFrame

def write_weekly():
    data = pd.read_excel('zmy-weekly.xlsx', sheet_name='3月', engine='openpyxl')
    print(data)
    # Example modifications
    data.loc[2][0] = '3月5周'
    data.loc[2][2] = '工作事项4'
    # ... (additional modifications) ...
    DataFrame(data).to_excel('new.xlsx', sheet_name='3月', index=False, header=True)

write_weekly()

Finally, the author reflects that while direct Excel editing might be faster, using Python and Pandas provides a reproducible and automated way to manage weekly reports.