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
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.
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:
Quote:
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)
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
Re: How to merge cells and put border on text after exporting datagridview data in ex
Quote:
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.
Re: How to merge cells and put border on text after exporting datagridview data in ex
Quote:
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.
Re: How to merge cells and put border on text after exporting datagridview data in ex
Quote:
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.
Re: How to merge cells and put border on text after exporting datagridview data in ex
Quote:
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
Re: How to merge cells and put border on text after exporting datagridview data in ex
Quote:
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.
Re: How to merge cells and put border on text after exporting datagridview data in ex
Quote:
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.
