API Reference

This is intended for users of pyexcel.

Note

sphinx on ReadTheDocs cannot produce api docs. Please read it from [pypi](http://pythonhosted.org/pyexcel/api.html)

Signature functions

These flags can be passed on to control plugin behaviors:

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.

Alternatively, you could uninstall the unwanted pyexcel plugin using pip.

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

Saving data to excel file

save_as(**keywords) Save a sheet from a data srouce to another one
save_book_as(**keywords) Save a book from a data source to another one

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

_images/entity-relationship-diagram.png

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.to_dict() Convert the book to a dictionary

Save changes

Book.save_to(source) Save to a writeable data source
Book.save_as(filename) 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

Save changes

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

Attributes

Sheet.row Row representation.
Sheet.column Column representation.
Sheet.number_of_rows() Number of rows in the data sheet
Sheet.number_of_columns() Number of columns in the data sheet
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 the data 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
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
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

Formatting

Sheet.format(formatter[, on_demand]) Apply a formatting action for the whole sheet
Sheet.apply_formatter(aformatter) Apply the formatter immediately.
Sheet.add_formatter(aformatter) Add a lazy formatter.
Sheet.remove_formatter(aformatter) Remove a formatter
Sheet.clear_formatters() Clear all formatters
Sheet.freeze_formatters() Apply all added formatters and clear them

Filtering

Sheet.filter(afilter) Apply the filter with immediate effect
Sheet.add_filter(afilter) Apply a filter
Sheet.remove_filter(afilter) Remove a named filter
Sheet.clear_filters() Clears all filters
Sheet.freeze_filters() Apply all filters and delete them

Conversion

Sheet.to_array() Returns an array after filtering
Sheet.to_dict([row]) Returns a dictionary
Sheet.to_records([custom_headers]) Returns the content as an array of dictionaries

Anti-conversion

dict_to_array(*arg, **keywords)
from_records(*arg, **keywords)

Transformation

Sheet.transpose() Roate the data table by 90 degrees
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

Row access

NamedRow(matrix) Series Sheet would have Named Row instead of Row
NamedRow.format([row_index, formatter, ...]) Format a row
NamedRow.select(names) Delete row indices other than specified

Column access

NamedColumn(matrix) Series Sheet would have Named Column instead of Column
NamedColumn.format([column_index, ...]) Format a column
NamedColumn.select(names) Delete columns other than specified

Data formatters

ColumnFormatter(column_index, formatter) Apply formatting on columns
NamedColumnFormatter(column_index, formatter) Apply formatting using named columns
RowFormatter(row_index, formatter) Row Formatter
NamedRowFormatter(row_index, formatter) Formatting rows using named rows
SheetFormatter(formatter) Apply the formatter to all cells in the sheet

Data Filters

ColumnFilter(indices) Filters out a list of columns
SingleColumnFilter(index) Filters out a single column index
OddColumnFilter() Filters out odd indexed columns
EvenColumnFilter() Filters out even indexed columns
ColumnValueFilter(func) Filters out rows based on its row values
RowFilter(indices) Filters a list of rows
SingleRowFilter(index) Filters out a single row
OddRowFilter() Filters out odd indexed rows
EvenRowFilter() Filters out even indexed rows
RowValueFilter(func) Filters out rows based on its row values
RegionFilter(row_slice, column_slice) Filter on both row index and column index