API Reference

This is intended for users of pyexcel.

Signature functions

Obtaining data from excel file

get_array(**keywords)	Obtain an array from an excel source
get_dict([name_columns_by_row])	Obtain a dictionary from an excel source
get_records([name_columns_by_row])	Obtain a list of records from an excel source
get_book_dict(**keywords)	Obtain a dictionary of two dimensional arrays
get_book(**keywords)	Get an instance of Book from an excel source
get_sheet(**keywords)	Get an instance of Sheet from an excel source
iget_array(**keywords)	Obtain a generator of an two dimensional array from an excel source
iget_records(**keywords)	Obtain a generator of a list of records from an excel source
free_resources()	Close file handles opened by signature functions that starts with ‘i’

Saving data to excel file

save_as(**keywords)	Save a sheet from a data source to another one
isave_as(**keywords)	Save a sheet from a data source to another one with less memory
save_book_as(**keywords)	Save a book from a data source to another one
isave_book_as(**keywords)	Save a book from a data source to another one

These flags can be passed on all signature functions:

auto_detect_int

Automatically convert float values to integers if the float number has no decimal values(e.g. 1.00). By default, it does the detection. Setting it to False will turn on this behavior

It has no effect on pyexcel-xlsx because it does that by default.

auto_detect_float

Automatically convert text to float values if possible. This applies only pyexcel-io where csv, tsv, csvz and tsvz formats are supported. By default, it does the detection. Setting it to False will turn on this behavior

auto_detect_datetime

Automatically convert text to python datetime if possible. This applies only pyexcel-io where csv, tsv, csvz and tsvz formats are supported. By default, it does the detection. Setting it to False will turn on this behavior

library

Name a pyexcel plugin to handle a file format. In the situation where multiple plugins were pip installed, it is confusing for pyexcel on which plugin to handle the file format. For example, both pyexcel-xlsx and pyexcel-xls reads xlsx format. Now since version 0.2.2, you can pass on library=”pyexcel-xls” to handle xlsx in a specific function call.

It is better to uninstall the unwanted pyexcel plugin using pip if two plugins for the same file type are not absolutely necessary.

Cookbook

merge_csv_to_a_book(filelist[, outfilename])	merge a list of csv files into a excel book
merge_all_to_a_book(filelist[, outfilename])	merge a list of excel files into a excel book
split_a_book(file_name[, outfilename])	Split a file into separate sheets
extract_a_sheet_from_a_book(file_name, sheetname)	Extract a sheet from a excel book

Book

Here’s the entity relationship between Book, Sheet, Row and Column

Constructor

Book([sheets, filename, path])

Read an excel book that has one or more sheets

Attribute

Book.number_of_sheets()	Return the number of sheets
Book.sheet_names()	Return all sheet names

Conversions

Book.bookdict	Get/Set data in/from bookdict format
Book.url	Set data in url format
Book.csv	Get/Set data in/from csv format
Book.tsv	Get/Set data in/from tsv format
Book.csvz	Get/Set data in/from csvz format
Book.tsvz	Get/Set data in/from tsvz format
Book.xls	Get/Set data in/from xls format
Book.xlsm	Get/Set data in/from xlsm format
Book.xlsx	Get/Set data in/from xlsx format
Book.ods	Get/Set data in/from ods format
Book.stream	Return a stream in which the content is properly encoded

Save changes

Book.save_as(filename, **keywords)	Save the content to a new file
Book.save_to_memory(file_type[, stream])	Save the content to a memory stream
Book.save_to_database(session, tables[, ...])	Save data in sheets to database tables

Sheet

Constructor

Sheet([sheet, name, name_columns_by_row, ...])

Two dimensional data container for filtering, formatting and iteration

Attributes

Sheet.content	Plain representation without headers
Sheet.number_of_rows()	The number of rows
Sheet.number_of_columns()	The number of columns
Sheet.row_range()	Utility function to get row range
Sheet.column_range()	Utility function to get column range

Iteration

Sheet.rows()	Returns a top to bottom row iterator
Sheet.rrows()	Returns a bottom to top row iterator
Sheet.columns()	Returns a left to right column iterator
Sheet.rcolumns()	Returns a right to left column iterator
Sheet.enumerate()	Iterate cell by cell from top to bottom and from left to right
Sheet.reverse()	Opposite to enumerate
Sheet.vertical()	Default iterator to go through each cell one by one from
Sheet.rvertical()	Default iterator to go through each cell one by one from rightmost

Cell access

Sheet.cell_value(row, column[, new_value])	Random access to table cells
Sheet.__getitem__(aset)

Row access

Sheet.row_at(index)	Gets the data at the specified row
Sheet.set_row_at(row_index, data_array)	Update a row data range
Sheet.delete_rows(row_indices)	Delete one or more rows
Sheet.extend_rows(rows)	Take ordereddict to extend named rows

Column access

Sheet.column_at(index)	Gets the data at the specified column
Sheet.set_column_at(column_index, data_array)	Updates a column data range
Sheet.delete_columns(column_indices)	Delete one or more columns
Sheet.extend_columns(columns)	Take ordereddict to extend named columns

Data series

Any column as row name

Sheet.name_columns_by_row(row_index)	Use the elements of a specified row to represent individual columns
Sheet.rownames	Return row names if any
Sheet.named_column_at(name)	Get a column by its name
Sheet.set_named_column_at(name, column_array)	Take the first row as column names
Sheet.delete_named_column_at(name)	Works only after you named columns by a row

Any row as column name

Sheet.name_rows_by_column(column_index)	Use the elements of a specified column to represent individual rows
Sheet.colnames	Return column names if any
Sheet.named_row_at(name)	Get a row by its name
Sheet.set_named_row_at(name, row_array)	Take the first column as row names
Sheet.delete_named_row_at(name)	Take the first column as row names

Conversion

Sheet.array	Get/Set data in/from array format
Sheet.records	Get/Set data in/from records format
Sheet.dict	Get/Set data in/from dict format
Sheet.url	Set data in url format
Sheet.csv	Get/Set data in/from csv format
Sheet.tsv	Get/Set data in/from tsv format
Sheet.csvz	Get/Set data in/from csvz format
Sheet.tsvz	Get/Set data in/from tsvz format
Sheet.xls	Get/Set data in/from xls format
Sheet.xlsm	Get/Set data in/from xlsm format
Sheet.xlsx	Get/Set data in/from xlsx format
Sheet.ods	Get/Set data in/from ods format
Sheet.stream	Return a stream in which the content is properly encoded

Formatting

Sheet.format(formatter)

Apply a formatting action for the whole sheet

Filtering

Sheet.filter([column_indices, row_indices])

Apply the filter with immediate effect

Transformation

Sheet.transpose()
Sheet.map(custom_function)	Execute a function across all cells of the sheet
Sheet.region(topleft_corner, bottomright_corner)	Get a rectangle shaped data out
Sheet.cut(topleft_corner, bottomright_corner)	Get a rectangle shaped data out and clear them in position
Sheet.paste(topleft_corner[, rows, columns])	Paste a rectangle shaped data after a position

Save changes

Sheet.save_as(filename, **keywords)	Save the content to a named file
Sheet.save_to_memory(file_type[, stream])
Sheet.save_to_database(session, table[, ...])	Save data in sheet to database table