Excel Borders

It is strange that you are mixing index variables:

itemView1.Items(sd)

   and

itemView1.Items(i)

What is the FailText style supposed to do?

Since you're putting the border all the way around each cell in columns 1 through 8, and the right border of column x is indistinguishable visually from the left border of column x + 1, you actually can't tell if the code is putting a border on the right of columns 1-7--not that there's an obvious reason for the right border to be treated differently from the others.

Are you stopping with the debugger immediately after the style is applied to cell ro, 8, and looking at the worksheet, to make sure that there isn't something else farther down the line that's messing you up?

I don't see the any styles that might overwrite the borders.  I did discover that only style6.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous seems to work.

I tried to do each border alone - but only xlEdgeLeft worked.

I'm not able to look at the worksheet while I create it, because each report generates a new excel file - and the file isn't visible until everything is finished

  xlWorkSheet.SaveAs(fileName)

        xlWorkBook.Close()
        xlApp.Quit()

Select allOpen in new window

Thanks for your help!

Oh, also, I'm not sure why I had a different variable there.  They are both equal to the same value - I programmed the basic function almost 2 years ago, so I don't recall why I would've done that - I'm assuming it was a mistake.  I changed it and it still runs correctly except for the borders.

You should be able to see what's happening in Excel as you step through your VB application, unless you've given a command

                xlApp.Visible = False

If you have that in place, then turn it off for debugging purposes. Then put a break at the start of the code you posted, and step through line by line, watching to see what happens in Excel.

I don't have xlApp.Visible in my code at all.  I'm wondering if the reason I can't see the excel file as my application is running is because I create a new excel file each time the file is run.

I'm including the entire code from the sub - it's pulling the data from a listview.  Again, everything works except the border style (style6).

Dim sDir As String = GetMyCustomDesktopFolderPath()
        Dim fileName As String = sDir & "\" & txtExcelName.Text
        Dim CountRows As Integer = 0
        Dim maxNum As Integer = 33
        Dim ro As Integer = 4

        ' If the workbooks already exist, prompt to delete.
        Dim answer As MsgBoxResult

        If File.Exists(fileName) = True Then
            answer = MsgBox("Delete existing workbook (" & fileName & ")?", MsgBoxStyle.YesNo)
            If answer = MsgBoxResult.Yes Then
                If Dir(fileName) <> "" Then Kill(fileName)
            Else
                Exit Sub
            End If
        End If

        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value


        'Setup the Excel File
        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")
        xlWorkSheet.Name = "Page 1"
        xlWorkSheet.PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
        xlWorkSheet.PageSetup.LeftMargin = 0.1
        xlWorkSheet.PageSetup.RightMargin = 0.1
        xlWorkSheet.PageSetup.TopMargin = 0.5

        'Set Column Width
        xlWorkSheet.Columns("A").ColumnWidth = 25
        xlWorkSheet.Columns("B").ColumnWidth = 35
        xlWorkSheet.Columns("D").ColumnWidth = 10
        xlWorkSheet.Columns("E").ColumnWidth = 10
        xlWorkSheet.Columns("F").ColumnWidth = 10
        xlWorkSheet.Columns("G").ColumnWidth = 10
        xlWorkSheet.Columns("H").ColumnWidth = 10

        'Style for Vendor Information
        Dim style2 As Excel.Style = xlWorkSheet.Application.ActiveWorkbook.Styles.Add("VendorStyle")
        style2.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.WhiteSmoke)
        style2.Font.Bold = True

        'Style for Products that do not have sufficient quantity on hand or on order
        Dim style3 As Excel.Style = xlWorkSheet.Application.ActiveWorkbook.Styles.Add("FailText")
        style3.Font.Color = Color.Red
        style3.Font.Bold = True
        style3.HorizontalAlignment = Excel.Constants.xlCenter

        'Style for Products that do not have sufficient quantity on hand or on order
        Dim style7 As Excel.Style = xlWorkSheet.Application.ActiveWorkbook.Styles.Add("PassText")
        style7.Font.Color = Color.Green
        style7.Font.Bold = True
        style7.HorizontalAlignment = Excel.Constants.xlCenter

        'Style for Report
        Dim style4 As Excel.Style = xlWorkSheet.Application.ActiveWorkbook.Styles.Add("ReportTitle")
        style4.Font.Color = Color.DarkGray
        style4.Font.Size = 16
        style4.Font.Bold = True
        style4.HorizontalAlignment = Excel.Constants.xlLeft

        'Not used at the moment
        Dim style5 As Excel.Style = xlWorkSheet.Application.ActiveWorkbook.Styles.Add("BGRead")
        style5.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.WhiteSmoke)

        'Style for Headers
        Dim style As Excel.Style = xlWorkSheet.Application.ActiveWorkbook.Styles.Add("NewStyle")
        style.Font.Bold = True
        style.Font.Color = Color.White
        style.HorizontalAlignment = Excel.Constants.xlCenter
        style.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray)

        'Styles for Borders - only XLEdgeLEFT Works.
        Dim style6 As Excel.Style = xlWorkSheet.Application.ActiveWorkbook.Styles.Add("CellBord")
        style6.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous
        style6.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous
        style6.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous
        style6.Borders(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlThin
        style6.Borders(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlThin
        style6.Borders(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlThin
        style6.Borders(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlThin
        
        'Set Report & Column Titles
        xlWorkSheet.Cells(1, 1).Style = "ReportTitle"
        xlWorkSheet.Cells(1, 1) = "Item Summery Report Pass or Fail"
        xlWorkSheet.Cells(2, 1) = "Report Date: " & myStartDate
        xlWorkSheet.Cells(3, 1) = "Sku"
        xlWorkSheet.Cells(3, 2) = "Name"
        xlWorkSheet.Cells(3, 3) = "Sold"
        xlWorkSheet.Cells(3, 4) = "On SO"
        xlWorkSheet.Cells(3, 5) = "On Hnd"
        xlWorkSheet.Cells(3, 6) = "On Ord"
        xlWorkSheet.Cells(3, 7) = "So/Mo"
        xlWorkSheet.Cells(3, 8) = "30d Fail"
        xlWorkSheet.Range("A3", "H3").RowHeight = 16
        xlWorkSheet.Range("A3", "H3").Style = "NewStyle"

        Dim lv As Integer = itemView1.Items.Count - 1
        Dim i, sd As Integer
        sd = 0
        Dim useSQL2 As Integer = 0

        For i = 0 To lv

            useSQL2 = 0
            CountRows += 1

            'Check if this is a header
            If itemView1.Items(i).SubItems(4).Equals(System.DBNull.Value) Or itemView1.Items(sd).SubItems(4).Text = "" Then
                itemView1.Items(i).SubItems(4).Text = ""
                useSQL2 = 1
            End If

            If useSQL2 = 1 Then
                'Secondary Header for Vendor Name
                xlWorkSheet.Cells(ro, 1) = itemView1.Items(sd).SubItems(1).Text
                xlWorkSheet.Cells(ro, 3) = itemView1.Items(sd).SubItems(2).Text
                xlWorkSheet.Cells(ro, 1).Style = "VendorStyle"
                xlWorkSheet.Cells(ro, 3).Style = "VendorStyle"
                xlWorkSheet.Cells(ro, 2).Style = "VendorStyle"
                xlWorkSheet.Cells(ro, 4).Style = "VendorStyle"
                xlWorkSheet.Cells(ro, 5).Style = "VendorStyle"
                xlWorkSheet.Cells(ro, 6).Style = "VendorStyle"
                xlWorkSheet.Cells(ro, 7).Style = "VendorStyle"
                xlWorkSheet.Cells(ro, 8).Style = "VendorStyle"

            Else
                'Actual Product Row
                xlWorkSheet.Cells(ro, 1) = itemView1.Items(sd).Text
                xlWorkSheet.Cells(ro, 1).style = "CellBord"
                xlWorkSheet.Cells(ro, 2) = itemView1.Items(sd).SubItems(1).Text
                xlWorkSheet.Cells(ro, 1).style = "CellBord"
                xlWorkSheet.Cells(ro, 3) = CDbl(itemView1.Items(sd).SubItems(2).Text)
                xlWorkSheet.Cells(ro, 1).style = "CellBord"
                xlWorkSheet.Cells(ro, 4) = CDbl(itemView1.Items(sd).SubItems(3).Text)
                xlWorkSheet.Cells(ro, 1).style = "CellBord"
                xlWorkSheet.Cells(ro, 5) = CDbl(itemView1.Items(sd).SubItems(4).Text)
                xlWorkSheet.Cells(ro, 1).style = "CellBord"
                xlWorkSheet.Cells(ro, 6) = CDbl(itemView1.Items(sd).SubItems(5).Text)
                xlWorkSheet.Cells(ro, 1).style = "CellBord"
                xlWorkSheet.Cells(ro, 7) = CDbl(itemView1.Items(sd).SubItems(6).Text)
                xlWorkSheet.Cells(ro, 1).style = "CellBord"
                If itemView1.Items(sd).SubItems(7).Text = "Fail" Then
                    xlWorkSheet.Cells(ro, 8).Style = "FailText"
                Else
                    xlWorkSheet.Cells(ro, 8).Style = "PassText"
                End If
                xlWorkSheet.Cells(ro, 8) = itemView1.Items(sd).SubItems(7).Text
                xlWorkSheet.Cells(ro, 1).style = "CellBord"
            End If

            'Check if we need to make a nother header for a second page...  
            If CountRows > maxNum Then
                xlWorkSheet.HPageBreaks.Add(xlWorkSheet.Range("A" & (ro + 1), "H" & (ro + 1)))
                xlWorkSheet.Cells(ro + 1, 1) = "Sku"
                xlWorkSheet.Cells(ro + 1, 2) = "Name"
                xlWorkSheet.Cells(ro + 1, 3) = "Sold"
                xlWorkSheet.Cells(ro + 1, 4) = "On Sales Order"
                xlWorkSheet.Cells(ro + 1, 5) = "On Hand"
                xlWorkSheet.Cells(ro + 1, 6) = "On Order"
                xlWorkSheet.Cells(ro + 1, 7) = "Sold / Month"
                xlWorkSheet.Cells(ro + 1, 8) = "30 Day Fail"
                xlWorkSheet.Range("A" & (ro + 1), "H" & (ro + 1)).RowHeight = 16
                xlWorkSheet.Range("A" & (ro + 1), "H" & (ro + 1)).Style = "NewStyle"
                CountRows = 0
                ro += 2
            Else
                ro += 1
            End If
            sd += 1
            

        Next

        'Save here
        xlWorkSheet.SaveAs(fileName)

        xlWorkBook.Close()
        xlApp.Quit()

        releaseObject(xlApp)
        releaseObject(xlWorkBook)
        releaseObject(xlWorkSheet)

Select allOpen in new window

Thanks again for any help/insight.

I open up Excel a different way, which may explain the difference. I'm doing

            excelApp = GetObject(, "Excel.Application")

Select allOpen in new window

which connects to an existing Excel instance. However, you also need error handling to deal with the possibility that Excel isn't already open. If you'd like to go this route, I can post the full code section.

If that's not a route you want to go, then we can work through it in another way. What borders do you see if you comment out all the style settings except the one at line 136, for instance?

Looking at the code you posted, I see an apparent problem in lines 136-155: All of the .style settings are done to the cell in column 1. Also, in the style6 definition (lines 80-87), no linestyle is assigned to either the top or bottom borders (left is listed twice). Both of these are different from what you posted in your original message, so I'm not sure which reflects your actual code.

Thanks for the reply!

Yeah, in lines 80-87 I had it correct at one point, but then made changes trying to get it to work.  I wanted to post what I had originally, so I quickly typed it back in and obviously omitted some code.

style6.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous
        style6.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous
        style6.Borders(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous
 style6.Borders(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous
        style6.Borders(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlThin
        style6.Borders(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlThin
        style6.Borders(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlThin
        style6.Borders(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlThin

Select allOpen in new window

I will try eliminating borders from all but one of the cells and we'll see what that does.
I did eliminate all but one of the style declarations and only and only the left border works.  Top, Bottom, and Right even if they are the only edges defined don't work.

I will also try your way of connecting to the excel file.  This program is used in our office, and I don't know that people will always have excel on when generating reports - but maybe it would provide some insight all the same.

Also, the issue of the same column having the style applied at 136-155 is again the result of me trying to put the code back to where I had it so I could post it.  Each .style should be (and was) on a separate cell.

Thanks again for your help!  Seems like every where I look, this is just supposed to work.

One thought - these are the files I'm including:

Imports System.Data.Odbc
Imports System.IO
Imports System.Text
Imports System
Imports System.Net
Imports System.Drawing
Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Data.OleDb
Imports System.Threading

Select allOpen in new window

Is it possible I'm missing a file?

Thanks for your help on this.  I'm still stuck on why the borders aren't working, but I've settled for changing the background color for every other row, and that achieves the goal the user is looking for (readability).

I was able to use your technique to see the excel file being created, but ultimately, I couldn't find what was wrong.

Thanks again for your help/patience on this, I really appreciate it.

I'm sorry we couldn't figure out what the issue is, but I'm glad you have a workable solution.