UserSpace.new_cells_from_excel

UserSpace.new_cells_from_excel(book, range_, sheet=None, names_row=None, param_cols=None, param_order=None, transpose=False, names_col=None, param_rows=None)[source]

Create multiple cells from an Excel range.

This method reads values from a range in an Excel file, create cells and populate them with the values in the range. To use this method, openpyxl package must be installed.

The Excel file to read data from is specified by book parameters. The range_ can be a range address, such as “$G4:$K10”, or a named range. In case a range address is given, sheet must also be given.

By default, cells data are interpreted as being laid out side-by-side. names_row is a row index (starting from 0) to specify the row that contains the names of cells and parameters. Cells and parameter names must be contained in a single row. param_cols accepts a sequence (such as list or tuple) of column indexes (starting from 0) that indicate columns that contain cells arguments.

2-dimensional cells definitions

The optional names_col and param_rows parameters are used, when data for one cells spans more than one column. In such cases, the cells data is 2-dimensional, and there must be parameter row(s) across the columns that contain arguments of the parameters. A sequence of row indexes that indicate parameter rows is passed to param_rows. The names of those parameters must be contained in the same rows as parameter values (arguments), and names_col is to indicate the column position at which the parameter names are defined.

Horizontal arrangement

By default, cells data are interpreted as being placed side-by-side, regardless of whether one cells corresponds to a single column or multiple columns. transpose parameter is used to alter this orientation, and if it is set to True, cells values are interpreted as being placed one above the other. “row(s)” and “col(s)” in the parameter names are interpreted inversely, i.e. all indexes passed to “row(s)” parameters are interpreted as column indexes, and all indexes passed to “col(s)” parameters as row indexes.

Parameters
  • book (str) – Path to an Excel file.

  • range (str) – Range expression, such as “A1”, “$G4:$K10”, or named range “NamedRange1”.

  • sheet (str) – Sheet name (case ignored).

  • names_row (optional) – an index number indicating what row contains the names of cells and parameters. Defaults to the top row (0).

  • param_cols (optional) – a sequence of index numbers indicating parameter columns. Defaults to only the leftmost column ([0]).

  • names_col (optional) – an index number, starting from 0, indicating what column contains additional parameters.

  • param_rows (optional) – a sequence of index numbers, starting from 0, indicating rows of additional parameters, in case cells are defined in two dimensions.

  • transpose (optional) – Defaults to False. If set to True, “row(s)” and “col(s)” in the parameter names are interpreted inversely, i.e. all indexes passed to “row(s)” parameters are interpreted as column indexes, and all indexes passed to “col(s)” parameters as row indexes.

  • param_order (optional) – a sequence to reorder the parameters. The elements of the sequence are the indexes of param_cols elements, and optionally the index of param_rows elements shifted by the length of param_cols.

See also

new_space_from_excel(): Create Spaces and Cells from Excel file.