File Tools

There are a number of tools for Python which help manage reading and writing Excel files. Most have been around for a long time and are mature.

The below list of them came from the website http://www.python-excel.org/.

openpyxl

openpyxl homepage

Licence: MIT/Expat

Openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.

It was born from lack of existing library to read/write natively from Python the Office Open XML format.

It is a comprehensive library to create, modify and save Excel files using operations akin to Excel itself.

Features:

• Cells
• Charts
• Chartsheets
• Comments
• Descriptors
• Drawing
• Formatting
• Pivot Tables
• Read Excel files
• Styles
• Workbook operations
• Worksheet operations
• Write Excel files

Pandas

pandas homepage

Licence:

pandas is an open source, BSD-licensed library providing high-performance, easy-to-use data structures and data analysis tools for the Python programming language.

It is comprehensive for data analyisis and plays well with numpy and scipy. It facilitates a huge range of operations for data analysis. The focus of this section of this review is Excel file operations so I will only list the related aspect. pandas may well be mentioned elsewhere in this review if it does things related to that section.

The read_excel() method can read Excel 2003 (.xls) files using the xlrd Python module. Excel 2007+ (.xlsx) files can be read using either xlrd or openpyxl. Binary Excel (.xlsb) files can be read using pyxlsb. The to_excel() instance method is used for saving a DataFrame to Excel. Generally the semantics are similar to working with csv data. See the cookbook for some advanced strategies.

To write a DataFrame object to a sheet of an Excel file, you can use the to_excel instance method. The arguments are largely the same as to_csv, the first argument being the name of the excel file, and the optional second argument the name of the sheet to which the DataFrame should be written. Files with a .xls extension will be written using xlwt and those with a .xlsx extension will be written using xlsxwriter (if available) or openpyxl.

Features:

• IO tools (xls, xlsx, xlsb, text, CSV, HDF5, …)
  • Excel Files
    • Reading Excel files
      • Specifying sheets
      • Reading a MultiIndex
      • Parsing specific columns
      • Parsing dates
      • Cell converters
      • Dtype specifications
    • Writing Excel files
      • Writing Excel files to disk
      • Writing Excel files to memory
      • Excel writer engines
      • Style and formatting

Pyxlsx

pyxlsx homepage

Licence:

A package to read/write xlsx worksheet like dictionary, based on openpyxl.

• Create a new xlsx file and write to it
• Open an existing xlsx file
• Append rows to a worksheet
• Read from / write to a worksheet by row
• Read from a worksheet by column
• Read cell directly from Worksheet, Header, ContentRow
• Read adjacent cells of a certain cell

xlrd

xlrd homepage

Licence:

xlrd is a library for reading data and formatting information from Excel files, whether they are .xls or .xlsx files.

• Handling of Unicode
• Dates in Excel spreadsheets
• Named references, constants, formulas, and macros
• Formatting information in Excel Spreadsheets
• Loading worksheets on demand
• XML vulnerabilities and Excel files
• API Reference

XlsxWriter

xlsxwriter homepage

Licence:

XlsxWriter is a Python module that can be used to write text, numbers, formulas and hyperlinks to multiple worksheets in an Excel 2007+ XLSX file. It supports features such as formatting and many more, including:

• 100% compatible Excel XLSX files.
• Full formatting.
• Merged cells.
• Defined names.
• Charts.
• Autofilters.
• Data validation and drop down lists.
• Conditional formatting.
• Worksheet PNG/JPEG/BMP/WMF/EMF images.
• Rich multi-format strings.
• Cell comments.
• Textboxes.
• Integration with Pandas.
• Memory optimization mode for writing large files.

It supports Python 2.7, 3.4+ and PyPy and uses standard libraries only.

• Workbook
• Worksheet
• Worksheet (Page Setup)
• Format
• The Chart
• The Chartsheet
• Working with Cell Notation
• Working with and Writing Data
• Working with Formulas
• Working with Dates and Time
• Working with Colors
• Working with Charts
• Working with Object Positioning
• Working with Autofilters
• Working with Data Validation
• Working with Conditional Formatting
• Working with Worksheet Tables
• Working with Textboxes
• Working with Sparklines
• Working with Cell Comments
• Working with Outlines and Grouping
• Working with Memory and Performance
• Working with VBA Macros
• Working with Python Pandas and XlsxWriter

xlutils

xlutils homepage

Licence:

This package provides a collection of utilities for working with Excel files. Since these utilities may require either or both of the xlrd and xlwt packages, they are collected together here, separate from either package. The utilities are grouped into several modules within the package, each of them is documented below:

• xlutils.copy
  • Tools for copying xlrd.Book objects to xlwt.Workbook objects.
• xlutils.display
  • Utility functions for displaying information about xlrd-related objects in a user-friendly and safe fashion.
• xlutils.filter
  • A mini framework for splitting and filtering existing Excel files into new Excel files.
• xlutils.margins
  • Tools for finding how much of an Excel file contains useful data.
• xlutils.save
  • Tools for serializing xlrd.Book objects back to Excel files.
• xlutils.styles
  • Tools for working with formatting information expressed the styles found in Excel files.
• xlutils.view
  • Easy to use views of the data contained in a workbook’s sheets.

xlwt

xlwt homepage

Licence:

xlwt is a library for writing data and formatting information to older Excel files (ie: .xls)

• add_sheet
• save
• write

Formatting

• Number format
• Font
• Alignment
• Border
• Background
• Protection