Announcements
Attention for Customers without Multi-Factor Authentication or Single Sign-On - OTP Verification rolls out April 2025. Read all about it here.

.net excel cell format border

snappyjazz
Advocate

.net excel cell format border

snappyjazz
Advocate
Advocate

I am using Inventor 2019.4 & MS visual studio express 2017. My code is making a report of Custom Content progress via excel.  All formatting  calls work, except for putting a bottom border on. Even the border call ".includeborder" works. I am relatively new to .net and don't know if there's something wrong in the code, or if I'm not using the right command. Do you have any ideas?

 

PS. the section is between =\/= & =/\=.  Also note the many failed attempts in red. sorry for the slop.

Imports System
Imports System.Type
Imports System.Activator
Imports System.Runtime.InteropServices
Imports Microsoft.Office.Core
Imports Excel = Microsoft.Office.Interop.Excel
Imports Inventor

    Private Sub ListToExcel(ByVal CCArray() As ReportObject)

        'create a new excel
        Dim XlApp As Excel.Application = CreateObject("Excel.Application")
        XlApp.Visible = True

        'create a new workbook in excel
        Dim XlBook As Excel.Workbook = XlApp.Workbooks.Add()

        'gets the active worksheet in excel
        Dim XlSheet As Excel.Worksheet = CType(XlBook.ActiveSheet, Excel.Worksheet)

        'create a range
        Dim RepRange As Excel.Range = XlSheet.Range("A1")
'==\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/=\/==
        'create node cell style
        Dim NodeStyle As Excel.Style = XlSheet.Application.ActiveWorkbook.Styles.Add("_CBNodeStyle")
        'NodeStyle.Borders.Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlThick
        'NodeStyle.Borders.Value(Excel.XlBordersIndex.xlEdgeBottom) = Excel.XlBorderWeight.xlThick)
        'NodeStyle.Borders.Value = Excel.XlBordersIndex.xlEdgeBottom
        'NodeStyle.Borders(xlEdgeBottom).LineStyle = xlContinuous
        'MsgBox(NodeStyle.Borders._Default(Excel.XlBordersIndex.xlEdgeBottom))
        'NodeStyle.Borders(9).LineStyle = Excel.XlLineStyle.xlContinuous
        'NodeStyle.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous
        'NodeStyle.Borders(Excel.XlBordersIndex.xlEdgeBottom) = True
        'XlSheet.UsedRange.Borders.LineStyle = Excel.XlLineStyle.xlContinuous
        'NodeStyle.IncludeBorder = Excel.XlBordersIndex.xlEdgeBottom
        'NodeStyle.Borders.Weight = Excel.XlBorderWeight.xlThick
        'NodeStyle.Borders.LineStyle = Excel.XlLineStyle.xlContinuous
        NodeStyle.Font.Italic = True
        NodeStyle.Font.Size = 14
        NodeStyle.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightSkyBlue)
        'NodeStyle.Interior.Color = RGB(0, 176, 240)
'==/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\=/\==
        'create default family cell style
        Dim FamStyle As Excel.Style = XlSheet.Application.ActiveWorkbook.Styles.Add("_CBFamStyle")
        FamStyle.Borders.Item(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlMedium
        NodeStyle.Borders.Item(Excel.XlBordersIndex.xlEdgeBottom).Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
        FamStyle.Font.Bold = True
        FamStyle.Font.Size = 12
        FamStyle.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Orange)

        'create finished family cell style
        Dim FinFamStyle As Excel.Style = XlSheet.Application.ActiveWorkbook.Styles.Add("_CBFinFamStyle")
        FinFamStyle.Borders.Item(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlMedium
        NodeStyle.Borders.Item(Excel.XlBordersIndex.xlEdgeBottom).Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black)
        FinFamStyle.Font.Bold = True
        FinFamStyle.Font.Size = 12
        FinFamStyle.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Lime)


        'width of columns
        XlSheet.Columns.AutoFit()

        'loop each object in the array
        For Each RepObj In CCArray
            'if the type is "N" for node then
            If RepObj.ReportType = "N" Then
                'gets the cell of the current object
                RepRange = XlSheet.Range(RepObj.ReportColumn & RepObj.ReportRow + 1)
                'set the cell style
                RepRange.Style = NodeStyle
                'RepRange.Style.Borders(Excel.XlBordersIndex.xlEdgeBottom).linetype = Excel.XlLineStyle.xlContinuous
                'fill in the cell value
                RepRange.Value = RepObj.ReportName
            ElseIf RepObj.ReportType = "F" And RepObj.ReportDesc.Substring(RepObj.ReportDesc.ToString, 3) = "_CB-" Then
                'gets the cell of the current object
                RepRange = XlSheet.Range(RepObj.ReportColumn & RepObj.ReportRow + 1)
                'set the cell style
                RepRange.Style = FinFamStyle
                'fill in the cell value
                RepRange.Value = RepObj.ReportName
            Else
                'gets the cell of the current object
                RepRange = XlSheet.Range(RepObj.ReportColumn & RepObj.ReportRow + 1)
                'set the cell style
                RepRange.Style = FamStyle
                'fill in the cell value
                RepRange.Value = RepObj.ReportName
            End If



        Next

        XlApp = Nothing
    End Sub

 

0 Likes
Reply
785 Views
0 Replies
Replies (0)