Results 1 to 10 of 10

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

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    50

    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,487

    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,530

    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,596

    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
    Member
    Join Date
    Apr 2019
    Posts
    50

    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
    Member
    Join Date
    Apr 2019
    Posts
    50

    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
    Member
    Join Date
    Apr 2019
    Posts
    50

    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,596

    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.

  9. #9

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    50

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

    Quote Originally Posted by ChrisE View Post
    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
    Hello Sir. It's been a long time since I have posted this question seeking to merge cells. Thank you sir for your reply but I don' t want to merge cells this way: oSheet.Range("D9", "H13").Merge() since I don't have specific cells to merge. What I want to merge is the range where the theparagraph that comes after exported data. This is the code that will create Text after exported data:
    Code:
    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."
    I want to merge the cells in that area.

  10. #10

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    50

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

    Quote Originally Posted by ChrisE View Post
    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
    Hello Sir. It's been a long time since I have posted this question seeking to merge cells. Thank you sir for your reply but I don' t want to merge cells this way: oSheet.Range("D9", "H13").Merge() since I don't have specific cells to merge. What I want to merge is the range where the theparagraph that comes after exported data. This is the code that will create Text after exported data:
    Code:
    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."
    I want to merge the cells in that highlighted portion.
    This is what I did:
    Code:
    xlWorkSheet.Range(lastRowForGrid + 18, 1).Merge
    Please help me coz until now I did not get the solution.
    Last edited by ronelpisan; Mar 13th, 2020 at 10:12 AM. Reason: Specification

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
  •  



Click Here to Expand Forum to Full Width