Solved

Excel Borders

Posted on 2014-01-13
10
1,012 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
Comment Utility
It is strange that you are mixing index variables:

itemView1.Items(sd)

   and

itemView1.Items(i)
0
 
LVL 20

Expert Comment

by:ElrondCT
Comment Utility
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
Comment Utility
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
 
LVL 20

Expert Comment

by:ElrondCT
Comment Utility
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
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 20

Expert Comment

by:ElrondCT
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I'm sorry we couldn't figure out what the issue is, but I'm glad you have a workable solution.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now