dcsimg
Results 1 to 8 of 8

Thread: How to merge cells and put border on text after exporting datagridview data in excel?

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Apr 2019
    Posts
    30

    How to merge cells and put border on text after exporting datagridview data in excel?

    I want to merge and put borders around text. This text is comes after datagridview data.

    This is my code:
    Code:
    Imports Microsoft.Office.Interop
    
    Public Class Form1
    
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'TODO: This line of code loads data into the 'ATSDatabaseDataSet.ATS' table. You can move, or remove it, as needed.
            Me.ATSTableAdapter.Fill(Me.ATSDatabaseDataSet.ATS)
    
        End Sub
    
        Private Sub DataGridView1_CellFormatting(sender As Object, e As System.Windows.Forms.DataGridViewCellFormattingEventArgs) Handles DataGridView1.CellFormatting
            DataGridView1.Rows(e.RowIndex).HeaderCell.Value = CStr(e.RowIndex + 1)
        End Sub
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
            Dim i As Integer
            Dim j As Integer
    
            xlApp = New Excel.Application
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("sheet1")
    
            For i = 0 To DataGridView1.RowCount - 2
                For j = 0 To DataGridView1.ColumnCount - 1
                    xlWorkSheet.Cells(i + 18, j + 2) = DataGridView1(j, i).Value.ToString()
                Next
            Next
            Dim lastRowForGrid As Integer = (DataGridView1.ColumnCount - 1) + 18
            xlWorkSheet.Cells(lastRowForGrid + 18, 1) = "This is the paragraph after datagridview data."
    
    
            xlWorkSheet.SaveAs("C:\Users\Programmer RBP\Desktop\vbexcel.xlsx")
            xlWorkBook.Close()
            xlApp.Quit()
    
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
    
            MsgBox("You can find the file C:\Users\Programmer RBP\Desktop\vbexcel.xlsx")
        End Sub
    End Class

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,331

    Re: How to merge cells and put border on text after exporting datagridview data in ex

    This is a very good Excel "tutorial" even though it is for VB 6.0.

    http://www.vbforums.com/showthread.p...6-(or-VB5-VBA)

    Look at post #12. It explains using a macro to generate code to use as a base. The code it generates is usually very helpful.
    Please remember next time...elections matter!

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,228

    Re: How to merge cells and put border on text after exporting datagridview data in ex

    That is good advice.

    Also note that I made a minor (but significant) mistake in the code example I gave previously, I showed this:
    Code:
            Dim lastRowForGrid as Integer = (DataGridView1.ColumnCount - 1) + 18
    ...but it should of course have been:
    Code:
            Dim lastRowForGrid as Integer = (DataGridView1.RowCount - 1) + 18
    Then your line after that should be altered as apt, eg:
    Code:
            xlWorkSheet.Cells(lastRowForGrid + 2, 1) = "This is the paragraph after datagridview data."
    (2 assumes you want a blank line between the grid data and the text)

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,085

    Re: How to merge cells and put border on text after exporting datagridview data in ex

    here some samples how to Format Cells / Ranges in Excel
    create Borders with Range

    Code:
    Option Strict On
    
    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop
    
    Public Class Form4
        Public Enum Excel_Colors
            rgbAliceBlue = 16775408          'Alice Blue
            rgbAntiqueWhite = 14150650       'Antique White
            rgbAqua = 16776960               'Aqua
            rgbAquamarine = 13959039         'Aquamarine
            rgbAzure = 16777200              'Azure
            rgbBeige = 14480885              'Beige
            rgbBisque = 12903679             'Bisque
            rgbBlack = 0                     'Black
            rgbBlanchedAlmond = 13495295     'Blanched Almond
            rgbBlue = 16711680               'Blue
            rgbBlueViolet = 14822282         'Blue Violet
            rgbBrown = 2763429               'Brown
            rgbBurlyWood = 8894686           'Burly Wood
            rgbCadetBlue = 10526303          'Cadet Blue
            rgbChartreuse = 65407            'Chartreuse
            rgbCoral = 5275647               'Coral
            rgbCornflowerBlue = 15570276     'Cornflower Blue
            rgbCornsilk = 14481663           'Cornsilk
            rgbCrimson = 3937500             'Crimson
            rgbDarkBlue = 9109504            'Dark Blue
            rgbDarkCyan = 9145088            'Dark Cyan
            rgbDarkGoldenrod = 755384        'Dark Goldenrod
            rgbDarkGray = 11119017           'Dark Gray
            rgbDarkGreen = 25600             'Dark Green
            rgbDarkGrey = 11119017           'Dark Grey
            rgbDarkKhaki = 7059389           'Dark Khaki
            rgbDarkMagenta = 9109643         'Dark Magenta
            rgbDarkOliveGreen = 3107669      'Dark Olive Green
            rgbDarkOrange = 36095            'Dark Orange
            rgbDarkOrchid = 13382297         'Dark Orchid
            rgbDarkRed = 139                 'Dark Red
            rgbDarkSalmon = 8034025          'Dark Salmon
            rgbDarkSeaGreen = 9419919        'Dark Sea Green
            rgbDarkSlateBlue = 9125192       'Dark Slate Blue
            rgbDarkSlateGray = 5197615       'Dark Slate Gray
            rgbDarkSlateGrey = 5197615       'Dark Slate Grey
            rgbDarkTurquoise = 13749760      'Dark Turquoise
            rgbDarkViolet = 13828244         'Dark Violet
            rgbDeepPink = 9639167            'Deep Pink
            rgbDeepSkyBlue = 16760576        'Deep Sky Blue
            rgbDimGray = 6908265             'Dim Gray
            rgbDimGrey = 6908265             'Dim Grey
            rgbDodgerBlue = 16748574         'Dodger Blue
            rgbFireBrick = 2237106           'Fire Brick
            rgbFloralWhite = 15792895        'Floral White
            rgbForestGreen = 2263842         'Forest Green
            rgbFuchsia = 16711935            'Fuchsia
            rgbGainsboro = 14474460          'Gainsboro
            rgbGhostWhite = 16775416         'Ghost White
            rgbGold = 55295                  'Gold
            rgbGoldenrod = 2139610           'Goldenrod
            rgbGray = 8421504                'Gray
            rgbGreen = 32768                 'Green
            rgbGreenYellow = 3145645         'Green Yellow
            rgbGrey = 8421504                'Grey
            rgbHoneydew = 15794160           'Honeydew
            rgbHotPink = 11823615            'Hot Pink
            rgbIndianRed = 6053069           'Indian Red
            rgbIndigo = 8519755              'Indigo
            rgbIvory = 15794175              'Ivory
            rgbKhaki = 9234160               'Khaki
            rgbLavender = 16443110           'Lavender
            rgbLavenderBlush = 16118015      'Lavender Blush
            rgbLawnGreen = 64636             'Lawn Green
            rgbLemonChiffon = 13499135       'Lemon Chiffon
            rgbLightBlue = 15128749          'Light Blue
            rgbLightCoral = 8421616          'Light Coral
            rgbLightCyan = 9145088           'Light Cyan
            rgbLightGoldenrodYellow = 13826810   'LightGoldenrodYellow
            rgbLightGray = 13882323          'Light Gray
            rgbLightGreen = 9498256          'Light Green
            rgbLightGrey = 13882323          'Light Grey
            rgbLightPink = 12695295          'Light Pink
            rgbLightSalmon = 8036607         'Light Salmon
            rgbLightSeaGreen = 11186720      'Light Sea Green
            rgbLightSkyBlue = 16436871       'Light Sky Blue
            rgbLightSlateGray = 10061943     'Light Slate Gray
            rgbLightSteelBlue = 14599344     'Light Steel Blue
            rgbLightYellow = 14745599        'Light Yellow
            rgbLime = 65280                  'Lime
            rgbLimeGreen = 3329330           'Lime Green
            rgbLinen = 15134970              'Linen
            rgbMaroon = 128                  'Maroon
            rgbMediumAquamarine = 11206502   'Medium Aquamarine
            rgbMediumBlue = 13434880         'Medium Blue
            rgbMediumOrchid = 13850042       'Medium Orchid
            rgbMediumPurple = 14381203       'Medium Purple
            rgbMediumSeaGreen = 7451452      'Medium Sea Green
            rgbMediumSlateBlue = 15624315    'Medium Slate Blue
            rgbMediumSpringGreen = 10156544  'Medium Spring Green
            rgbMediumTurquoise = 13422920    'Medium Turquoise
            rgbMediumVioletRed = 8721863     'Medium Violet Red
            rgbMidnightBlue = 7346457        'Midnight Blue
            rgbMintCream = 16449525          'Mint Cream
            rgbMistyRose = 14804223          'Misty Rose
            rgbMoccasin = 11920639           'Moccasin
            rgbNavajoWhite = 11394815        'Navajo White
            rgbNavy = 8388608                'Navy
            rgbNavyBlue = 8388608            'Navy Blue
            rgbOldLace = 15136253            'Old Lace
            rgbOlive = 32896                 'Olive
            rgbOliveDrab = 2330219           'Olive Drab
            rgbOrange = 42495                'Orange
            rgbOrangeRed = 17919             'Orange Red
            rgbOrchid = 14053594             'Orchid
            rgbPaleGoldenrod = 7071982       'Pale Goldenrod
            rgbPaleGreen = 10025880          'Pale Green
            rgbPaleTurquoise = 15658671      'Pale Turquoise
            rgbPaleVioletRed = 9662683       'Pale Violet Red
            rgbPapayaWhip = 14020607         'Papaya Whip
            rgbPeachPuff = 12180223          'Peach Puff
            rgbPeru = 4163021                'Peru
            rgbPink = 13353215               'Pink
            rgbPlum = 14524637               'Plum
            rgbPowderBlue = 15130800         'Powder Blue
            rgbPurple = 8388736              'Purple
            rgbRed = 255                     'Red
            rgbRosyBrown = 9408444           'Rosy Brown
            rgbRoyalBlue = 14772545          'Royal Blue
            rgbSalmon = 7504122              'Salmon
            rgbSandyBrown = 6333684          'Sandy Brown
            rgbSeaGreen = 5737262            'Sea Green
            rgbSeashell = 15660543           'Seashell
            rgbSienna = 2970272              'Sienna
            rgbSilver = 12632256             'Silver
            rgbSkyBlue = 15453831            'Sky Blue
            rgbSlateBlue = 13458026          'Slate Blue
            rgbSlateGray = 9470064           'Slate Gray
            rgbSnow = 16448255               'Snow
            rgbSpringGreen = 8388352         'Spring Green
            rgbSteelBlue = 11829830          'Steel Blue
            rgbTan = 9221330                 'Tan
            rgbTeal = 8421376                'Teal
            rgbThistle = 14204888            'Thistle
            rgbTomato = 4678655              'Tomato
            rgbTurquoise = 13688896          'Turquoise
            rgbViolet = 15631086             'Violet
            rgbWheat = 11788021              'Wheat
            rgbWhite = 16777215              'White
            rgbWhiteSmoke = 16119285         'White Smoke
            rgbYellow = 65535                'Yellow
            rgbYellowGreen = 3329434         'Yellow Green
        End Enum
    
        Public Enum Excel_ColorIndex
            Automatic = -4105
            Aqua = 42
            Black = 1
            Blue = 5
            BlueGray = 47
            BrightGreen = 4
            Brown = 53
            Cream = 19
            DarkBlue = 11
            DarkGreen = 51
            DarkPurple = 21
            DarkRed = 9
            DarkTeal = 49
            DarkYellow = 12
            Gold = 44
            Gray25 = 15
            Gray40 = 48
            Gray50 = 16
            Gray80 = 56
            Green = 10
            Indigo = 55
            Lavender = 39
            LightBlue = 41
            LightGreen = 35
            LightLavender = 24
            LightOrange = 45
            LightTurquoise = 20
            LightYellow = 36
            Lime = 43
            NavyBlue = 23
            OliveGreen = 52
            Orange = 46
            PaleBlue = 37
            Pink = 7
            Plum = 18
            PowderBlue = 17
            Red = 3
            Rose = 38
            Salmon = 22
            SeaGreen = 50
            SkyBlue = 33
            Tan = 40
            Teal = 14
            Turquoise = 8
            Violet = 13
            White = 2
            Yellow = 6
            '    Blue = 32 'duplicate of 5
            '    DarkBlue = 25 'duplicate of 11
            '    DarkRed = 30 'duplicate of 9
            '    LightTurquoise = 34 'duplicate of 20
            '    Pink = 26 'duplicate of 7
            '    Plum = 54 'duplicate of 18
            '    Teal = 31 'duplicate of 14
            '    Turquoise = 28 'duplicate of 8
            '    Violet = 29 'duplicate of 13
            '    Yellow = 27 'duplicate of 6
        End Enum
    
    
      
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
    
            Dim oXL As Excel.Application
            Dim oWB As Excel.Workbook
            Dim oSheet As Excel.Worksheet
            oXL = CType(CreateObject("Excel.Application"), Excel.Application)
            oWB = oXL.Workbooks.Add
            oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)
    
            oSheet.Range("A2").Value = "vbForums.com"
            oSheet.Range("B2").Value = "Summer is comming"
            oSheet.Range("C2").Value = "got to go now"
            'Excelformat
            oSheet.Range("A2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
            oSheet.Range("E2").HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            oSheet.Range("A2").ColumnWidth = 15
            oSheet.Range("B2").ColumnWidth = 20
            oSheet.Range("C2").ColumnWidth = 15.5
    
            'set Font Color and the Interior Color
            With oSheet.Range("A2", "C2")
                .Font.Bold = True
                .Font.Color = Excel_Colors.rgbIndianRed
                .Interior.Color = Excel_Colors.rgbKhaki
            End With
    
          
            'create Range with Border Color
            With oSheet.Range("D9", "H13")
                .BorderAround(Excel.XlLineStyle.xlContinuous, _
                Excel.XlBorderWeight.xlMedium, CType(Excel_ColorIndex.BrightGreen, XlColorIndex))
            End With
    
            'Save Excel
            oSheet.SaveAs("E:\ExcelColor.xlsx")
            Me.Cursor = Cursors.Default
            oXL.Quit()
            releaseObject(oXL)
            releaseObject(oWB)
            End_Excel_App(datestart, dateend)
        End Sub
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    
        Dim datestart As Date = Date.Now
        Dim dateend As Date = Date.Now
    
        Private Sub End_Excel_App(ByVal datestart As Date, ByVal dateEnd As Date)
            Dim xlp() As Process = Process.GetProcessesByName("Microsoft Excel")
            For Each Process As Process In xlp
                If Process.StartTime >= datestart And Process.StartTime <= dateEnd Then
                    Process.Kill()
                    Exit For
                End If
            Next
        End Sub
    End Class
    HTH
    Last edited by ChrisE; May 30th, 2019 at 01:18 PM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Apr 2019
    Posts
    30

    Re: How to merge cells and put border on text after exporting datagridview data in ex

    Quote Originally Posted by si_the_geek View Post
    That is good advice.

    Also note that I made a minor (but significant) mistake in the code example I gave previously, I showed this:

    ...but it should of course have been:
    Code:
            Dim lastRowForGrid as Integer = (DataGridView1.RowCount - 1) + 18
    Then your line after that should be altered as apt, eg:
    Code:
            xlWorkSheet.Cells(lastRowForGrid + 2, 1) = "This is the paragraph after datagridview data."
    (2 assumes you want a blank line between the grid data and the text)
    Thank you sir for the correction.

  6. #6

    Thread Starter
    Junior Member
    Join Date
    Apr 2019
    Posts
    30

    Re: How to merge cells and put border on text after exporting datagridview data in ex

    Quote Originally Posted by ChrisE View Post
    here some samples how to Format Cells / Ranges in Excel
    create Borders with Range

    Code:
    Option Strict On
    
    Imports Microsoft.Office.Interop.Excel
    Imports Microsoft.Office.Interop
    
    Public Class Form4
        Public Enum Excel_Colors
            rgbAliceBlue = 16775408          'Alice Blue
            rgbAntiqueWhite = 14150650       'Antique White
            rgbAqua = 16776960               'Aqua
            rgbAquamarine = 13959039         'Aquamarine
            rgbAzure = 16777200              'Azure
            rgbBeige = 14480885              'Beige
            rgbBisque = 12903679             'Bisque
            rgbBlack = 0                     'Black
            rgbBlanchedAlmond = 13495295     'Blanched Almond
            rgbBlue = 16711680               'Blue
            rgbBlueViolet = 14822282         'Blue Violet
            rgbBrown = 2763429               'Brown
            rgbBurlyWood = 8894686           'Burly Wood
            rgbCadetBlue = 10526303          'Cadet Blue
            rgbChartreuse = 65407            'Chartreuse
            rgbCoral = 5275647               'Coral
            rgbCornflowerBlue = 15570276     'Cornflower Blue
            rgbCornsilk = 14481663           'Cornsilk
            rgbCrimson = 3937500             'Crimson
            rgbDarkBlue = 9109504            'Dark Blue
            rgbDarkCyan = 9145088            'Dark Cyan
            rgbDarkGoldenrod = 755384        'Dark Goldenrod
            rgbDarkGray = 11119017           'Dark Gray
            rgbDarkGreen = 25600             'Dark Green
            rgbDarkGrey = 11119017           'Dark Grey
            rgbDarkKhaki = 7059389           'Dark Khaki
            rgbDarkMagenta = 9109643         'Dark Magenta
            rgbDarkOliveGreen = 3107669      'Dark Olive Green
            rgbDarkOrange = 36095            'Dark Orange
            rgbDarkOrchid = 13382297         'Dark Orchid
            rgbDarkRed = 139                 'Dark Red
            rgbDarkSalmon = 8034025          'Dark Salmon
            rgbDarkSeaGreen = 9419919        'Dark Sea Green
            rgbDarkSlateBlue = 9125192       'Dark Slate Blue
            rgbDarkSlateGray = 5197615       'Dark Slate Gray
            rgbDarkSlateGrey = 5197615       'Dark Slate Grey
            rgbDarkTurquoise = 13749760      'Dark Turquoise
            rgbDarkViolet = 13828244         'Dark Violet
            rgbDeepPink = 9639167            'Deep Pink
            rgbDeepSkyBlue = 16760576        'Deep Sky Blue
            rgbDimGray = 6908265             'Dim Gray
            rgbDimGrey = 6908265             'Dim Grey
            rgbDodgerBlue = 16748574         'Dodger Blue
            rgbFireBrick = 2237106           'Fire Brick
            rgbFloralWhite = 15792895        'Floral White
            rgbForestGreen = 2263842         'Forest Green
            rgbFuchsia = 16711935            'Fuchsia
            rgbGainsboro = 14474460          'Gainsboro
            rgbGhostWhite = 16775416         'Ghost White
            rgbGold = 55295                  'Gold
            rgbGoldenrod = 2139610           'Goldenrod
            rgbGray = 8421504                'Gray
            rgbGreen = 32768                 'Green
            rgbGreenYellow = 3145645         'Green Yellow
            rgbGrey = 8421504                'Grey
            rgbHoneydew = 15794160           'Honeydew
            rgbHotPink = 11823615            'Hot Pink
            rgbIndianRed = 6053069           'Indian Red
            rgbIndigo = 8519755              'Indigo
            rgbIvory = 15794175              'Ivory
            rgbKhaki = 9234160               'Khaki
            rgbLavender = 16443110           'Lavender
            rgbLavenderBlush = 16118015      'Lavender Blush
            rgbLawnGreen = 64636             'Lawn Green
            rgbLemonChiffon = 13499135       'Lemon Chiffon
            rgbLightBlue = 15128749          'Light Blue
            rgbLightCoral = 8421616          'Light Coral
            rgbLightCyan = 9145088           'Light Cyan
            rgbLightGoldenrodYellow = 13826810   'LightGoldenrodYellow
            rgbLightGray = 13882323          'Light Gray
            rgbLightGreen = 9498256          'Light Green
            rgbLightGrey = 13882323          'Light Grey
            rgbLightPink = 12695295          'Light Pink
            rgbLightSalmon = 8036607         'Light Salmon
            rgbLightSeaGreen = 11186720      'Light Sea Green
            rgbLightSkyBlue = 16436871       'Light Sky Blue
            rgbLightSlateGray = 10061943     'Light Slate Gray
            rgbLightSteelBlue = 14599344     'Light Steel Blue
            rgbLightYellow = 14745599        'Light Yellow
            rgbLime = 65280                  'Lime
            rgbLimeGreen = 3329330           'Lime Green
            rgbLinen = 15134970              'Linen
            rgbMaroon = 128                  'Maroon
            rgbMediumAquamarine = 11206502   'Medium Aquamarine
            rgbMediumBlue = 13434880         'Medium Blue
            rgbMediumOrchid = 13850042       'Medium Orchid
            rgbMediumPurple = 14381203       'Medium Purple
            rgbMediumSeaGreen = 7451452      'Medium Sea Green
            rgbMediumSlateBlue = 15624315    'Medium Slate Blue
            rgbMediumSpringGreen = 10156544  'Medium Spring Green
            rgbMediumTurquoise = 13422920    'Medium Turquoise
            rgbMediumVioletRed = 8721863     'Medium Violet Red
            rgbMidnightBlue = 7346457        'Midnight Blue
            rgbMintCream = 16449525          'Mint Cream
            rgbMistyRose = 14804223          'Misty Rose
            rgbMoccasin = 11920639           'Moccasin
            rgbNavajoWhite = 11394815        'Navajo White
            rgbNavy = 8388608                'Navy
            rgbNavyBlue = 8388608            'Navy Blue
            rgbOldLace = 15136253            'Old Lace
            rgbOlive = 32896                 'Olive
            rgbOliveDrab = 2330219           'Olive Drab
            rgbOrange = 42495                'Orange
            rgbOrangeRed = 17919             'Orange Red
            rgbOrchid = 14053594             'Orchid
            rgbPaleGoldenrod = 7071982       'Pale Goldenrod
            rgbPaleGreen = 10025880          'Pale Green
            rgbPaleTurquoise = 15658671      'Pale Turquoise
            rgbPaleVioletRed = 9662683       'Pale Violet Red
            rgbPapayaWhip = 14020607         'Papaya Whip
            rgbPeachPuff = 12180223          'Peach Puff
            rgbPeru = 4163021                'Peru
            rgbPink = 13353215               'Pink
            rgbPlum = 14524637               'Plum
            rgbPowderBlue = 15130800         'Powder Blue
            rgbPurple = 8388736              'Purple
            rgbRed = 255                     'Red
            rgbRosyBrown = 9408444           'Rosy Brown
            rgbRoyalBlue = 14772545          'Royal Blue
            rgbSalmon = 7504122              'Salmon
            rgbSandyBrown = 6333684          'Sandy Brown
            rgbSeaGreen = 5737262            'Sea Green
            rgbSeashell = 15660543           'Seashell
            rgbSienna = 2970272              'Sienna
            rgbSilver = 12632256             'Silver
            rgbSkyBlue = 15453831            'Sky Blue
            rgbSlateBlue = 13458026          'Slate Blue
            rgbSlateGray = 9470064           'Slate Gray
            rgbSnow = 16448255               'Snow
            rgbSpringGreen = 8388352         'Spring Green
            rgbSteelBlue = 11829830          'Steel Blue
            rgbTan = 9221330                 'Tan
            rgbTeal = 8421376                'Teal
            rgbThistle = 14204888            'Thistle
            rgbTomato = 4678655              'Tomato
            rgbTurquoise = 13688896          'Turquoise
            rgbViolet = 15631086             'Violet
            rgbWheat = 11788021              'Wheat
            rgbWhite = 16777215              'White
            rgbWhiteSmoke = 16119285         'White Smoke
            rgbYellow = 65535                'Yellow
            rgbYellowGreen = 3329434         'Yellow Green
        End Enum
    
        Public Enum Excel_ColorIndex
            Automatic = -4105
            Aqua = 42
            Black = 1
            Blue = 5
            BlueGray = 47
            BrightGreen = 4
            Brown = 53
            Cream = 19
            DarkBlue = 11
            DarkGreen = 51
            DarkPurple = 21
            DarkRed = 9
            DarkTeal = 49
            DarkYellow = 12
            Gold = 44
            Gray25 = 15
            Gray40 = 48
            Gray50 = 16
            Gray80 = 56
            Green = 10
            Indigo = 55
            Lavender = 39
            LightBlue = 41
            LightGreen = 35
            LightLavender = 24
            LightOrange = 45
            LightTurquoise = 20
            LightYellow = 36
            Lime = 43
            NavyBlue = 23
            OliveGreen = 52
            Orange = 46
            PaleBlue = 37
            Pink = 7
            Plum = 18
            PowderBlue = 17
            Red = 3
            Rose = 38
            Salmon = 22
            SeaGreen = 50
            SkyBlue = 33
            Tan = 40
            Teal = 14
            Turquoise = 8
            Violet = 13
            White = 2
            Yellow = 6
            '    Blue = 32 'duplicate of 5
            '    DarkBlue = 25 'duplicate of 11
            '    DarkRed = 30 'duplicate of 9
            '    LightTurquoise = 34 'duplicate of 20
            '    Pink = 26 'duplicate of 7
            '    Plum = 54 'duplicate of 18
            '    Teal = 31 'duplicate of 14
            '    Turquoise = 28 'duplicate of 8
            '    Violet = 29 'duplicate of 13
            '    Yellow = 27 'duplicate of 6
        End Enum
    
    
      
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    
    
            Dim oXL As Excel.Application
            Dim oWB As Excel.Workbook
            Dim oSheet As Excel.Worksheet
            oXL = CType(CreateObject("Excel.Application"), Excel.Application)
            oWB = oXL.Workbooks.Add
            oSheet = CType(oWB.ActiveSheet, Excel.Worksheet)
    
            oSheet.Range("A2").Value = "vbForums.com"
            oSheet.Range("B2").Value = "Summer is comming"
            oSheet.Range("C2").Value = "got to go now"
            'Excelformat
            oSheet.Range("A2").HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter
            oSheet.Range("E2").HorizontalAlignment = Excel.XlHAlign.xlHAlignRight
            oSheet.Range("A2").ColumnWidth = 15
            oSheet.Range("B2").ColumnWidth = 20
            oSheet.Range("C2").ColumnWidth = 15.5
    
            'set Font Color and the Interior Color
            With oSheet.Range("A2", "C2")
                .Font.Bold = True
                .Font.Color = Excel_Colors.rgbIndianRed
                .Interior.Color = Excel_Colors.rgbKhaki
            End With
    
          
            'create Range with Border Color
            With oSheet.Range("D9", "H13")
                .BorderAround(Excel.XlLineStyle.xlContinuous, _
                Excel.XlBorderWeight.xlMedium, CType(Excel_ColorIndex.BrightGreen, XlColorIndex))
            End With
    
            'Save Excel
            oSheet.SaveAs("E:\ExcelColor.xlsx")
            Me.Cursor = Cursors.Default
            oXL.Quit()
            releaseObject(oXL)
            releaseObject(oWB)
            End_Excel_App(datestart, dateend)
        End Sub
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
    
        Dim datestart As Date = Date.Now
        Dim dateend As Date = Date.Now
    
        Private Sub End_Excel_App(ByVal datestart As Date, ByVal dateEnd As Date)
            Dim xlp() As Process = Process.GetProcessesByName("Microsoft Excel")
            For Each Process As Process In xlp
                If Process.StartTime >= datestart And Process.StartTime <= dateEnd Then
                    Process.Kill()
                    Exit For
                End If
            Next
        End Sub
    End Class
    HTH
    Thanks for sharing..but I want to merge those cells(after the paragraph). Thanks.

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Apr 2019
    Posts
    30

    Re: How to merge cells and put border on text after exporting datagridview data in ex

    Quote Originally Posted by si_the_geek View Post
    That is good advice.

    Also note that I made a minor (but significant) mistake in the code example I gave previously, I showed this:

    ...but it should of course have been:
    Code:
            Dim lastRowForGrid as Integer = (DataGridView1.RowCount - 1) + 18
    Then your line after that should be altered as apt, eg:
    Code:
            xlWorkSheet.Cells(lastRowForGrid + 2, 1) = "This is the paragraph after datagridview data."
    (2 assumes you want a blank line between the grid data and the text)
    Thanks for the correction but I want to merge those cells(after the paragraph). Thanks.

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,085

    Re: How to merge cells and put border on text after exporting datagridview data in ex

    Quote Originally Posted by ronelpisan View Post
    Thanks for sharing..but I want to merge those cells(after the paragraph). Thanks.
    dont know what the problem is
    just add this to my sample,
    to merge Cell-Range =
    Code:
       
      'create Range with Border Color
            With oSheet.Range("D9", "H13")
    
                .BorderAround(Excel.XlLineStyle.xlDouble, _
                Excel.XlBorderWeight.xlMedium, CType(Excel_ColorIndex.Brown, XlColorIndex))
    
            End With
            oSheet.Range("D9").Value = "here you put your xxxxxxxxxxxx " & _
                          "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx " & _
                          "################################################"
    
            'merge the Range
            oSheet.Range("D9", "H13").Merge()
            'this would create multiline
            oSheet.Range("D9").WrapText = True
    
       
            'Save Excel
            oSheet.SaveAs("E:\ExcelColor.xlsx")
    
    '......
    to undo the Merge
    Code:
        oSheet.Range("D9", "H13").MergeCells = False
    Last edited by ChrisE; May 31st, 2019 at 01:21 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width