Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Borders

Posted on 2014-01-13
10
Medium Priority
?
1,211 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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview
Suggested Courses

971 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