none
Putting borders in all cells of a worksheet of an excel file .xlsx that has value, how i do that? RRS feed

  • Question

  • I wish to put borders in all cells that have some value or something written on the sheet of excel application for once, i already know the code to put borders in cells, but, i want to find a way of knowing if the cells in the sheet have something written and the put borders in all of the cells for once. help please D:
    Friday, June 22, 2018 3:25 AM

All replies

  • Here is something to start off with.

    Option Strict On
    Option Infer Off
    
    Imports Excel = Microsoft.Office.Interop.Excel
    Imports Microsoft.Office
    Imports System.Runtime.InteropServices
    Module Module1
        Public Sub SomeUserCode()
            Dim Filename As String = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Today.xlsx")
    
            If IO.File.Exists(Filename) Then
                IO.File.Delete(Filename)
            End If
    
            Dim dt As New DataTable
            Dim dc As New DataColumn("ID", GetType(String))
            Dim dc1 As New DataColumn("Nome Cliente", GetType(String))
            Dim dcmail As New DataColumn("Email", GetType(String))
            Dim dctelemovel As New DataColumn("Telemovel", GetType(String))
            Dim dc2 As New DataColumn("Produto", GetType(String))
            Dim dc3 As New DataColumn("PVP", GetType(String))
            Dim dc4 As New DataColumn("Desconto", GetType(String))
            Dim dc5 As New DataColumn("Preço Final", GetType(String))
            Dim dc6 As New DataColumn("Preço Final S/ Iva", GetType(String))
    
            dt.Columns.Add(dc)
            dt.Columns.Add(dc1)
            dt.Columns.Add(dcmail)
            dt.Columns.Add(dctelemovel)
            dt.Columns.Add(dc2)
            dt.Columns.Add(dc3)
            dt.Columns.Add(dc4)
            dt.Columns.Add(dc5)
            dt.Columns.Add(dc6)
            dt.TableName = "Resumo"
    
            For x As Integer = 0 To 5 - 1
                Dim dr As DataRow = dt.NewRow
    
                dr("ID") = "1234"
                dr("Nome Cliente") = "Company"
                dr("Email") = "andre@domain.pt"
                dr("Telemovel") = "915651268"
                dr("Produto") = "Gasoleo"
                dr("PVP") = "1.23"
                dr("Desconto") = "0.01"
                dr("Preço Final") = "123"
                dr("Preço Final S/ Iva") = "100"
                dt.Rows.Add(dr)
    
            Next
    
    
            Dim xlApp As Excel.Application = Nothing
            Dim xlWorkBooks As Excel.Workbooks = Nothing
            Dim xlWorkBook As Excel.Workbook = Nothing
            Dim xlWorkSheets As Excel.Sheets = Nothing
    
            Dim sheet As Excel.Worksheet = Nothing
            xlApp = New Excel.Application
            xlApp.DisplayAlerts = False
    
            xlWorkBooks = xlApp.Workbooks
            xlWorkBook = xlWorkBooks.Add()
            Dim colindex As Integer = 0
            Dim rowindex As Integer = 0
            Dim dcolumn As System.Data.DataColumn
            Dim drow As System.Data.DataRow
    
            For Each dcolumn In dt.Columns
                colindex = colindex + 1
                xlApp.Cells(1, colindex) = dcolumn.ColumnName
            Next
    
            xlWorkSheets = xlWorkBook.Sheets
            sheet = CType(xlWorkSheets(1), Excel.Worksheet)
            For Each drow In dt.Rows
                rowindex = rowindex + 1
                colindex = 0
                For Each dcolumn In dt.Columns
                    Dim formatRange As Excel.Range = sheet.UsedRange
                    Dim cell As Excel.Range = CType(formatRange.Cells(3, 3), Excel.Range)
                    colindex = colindex + 1
                    xlApp.Cells(rowindex + 1, colindex) = drow(dcolumn.ColumnName)
                Next
            Next
    
            With sheet.Range("D2:D6")
                .Interior.ColorIndex = 6
                With .Font
                    .ColorIndex = 4
                    .Size = 8
                    .Name = "Tahoma"
                    .Underline = Excel.XlUnderlineStyle.xlUnderlineStyleSingle
                    .Bold = True
                End With
                .Borders.Color = 4
                .Borders.LineStyle = Excel.XlLineStyle.xlDash
            End With
    
            xlWorkBook.SaveAs(Filename)
    
            xlWorkBook.Close(False, "", Nothing)
            Marshal.ReleaseComObject(xlWorkBook)
            Marshal.ReleaseComObject(sheet)
            xlApp.UserControl = True
            xlApp.Quit()
    
        End Sub
    End Module
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, June 22, 2018 10:02 AM
    Moderator
  • i finished already. :)
    Sunday, June 24, 2018 7:51 PM
  • i finished already. :)

    Hi 

    I am glad you have got your solution, we appreciated you shared us your solution and mark it as an answer.

    Best regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, July 4, 2018 8:54 AM
  • i finished already. :)

    You only closed the first thread linked above.  The other two threads are still open and awaiting your reply.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Wednesday, July 4, 2018 2:12 PM
    Moderator