07-17-2019
09:19 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report
07-17-2019
09:19 AM
.net excel cell format border
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