[RESOLVED] Vb.net excel hide gridlines
Can someone please help me with the command to hide gridlines when creating an Excel file?
I use the following code :
Code:
Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet, xlWorkSheet2, xlWorkSheet3 As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
xlWorkSheet.Name = "BYLAE A"
xlWorkSheet2 = xlWorkBook.Sheets.Add
xlWorkSheet2.Name = "BYLAE B"
xlWorkSheet3 = xlWorkBook.Sheets.Add
xlWorkSheet3.Name = "BYLAE C"
And my imports are :
Code:
Imports System.ComponentModel
Imports System.IO
Imports ClosedXML.Excel
Imports HtmlAgilityPack
Imports Microsoft.Office.Interop
Imports MySql.Data.MySqlClient
Imports Z.Dapper.Plus
I would have thought xlWorkSheet.displaygridline = false would do the trick, but it is not recognized as a command
Re: Vb.net excel hide gridlines
The below thread is for VBA, but it might shed light on what object you need to reference to turn them off.
https://stackoverflow.com/questions/...g-activewindow
Re: Vb.net excel hide gridlines
When it comes to working out how to do things inside Excel via code, let Excel itself help you out... open Excel, and record a Macro of you performing the task manually, then view the code of the macro.
You should be able to spot which line(s) of the code matter, and with some minor modifications you can use the relevant part(s) of that code in your VB.Net code.
Re: Vb.net excel hide gridlines
Thank you for that tip Si
However I have tried :
Code:
xlWorkSheet.DisplayGridlines = False
I get the following error when I run (not in the editing mode of Visual studio)
System.MissingMemberException: 'Public member 'DisplayGridlines' on type 'Worksheet' not found.'
I also tried :
Code:
xlWorkSheet.Activate()
xlWorkSheet.DisplayGridlines = False
I get the same error message.
Regards
Re: Vb.net excel hide gridlines
Re: Vb.net excel hide gridlines
@GideonE
don't try and hide the Gridlines, just define your range and color the Borders to White
I did need this in the past so here a sample
Code:
Option Strict On
Imports Microsoft.Office.Interop.Excel
Imports Microsoft.Office.Interop
Public Class Form1
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(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Try
Dim xlApp As New Microsoft.Office.Interop.Excel.Application()
Dim xlWb As Microsoft.Office.Interop.Excel.Workbook
xlWb = xlApp.Workbooks.Open("D:\TestFolder\vbexcel.xlsx")
Dim xlSt As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWb.Worksheets("Tabelle4"), Worksheet)
'Select Range and Color
With xlSt.Range("A2", "F10")
.Font.Bold = True
.Borders.Color = Excel_Colors.rgbWhite
End With
xlWb.Save()
xlApp.Quit()
xlApp = Nothing
Catch g As Exception
MessageBox.Show(g.ToString)
End Try
End Sub
End Class
Re: Vb.net excel hide gridlines
Quote:
Originally Posted by
GideonE
Thank you for that tip Si
However I have tried :
Code:
xlWorkSheet.DisplayGridlines = False
I get the following error when I run (not in the editing mode of Visual studio)
System.MissingMemberException: 'Public member 'DisplayGridlines' on type 'Worksheet' not found.'
I also tried :
Code:
xlWorkSheet.Activate()
xlWorkSheet.DisplayGridlines = False
I get the same error message.
Regards
I think you’ll find DisplayGridlines is a member of Range, rather than WorkSheet.Try this…
Code:
Dim x as Range = Worksheets("Sheet name").Cells
x.DisplayGridlines = False
Re: Vb.net excel hide gridlines
Actually, after some more research, it appears displaygridlines is a member of window...
https://learn.microsoft.com/en-us/of...splaygridlines
Re: Vb.net excel hide gridlines
Quote:
Originally Posted by
.paul.
Agreed.
Code:
xlApp.ActiveWindow.DisplayGridlines = True / False
Where xlApp is your instance of the Excel application.
Re: Vb.net excel hide gridlines
Thank you dbasnett that did it for me.
Re: Vb.net excel hide gridlines
Quote:
Originally Posted by
GideonE
Thank you dbasnett that did it for me.
It was actually .paul.
