-
May 30th, 2019, 02:32 AM
#1
Thread Starter
Member
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
-
May 30th, 2019, 05:46 AM
#2
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!
-
May 30th, 2019, 05:52 AM
#3
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)
-
May 30th, 2019, 01:06 PM
#4
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.
-
May 30th, 2019, 08:32 PM
#5
Thread Starter
Member
Re: How to merge cells and put border on text after exporting datagridview data in ex
Originally Posted by si_the_geek
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.
-
May 30th, 2019, 08:55 PM
#6
Thread Starter
Member
Re: How to merge cells and put border on text after exporting datagridview data in ex
Originally Posted by ChrisE
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.
-
May 30th, 2019, 08:57 PM
#7
Thread Starter
Member
Re: How to merge cells and put border on text after exporting datagridview data in ex
Originally Posted by si_the_geek
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.
-
May 31st, 2019, 12:55 AM
#8
Re: How to merge cells and put border on text after exporting datagridview data in ex
Originally Posted by ronelpisan
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.
-
Mar 13th, 2020, 09:55 AM
#9
Thread Starter
Member
Re: How to merge cells and put border on text after exporting datagridview data in ex
Originally Posted by ChrisE
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.
-
Mar 13th, 2020, 09:57 AM
#10
Thread Starter
Member
Re: How to merge cells and put border on text after exporting datagridview data in ex
Originally Posted by ChrisE
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|