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.

Excel Number Formatting

Excel Currency Formatting

Excel Date Formatting

Bold the fonts of a specific row or cell

Bold entire row of an Excel Document

Bold specific cell

Add border to a specific cell

Border around multiple cells in excel

Excel Cell coloring

Cell background color

Cell font color , size

Excel Styles to named range

How to merge Excel cells

Adding Custom header to the excel file

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





net-informations.com (C) 2019    Founded by raps mk
All Rights Reserved. All other trademarks are property of their respective owners.