Python Command: File

Syntax

Example

Description/Remarks

file.delete(filepath)

file.delete("myfile.txt")

Deletes the file if it exists.

file.exists(filepath)

file.exists("myfile.txt")

Returns True if the file exists, or False if it does not.

file.getpath(path)

print file.getpath(11)

Prints the application installation path to the console.

Returns the requested path as a string:

0: SPC Standards

1: SPC Data

2: DMS Standards

3: DMS Data

4: Archive Data

5: User Documents 1

6: Traceability

7: SPC Templates

8: Import/Export

9: User Documents 2

10: Configuration Data

11: Application Path

12: Settings.hsi path

13: Cms.ini path (the windows path).

14: Windows temp path

Note:

• Paths 0–9 can be set on the Tables dialog in the System Administration module.

• Path 10 is set in the Cms.ini file.

• Path 11 is the location from where the current application is running.

• Path 12 is set in redirect.hsi

• Paths 13 and 14 are set by the operating system.

• Using file.getpath(13) on a Windows Terminal Server farm will return the shared Windows folder (not the Windows folder from the user's Home directory).

file.getuserdoc1()

print file.getuserdoc1()

Prints to the console the User Documents 1 path.

Returns the full User Documents 1 path as a string.

The user documents 1 (or 2) path can be set on the Tables dialog in the System Administration module.

file.getuserdoc2()

print file.getuserdoc2()

Prints to the console the User Documents 2 path.

Returns the full User Documents 2 path as a string.

The user documents 1 (or 2) path can be set on the Tables dialog in the System Administration module.

file.text.readall(filepath)

file.text.readall("myfile.txt")

Reads and returns the contents of a text file as a string.

file.text.readlines(filepath)

file.text.readlines("myfile.txt")

Reads the contents of a text file and then returns a list of strings, one for each line in the file.

file.text.write(filepath, contents, append)

file.text.write("myfile.txt", "Hello", True)

This appends the string "Hello" to the file "myfile.txt".

Writes a string to a file. When append is True, the file is appended to rather than written over.

file.text.writelines(filepath, lines, append)

contents = ["Hello", "Goodbye"]

file.Text.writelines("myfile.txt", contents, True)

This appends the lines "Hello" and "Goodbye" to the file "myfile.txt".

Writes a list of strings to a file, each as its own line. When append is True, the file is appended to rather than written over.

file.xls.addpicture(startname, endname, filename)

file.xls.addpicture("A1", "H16", "P:\images\example.jpg")

Adds an image to an Excel file.

startname and endname are formatted like "A1" and specify where the upper left and lower right corners of the image should be placed.

filename specifies the image file to add to the Excel file. Valid file types are .bmp, .gif, .jpg, .png, and .tiff.

file.xls.addpicture(startrow, startcolumn, endrow, endcolumn, filename)

file.xls.addpicture(0, 0, 7, 16, "P:\images\example.jpg")

Adds an image to an Excel file.

Row and column numbers begin counting at 0. They specify where the upper left and lower right corners of the image should be placed.

filename specifies the image file to add to the Excel file. Valid file types are .bmp, .gif, .jpg, .png, and .tiff.

file.xls.autosizecolumn(column)

file.xls.autosizecolumn(0)

Automatically sizes the specified column based on the column's current contents.

Column numbering starts at 0.

file.xls.autosizecolumn(columnName)

file.xls.autosizecolumn("A")

Automatically sizes the specified column based on the column's current contents.

Column names are the letters typically used in spreadsheets, such as "A" or "BC".

file.xls.calculate()

file.xls.calculate()

Calculates Excel formulas. You may need to execute this method when you update cell values after setting formulas, and you want the formulas to update. All formulas are evaluated when a file is read into memory.

file.xls.clear()

See example below

Clears the current workbook.

file.xls.columncnt

Deprecated - use file.xls.lastcol instead.

file.xls.columncount

numcols = file.xls.columncount

Gets the number of columns in the file.

file.xls.getcell(cellname, isNumeric)  

file.xls.getcell("A3", True)

Returns the value of a cell given its name (for example: "F14" or "A3"). When isNumeric is True, the value is returned as a number. Otherwise, the value is treated as text.

This does not return datetime cells correctly. Use the getcelldt command to get datetime cells.

file.xls.getcell(row, column, isNumeric)

file.xls.getcell(1, 2, False)

Returns the value of a cell given the row and column. When isNumeric is True, the value is returned as a number. Otherwise, the value is treated as text.

This does not return datetime cells correctly. Use the getcelldt command to get datetime cells.

file.xls.getcelldt(name, hasdate, hastime)

print file.xls.getcelldt("B1", True, False)

This retrieves the date from the cell with name "B1".

Returns the date and/or time as a string from a date or time cell with the specified name. The hasdate argument specifies that there is a date component in the cell, The hastime argument specifies that there is a time component in the cell. Note that file.xls.setcell() can be used to set datetime cells.

file.xls.getcelldt(row, column, hasdate, hastime)

print file.xls.getcelldt(0, 1, False, True)

This retrieves the time from the cell at row 0, column 1.

Returns the date and/or time as a string from a date or time cell with the specified row and column. The hasdate argument specifies that there is a date component in the cell, The hastime argument specifies that there is a time component in the cell. Note that file.xls.setcell() can be used to set datetime cells.

file.xls.getcolumn(index, isnumeric, startrow, endrow)

print file.xls.getcolumn(1, False, 0, 99)

This prints to the console a list of the first 100 rows in the spreadsheet for the second column.

Returns a list of cell values, one for each cell in a column at the specified index from the startrow to the endrow. Setting isnumeric to True parses each cell as a number, or None if an invalid number.

This does not return datetime columns correctly. Use the getcolumndt command to get datetime columns.

file.xls.getcolumn(name, isnumeric, startrow, endrow)

print file.xls.getcolumn("B", False, 0, 99)

This prints to the console a list of the first 100 rows in the spreadsheet for the column named "B" in Excel.

Returns a list of cell values, one for each cell in a column with the specified name from the startrow to the endrow. Setting isnumeric to True parses each cell as a number, or None if an invalid number.

This does not return datetime columns correctly. Use the getcolumndt command to get datetime columns.

file.xls.getcolumndt(index, hasdate, hastime, startrow, endrow)

print file.xls.getcolumndt(1, True, False, 0, 99)

This prints to the console a list of the first 100 rows in the spreadsheet for the second column. This assumes the column has a date, but no time component.

Returns a list of cell values, one for each cell in a column with the specified index from the startrow to the endrow. Set hasdate to True when the column has a date component, set hastime to True when the column has a time component.

file.xls.getcolumndt(name, hasdate, hastime, startrow, endrow)

print file.xls.getcolumndt("B", False, True, 0, 99)

This prints to the console a list of the first 100 rows in the spreadsheet for the second column. This assumes the column has a time, but no date component.

Returns a list of cell values, one for each cell in a column with the specified name from the startrow to the endrow. Set hasdate to True when the column has a date component, set hastime to True when the column has a time component.

file.xls.getrow(rownumber)

file.xls.getrow(0)

Returns a list of cell values in the 1st row of the spreadsheet.

Returns a list of cell values, one for each cell in the specified row. Row numbering starts at 0.

file.xls.lastcol

See example below

Gets the zero-based number of the last column that is not empty.

Returns -1 if there are no columns.

file.xls.lastrow

See example below

Gets the zero-based number of the last row that is not empty.

Returns -1 if there are no rows.

file.xls.read(filename)

file.xls.read("datalog.xlsx")

Reads the specified file as a workbook. This includes Excel files or delimited text files such as CSV or TAB. All formulas are evaluated when a file is read. Returns True if file is opened successfully.

file.xls.readdelim

file.xls.readdelim = ','

Gets or sets the delimiting character for reading delimited text files. This usually is a comma or a tab ('\t'). This is used for any file that does not have an extension of .xls or .xlsx.

file.xls.rowcnt

Deprecated - use file.xls.lastrow instead.

file.xls.rowcount

numrows = file.xls.rowcount

Gets the number of rows in the file.

file.xls.setcell(cellname, value, isNumeric)

file.xls.setcell("B1", "some text", False)

Sets the value of a cell given its name (for example: "F14" or "A3"). The value can be a number or a string, but isNumeric must be set accordingly.

file.xls.setcell(row, column, value, isNumeric)

file.xls.setcell(2, 5, 45, True)

Sets the value of a cell given row and column numbers. The value can be a number or a string, but isNumeric must be set accordingly.

file.xls.setcellborder(name, topcolor=None, bottomcolor=None, leftcolor=None, rightcolor=None, type=2)

file.xls.setcellborder("A1", "DarkBlue", "DarkBlue", "DarkBlue", "DarkBlue", 5)

Sets the borders of a cell given its name (for example: "F14" or "A3").

To set the color, specify its colorname. (For a full list of colornames - such as "red", "orange", "yellow", "green", "blue", "white", and many more - see https://www.colorcodehex.com/html-color-names.html or http://cng.seas.rochester.edu/CNG/docs/x11color.html.)

Options for type are:

 13 : SlantedDashDot

 12 : MediumDashDotDot

 11 : DashDotDot

 10 : MediumDashDot

 9 : DashDot

 8 : MediumDash

 7 : Hair

 6 : Double

 5 : Thick

 4 : Dotted

 3 : Dashed

 2 : Medium

 1 : Thin

 0 : None

file.xls.setcellborder(row, column, topcolor=None, bottomcolor=None, leftcolor=None, rightcolor=None, type=2)

file.xls.setcellborder(0, 0, "DarkBlue", "DarkBlue", "DarkBlue", "DarkBlue", 5)

Sets the borders of a cell given row and column numbers, both of which start numbering at 0.

See above for color and type details.

file.xls.setcellfont(name, color='black', size=10, bold=False, italic=False, orientation=0)

file.xls.setcellfont("A1", "DarkBlue", 14, True, True)

Sets the font for a cell given its name (for example: "F14" or "A3").

To set the color, specify its colorname. (For a full list of colornames - such as "red", "orange", "yellow", "green", "blue", "white", and many more - see https://www.colorcodehex.com/html-color-names.html or http://cng.seas.rochester.edu/CNG/docs/x11color.html.)

Options for orientation are:

 0 : Normal

 1 : Clockwise

 2 : Counter-clockwise

file.xls.setcellfont(row, column, color='black', size=10, bold=False, italic=False, orientation=0)

file.xls.setcellfont(0, 0, "DarkBlue", 14, True, True)

Sets the font for a cell given row and column numbers, both of which start numbering at 0.

See above for color and font details.

file.xls.setcolumnwidth(column, width=8)

file.xls.setcolumnwidth(0, 16)

Sets the width of the specified column.

Width number represents the approximate number of 0's that will fit in the column. Default width is 8. Setting width to 0 will hide the column.

Column numbering starts at 0.

file.xls.setcolumnwidth(columnName, width=8)

file.xls.setcolumnwidth("A", 16)

Sets the width of the specified column.

Width number represents the approximate number of 0's that will fit in the column. Default width is 8. Setting width to 0 will hide the column.

Column names are the letters typically used in spreadsheets, such as "A" or "BC".

file.xls.setformula(cellname, value)

file.xls.setformula("C2", "A1+B1")

Sets the formula of a cell given its formatted name (for example: "F14" or "A3"). The formula must be a string and it cannot begin with the equal sign (=). The formula is calculated when it is set.

file.xls.setformula(row, column, value)  

file.xls.setformula(3, 4, "A1+B1")

Sets the formula of a cell given row and column numbers. The formula must be a string and it cannot begin with the equal sign (=). The formula is calculated when it is set.

file.xls.setrangeborder(startname, endname, topcolor=None, bottomcolor=None, leftcolor=None, rightcolor=None, type=2)

file.xls.setrangeborder("A1", "H16", "DarkBlue", "DarkBlue", "DarkBlue", "DarkBlue", 5)

Sets the border for a range of cells.

startname and endname are formatted like "A1" and specify the upper left and lower right corners of the range of cells.

To set the color, specify its colorname. (For a full list of colornames - such as "red", "orange", "yellow", "green", "blue", "white", and many more - see https://www.colorcodehex.com/html-color-names.html or http://cng.seas.rochester.edu/CNG/docs/x11color.html.)

Options for type are:

 13 : SlantedDashDot

 12 : MediumDashDotDot

 11 : DashDotDot

 10 : MediumDashDot

 9 : DashDot

 8 : MediumDash

 7 : Hair

 6 : Double

 5 : Thick

 4 : Dotted

 3 : Dashed

 2 : Medium

 1 : Thin

 0 : None

file.xls.setrangeborder(startrow, startcolumn, endrow, endcolumn, topcolor=None, bottomcolor=None, leftcolor=None, rightcolor=None, type=2)

file.xls.setcellborder(0, 0, 7, 16, "DarkBlue", "DarkBlue", "DarkBlue", "DarkBlue", 5)

Sets the border for a range of cells.

Row and column numbers begin counting at 0. They specify the upper left and lower right corners of the range of cells.

See above for color and type details.

file.xls.setrangefont(startname, endname, color='black', size=10, bold=False, italic=False, orientation=0)

file.xls.setrangefont("A1", "H16", "DarkBlue", 14, True, True)

Sets the font for a range of cells.

startname and endname are formatted like "A1" and specify the upper left and lower right corners of the range of cells.

To set the color, specify its colorname. (For a full list of colornames - such as "red", "orange", "yellow", "green", "blue", "white", and many more - see https://www.colorcodehex.com/html-color-names.html or http://cng.seas.rochester.edu/CNG/docs/x11color.html.)

Options for orientation are:

 0 : Normal

 1 : Clockwise

 2 : Counter-clockwise

file.xls.setrangefont(startrow, startcolumn, endrow, endcolumn, color='black', size=10, bold=False, italic=False, orientation=0)

file.xls.setrangefont(0, 0, 7, 16, "DarkBlue", 14, True, True)

Sets the font for a range of cells.

Row and column numbers begin counting at 0. They specify the upper left and lower right corners of the range of cells.

See above for color and font details.

file.xls.sheetcnt

See example below

Gets the number of sheets in a workbook.

file.xls.sheetidx

See example below

Gets or Sets the index of the current sheet, zero being the first sheet.

file.xls.write(filename)

file.xls.write("myfile.xlsx")

Writes the specified file as a workbook. This includes Excel, CSV, or TAB files.