TemplateUseVB

Imports GemBox.Spreadsheet

Module TemplateUseVB

    Sub Main()
        ' TODO: If using GemBox.Spreadsheet Professional, put your serial key below.
        ' Otherwise, if you are using GemBox.Spreadsheet Free, comment out the 
        ' following line (Free version doesn't have SetLicense method). 
        ' SpreadsheetInfo.SetLicense("YOUR-SERIAL-KEY-HERE")

        Dim ef As ExcelFile = New ExcelFile

        ef.LoadXls("..\InvoiceTemplate.xls")

        FillInvoiceData(ef.Worksheets(0))

        Dim fileName As String = "Invoice14.xls"

        ef.SaveXls(fileName)

        TryToDisplayGeneratedFile(fileName)
    End Sub

    Sub FillInvoiceData(ByVal ws As ExcelWorksheet)
        ws.Cells("J5").Value = 14
        ws.Cells("J6").Value = New DateTime(2004, 12, 20)
        ws.Cells("J6").Style.NumberFormat = "m/dd/yyyy"

        ws.Cells("D12").Value = "ACME Corp"
        ws.Cells("D13").Value = "240 Old Country Road, Springfield, IL"
        ws.Cells("D14").Value = "USA"
        ws.Cells("D15").Value = "Joe Smith"

        ws.Cells("E18").Value = "6-Dec-04 until 17-Dec-04."

        Dim i As Integer
        For i = 0 To data.GetLength(0) - 1 Step i + 1
            ws.Cells(21 + i, 1).Value = data(i, 0)
            ws.Cells(21 + i, 1).Style.NumberFormat = "dddd, mmmm dd, yyyy"
            ws.Cells(21 + i, 4).Value = data(i, 1)
        Next

        ws.Cells("B36").Value = "Payment via check."
    End Sub

    Private data(,) As Object = New Object(,) _
    { _
        {New DateTime(2004, 12, 6), 8}, _
        {New DateTime(2004, 12, 7), 9}, _
        {New DateTime(2004, 12, 8), 8}, _
        {New DateTime(2004, 12, 9), 7}, _
        {New DateTime(2004, 12, 10), 7}, _
        {New DateTime(2004, 12, 13), 9}, _
        {New DateTime(2004, 12, 14), 8}, _
        {New DateTime(2004, 12, 15), 9}, _
        {New DateTime(2004, 12, 16), 9}, _
        {New DateTime(2004, 12, 17), 6} _
    }

    Sub TryToDisplayGeneratedFile(ByVal fileName As String)
        Try
            System.Diagnostics.Process.Start(fileName)
        Catch
            Console.WriteLine(fileName + " created in application folder.")
        End Try
    End Sub

End Module