Master Python Excel Libraries: xlrd, xlwt, xlutils, xlwings, openpyxl & More

Introduction

This article provides a practical overview of the most commonly used Python libraries for handling Excel files, covering installation, basic usage, and code snippets for each library.

xlrd – Reading Excel

Install: pip install xlrd Read a workbook:

import xlrd
wb = xlrd.open_workbook('file.xls')
sheet = wb.sheet_by_index(0)
value = sheet.cell_value(row, col)

xlwt – Writing Excel

Install: pip install xlwt Create a workbook and write data:

import xlwt
wb = xlwt.Workbook()
sheet = wb.add_sheet('Sheet1')
sheet.write(0, 0, 'Hello')
wb.save('output.xls')

xlutils – Copying and Modifying

Install: pip install xlutils Copy a workbook and edit:

from xlutils.copy import copy
import xlrd
rb = xlrd.open_workbook('source.xls')
wb = copy(rb)
ws = wb.get_sheet(0)
ws.write(0, 0, 'Modified')
wb.save('modified.xls')

xlwings – Advanced Excel Automation

Install: pip install xlwings Open Excel, read/write cells, and create charts:

import xlwings as xw
app = xw.App(visible=True)
wb = xw.Book('file.xlsx')
sheet = wb.sheets[0]
sheet.range('A1').value = 'xlwings'
chart = sheet.charts.add()
chart.set_source_data(sheet.range('A1').expand())
wb.save()
app.quit()

openpyxl – Modern XLSX Manipulation

Install: pip install openpyxl Create a workbook, write data, style cells, and generate charts:

from openpyxl import Workbook
from openpyxl.chart import BarChart, Reference
wb = Workbook()
ws = wb.active
ws.append(['Number', 'Batch 1', 'Batch 2'])
ws.append([5, 10, 15])
chart = BarChart()
chart.add_data(Reference(ws, min_col=2, max_col=3, min_row=1, max_row=2), titles_from_data=True)
ws.add_chart(chart, 'E5')
wb.save('example.xlsx')

XlsxWriter – Fast XLSX Generation

Install: pip install XlsxWriter Write text, numbers, formulas, images, and create charts:

import xlsxwriter
workbook = xlsxwriter.Workbook('demo.xlsx')
worksheet = workbook.add_worksheet()
worksheet.write('A1', 'Hello')
worksheet.write_number('B2', 123)
worksheet.write_formula('C3', '=SUM(B1:B2)')
chart = workbook.add_chart({'type': 'column'})
chart.add_series({'values': '=Sheet1!$B$1:$B$5'})
worksheet.insert_chart('E5', chart)
workbook.close()

win32com – COM Automation (Windows Only)

Install: pip install pypiwin32 Open Excel via COM, read/write cells, and save a copy:

import win32com.client as win32
excel = win32.Dispatch('Excel.Application')
excel.Visible = False
wb = excel.Workbooks.Open(r'C:\path\file.xlsx')
sheet = wb.Worksheets('Sheet1')
value = sheet.Cells(1, 2).Value
sheet.Cells(2, 1).Value = 'win32com'
wb.SaveAs(r'C:\path
ew.xlsx')
wb.Close()
excel.Quit()

pandas – DataFrame Based Excel I/O

Install: pip install pandas Read an Excel sheet into a DataFrame, modify, and write back:

import pandas as pd
df = pd.read_excel('input.xlsx', sheet_name='Sheet1')
df.loc[4] = [4, 'john', 'pandas']
df['new_col'] = None
df.to_excel('output.xlsx', index=False, sheet_name='Sheet1')

Conclusion

By selecting the appropriate library—xlrd/xlwt for legacy .xls files, openpyxl or XlsxWriter for modern .xlsx creation, xlwings for interactive automation, win32com for Windows COM control, or pandas for high‑level data analysis—you can efficiently read, write, and manipulate Excel workbooks in Python.