pyexcel.Sheet

class pyexcel.Sheet(sheet=None, name='pyexcel sheet', name_columns_by_row=-1, name_rows_by_column=-1, colnames=None, rownames=None, transpose_before=False, transpose_after=False)[source]

Two dimensional data container for filtering, formatting and iteration

Sheet is a container for a two dimensional array, where individual cell can be any Python types. Other than numbers, value of thsee types: string, date, time and boolean can be mixed in the array. This differs from Numpy’s matrix where each cell are of the same number type.

In order to prepare two dimensional data for your computation, formatting functions help convert array cells to required types. Formatting can be applied not only to the whole sheet but also to selected rows or columns. Custom conversion function can be passed to these formatting functions. For example, to remove extra spaces surrounding the content of a cell, a custom function is required.

Filtering functions are used to reduce the information contained in the array.

__init__(sheet=None, name='pyexcel sheet', name_columns_by_row=-1, name_rows_by_column=-1, colnames=None, rownames=None, transpose_before=False, transpose_after=False)

Constructor

Parameters:
  • sheet – two dimensional array
  • name – this becomes the sheet name.
  • name_columns_by_row – use a row to name all columns
  • name_rows_by_column – use a column to name all rows
  • colnames – use an external list of strings to name the columns
  • rownames – use an external list of strings to name the rows

Methods

__init__([sheet, name, name_columns_by_row, ...]) Constructor
add_filter(afilter) Apply a filter
add_formatter(aformatter) Add a lazy formatter.
apply_formatter(aformatter) Apply the formatter immediately.
cell_value(row, column[, new_value]) Random access to the data cells
clear_filters() Clears all filters
clear_formatters() Clear all formatters
column_at(index) Gets the data at the specified column
column_range() Utility function to get column range
columns() Returns a left to right column iterator
contains(predicate) Has something in the table
cut(topleft_corner, bottomright_corner) Get a rectangle shaped data out and clear them in position
delete_columns(column_indices) Delete one or more columns
delete_named_column_at(name) Works only after you named columns by a row
delete_named_row_at(name) Take the first column as row names
delete_rows(row_indices) Delete one or more rows
enumerate() Iterate cell by cell from top to bottom and from left to right
extend_columns(columns) Take ordereddict to extend named columns
extend_columns_with_rows(rows) Put rows on the right most side of the data
extend_rows(rows) Take ordereddict to extend named rows
filter(afilter) Apply the filter with immediate effect
format(formatter[, on_demand]) Apply a formatting action for the whole sheet
freeze_filters() Apply all filters and delete them
freeze_formatters() Apply all added formatters and clear them
get_csv(**keywords)
get_csvz(**keywords)
get_django(**keywords)
Sheet.get_grid
Sheet.get_html
Sheet.get_json
Sheet.get_latex
Sheet.get_latex_booktabs
Sheet.get_mediawiki
get_ods(**keywords)
Sheet.get_orgtbl
Sheet.get_pipe
Sheet.get_plain
Sheet.get_rst
Sheet.get_simple
get_sql(**keywords)
get_texttable(**keywords)
get_tsv(**keywords)
get_tsvz(**keywords)
get_xls(**keywords)
get_xlsm(**keywords)
get_xlsx(**keywords)
insert(topleft_corner[, rows, columns]) Insert a rectangle shaped data after a position
map(custom_function) Execute a function across all cells of the sheet
name_columns_by_row(row_index) Use the elements of a specified row to represent individual columns
name_rows_by_column(column_index) Use the elements of a specified column to represent individual rows
named_column_at(name) Get a column by its name
named_columns()
named_row_at(name) Get a row by its name
named_rows()
number_of_columns() Number of columns in the data sheet
number_of_rows() Number of rows in the data sheet
paste(topleft_corner[, rows, columns]) Paste a rectangle shaped data after a position
rcolumns() Returns a right to left column iterator
region(topleft_corner, bottomright_corner) Get a rectangle shaped data out
register_presentation(file_type)
remove_filter(afilter) Remove a named filter
remove_formatter(aformatter) Remove a formatter
reverse() Opposite to enumerate
row_at(index) Gets the data at the specified row
row_range() Utility function to get row range
rows() Returns a top to bottom row iterator
rrows() Returns a bottom to top row iterator
rvertical() Default iterator to go through each cell one by one from rightmost
save_as(filename, **keywords) Save the content to a named file
save_to(source) Save to a writeable data source
save_to_database(session, table[, ...]) Save data in sheet to database table
save_to_django_model(model[, initializer, ...]) Save to database table through django model
save_to_memory(file_type[, stream]) Save the content to memory
set_column_at(column_index, data_array[, ...]) Updates a column data range
set_named_column_at(name, column_array) Take the first row as column names
set_named_row_at(name, row_array) Take the first column as row names
set_row_at(row_index, data_array[, starting]) Update a row data range
to_array() Returns an array after filtering
to_dict([row]) Returns a dictionary
to_records([custom_headers]) Returns the content as an array of dictionaries
transpose() Roate the data table by 90 degrees
validate_filters() Re-apply filters
vertical() Default iterator to go through each cell one by one from

Attributes

colnames Return column names
column Column representation.
content
csv
csvz
django
Sheet.grid
Sheet.html
Sheet.json
Sheet.latex
Sheet.latex_booktabs
Sheet.mediawiki
ods
Sheet.orgtbl
Sheet.pipe
Sheet.plain
row Row representation.
rownames Return row names
Sheet.rst
Sheet.simple
sql
texttable
tsv
tsvz
xls
xlsm
xlsx