UserSpace.new_excel_range#

UserSpace.new_excel_range(name, path, range_, sheet=None, keyids=None, loadpath=None)#

Creates a Reference to an Excel range

Reads an Excel range from an Excel file, creates an ExcelRange object and assigns it to a Reference named name.

The object returned by this method is an ExcelRange object. It is a mapping object, and has the same methods and operations as other mapping objects, such as dict. The user can read and write values to an Excel file through the object by the same operators and methods as dict.

ExcelRange objects are associated to the Model of the bound References.

Bindings between ExcelRange objects and References are kept track of in the belonging Model, an ExcelRange object is deleted when all the References bound to the object is deleted.

ExcelRange objects cannot have Excel ranges overlapping with others.

An ExcelRange object is deleted when all the References bound to the object is deleted.

The Excel range is read from a workbook specified by loadpath and saved to path. If no loadpath is given, path is used also for reading.

The path is a path-like object, and can be either a relative or absolute path. If a relative path is given, the output file becomes an internal data file, and when this Model is saved by write_model() or zip_model(), the file is saved in the model folder or the zipped file output by the functions, and path is interpreted as a path relative to the model path. If an absolute path is given, the output file becomes an external data file and the file is saved outside the model folder or zip file.

The range_ parameter takes a string that indicates an Excel range, such as “A1:D5”, or the name of a named range. When the name of a named range is specified, the sheet argument is ignored.

The keyids paramter is for specifying rows and columns in the range to be interpreted as key rows and columns. The keyids parameter takes a list of strings, each element of which is a string starting with “r” or “c” followed by a 0-indexed integer. For example, ["r0", "c1"] indicates that the 1st row and the 2nd column in range_ are interpreted as keys in that order. If keyids is not given, all rows and columns are interpreted as value rows and columns, and the values are assigned to 0-indexed integer keys.

Example

Suppose below is the range “A1:D4” on Sheet1 in Book1.xlsx.

	AA	BB
0	11	21
1	12	22
2	13	23

The next code creates a Reference named x in a Space space:

>>> xlr = space.new_excel_range("x", "files/Book1.xlsx", "A1:D4",
        sheet="Sheet1", keys=["r0", "c0"], loadpath="Book1.xlsx")

The values in the range are accessible through the [] operator. “r0” in the keyids parameter denotes the first row, and “c0” denotes the first column. So keys to be passed in the [] operator are taken from the row and the column, for example:

>>> xlr["BB", 1]
22

>>> space.x["BB", 1]
22

>>> dict(xlr)
{('AA', 1): 11,
 ('AA', 2): 12,
 ('AA', 3): 13,
 ('BB', 1): 21,
 ('BB', 2): 22,
 ('BB', 3): 23}

Multiple ExcelRange objects cannot be created on overlapping ranges. When keyids is omitted, 0-indexed integer keys are assigned:

>>> xlr2 = space.new_excel_range("y", "files/Book1.xlsx", "B2:D4",
        sheet="Sheet1", loadpath="Book1.xlsx")
ValueError: cannot add spec

>>> del space.x

>>> xlr2 = space.new_excel_range("y", "files/Book1.xlsx", "B2:D4",
        sheet="Sheet1", loadpath="Book1.xlsx")

>>> dict(xlr2)
{(0, 0): 11, (0, 1): 21, (1, 0): 12, (1, 1): 22, (2, 0): 13, (2, 1): 23}

Note

This method reads and writes values from Excel files, not formulas. From formulas cells in the loadpath file, last-saved values stored in the file are read in.

Parameters:

name – A name of a Reference or a Cells object with no arguments.
path – The path of the output Excel file. Can be a str or path-like object.
range (str) – A range expression such as “A1:D5”, or a named range name string.
sheet – The sheet name of the range. Ignored when a named range is given to range_.
keyids (optional) – A list of indicating rows and columns to be interpreted as keys. For example, ['r0', 'c0'] indicates the fist row and the first column are to interpreted as keys in that order.
loadpath (optional) – The path of the input Excel file.