Skip to content

Read Excel Sheets and Files

Examples

Go

Excel files can be uploaded into a study as binary file. Alternately, Excel files can be placed in the input directory of the playpen. An Excel file can then be read into memory as a Golang or Python data structure from either of these locations. There are two functions for reading an Excel file:

Read a single sheet

singleSheet, warn, err := u.ReadExcelS(excelFilename | uploadType, colDesc, sheetName)

Or read all the sheets in an Excel workbook

multipleSheets, warn, err := u.ReadExcel(excelFilename | uploadType, colDesc)

Where :

excelFilename - This is the name of the Excel file to read, i.e., "parameters2022.xlsx"

uploadType - Uploads are given a type when they are saved. Specify the type of the file you want to get from the database. This assumes there was only one file of this type uploaded.

colDesc - This is a map[string]string where map[fieldName]=fieldTypes. The field name corresponds to a column name in the Excel file and is treated as case-insensitive. Valid field types include:

  • char
  • num
  • int
  • bool
  • date

Specifying a "*" for a field name will instruct the reader to match all the remaining fields and use the specified type for those fields. If you use "*" as the type, ReadExcel will additionally try to determine the types for each field.

sheetName - This is the name of the sheet in the workbook, i.e., "Sheet1"

singleSheet - This is the Golang data structure, []map[string]interface{}, containing the requested data, i.e., singleSheet[rowNumber][fieldName] = value

multipleSheets - This is the Golang data structure, map[][]map[string]interface{}, containing the requested data, multipleSheets[sheetName][rowNumber][fieldName] = value

warn - a possibly empty string containing the warnings from the parsing of the Excel workbook, including messages about conversion errors and missing fields. It is up to the user as to whether to ignore these messages.

err - a possibly nil value that should be checked by the user.

Both functions look for the Excel file in the current study first. If the file is not found in the study, a NOTE will be printed in the log and the Excel file will be attempted to be loaded from the input directory. If not found either place, an error will be produced.

colDesc := make(map[string]string)
colDesc["int"] = "int"
colDesc["num"] = "num"
colDesc["date"] = "date"
rows, warn, err := u.ReadExcelS("types.xlsx", colDesc, "Sheet1")
log.Println(rows, err)
log.Println(warn)

colDesc2 := map[string]string{
"Pool ID" :  "char", "*" : "char" }
sheets, warnNew, err := u.ReadExcel("pool-logic.xlsx", colDesc2)
log.Println(sheets)
log.Println("Error", err)
log.Println(warnNew)

colDesc2 := map[string]string{"*" : "*" }
sheets, warnNew, err := u.ReadExcel("pool-logic.xlsx", colDesc2)
log.Println(sheets)
log.Println("Error", err)
log.Println(warnNew)

Python

Excel files can be uploaded into a study as binary files. Alternately, Excel files can be placed in the input directory of the playpen. An Excel file can then be read into memory as a Golang or Python data structure from either of these locations. There are two functions for reading an Excel file:

Read a single sheet:

from sdk import excel

sheets, warn, err = excel.ReadExcelS(excelFilename | uploadType, colDesc, sheetName, runID)

Or read all the sheets in an Excel workbook:

sheets, warnNew, err = excel.ReadExcel(excelFilename | uploadType, colDesc, runID)

Where :

excelFilename - This is the name of the Excel file to read, i.e., "parameters2022.xlsx".

uploadType - Uploads are given a type when they are saved. Specify the type of the file you want to get from the database. This assumes there was only one file of this type uploaded.

colDesc - This is a dict() where dict[fieldName]=fieldTypes. The field name corresponds to a column name in the Excel file and is treated as case-insensitive. Valid field types include:

  • char
  • num
  • int
  • bool
  • date

Specifying a "*" for a field name will instruct the reader to match all the remaining fields and use the specified type for those fields.

sheetName - This is the name of the sheet in the workbook, i.e., "Sheet1".

runID - This is an integer representing the current job key obtained from handle.options['RunID']. Use 0 if you want only to look in the input directory for the Excel workbook.

singleSheet - This is the Python data structure, []dict(), containing the requested data, i.e., singleSheet[rowNumber][fieldName] = value

multipleSheets - This is the Python data structure, dict()[]dict(), containing the requested data, i.e., multipleSheets[sheetName][rowNumber][fieldName] = value

warn - This is a possibly empty string containing the warnings from the parsing of the Excel workbook, including messages about conversion errors and missing fields. It is up to the user whether to ignore these messages.

err - a possibly None value that should be checked by the user.

Both functions look for the Excel file in the current study first. If the file is not found in the study, a NOTE will be printed in the log and the Excel file will be attempted to be loaded from the input directory. If not found either place, an error will be produced.

from sdk import excel

colDesc2=dict()
colDesc2["Pool ID"] = "char"
colDesc2["Logic 1"] = "char"
colDesc2["Logic 2"] = "char"
colDesc2["Logic 3"] = "char"
colDesc2["Logic 4"] = "char"
colDesc2["Logic 5"] = "char"
colDesc2["Logic 6"] = "char"
sheets, warn, err = excel.ReadExcel("pool-logic.xlsx", colDesc2, 0)
print(sheets)