ug二次开发 vb.net nx1980 写入Excel

 1.写入Excel

Imports System
Imports NXOpen

Module NXJournal
    Sub Main(ByVal args() As String)

        Dim theSession As NXOpen.Session = NXOpen.Session.GetSession()
        Dim workPart As NXOpen.Part = theSession.Parts.Work

        Dim displayPart As NXOpen.Part = theSession.Parts.Display
        Dim ExcelFileOpen = theSession.SpreadsheetManager().OpenFile("C:\\t.xlsx", SpreadsheetManager.OpenMode.Write)
        Dim worksheet = ExcelFileOpen.GetWorksheetIndex("Sheet1")

        Dim Rows As Integer = 2
        Dim Cols As Integer = 3
        Dim CellData(Rows * Cols - 1) As SpreadsheetCellData '要指定类组的长度，对应赋值，不要有空的不然写入EXCEL出错。

        Dim i As Integer = 0
        Dim s As Integer = 0
        For s = 0 To Rows - 1 '行数
            For i = 0 To Cols - 1 '列数
                CellData(i + s * Cols) = theSession.SpreadsheetManager().CreateCellData()
                CellData(i + s * Cols).Type = SpreadsheetCellData.Types.String
                CellData(i + s * Cols).StringValue = "Test" & (i + s * Cols)
            Next i
        Next s
        ExcelFileOpen.WriteSpreadsheetRange(worksheet, 1, 1, Rows, Cols, CellData)
        CellData = Nothing
        ExcelFileOpen.CloseFile(True)
        ExcelFileOpen.Dispose()
        ExcelFileOpen = Nothing
        MsgBox("写入成功")
    End Sub
End Module

 2.读Excel

Imports System
Imports NXOpen

Module NXJournal
    Sub Main(ByVal args() As String)

        Dim theSession As NXOpen.Session = NXOpen.Session.GetSession()
        Dim workPart As NXOpen.Part = theSession.Parts.Work

        Dim displayPart As NXOpen.Part = theSession.Parts.Display
        Dim ExcelFileOpen = theSession.SpreadsheetManager().OpenFile("C:\\t.xlsx", SpreadsheetManager.OpenMode.Write)
        Dim worksheet = ExcelFileOpen.GetWorksheetIndex("Sheet1")
        Dim CellData As SpreadsheetCellData()

        ' -------------读数据------------------------------------
        ExcelFileOpen.ReadRange(worksheet, 1, 1, 3, 3, CellData)
        ' ExcelFileOpen.ReadSpreadsheetRange(worksheet, 1, 1, 3, 3, CellData)
        '读取数据中前5个数据分别是下面
        'worksheet: worksheet ID
        'rowstart: Starting row of the range
        'colstart: Starting column of the range
        'rowend: Ending row of the range
        'colend: Ending column of the range

        Dim i = 0
        For i = 0 To CellData.Length - 1
            If CellData(i).Type = SpreadsheetCellData.Types.Int Then
                MsgBox(CellData(i).IntValue() & " INT " & i & " " & CellData(i).Type)
            End If
            If CellData(i).Type = SpreadsheetCellData.Types.String Then
                MsgBox(CellData(i).StringValue() & " STR " & i & " " & CellData(i).Type)
            End If
            If CellData(i).Type = SpreadsheetCellData.Types.Logical Then
                MsgBox(CellData(i).LogicalValue() & " Logical " & i & " " & CellData(i).Type)
            End If
            If CellData(i).Type = SpreadsheetCellData.Types.Double Then
                MsgBox(CellData(i).DoubleValue() & " Double " & i & " " & CellData(i).Type)
            End If
            If CellData(i).Type = SpreadsheetCellData.Types.Formula Then
                MsgBox(CellData(i).FormulaValue() & " Formula " & i & " " & CellData(i).Type)
            End If
        Next

        ' ExcelFileOpen.SetRangeBackgroundColor(worksheet, 2, 2, 2, 2, {230.0, 98.0, 105.0}) '设置单元格背景色 *仅对有值的单元格有效 后面.0要有
        'ExcelFileOpen.SetSheetTabBackgroundColor(worksheet, {230.0, 98.0, 105.0}) '设置表栏
        CellData = Nothing
        ExcelFileOpen.CloseFile(True)
        ExcelFileOpen.Dispose()
        ExcelFileOpen = Nothing
        MsgBox("读取成功")
    End Sub
End Module

3.用在UIstyler

    Public Sub OnMenuCallback(ByVal tree As NXOpen.BlockStyler.Tree, ByVal node As NXOpen.BlockStyler.Node, ByVal columnID As Integer)
        Dim menu = tree.CreateMenu()
        menu.AddMenuItem(0， "导出电子表格")
        tree.SetMenu(menu)

    End Sub

    Public Sub onmenucommandcallback(ByVal tree As NXOpen.BlockStyler.Tree, ByVal node As NXOpen.BlockStyler.Node, ByVal menu_item_id As Integer)
        If RowNums = 0 Then Exit Sub
        'Dim ExcelFile = theSession.SpreadsheetManager().OpenFile("C:\\t.xlsx", SpreadsheetManager.OpenMode.Write)

        'Dim worksheet = ExcelFile.GetWorksheetIndex("Sheet1")
        'Dim root = tree.RootNode()

        'Dim RowS As Integer = RowNums
        'Dim Cols = tree.NumberOfColumns
        'Dim CellData(RowS * Cols - 1) As SpreadsheetCellData '要指定类组的长度，对应赋值，不要有空的不然写入EXCEL出错。

        'Dim i As Integer = 0
        'For i = 1 To Cols + 1
        '    ExcelFile.DeleteColumn(worksheet, i)
        'Next i
        'Dim s As Integer = 0
        'For s = 0 To RowS - 1 '行数
        '    For i = 0 To Cols - 1 '列数
        '        CellData(i + s * Cols) = theSession.SpreadsheetManager().CreateCellData()
        '        CellData(i + s * Cols).Type = SpreadsheetCellData.Types.String
        '        CellData(i + s * Cols).StringValue = root.GetColumnDisplayText(i)
        '    Next i
        '    root = root.NextSiblingNode()
        'Next s


        'ExcelFile.WriteSpreadsheetRange(worksheet, 1, 1, RowS, Cols, CellData)

        'CellData = Nothing
        'ExcelFile.CloseFile(True)
        'ExcelFile.Dispose()
        'ExcelFile = Nothing

        Try
            Dim xlapp = CreateObject("Excel.Application")
            'Dim xlbook = xlapp.workbooks.open("C:\t.xlsx")
            Dim xlbook = xlapp.Workbooks.add()
            Dim xlSheet = xlbook.Worksheets(1)

            xlapp.Visible = True
            '---------把Excel放罢最前面------
            ' Private Declare Function SetForegroundWindow Lib "user32" (ByVal hWnd As Long) As Long
            ' Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
            '在调用前先把这两句放Public Class后，先声明
            Dim mhwnd As Long
            mhwnd = FindWindow("XLMAIN", vbNullString)
            SetForegroundWindow(mhwnd)
            '---------------------------------
            Dim root = tree.RootNode()
            Dim RowS As Integer = RowNums
            Dim Cols = tree.NumberOfColumns
            Dim i As Integer = 0
            Dim s As Integer = 0
            For s = 0 To RowS - 1 '行数
                For i = 0 To Cols - 1 '列数
                    xlSheet.Cells(s + 1, i + 1).Value = root.GetColumnDisplayText(i)  '把tree的值写入Excel单元格中
                Next i
                root = root.NextSiblingNode() '循环tree的节点
            Next s
            xlSheet = Nothing
            xlbook = Nothing
            xlapp = Nothing

        Catch
            MsgBox("失败")
        End Try
        ' xlapp = Nothing '释放xlApp对象

    End Sub