SpreadsheetManager

Submitted by iamfallen on Wed, 11/11/2020 - 10:14
Forums: 
Journaling / NXOpen API

Does anyone have experience with SpreadsheetManager in VB? I found an example for Python that works good when ran. When I looked it up in the API doc it appears that should work for opening both Native and Teamcenter Excel files. I can't seem to make it work in VB for either in my testing though.

Original Python Working Example (nx_api6227):
def readFile(self):
try:
xlsFile = self.theSession.SpreadsheetManager.OpenFile(
self.xlsFileSpec, NXOpen.SpreadsheetManagerOpenMode.Read)

self.theLW.Open()
self.theLW.WriteLine("Reading: " + self.xlsFileSpec)

if xlsFile:
data = []
#ReadRange(worksheet, rowstart, colstart, rowend, colend)
data = xlsFile.ReadRange(1, 1, 1, 2, 3)
numData = len(data)
self.theLW.WriteLine("Data Entries: " + str(numData))
for i in range(numData):
cellType = data[i].Type
info = "Data:" + str(i) + ", Type:" + str(cellType)

if cellType == NXOpen.SpreadsheetCellDataTypes.Int:
info += " IntValue = " + str(data[i].IntValue)
elif cellType == NXOpen.SpreadsheetCellDataTypes.Double:
info += " DoubleValue = " + str(data[i].DoubleValue)
elif cellType == NXOpen.SpreadsheetCellDataTypes.Formula:
info += " FormulaValue = " + str(data[i].FormulaValue)
elif cellType == NXOpen.SpreadsheetCellDataTypes.Logical:
info += " LogicalValue = " + str(data[i].LogicalValue)
else:
info += " StringValue = " + str(data[i].StringValue)

self.theLW.WriteLine(" " + info)

except:
self.reportException()

finally:
if xlsFile:
xlsFile.CloseFile(False)

My VB Code:
Dim xlFileLocation As String = "C:\temp\test.xlsx" 'xlFileLocation = "@DB/TEST/001"

InfoWindow.WriteLine(xlFileLocation)
Dim theSpreadSheetManager As SpreadsheetManager = theSession.SpreadsheetManager
Dim xlsFile = theSpreadSheetManager.OpenFile(xlFileLocation, SpreadsheetManager.OpenMode.Read)
InfoWindow.WriteLine(xlsFile.GetNumberofsheets & " Sheets found")
If Not IsNothing(xlsFile) Then
Dim Data() As SpreadsheetCellData = Nothing
'ReadRange(worksheet, rowstart, colstart, rowend, colend)

Dim worksheet = xlsFile.GetWorksheetIndex("INSTRUCTIONS")
xlsFile.ReadRange(worksheet, 1, 1, 2, 3, Data)
Dim numData = Data.Length

For Each theData In Data
Dim cellType = theData.Type
'info = "Data:" + Str(i) + ", Type:" + Str(cellType)
InfoWindow.WriteLine("in the data")

Next
End If

NXJournaling - Thu, 11/12/2020 - 13:46

The following was tested with a spreadsheet saved outside of TC. I did not try it on one saved in TC.

Option Strict Off
Imports System
Imports NXOpen

Module open_spreadsheet

Dim theSession As Session = Session.GetSession()
Dim InfoWindow As ListingWindow = theSession.ListingWindow

Sub Main()

InfoWindow.Open()

Dim xlFileLocation As String = "C:\temp\test.xlsx" 'xlFileLocation = "@DB/TEST/001"

InfoWindow.WriteLine(xlFileLocation)
Dim theSpreadSheetManager As SpreadsheetManager = theSession.SpreadsheetManager
Dim xlsFile As spreadsheetexternal = theSpreadSheetManager.OpenFile(xlFileLocation, SpreadsheetManager.OpenMode.Read)
InfoWindow.WriteLine(xlsFile.GetNumberofsheets & " Sheets found")
If Not IsNothing(xlsFile) Then
Dim sheetNames() As String = Nothing
xlsFile.GetWorksheetNames(sheetNames)
InfoWindow.WriteLine("Sheet names:")
For Each sheetName As String In sheetNames
InfoWindow.WriteLine(sheetName)
Next
InfoWindow.WriteLine("")

Dim Data() As SpreadsheetCellData = Nothing
'ReadRange(worksheet, rowstart, colstart, rowend, colend)

Dim worksheet As Integer = xlsFile.GetWorksheetIndex("INSTRUCTIONS")
xlsFile.ReadRange(worksheet, 1, 1, 2, 2, Data)
Dim numData = Data.Length
'InfoWindow.WriteLine("Data.Length: " & Data.Length.ToString)

For i As Integer = 5 To Data.Length - 1
InfoWindow.WriteLine(Data(i).StringValue)
Next
End If

xlsFile.CloseFile(False)

End Sub

End Module