Reference Spreadsheet in a Business Rule

[agent09]

Is there a way to reference the contents of a spreadsheet created in OneStream via a business rule? I am trying to build a logic that will reference cells on the created spreadsheet in my BR.

[ChristianW]

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

[ckattookaran]

You could try using OpenXML; this is embedded in OS. All you need to do is use the import statements. I like EPPlus However, that one is external, and the free version stopped at 4.3 (not 5, I'm sure). Read upon those two, and you can do the reading and writing of EXCEL documents a bit easier.