Python for Excel, Word, and CSV Manipulation: Tools, Installation, and Code Examples

Data processing is a major use case for Python, and Excel is the most popular data‑handling software, so Python developers often need to work with Excel files. Several ready‑made libraries exist, including xlrd & xlwt & xlutils , XlsxWriter , and OpenPyXL , as well as the native Windows Excel COM interface.

The following table compares the capabilities of these tools:

Type

xlrd&xlwt&xlutils

XlsxWriter

OpenPyXL

Excel Open Interface

Read

Supported

Not supported

Supported

Supported

Write

Supported

Supported

Supported

Supported

Modify

Supported

Not supported

Supported

Supported

xls

Supported

Not supported

Not supported

Supported

xlsx

High version

Supported

Supported

Supported

Large file

Not supported

Supported

Supported

Not supported

Efficiency

Fast

Fast

Fast

Very slow

Features

Weak

Powerful

Average

Very powerful

Below is a quick guide to using the xlrd & xlwt & xlutils suite.

xlrd & xlwt & xlutils Introduction

xlrd : read Excel files;

xlwt : write Excel files;

xlutils : utilities for copying, splitting, filtering, etc.

Installation

Install the three packages with pip:

$ pip install xlrd xlwt xlutils

Writing Excel

Example code to create a workbook with two sheets and write sample data:

# import xlwt library
import xlwt

# create xls file object
wb = xlwt.Workbook()

# add two sheets
sh1 = wb.add_sheet('成绩')
sh2 = wb.add_sheet('汇总')

# write header
sh1.write(0, 0, '姓名')
sh1.write(0, 1, '专业')
sh1.write(0, 2, '科目')
sh1.write(0, 3, '成绩')

# write rows
sh1.write(1, 0, '张三')
sh1.write(1, 1, '信息与通信工程')
sh1.write(1, 2, '数值分析')
sh1.write(1, 3, 88)

sh1.write(2, 0, '李四')
sh1.write(2, 1, '物联网工程')
sh1.write(2, 2, '数字信号处理分析')
sh1.write(2, 3, 95)

sh1.write(3, 0, '王华')
sh1.write(3, 1, '电子与通信工程')
sh1.write(3, 2, '模糊数学')
sh1.write(3, 3, 90)

# write summary sheet
sh2.write(0, 0, '总分')
sh2.write(1, 0, 273)

# save file
wb.save('test.xls')

Running the script generates test.xls which can be opened in Excel to view the data.

Reading Excel

Example code to open the previously created file and print sheet information and cell values:

# import xlrd library
import xlrd

# open workbook
wb = xlrd.open_workbook('test_w.xls')
print('sheet 数量:', wb.nsheets)
print('sheet 名称:', wb.sheet_names())

sh1 = wb.sheet_by_index(0)
print('sheet %s 共 %d 行 %d 列' % (sh1.name, sh1.nrows, sh1.ncols))
print('第一行第二列的值为:', sh1.cell_value(0, 1))

rows = sh1.row_values(0)
cols = sh1.col_values(1)
print('第一行的值为:', rows)
print('第二列的值为:', cols)

for sh in wb.sheets():
    for r in range(sh.nrows):
        print(sh.row(r))

The output includes sheet count, names, and cell contents. The cell type codes are explained in the following table:

Value

Type

Description

0

empty

空

1

string

字符串

2

number

数字

3

date

日期

4

boolean

布尔值

5

error

错误

Modifying Excel

Using xlutils to copy an existing workbook and edit cells:

# import modules
import xlrd
from xlutils.copy import copy

# open original file
readbook = xlrd.open_workbook('test_w.xls')

# copy workbook
wb = copy(readbook)

# edit first sheet
sh1 = wb.get_sheet(0)
sh1.write(4, 0, '王欢')
sh1.write(4, 1, '通信工程')
sh1.write(4, 2, '机器学习')
sh1.write(4, 3, 89)

# edit second sheet
sh1 = wb.get_sheet(1)
sh1.write(1, 0, 362)

# save changes
wb.save('test.xls')

The modified file shows the new rows and updated totals.

Formatting and Advanced Features

By setting styles, fonts, number formats, and cell merging, you can produce richer Excel files. Example:

# import xlwt
import xlwt

styleBR = xlwt.easyxf('font: name Times New Roman, color-index red, bold on')
styleNum = xlwt.easyxf(num_format_str='#,##0.00')
styleDate = xlwt.easyxf(num_format_str='YYYY-MM-DD')

wb = xlwt.Workbook()
sh1 = wb.add_sheet('成绩')
sh2 = wb.add_sheet('汇总')

sh1.write(0, 0, '姓名', styleBR)
sh1.write(0, 1, '日期', styleBR)
sh1.write(0, 2, '成绩', styleBR)

sh1.write(1, 0, '张三')
sh1.write(1, 1, '2020-07-01', styleDate)
sh1.write(1, 2, 90, styleNum)

sh1.write_merge(3, 3, 0, 1, '总分', xlwt.Alignment())
sh1.write(3, 2, xlwt.Formula('C2+C3'))

wb.save('test.xls')

Python Manipulating Word Documents

Installing python-docx

Install the python-docx library (version 0.8.10) with:

$ pip install python-docx

Writing Word

Create a document, add a title, headings, paragraphs, and save:

# import libraries
from docx import Document
from docx.shared import Pt, Inches
from docx.oxml.ns import qn

# new document
doc1 = Document()

doc1.add_heading('如何使用 Python 创建和操作 Word', 0)

doc1.add_paragraph(' Word 文档在我们现在的生活和工作中都用的比较多 ...')

doc1.add_heading('安装 python-docx 库', 1)

doc1.save('word1.docx')

Further examples demonstrate adding hierarchical headings, styled runs (font size, bold, italic, underline, color), bullet and numbered lists, images, and tables. Code snippets are wrapped in ... tags.

Reading Word

Read paragraphs, headings, and tables from an existing document:

# import Document
from docx import Document

# open document
doc1 = Document('word1.docx')
paragraphs = [p.text for p in doc1.paragraphs]
print('Paragraphs:', paragraphs)

# read tables
for table in doc1.tables:
    for row in table.rows:
        print([cell.text for cell in row.cells])

Python Manipulating CSV Files

Introduction

CSV (Comma‑Separated Values) stores tabular data as plain text. It is widely used for data exchange between programs.

CSV vs Excel

CSV

Excel

File extension .csv

File extension .xls or .xlsx

Plain‑text file

Binary file

Stores data only, no formatting

Can store data, formulas, formatting

Can be opened with text editors

Opened only with Excel

One header row only

Each cell has start/end markers

Lower memory consumption on import

Higher memory consumption

Basic Usage

Write CSV data using the csv.writer class:

import csv

with open('test.csv', 'w', newline='') as csvfile:
    writer = csv.writer(csvfile)
    writer.writerow(['id', 'name', 'age'])
    data = [('1001', '张三', '21'), ('1002', '李四', '31')]
    writer.writerows(data)

Read CSV data using the csv.reader class:

import csv

with open('test.csv', newline='') as csvfile:
    reader = csv.reader(csvfile, delimiter=' ')
    for row in reader:
        print(', '.join(row))

Sniffer Class

Detect CSV dialect and header presence:

import csv

with open('test.csv', newline='') as csvfile:
    dialect = csv.Sniffer().sniff(csvfile.read(1024))
    csvfile.seek(0)
    reader = csv.reader(csvfile, dialect)
    for row in reader:
        print(row)

Reader and Writer Objects

Key methods and attributes include next() , dialect , line_num for readers, and writerow() , writerows() , writeheader() for writers.

For complete code examples and screenshots, refer to the original article.