Python Command: File

Syntax

Example

Description/Remarks

New or changed in GainSeeker version

file.delete(filepath)

file.delete("myfile.txt")

Deletes the file if it exists.

file.exceltopdf(excelfile, pdfpath, settings, allsheets)

file.exceltopdf("q:\\docs\\myfile.xlsx", "h:\\daily\\report.pdf", "L", True)

Uses Microsoft Excel to open the file "q:\docs\myfile.xlsx" and save all worksheets to a .pdf file named "h:\daily\report.pdf", using Landscape orientation.

Creates a .pdf file from an Excel file. Requires Microsoft Excel 2007 or later to be installed.

excelfile is the path and filename of the Excel file. The filename extension .xls or .xlsx is required.

pdfpath is the path and filename of the .pdf file. The filename extension .pdf is required. If this file already exists, it will be overwritten.

settings determines the how pages are oriented in the .pdf file. Specify P (portrait), L (landscape), or an empty string (orientation configured in the Excel file).

allsheets can be True (save all worksheets to pdf) or False (save only the active sheet(s) to pdf).

If Excel is not installed or excelfile cannot be found, the resulting pdf file will contain an error message instead of the Excel file contents. Error messages displayed by Microsoft Excel cannot be suppressed with error.stoponerror = False .

8.9

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.htmltopdf(html, pdfpath, settings)

file.htmltopdf("q:\\docs\\myfile.html", "h:\\daily\\report.pdf", "L")

Saves the file "q:\docs\myfile.html" to a .pdf file named "h:\daily\report.pdf", using Landscape orientation and paper size Letter (8.5 x 11 inches).

file.htmltopdf("<p>Here is some text.</p>", "h:\\daily\\report.pdf", 'P -s A4 --header-right "Page [page] of [toPage]"')

Saves the specified fragment of HTML to a .pdf file named "h:\daily\report.pdf", using Portrait orientation, paper size A4, and a header for page numbers.

Creates a .pdf file from HTML, using wkhtmltopdf.exe in the GainSeeker application folder.

html can be either of these:

• the path and filename of an HTML file. The filename extension .htm or .html is required.

• a string of raw HTML that begins with the < character (for an opening tag).
  All references to external files (such as images, cascading style sheets, etc.) must specify absolute file paths.

pdfpath is the path and filename of the .pdf file. The filename extension .pdf is required. If this file already exists, it will be overwritten.

settings is a string that can include two types of parameters:

1. The first parameter determines the how pages are oriented in the .pdf file. Specify L for landscape, or use P or an empty string for portrait.

2. After the orientation, you can specify any other parameters that are built in to wkhtmltopdf.exe. For a basic list, run wkhtmltopdf.exe -h . For an extensive list, see https://wkhtmltopdf.org/usage/wkhtmltopdf.txt or run wkhtmltopdf.exe -H .
   Some parameters that specify strings - such as headers or titles - require those strings to be surrounded by double-quotes (not single quotes).
   Incorrect parameters can prevent the .pdf file from being created. You can use wkhtmltopdf.exe directly to test and troubleshoot these parameters.

This Python command will generate .pdf files with paper size Letter (8.5 x 11 inches) unless you use the settings parameter to specify a different paper size.

8.9

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.

8.8

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.

8.8

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.

8.8

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

8.8

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()

Clears the current workbook.

file.xls.clearworksheet()

file.xls.read(f:\\temp\\report.xlsx)

file.xls.sheetidx = 2

file.xls.clearworksheet()

file.xls.write(f:\\temp\\cleaned.xlsx)

This reads the file, goes to the 3rd worksheet, clears that worksheet, and writes out the results to a different file.

Clears the current worksheet.

For information on setting the current worksheet, see file.xls.sheetidx.

8.9

file.xls.columncnt

Deprecated - use file.xls.lastcol instead.

file.xls.columncount

numcols = file.xls.columncount

Gets the number of columns in the file.

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

8.7

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.

When isNumeric is False, the value is treated as text. (When reading a .csv file, if the cell can be interpreted as a numeric value, its value will be formatted without any symbols such as currency, percent, or scientific notation.)

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

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

9.3

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.

When isNumeric is False, the value is treated as text. (When reading a .csv file, if the cell can be interpreted as a numeric value, its value will be formatted without any symbols such as currency, percent, or scientific notation.)

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

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

9.3

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. (When reading a .csv file, time stamps do not include seconds.)

Note that file.xls.setcell() cannot be used to set datetime cells.

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

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.  (When reading a .csv file, time stamps do not include seconds.)

Note that file.xls.setcell() cannot be used to set datetime cells.

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

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.

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

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.

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

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.

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

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.

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

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 as text, one for each cell in the specified row. Row numbering starts at 0.

When reading a .csv file, if the cell can be interpreted as a numeric value, its value will be formatted without any symbols such as currency, percent, or scientific notation.

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

9.3

file.xls.lastcol

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

Returns -1 if there are no columns.

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

8.7

file.xls.lastrow

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

Returns -1 if there are no rows.

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

8.7

file.xls.read(filename)

file.xls.read("f:\\logs\\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.

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

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.

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

file.xls.rowcnt

Deprecated - use file.xls.lastrow instead.

file.xls.rowcount

numrows = file.xls.rowcount

Gets the number of rows in the file.

This command may not work correctly if the file contains an ASCII null character. For a workaround, see ASCII null character in text files.

8.7

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. (Cannot be used to set datetime cells.)

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. (Cannot be used to set datetime cells.)

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

8.8

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.

8.8

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

8.8

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.

8.8

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.

8.8

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

8.8

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

8.8

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.

8.8

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

8.8

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.

8.8

file.xls.setsheetbyname(name)

file.xls.setsheetbyname("Monthly Summary")

Makes the sheet with the specified name the current sheet. Returns True if successful.

file.xls.sheetcnt

Gets the number of sheets in a workbook.

file.xls.sheetidx

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

file.xls.sheetname

mycurrentsheet = file.xls.sheetname

Returns the name of the current sheet in the workbook.

file.xls.write(filename)

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

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