Format Excel Page in VB

Format Excel Page in VB.
NET
When programming with Excel in VB.Net, you can
interact with the objects provided by the Excel
object model. You can programmatically create a
worksheet, read worksheet, formatting cell etc. from your VB.Net
application. In this article we will take a closer look at manipulating Excel
cells from your VB.Net application.
The following source code using Microsoft Excel 12.0 Object Library.In
the previous section we saw how to import Microsoft Excel 12.0 Object
Library in the VB.Net project. In order to access the object model from
Visual VB.Net, you have to add the Microsoft Excel 12.0 Object Library to
you project. In the previous chapter you can see a step by step instruction
on how to add Excel library to your project.
How to add Excel Library
Format Excel Column (or Cell)

Format Excel cells to store values as text
Formating Excel cells to text format will solve the problem of losing leading
zeo values when you import data from other data sources to excel using
VB.Net.
Dim formatRange As Excel.Range
formatRange = xlWorkSheet.Range("a1", "b1")
formatRange.NumberFormat = "@"
xlWorkSheet.Cells(1, 1) = "098"
Excel Number Formatting

formatRange.NumberFormat = "#,###,###"
Excel Currency Formatting

formatRange.NumberFormat = "$ #,###,###.00"
Excel Date Formatting

formatRange.NumberFormat = "mm/dd/yyyy"
'formatRange.NumberFormat = "mm/dd/yyyy hh:mm:ss";
xlWorkSheet.Cells(1, 1) = "31/5/2014"
Bold the fonts of a specific row or cell

Bold entire row of an Excel Document
formatRange = xlWorkSheet.Range("a1")
formatRange.EntireRow.Font.Bold = True
xlWorkSheet.Cells(1, 5) = "Bold"
Bold specific cell

workSheet.Cells(2, 1).Font.Bold = True
Add border to a specific cell

Dim formatRange As Excel.Range = xlWorkSheet.UsedRange
Dim cell As Excel.Range = formatRange.Cells(3, 3)
Dim border As Excel.Borders = cell.Borders
border.LineStyle = Excel.XlLineStyle.xlContinuous
border.Weight = 2.0
Border around multiple cells in excel

formatRange = xlWorkSheet.Range("b2", "e9")
formatRange.BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
Excel.XlColorIndex.xlColorIndexAutomatic)
Excel Cell coloring

Cell background color

formatRange = xlWorkSheet.Range("b1", "b1")
formatRange.Interior.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
xlWorkSheet.Cells(1, 2) = "Red"
Cell font color , size

formatRange = xlWorkSheet.Range("b1", "b1")
formatRange.Font.Color =
formatRange.Font.Size = 10
xlWorkSheet.Cells(1, 2) = "Red"
Excel Styles to named range

Dim myStyle As Excel.Style = Globals.ThisWorkbook.Styles.Add("myStyle")
myStyle.Font.Name = "Verdana"
myStyle.Font.Size = 12
myStyle.Font.Color =
myStyle.Interior.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray)
myStyle.Interior.Pattern = Excel.XlPattern.xlPatternSolid
How to merge Excel cells

chartRange = xlWorkSheet.Range("b2", "e3")
chartRange.Merge()
Adding Custom header to the excel file

xlWorkSheet.Range("b2", "e3").Merge(False)
chartRange.FormulaR1C1 = "Your Heading Here"
chartRange.HorizontalAlignment = 3
chartRange.VerticalAlignment = 3
The following VB.Net program create a mark list in Excel file and format the
grid cells. First we merge these cell and create a custom header , then the
students name and totals format as BOLD . And finally create a border for
the whole mark list part.
Output:
Next : How to insert a Picture in Excel through programing on VB.NET

Download Source Code
Print Source Code
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim xlApp As Excel.Application = New
Microsoft.Office.Interop.Excel.Application()
If xlApp Is Nothing Then
MessageBox.Show("Excel is not properly installed!!")
Return
End If
Dim
Dim
Dim
Dim
xlWorkBook As Excel.Workbook
xlWorkSheet As Excel.Worksheet
misValue As Object = System.Reflection.Missing.Value
chartRange As Excel.Range
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")
'add data
xlWorkSheet.Cells(4,
2)
3)
4)
5)
=
=
=
=
""
"Student1"
"Student2"
"Student3"
2)
3)
4)
5)
=
=
=
=
"Term1"
"80"
"65"
"45"
2)
3)
4)
5)
=
=
=
=
"Term2"
"78"
"72"
"60"
xlWorkSheet.Cells(7, 2) = "Term3"
2)
3)
4)
5)
=
=
=
=
"Term4"
"75"
"82"
"68"
2)
3)
4)
5)
=
=
=
=
"Total"
"315"
"299"
"238"

chartRange.Merge()
chartRange.FormulaR1C1 = "MARK LIST"
chartRange.HorizontalAlignment = 3
chartRange.VerticalAlignment = 3
chartRange.Interior.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow)
chartRange.Font.Color =
chartRange.Font.Size = 20
chartRange.Font.Bold = True
chartRange.Font.Bold = True
chartRange.BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
Excel.XlColorIndex.xlColorIndexAutomatic)
xlWorkBook.SaveAs("d:\csharp.net-informations.xls",
Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
_
Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue,
misValue, misValue)
xlWorkBook.Close(True, misValue, misValue)
xlApp.Quit()
releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)
MessageBox.Show("File created !")
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
End Class

Format Excel Page in VB

Uploaded by

Copyright:

Available Formats

Format Excel Page in VB

Uploaded by

Document Information

Original Description:

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Format Excel Page in VB

Uploaded by

Copyright:

Available Formats

Format Excel Page in VB.

Format Excel Column (or Cell)

Excel Number Formatting

Excel Currency Formatting

Excel Date Formatting

Bold the fonts of a specific row or cell

Bold specific cell

Add border to a specific cell

Border around multiple cells in excel

Excel Cell coloring

Dim formatRange As Excel.Range

Cell font color , size

Excel Styles to named range

How to merge Excel cells

Adding Custom header to the excel file

Next : How to insert a Picture in Excel through programing on VB.NET

chartRange = xlWorkSheet.Range("b2", "e3")

You might also like