Python Libraries for CSV and Excel File Processing: csv, openpyxl, pandas, xlsxwriter, and More

This article provides a comprehensive overview of Python modules for handling CSV and various Excel file formats, outlining their purposes, main features, suitable scenarios, installation steps, and practical code examples.

1. csv module

Purpose: Read and write CSV data.

Features: Built‑in to the Python standard library, simple read/write functions, ideal for lightweight tabular data without complex formatting.

Typical use case: Simple data exchange between systems.

Reading a CSV file

name,age,city
Alice,30,New York
Bob,22,Los Angeles
Charlie,35,Chicago

import csv
with open('example.csv', mode='r', newline='', encoding='utf-8') as file:
    csv_reader = csv.reader(file)
    next(csv_reader)  # skip header
    for row in csv_reader:
        print(f'Name: {row[0]}, Age: {row[1]}, City: {row[2]}')

Output:

Name: Alice, Age: 30, City: New York
Name: Bob, Age: 22, City: Los Angeles
Name: Charlie, Age: 35, City: Chicago

2. openpyxl

Purpose: Read/write .xlsx Excel files with style and chart support.

Features: Create/modify workbooks, control cell styles, add charts.

Installation: pip install openpyxl Example:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
wb = Workbook()
ws = wb.active
ws.title = "员工信息"
header = ["姓名", "年龄", "城市"]
for col_num, column_title in enumerate(header, 1):
    cell = ws.cell(row=1, column=col_num)
    cell.value = column_title
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill(start_color="0070C0", end_color="0070C0", fill_type="solid")
    cell.alignment = Alignment(horizontal='center', vertical='center')
# add data rows ...
wb.save('employees.xlsx')

3. pandas

Purpose: Powerful data analysis, reading/writing CSV, Excel, and many other formats.

Features: DataFrame structure, extensive data manipulation functions, efficient numeric and statistical operations.

Installation: pip install pandas Example – CSV to Excel:

import pandas as pd
df = pd.read_csv('example.csv')
print(df)
df.to_excel('example.xlsx', index=False)

4. xlsxwriter

Purpose: Create .xlsx files with rich formatting and charts.

Features: Focus on generation, supports formats, conditional formatting, charts; cannot edit existing files.

Installation: pip install xlsxwriter Example:

import xlsxwriter
workbook = xlsxwriter.Workbook('report.xlsx')
worksheet = workbook.add_worksheet()
data = [['月份','销售额'],['一月',1200],['二月',1500],['三月',1800],['四月',2000],['五月',2200]]
for row_num, (month, sales) in enumerate(data):
    worksheet.write(row_num, 0, month)
    worksheet.write(row_num, 1, sales)
chart = workbook.add_chart({'type':'column'})
chart.add_series({'categories':'=Sheet1!$A$2:$A$6','values':'=Sheet1!$B$2:$B$6','name':'销售额'})
chart.set_title({'name':'月度销售额'})
worksheet.insert_chart('D2', chart)
workbook.close()

5. pyexcel

Purpose: Unified API for multiple spreadsheet formats (CSV, TSV, ODS, XLSX, etc.).

Features: Supports many formats, simple API, extensible via plugins.

Installation: pip install pyexcel pyexcel-xls pyexcel-xlsx Example – CSV to XLSX:

import pyexcel as pe
sheet = pe.get_sheet(file_name='example.csv')
sheet.save_as('example.xlsx')

6. xlrd & xlwt

Purpose: xlrd reads old .xls files; xlwt writes .xls files.

Installation: pip install xlrd xlwt Reading .xls:

import xlrd
wb = xlrd.open_workbook('input.xls')
sh = wb.sheet_by_index(0)
for row_idx in range(sh.nrows):
    for col_idx in range(sh.ncols):
        print(sh.cell_value(row_idx, col_idx), end='\t')
    print()

Writing .xls:

import xlwt
wb = xlwt.Workbook()
ws = wb.add_sheet('员工信息')
headers = ['姓名','年龄','城市']
for col_idx, header in enumerate(headers):
    ws.write(0, col_idx, header)
data = [('Alice',30,'New York'),('Bob',22,'Los Angeles'),('Charlie',35,'Chicago')]
for row_idx, (name, age, city) in enumerate(data, start=1):
    ws.write(row_idx, 0, name)
    ws.write(row_idx, 1, age)
    ws.write(row_idx, 2, city)
wb.save('output.xls')

7. xlutils

Purpose: Combine xlrd and xlwt to modify existing .xls files.

Installation: pip install xlrd xlwt xlutils Example – Append data:

import xlrd
from xlutils.copy import copy
rb = xlrd.open_workbook('input.xls', formatting_info=True)
wb = copy(rb)
ws = wb.get_sheet(0)
new_data = [('David',28,'Houston'),('Eva',24,'Boston')]
start_row = rb.sheet_by_index(0).nrows
for row_idx, (name, age, city) in enumerate(new_data, start=start_row):
    ws.write(row_idx, 0, name)
    ws.write(row_idx, 1, age)
    ws.write(row_idx, 2, city)
wb.save('modified_input.xls')

8. pyexcel‑xls & pyexcel‑xlsx

Plugins that let pyexcel handle .xls (via xlrd/xlwt) and .xlsx (via openpyxl) files.

Installation: pip install pyexcel pyexcel-xls pyexcel-xlsx Example – Convert .xls to .xlsx:

import pyexcel as pe
sheet = pe.get_sheet(file_name='example.xls')
sheet.save_as('example.xlsx')

9. tablib

Purpose: Small data‑management library supporting CSV, Excel, JSON, etc.

Installation: pip install tablib Example – Export to CSV and XLSX:

import tablib
data = tablib.Dataset()
data.headers = ['姓名','年龄','城市']
data.append(('Alice',30,'New York'))
data.append(('Bob',22,'Los Angeles'))
data.append(('Charlie',35,'Chicago'))
with open('employees_tablib.csv','w',encoding='utf-8') as f:
    f.write(data.export('csv'))
with open('employees_tablib.xlsx','wb') as f:
    f.write(data.export('xlsx'))

10. ezodf

Purpose: Read/write OpenDocument Spreadsheet (.ods) files.

Installation: pip install ezodf Example – Create .ods:

import ezodf
doc = ezodf.newdoc(doctype='ods', filename='output.ods')
sheet = ezodf.Sheet('员工信息', size=(4,3))
doc.sheets += sheet
headers = ['姓名','年龄','城市']
for col_idx, header in enumerate(headers):
    sheet[(0, col_idx)].set_value(header)
# add data rows ...
doc.save()

11. xlwings

Purpose: Control the Excel application itself from Python, enabling VBA‑style automation.

Installation: pip install xlwings Example – Read and write:

import xlwings as xw
wb = xw.Book('example.xlsx')
sheet = wb.sheets[0]
value = sheet.range('A1').value
print(f'Value in A1: {value}')
wb.close()
# create new workbook
wb = xw.Book()
sheet = wb.sheets.add('员工信息')
headers = ['姓名','年龄','城市']
for col_idx, header in enumerate(headers, start=1):
    sheet.range((1, col_idx)).value = header
# add data rows ...
wb.save('output_xlwings.xlsx')
wb.close()

12. readxl

Purpose: Lightweight library for reading Excel files (no write support).

Installation: pip install readxl Example – Read .xlsx:

import readxl
wb = readxl.load_workbook('example.xlsx')
sheet = wb.sheet_by_index(0)
for row in range(sheet.nrows):
    for col in range(sheet.ncols):
        print(sheet.cell(row, col).value, end='\t')
    print()

Conclusion

For simple CSV handling, the built‑in csv module is sufficient. When Excel-specific features or complex formatting are required, openpyxl or xlsxwriter are appropriate. For large‑scale data analysis, pandas offers the most power, while pyexcel provides a unified interface across many spreadsheet formats.