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 these 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.

Variables:
  • name – sheet name. use to change sheet name
  • row – access data row by row
  • column – access data column by column

Example:

>>> import pyexcel as p
>>> content = {'A': [[1]]}
>>> b = p.get_book(bookdict=content)
>>> b
A:
+---+
| 1 |
+---+
>>> b[0].name
'A'
>>> b[0].name = 'B'
>>> b
B:
+---+
| 1 |
+---+
__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)[source]

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
cell_value(row, column[, new_value]) Random access to table cells
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([column_indices, row_indices]) Apply the filter with immediate effect
format(formatter) Apply a formatting action for the whole sheet
get_array(**keywords) Get data in array format
get_bookdict(**keywords) Get data in bookdict format
get_csv(**keywords) Get data in csv format
get_csvz(**keywords) Get data in csvz format
get_dict(**keywords) Get data in dict format
Sheet.get_echarts_html
get_fods(**__) fods getter is not defined.
Sheet.get_gantt_html
Sheet.get_grid
get_handsontable_html(**keywords) Get data in handsontable.html format
Sheet.get_html
get_internal_array() present internal array
Sheet.get_json
Sheet.get_latex
Sheet.get_latex_booktabs
Sheet.get_mediawiki
Sheet.get_ndjson
get_ods(**keywords) Get data in ods format
Sheet.get_orgtbl
Sheet.get_pdf
Sheet.get_pipe
Sheet.get_plain
get_records(**keywords) Get data in records format
Sheet.get_rst
Sheet.get_simple
Sheet.get_sortable_html
get_svg(**keywords) Get data in svg format
get_texttable(**keywords) Get data in texttable format
get_tsv(**keywords) Get data in tsv format
get_tsvz(**keywords) Get data in tsvz format
get_url(**__) url getter is not defined.
get_xls(**keywords) Get data in xls format
get_xlsm(**keywords) Get data in xlsm format
get_xlsx(**keywords) Get data in xlsx format
init([sheet, name, name_columns_by_row, …]) custom initialization functions
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() iterate rows using column names
named_row_at(name) Get a row by its name
named_rows() iterate rows using row names
number_of_columns() The number of columns
number_of_rows() The number of rows
paste(topleft_corner[, rows, columns]) Paste a rectangle shaped data after a position
plot([file_type]) Visualize the data
rcolumns() Returns a right to left column iterator
region(topleft_corner, bottomright_corner) Get a rectangle shaped data out
register_input(file_type[, instance_name]) partial(func, *args, **keywords) - new function with partial application of the given arguments and keywords.
register_io(file_type[, presenter_func, …]) partial(func, *args, **keywords) - new function with partial application of the given arguments and keywords.
register_presentation(file_type[, …])
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 column to leftmost row and from bottom to top example:
save_as(filename, **keywords) Save the content to a named file
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_array(content, **keywords) Set data in array format
set_bookdict(content, **keywords) Set data in bookdict format
set_column_at(column_index, data_array[, …]) Updates a column data range
set_csv(content, **keywords) Set data in csv format
set_csvz(content, **keywords) Set data in csvz format
set_dict(content, **keywords) Set data in dict format
Sheet.set_echarts_html
set_fods(content, **keywords) Set data in fods format
Sheet.set_gantt_html
Sheet.set_grid
set_handsontable_html(_y, **_z) handsontable.html setter is not defined.
Sheet.set_html
Sheet.set_json
Sheet.set_latex
Sheet.set_latex_booktabs
Sheet.set_mediawiki
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
Sheet.set_ndjson
set_ods(content, **keywords) Set data in ods format
Sheet.set_orgtbl
Sheet.set_pdf
Sheet.set_pipe
Sheet.set_plain
set_records(content, **keywords) Set data in records format
set_row_at(row_index, data_array) Update a row data range
Sheet.set_rst
Sheet.set_simple
Sheet.set_sortable_html
set_svg(_y, **_z) svg setter is not defined.
set_texttable(_y, **_z) texttable setter is not defined.
set_tsv(content, **keywords) Set data in tsv format
set_tsvz(content, **keywords) Set data in tsvz format
set_url(content, **keywords) Set data in url format
set_xls(content, **keywords) Set data in xls format
set_xlsm(content, **keywords) Set data in xlsm format
set_xlsx(content, **keywords) Set data in xlsx format
to_array() Returns an array after filtering
to_dict([row]) Returns a dictionary
to_records([custom_headers]) Make an array of dictionaries
top([lines]) Preview top most 5 rows
top_left([rows, columns]) Preview top corner: 5x5
transpose() Rotate the data table by 90 degrees
vertical() Default iterator to go through each cell one by one from leftmost column to rightmost row and from top to bottom example:

Attributes

array Get/Set data in/from array format
bookdict Get/Set data in/from bookdict format
colnames Return column names if any
content Plain representation without headers
csv Get/Set data in/from csv format
csvz Get/Set data in/from csvz format
dict Get/Set data in/from dict format
Sheet.echarts_html
fods Set data in fods format
Sheet.gantt_html
Sheet.grid
handsontable_html Get data in handsontable.html format
Sheet.html
Sheet.json
Sheet.latex
Sheet.latex_booktabs
Sheet.mediawiki
Sheet.ndjson
ods Get/Set data in/from ods format
Sheet.orgtbl
Sheet.pdf
Sheet.pipe
Sheet.plain
records Get/Set data in/from records format
rownames Return row names if any
Sheet.rst
Sheet.simple
Sheet.sortable_html
stream Return a stream in which the content is properly encoded
svg Get data in svg format
texttable Get data in texttable format
tsv Get/Set data in/from tsv format
tsvz Get/Set data in/from tsvz format
url Set data in url format
xls Get/Set data in/from xls format
xlsm Get/Set data in/from xlsm format
xlsx Get/Set data in/from xlsx format