Reference Spreadsheet in a Business Rule

A xlsx sheet is nothing else than a zip file of xml documents. With xfzip.unzip and the xml capabilities of vb.net, you can access it's information, you will find details how excel stores its data on the internet.

Here is some code to access a xml files from Onestream:

'reading the excel file from the file system
Dim excelFileInfo As XFFileEx = BRApi.FileSystem.GetFile(si, FileSystemLocation.ApplicationDatabase, pathName, True, False, False, SharedConstants.Unknown, Nothing, True)
Dim excelFile As Byte() = excelFileInfo.XFFile.ContentFileBytes

'Unzip the excel file
Dim folderNameForExtract As String = folderName
Dim extractPath As String = $"{Environment.GetEnvironmentVariable ("Temp")}/{folderNameForExtract}/"

xfzip.Unzip(excelFile, extractPath)
'Sheet1 xml doc
Dim xmlFileNameSheet1 As String = "worksheets/sheet1.xml"
Dim xmlPathSheet1 As String = $"{extractPath}/xl/{xmlFileNameSheet1}"
Dim sheet1XmlFileAsString As String = System.IO.File.ReadAllText(xmlPathSheet1).ToString
'SharedStrings xml doc
Dim xmlFileNameSharedStrings As String = "sharedStrings.xml"
Dim xmlPathSharedStrings As String = $"{extractPath}/xl/{xmlFileNameSharedStrings}"
Dim sharedStringsXmlFileAsString As String = System.IO.File.ReadAllText(xmlPathSheet1).ToString

'File is loaded, delete unziped excel
System.IO.Directory.Delete(extractPath,True)

'Sheet1 file as XmlDocument 
Dim sheet1XmlFileAsXmlDoc As XmlDocument = New XmlDocument()
sheet1XmlFileAsXmlDoc.LoadXml(sheet1XmlFileAsString)
Dim sharedStringsXmlFileAsXmlDoc As XmlDocument = New XmlDocument()
sharedStringsXmlFileAsXmlDoc.LoadXml(sharedStringsXmlFileAsString)

'Extract a list of List of Nodes from the XML
Dim sheetDataAsNodeList As XmlNodeList = sheet1XmlFileAsXmlDoc.GetElementsByTagName("sheetData")

Numeric data is stored in the sheet1.xml file within the <v> </v> tag. If it is text, it is a little more complicated, it stores an index (or list number) in the <v> tag, you will find the text in  the sharedStrings.xml file.

This might not be the easiest way to access it, but it works.

I hope this helps and cheers