Net-informations.com
SiteMap  | About    

Format Excel Page in VB.NET

How to enter data with leading zero in excel 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

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




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

 CSharp Excel - Related Contents