IN VB.Net how to write to Excel file with formatting or with a template

Question

I want to use a template to format the output excel file created in vb.net. I could also use formatting like bold for headers instead of a template. Where can I find a solution? An example would be appreciated.

I wasnt able to find a solution

Answer 1

This is an (very basic) example of code to open, write and save an excel file using Microsoft.Office.Interop nameSpace. Cell formating is made at write time.

You can also pre-format excel file (painting headers rows and columns, bolding etc...) and use it as template. don't forget to save under another fileName to preserve your template file.

Imports Microsoft.Office.Interop

Public Class ExcelApp

    Public Shared xlapp As Excel.Application
    Public Shared xlbook As Excel.Workbook
    Public Shared xlsheet As Excel.Worksheet

    'open excel file
    Public Function OpenSheet(ByVal FileName As String, password As String) As Boolean

        Try
            xlapp = New Excel.Application
            'open file and sheet
            xlapp.Visible = False
            xlapp.DisplayAlerts = False
            xlbook = xlapp.Workbooks.Open(Filename:=FileName, Password:=password)
            xlsheet = xlapp.Sheets(1)
        Catch ex As Exception
            Return False
        End Try
        Return True
    End Function

    'write value in cell with some formating
    Public Sub WriteCell(ByVal SheetName As String, ByVal line As Integer, ByVal col As Integer, ByVal txt As String)

        Try
            xlapp.Sheets(SheetName).Select()
            xlapp.ActiveSheet.Cells(line, col).Value = txt
            xlapp.ActiveSheet.Cells(line, col).font.bold = True
            xlapp.ActiveSheet.Cells(line, col).Orientation = 60
            xlapp.ActiveSheet.Cells(line, col).interior.color = Color.Beige
        Catch ex As Exception
        End Try
    End Sub

    Public Function saveAndCloseExcelFile(ByVal FileName As String) As Boolean
        xlapp.DisplayAlerts = False
        Try
            xlapp.ActiveWorkbook.SaveAs(FileName)
        Catch ex As Exception
            Return False
        End Try

        xlbook.Close()
        xlapp.Quit()
        xlapp = Nothing
        GC.Collect()
        Return True
    End Function

End Class