Format Excel Page in VB.NET

When working with Excel in VB.Net, you have the ability to interact with the various objects provided by the Excel object model. This allows you to perform tasks such as creating worksheets, reading data from worksheets, and formatting cells, among others, directly from your VB.Net application. In this article, we will explore the process of manipulating Excel cells in more detail within the context of a VB.Net application.

The following source code utilizes the Microsoft Excel 12.0 Object Library. In the previous section, we learned how to import the Microsoft Excel 12.0 Object Library into a VB.Net project. In order to access the object model from Visual VB.Net, it is necessary to add the Microsoft Excel 12.0 Object Library to your project. For a step-by-step guide on how to add the Excel library to your project, please refer to the previous chapter.

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

Dim formatRange As Excel.Range formatRange = xlWorkSheet.Range("a1", "b1") formatRange.NumberFormat = "#,###,###" xlWorkSheet.Cells(1, 1) = "1234567890"

Excel Currency Formatting

Dim formatRange As Excel.Range formatRange = xlWorkSheet.Range("a1", "b1") formatRange.NumberFormat = "$ #,###,###.00" xlWorkSheet.Cells(1, 1) = "1234567890"

Excel Date Formatting

Dim formatRange As Excel.Range formatRange = xlWorkSheet.Range("a1", "b1") 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

Dim formatRange As Excel.Range 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

Dim formatRange As Excel.Range 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

Dim formatRange As Excel.Range 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

Dim formatRange As Excel.Range formatRange = xlWorkSheet.Range("b1", "b1") formatRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red) 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 = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red) 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 = xlWorkSheet.Range("b2", "e3") 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:


VB.Net Excel fomatting
Full Source VB.NET
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 xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value Dim chartRange As Excel.Range xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = xlWorkBook.Sheets("sheet1") 'add data xlWorkSheet.Cells(4, 2) = "" xlWorkSheet.Cells(4, 3) = "Student1" xlWorkSheet.Cells(4, 4) = "Student2" xlWorkSheet.Cells(4, 5) = "Student3" xlWorkSheet.Cells(5, 2) = "Term1" xlWorkSheet.Cells(5, 3) = "80" xlWorkSheet.Cells(5, 4) = "65" xlWorkSheet.Cells(5, 5) = "45" xlWorkSheet.Cells(6, 2) = "Term2" xlWorkSheet.Cells(6, 3) = "78" xlWorkSheet.Cells(6, 4) = "72" xlWorkSheet.Cells(6, 5) = "60" xlWorkSheet.Cells(7, 2) = "Term3" xlWorkSheet.Cells(7, 3) = "82" xlWorkSheet.Cells(7, 4) = "80" xlWorkSheet.Cells(7, 5) = "65" xlWorkSheet.Cells(8, 2) = "Term4" xlWorkSheet.Cells(8, 3) = "75" xlWorkSheet.Cells(8, 4) = "82" xlWorkSheet.Cells(8, 5) = "68" xlWorkSheet.Cells(9, 2) = "Total" xlWorkSheet.Cells(9, 3) = "315" xlWorkSheet.Cells(9, 4) = "299" xlWorkSheet.Cells(9, 5) = "238" chartRange = xlWorkSheet.Range("b2", "e3") chartRange.Merge() chartRange = xlWorkSheet.Range("b2", "e3") chartRange.FormulaR1C1 = "MARK LIST" chartRange.HorizontalAlignment = 3 chartRange.VerticalAlignment = 3 chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow) chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red) chartRange.Font.Size = 20 chartRange = xlWorkSheet.Range("b4", "e4") chartRange.Font.Bold = True chartRange = xlWorkSheet.Range("b9", "e9") chartRange.Font.Bold = True chartRange = xlWorkSheet.Range("b2", "e9") 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





Related Topics
  1. How to create an Excel file in VB.NET
  2. How to open or read an existing Excel file in VB.NET
  3. How to read an Excel file using VB.Net
  4. Add new worksheet in Excel file - VB.Net
  1. Delete worksheet from an excel file - VB.Net
  2. How to insert a Picture in Excel through programing on VB.NET
  3. How to insert a background Picture in Excel through VB.NET
  4. How to create a Chart in Excel in VB.NET
  5. How to export a Chart in Excel as Picture file from VB.NET
  6. How to Excel Chart in VB.NET Picture Box
  7. How to Excel DataBar in VB.NET
  8. How to Excel Data Validation Input Message
  9. Read and Import Excel File into DataSet or DataTable in vb.net
  10. How to insert cell data in an Excel file using OLEDB
  11. How to update cell data in an Excel file using OLEDB
  12. How to export from database to excel
  13. How to export from DataGridView to excel in VB.Net