Create an Excel 2007 file and change font for the ceel in the Excel in VB.NET 2005

From the following sections you can find how to create an Excel 2007 worksheet through VB.NET 2005. For creating an Excel 2007 worksheet in VB.NET 2005 , you have to add the Microsoft Excel 12.0 Object Library in you project.

1.            Create a new project and add a button to the Form.

Select Add reference from Project menu and in the COM Tab select  Microsoft Excel 12.0 Object Library and click OK button

2. Now you can start coding to create a new Excel file.

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

        Dim xlWorkBook As Excel.Workbook

        Dim xlWorkSheet As Excel.Worksheet

        Dim misValue As Object = System.Reflection.Missing.Value

        Dim chartRange As Excel.Range

        xlApp = New Excel.ApplicationClass

        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) = "Maths"

        xlWorkSheet.Cells(5, 3) = "80"

        xlWorkSheet.Cells(5, 4) = "65"

        xlWorkSheet.Cells(5, 5) = "45"

        xlWorkSheet.Cells(6, 2) = "English"

        xlWorkSheet.Cells(6, 3) = "78"

        xlWorkSheet.Cells(6, 4) = "72"

        xlWorkSheet.Cells(6, 5) = "60"

        xlWorkSheet.Cells(7, 2) = "Telugu"

        xlWorkSheet.Cells(7, 3) = "82"

        xlWorkSheet.Cells(7, 4) = "80"

        xlWorkSheet.Cells(7, 5) = "65"

        xlWorkSheet.Cells(8, 2) = "Science"

        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.FormulaR1C1 = "MARKS LIST"

        chartRange.HorizontalAlignment = 3

        chartRange.VerticalAlignment = 3
        chartRange.Font.FontStyle = "arial"
        chartRange.ColumnWidth = "120"

        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)

        xlWorkSheet.SaveAs("MarksExcel.csv")

        xlWorkBook.Close()

        xlApp.Quit()

        releaseObject(xlApp)

        releaseObject(xlWorkBook)

        releaseObject(xlWorkSheet)

        MsgBox("File created Successfully !")

    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