Using Python Libraries (xlrd, xlwt, openpyxl, pandas) to Read and Write Excel Files

In everyday work we often need to manipulate Excel files; this article demonstrates how to perform Excel operations in Python using several popular libraries.

The libraries covered are xlrd (reading .xls/.xlsx), xlwt (writing .xls), openpyxl (reading/writing .xlsx), and pandas (high‑level data manipulation and Excel I/O).

Installation is straightforward with pip:

<code>pip install xlrd</code>

<code>pip install xlwt</code>

<code>pip install openpyxl</code>

Using xlrd to read an Excel file involves importing the module, opening the workbook, selecting a sheet, and accessing cells by row/column indices.

Writing with xlwt requires creating a Workbook , adding a sheet, writing headers and data row by row, and saving the file:

<code># 使用xlwt生成xls的excel文件
import xlwt
workbook = xlwt.Workbook(encoding='utf-8')
sheet = workbook.add_sheet('瓜子二手车')
for col, column in enumerate(columns):
    sheet.write(0, col, column)
for row, data in enumerate(datas):
    for col, column_data in enumerate(data):
        sheet.write(row+1, col, column_data)
workbook.save('瓜子二手车1.xls')
</code>

With openpyxl you create a Workbook , obtain the active worksheet, append the header row and subsequent data rows, then save as .xlsx:

<code># 使用openpyxl生成xlsx的excel文件
from openpyxl import Workbook
workbook = Workbook()
sheet = workbook.active
sheet.title = '默认title'
sheet.append(columns)
for data in datas:
    sheet.append(data)
workbook.save('瓜子二手车2.xlsx')
</code>

pandas simplifies the process: read a tab‑separated text file into a DataFrame and export it directly to Excel:

<code># 使用pandas生成xlsx的excel文件
import pandas as pd
rcv_data = pd.read_csv('二手车.txt', sep='\t')
rcv_data.to_excel('瓜子二手车3.xlsx', index=False)
</code>

The article includes a concrete example where a txt file containing used‑car data is parsed to obtain column headers and rows (shown with ic(columns) and ic(datas) ), and then the three methods above are used to write the data into Excel files, with screenshots of the resulting files.

Overall, the three approaches—xlwt/xlrd, openpyxl, and pandas—cover most practical scenarios for Excel manipulation in Python and are valuable tools for developers and data analysts.