Solved

Excel Borders

Posted on 2014-01-13
10
1,095 Views
Last Modified: 2014-01-18
Need some help with some borders.  I've been looking around for different ways to implement borders but I can't seem to get them to work.

Here's the code I'm using:
        Dim style6 As Excel.Style = xlWorkSheet.Application.ActiveWorkbook.Styles.Add("CellBord")
        style6.Borders(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous
        style6.Borders(Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlContinuous
        style6.Borders(Excel.XlBordersIndex.xlEdgeBottom).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

Open in new window


This is putting a right border on the cells (except the last one).  Here's how I use the style:

 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, 2).Style = "CellBord"
                xlWorkSheet.Cells(ro, 3) = CDbl(itemView1.Items(sd).SubItems(2).Text)
                xlWorkSheet.Cells(ro, 3).Style = "CellBord"
                xlWorkSheet.Cells(ro, 4) = CDbl(itemView1.Items(sd).SubItems(3).Text)
                xlWorkSheet.Cells(ro, 4).Style = "CellBord"
                xlWorkSheet.Cells(ro, 5) = CDbl(itemView1.Items(sd).SubItems(4).Text)
                xlWorkSheet.Cells(ro, 5).Style = "CellBord"
                xlWorkSheet.Cells(ro, 6) = CDbl(itemView1.Items(sd).SubItems(5).Text)
                xlWorkSheet.Cells(ro, 6).Style = "CellBord"
                xlWorkSheet.Cells(ro, 7) = CDbl(itemView1.Items(sd).SubItems(6).Text)
                xlWorkSheet.Cells(ro, 7).Style = "CellBord"
                xlWorkSheet.Cells(ro, 8).Style = "FailText"
                xlWorkSheet.Cells(ro, 8) = itemView1.Items(i).SubItems(7).Text
                xlWorkSheet.Cells(ro, 8).Style = "CellBord"

Open in new window


Any suggestions?  Thanks for the help!
0
Comment
Question by:slightlyoff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 96

Expert Comment

by:Bob Learned
ID: 39779466
It is strange that you are mixing index variables:

itemView1.Items(sd)

   and

itemView1.Items(i)
0
 
LVL 20

Expert Comment

by:ElrondCT
ID: 39779492
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?
0
 
LVL 1

Author Comment

by:slightlyoff
ID: 39779817
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()

Open 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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 20

Expert Comment

by:ElrondCT
ID: 39780315
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.
0
 
LVL 1

Author Comment

by:slightlyoff
ID: 39783040
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)

Open in new window


Thanks again for any help/insight.
0
 
LVL 20

Expert Comment

by:ElrondCT
ID: 39784518
I open up Excel a different way, which may explain the difference. I'm doing
            excelApp = GetObject(, "Excel.Application")

Open 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.
0
 
LVL 1

Author Comment

by:slightlyoff
ID: 39785472
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

Open 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

Open in new window


Is it possible I'm missing a file?
0
 
LVL 20

Accepted Solution

by:
ElrondCT earned 500 total points
ID: 39785614
Missing an Imports won't cause this kind of a problem; it would just mean that you'd have to use a more fully qualified name, or the code wouldn't compile. (If you don't import Excel, then you have to say Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, or you'll get an error.)

I would encourage you to step through the code, line by line, to make sure it's going in the directions you expect. It'll certainly be easier if you can have the Excel output visible while it's happening. You might use my method of opening Excel just for testing; if you can count on Excel being open (which you can make sure of for your test run), I think you should be able to just swap out your New Excel.Application for GetObject(, "Excel.Application").
0
 
LVL 1

Author Comment

by:slightlyoff
ID: 39791274
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.
0
 
LVL 20

Expert Comment

by:ElrondCT
ID: 39791389
I'm sorry we couldn't figure out what the issue is, but I'm glad you have a workable solution.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question