Editing a spreadsheet controlled Tabular Note

Submitted by iamfallen on Thu, 10/15/2015 - 04:16
Forums: 
Journaling / NXOpen API

Is it possible to edit the contents of a cell in a spreadsheet controlled tabular note? I can read and write to a non spreadsheet controlled tab note, but I am only able to read from one that is spreadsheet controlled. The code does not error when "writing", it just does not change the text.

NXJournaling - Mon, 10/19/2015 - 08:53

You can determine if a cell is driven by the spreadsheet by looking at the cell preferences. The code below shows how to do this.

To edit the spreadsheet values, I think you would have to extract the spreadsheet to a file, manipulate the values, and then store the spreadsheet back in the part - but I have not verified this workflow yet. The relevant commands would be .ExtractSpreadsheet and .StoreSpreadsheet.

Option Strict Off
Imports System
Imports System.Collections.Generic
Imports NXOpen
Imports NXOpen.UF
 
Module Module1
 
    Dim theSession As Session = Session.GetSession()
    Dim theUfSession As UFSession = UFSession.GetUFSession()
 
    Dim workPart As Part = theSession.Parts.Work
    Dim lw As ListingWindow = theSession.ListingWindow
 
    Sub Main()
 
        If IsNothing(theSession.Parts.BaseWork) Then
            'active part required
            Return
        End If
 
        lw.Open()
 
        Const undoMarkName As String = "NXJ journal"
        Dim markId1 As Session.UndoMarkId
        markId1 = theSession.SetUndoMark(Session.MarkVisibility.Visible, undoMarkName)
 
        Dim myTabularNoteTags As New List(Of Tag)
        If FindTabularNotes(myTabularNoteTags) = 0 Then
            'no tabular notes to process
            Return
        End If
 
        For Each tableNote As Tag In myTabularNoteTags
 
            Dim numRows As Integer
            theUfSession.Tabnot.AskNmRows(tableNote, numRows)
 
            Dim numCols As Integer
            theUfSession.Tabnot.AskNmColumns(tableNote, numCols)
 
            Dim tableSectionTag As Tag
            theUfSession.Tabnot.AskNthSection(tableNote, 0, tableSectionTag)
 
            For i As Integer = 0 To numRows - 1
                Dim rowTag As Tag
                theUfSession.Tabnot.AskNthRow(tableNote, i, rowTag)
 
                For j As Integer = 0 To numCols - 1
                    Dim colTag As Tag
                    theUfSession.Tabnot.AskNthColumn(tableNote, j, colTag)
 
                    Dim cellTag As Tag
                    theUfSession.Tabnot.AskCellAtRowCol(rowTag, colTag, cellTag)
 
 
                    Dim theCellPrefs As UFTabnot.CellPrefs
                    theUfSession.Tabnot.AskCellPrefs(cellTag, theCellPrefs)
 
                    Dim spreadSheetTag As Tag = Tag.Null
                    Dim sheetNum As Integer
                    Dim ssRow As Integer
                    Dim ssCol As Integer
 
                    spreadSheetTag = theCellPrefs.referenced_spreadsheet
                    If spreadSheetTag <> Tag.Null Then
                        lw.WriteLine("this cell references a spreadsheet cell")
                        sheetNum = theCellPrefs.ss_sheet
                        ssRow = theCellPrefs.ss_row
                        ssCol = theCellPrefs.ss_col
                        lw.WriteLine("Sheet: " & sheetNum.ToString & " row: " & ssRow.ToString & " column: " & ssCol.ToString)
 
                    End If
 
 
                    Dim cellText As String
                    theUfSession.Tabnot.AskCellText(cellTag, cellText)
                    lw.WriteLine("ask cell text: " & cellText)
 
                    Dim cellEvalText As String
                    theUfSession.Tabnot.AskEvaluatedCellText(cellTag, cellEvalText)
                    lw.WriteLine("ask evaluated text: " & cellEvalText)
 
                    lw.WriteLine("")
 
                    theUfSession.Tabnot.SetCellText(cellTag, "junk")
 
 
                Next
 
            Next
 
        Next
 
        lw.Close()
 
    End Sub
 
    Function FindTabularNotes(ByRef theTabNotes As List(Of Tag)) As Integer
 
        Dim tmpTabNote As NXOpen.Tag = NXOpen.Tag.Null
        Dim type As Integer
        Dim subtype As Integer
 
        Do
            theUfSession.Obj.CycleObjsInPart(workPart.Tag, UFConstants.UF_tabular_note_type, tmpTabNote)
            If tmpTabNote = NXOpen.Tag.Null Then
                Continue Do
            End If
            If tmpTabNote <> NXOpen.Tag.Null Then
                theUfSession.Obj.AskTypeAndSubtype(tmpTabNote, type, subtype)
                If subtype = UFConstants.UF_tabular_note_subtype Then
 
                    theTabNotes.Add(tmpTabNote)
 
                End If
            End If
        Loop Until tmpTabNote = NXOpen.Tag.Null
 
        Return theTabNotes.Count
 
    End Function
 
    Public Function GetUnloadOption(ByVal dummy As String) As Integer
 
        'Unloads the image immediately after execution within NX
        GetUnloadOption = NXOpen.Session.LibraryUnloadOption.Immediately
 
    End Function
 
End Module

iamfallen - Mon, 10/19/2015 - 11:19

Any Idea what the spreadsheet name would be? The help file only lists:
"MODELING_SHEET" - modeling spreadsheet data
"DEFAULT_SHEET" - Gateway spreadsheet data
"PART_FAMILY_SHEET" - Part Family spreadsheet data

I tried the modeling and default sheets. Both times I got:

The first parameter passed in was invalid.
NXOpen.NXException: The first parameter passed in was invalid.
   at NXOpen.UF.UFXs.ExtractSpreadsheet(String spreadsheet_name, String file_name)
   at Redacted.RedactedForm.Replace(String RedactedItem, String SelectedReplacement, Boolean IsVisible) in C:\Users\Redacted\program.vb:line 545
<code>

NXJournaling - Mon, 10/19/2015 - 11:24

The code below doesn't quite work, but it is close. The code looks at each tabular note; if the note in row 1, col 1 references a spreadsheet, it will attempt to modify the note. It successfully extracts and edits the spreadsheet; but it errors on the call to .StoreSpreadsheet. It doesn't seem to like the name of the spreadsheet even though it is the name that was extracted from the spreadsheet. This might be a case for GTAC to investigate. My guess is that the .StoreSpreadsheet method has not been updated to handle tabular note spreadsheets.

For what it's worth, here's the code (edit the value of "tempFile" to something appropriate for your setup before running it):

Option Strict Off
Imports System
Imports System.Collections.Generic
Imports NXOpen
Imports NXOpen.UF
 
Module Module2
 
    Dim theSession As Session = Session.GetSession()
    Dim theUfSession As UFSession = UFSession.GetUFSession()
    Dim theUISession As UI = UI.GetUI
 
    Dim workPart As Part = theSession.Parts.Work
    Dim lw As ListingWindow = theSession.ListingWindow
 
    Sub Main()
 
        If IsNothing(theSession.Parts.BaseWork) Then
            'active part required
            Return
        End If
 
        lw.Open()
 
        Const undoMarkName As String = "NXJ journal"
        Dim markId1 As Session.UndoMarkId
        markId1 = theSession.SetUndoMark(Session.MarkVisibility.Visible, undoMarkName)
 
        Dim myTabularNoteTags As New List(Of Tag)
        If FindTabularNotes(myTabularNoteTags) = 0 Then
            'no tabular notes to process
            Return
        End If
 
        Const tempFile As String = "C:\Temp\sheetnote.xlsx"
 
        For Each tableNote As Tag In myTabularNoteTags
 
            Dim numRows As Integer
            theUfSession.Tabnot.AskNmRows(tableNote, numRows)
 
            Dim numCols As Integer
            theUfSession.Tabnot.AskNmColumns(tableNote, numCols)
 
            Dim tableSectionTag As Tag
            theUfSession.Tabnot.AskNthSection(tableNote, 0, tableSectionTag)
 
            'edit cell 0,0
 
            Dim rowTag As Tag
            theUfSession.Tabnot.AskNthRow(tableNote, 0, rowTag)
 
            Dim colTag As Tag
            theUfSession.Tabnot.AskNthColumn(tableNote, 0, colTag)
 
            Dim cellTag As Tag
            theUfSession.Tabnot.AskCellAtRowCol(rowTag, colTag, cellTag)
 
 
            Dim theCellPrefs As UFTabnot.CellPrefs
            theUfSession.Tabnot.AskCellPrefs(cellTag, theCellPrefs)
 
            Dim spreadSheetTag As Tag = Tag.Null
            Dim sheetNum As Integer
            Dim ssRow As Integer
            Dim ssCol As Integer
 
            spreadSheetTag = theCellPrefs.referenced_spreadsheet
            If spreadSheetTag <> Tag.Null Then
                lw.WriteLine("this cell references a spreadsheet cell")
 
                Dim ssObj As NXObject
                ssObj = Utilities.NXObjectManager.Get(spreadSheetTag)
                lw.WriteLine("Spread sheet name: " & ssObj.Name)
 
                sheetNum = theCellPrefs.ss_sheet
                ssRow = theCellPrefs.ss_row
                ssCol = theCellPrefs.ss_col
                lw.WriteLine("Sheet: " & sheetNum.ToString & " row: " & ssRow.ToString & " column: " & ssCol.ToString)
 
                theUfSession.Xs.ExtractSpreadsheet(ssObj.Name, tempFile)
 
                EditSpreadSheetNote(tempFile, theCellPrefs, "ss text edit")
 
                'lw.WriteLine("storing Excel file")
                theUfSession.Xs.StoreSpreadsheet(ssObj.Name, tempFile)
 
 
            Else
                'edit normal cell text
                theUfSession.Tabnot.SetCellText(cellTag, "normal text edit")
 
            End If
 
 
            lw.WriteLine("")
 
 
 
        Next
 
        lw.Close()
 
    End Sub
 
    Function FindTabularNotes(ByRef theTabNotes As List(Of Tag)) As Integer
 
        Dim tmpTabNote As NXOpen.Tag = NXOpen.Tag.Null
        Dim type As Integer
        Dim subtype As Integer
 
        Do
            theUfSession.Obj.CycleObjsInPart(workPart.Tag, UFConstants.UF_tabular_note_type, tmpTabNote)
            If tmpTabNote = NXOpen.Tag.Null Then
                Continue Do
            End If
            If tmpTabNote <> NXOpen.Tag.Null Then
                theUfSession.Obj.AskTypeAndSubtype(tmpTabNote, type, subtype)
                If subtype = UFConstants.UF_tabular_note_subtype Then
 
                    theTabNotes.Add(tmpTabNote)
 
                End If
            End If
        Loop Until tmpTabNote = NXOpen.Tag.Null
 
        Return theTabNotes.Count
 
    End Function
 
    Sub EditSpreadSheetNote(ByVal ssFile As String, ByVal theCellPrefs As UFTabnot.CellPrefs, ByVal newText As String)
 
        Dim objExcel As Object
        Dim objWorkbook As Object
        Dim objWorksheet As Object
 
        Dim markId1 As Session.UndoMarkId
        markId1 = theSession.SetUndoMark(Session.MarkVisibility.Visible, "Excel import points")
 
        Dim excelFileExists As Boolean = False
        'cell pref row seems to be "one" based while the column seems to be "zero" based
        Dim row As Long = theCellPrefs.ss_row
        Dim column As Long = theCellPrefs.ss_col + 1
 
        'lw.WriteLine("row: " & row.ToString & " column: " & column.ToString)
 
        'create Excel object
        Try
            objExcel = CreateObject("Excel.Application")
        Catch ex As Exception
            'handle error, warn user
            Return
        End Try
 
        If objExcel Is Nothing Then
            theUISession.NXMessageBox.Show("Error", NXMessageBox.DialogType.Error, "Could not start Excel, journal exiting")
            theSession.UndoToMark(markId1, "journal")
            Exit Sub
        End If
 
        If IO.File.Exists(ssFile) Then
            'Open the Excel file
            excelFileExists = True
            objWorkbook = objExcel.Workbooks.Open(ssFile)
        Else
            theUISession.NXMessageBox.Show("Error", NXMessageBox.DialogType.Error, "Excel file does not exist")
            theSession.UndoToMark(markId1, "journal")
            Exit Sub
        End If
 
        If objWorkbook Is Nothing Then
            theUISession.NXMessageBox.Show("Error", NXMessageBox.DialogType.Error, "Could not open Excel file: " & ssFile & ControlChars.NewLine & "journal exiting.")
            theSession.UndoToMark(markId1, "journal")
            Exit Sub
        End If
 
        objExcel.Visible = True
 
        objWorksheet = objWorkbook.WorkSheets(theCellPrefs.ss_sheet)
 
 
 
 
        objWorksheet.cells(row, column).value = newText
 
 
        objWorkbook.Save()
        objWorkbook.Close()
 
        objExcel.Quit()
        objWorksheet = Nothing
        objWorkbook = Nothing
        objExcel = Nothing
 
    End Sub
 
    Public Function GetUnloadOption(ByVal dummy As String) As Integer
 
        'Unloads the image immediately after execution within NX
        GetUnloadOption = NXOpen.Session.LibraryUnloadOption.Immediately
 
    End Function
 
End Module

iamfallen - Tue, 10/20/2015 - 08:58

I spoke with GTAC this morning. 

I have confirmed that we do not have any way to do this using NXOpen.  The extract/store spreadsheet functions are only expected to work with three specific spreadsheets, as documented in the Open C Reference for the original C function from which 
the wrapper method is created:
 
  Spreadsheet object name:
    "MODELING_SHEET" - modeling spreadsheet data
    "DEFAULT_SHEET" - Gateway spreadsheet data
    "PART_FAMILY_SHEET" - Part Family spreadsheet data
 
While you might be able to successfully extract a spreadsheet with a different name - such as TABNOT_SPREADSHEET2 - The related Store function is not going to work with names other than what is shown in the docs.  This is why you are seeing the 
error message about "The first parameter passed in was invalid.
   at NXOpen.UF.UFXs.StoreSpreadsheet(String spreadsheet_name..." -- it will only accept one of the names shown in the documentation.

This IR was converted into an enhancement request (ER) on 20-OCT-2015
and is now referenced as ER Number 7540975. It is OPEN for review at this time.

They suggested to convert the tabular notes from spreadsheet controlled via ConvertToNonSpreadsheetTabnote().

Thank you for your help.