1. Products
  2.   Spreadsheet
  3.   Python
  4.   PyExcel  

PyExcel  

 
 

Open Source Python Library for Spreadsheets

Read, Create, Merge & Filter Data in CSV, ODS, XLS, XLSX & XLSM Spreadsheet Files via Python API.

PyExcel is an open source single Python API that supports working with several popular spreadsheet formats. It enables software programmers to read, manipulate and write data in CSV, ODS, XLS, XLSX, and XLSM file formats. The API greatly focused on data rather than file formats. The library helps web developers to handle most of the excel file formats by providing a common programming interface.

It is very easy to get or export data from MS Excel files. Mostly it requires one-liner code to get data from the excel files. There is only one API to read and write data in various excel file formats. Several important features are fully supported by the API, such as reading and updating data in a spreadsheet, merging all excel files in one directory, splitting workbook, extract sheet from a workbook, merging rows and cells, styling columns, add or remove a column from a sheet, data filtering and many more. The API provides a list of file formats supported by external plugins. You can use pip to add or remove a plugin.

Previous Next

Getting Started with PyExcel

First of all, you need to have Python 2.6 or higher installed on your system. The recommended way to install is via pip. Please use the following command.

Install PyExcel via PIP Command

 pip install pyexcel

Python API to Work with Excel Spreadsheet

PyExcel API enables software developers to access, read, write and manipulate data in various excel formats such as CSV, ODS, XLS, XLSX, and XLSM. It is very easy to import or export data from Excel files using PyExcel API. It supports adding a new row to an existing file, update a row in an existing file, add or update the column to an existing file, merge or split sheets, search data,  convert excel files to other formats and much more. 

Add New Row to Existing Spreadsheet File via Python API

import pyexcel as pe
sheet = pe.get_sheet(file_name="example.xls")
sheet.row += [12, 11, 10]
sheet.save_as("new_example.xls")
pe.get_sheet(file_name="new_example.xls")

Merge & Split an Excel Worksheet

PyExcel API gives developer the capability to merge files that are scattered in a directory into a single Excel workbook. Each file will become a sheet inside a workbook. It also provides features for mixing and matching with other excel formats like XLS, CSV, XLSM and ODS. It also facilitates you to split a workbook into several files.  Suppose you have a book containing several worksheets, by using the Open source PyExcel library you can separate each into a single sheet excel file.

Merge or Split Excel Files via Python API

//merge two files into one file
from pyexcel.cookbook import merge_two_files
merge_two_files("example.csv", "example.xls", "output.xls")

//Split a workbook into single sheet files

from pyexcel.cookbook import split_a_book
split_a_book("megabook.xls", "output.xls")
import glob
outputfiles = glob.glob("*_output.xls")
for file in sorted(outputfiles):
print(file)

Data Filtering in Spreadsheets

PyExcel API provides features for developers to filter data according to their own needs. Let’s suppose you have a big amount of data and you want to filter out some data from it.  Using the API you can easily do that. It also supports filtering empty rows or columns from a sheet.  You can also save filter data into another file.

How to Filter Out Empty Rows from Excel Sheet inside Python Apps

import pyexcel as pe
sheet = pe.Sheet([[1,2,3],['','',''],['','',''],[1,2,3]])
//define filter
def filter_row(row_index, row):
    result = [element for element in row if element != '']
    return len(result)==0
// Apply filter
del sheet.row[filter_row]
 English