Excel Form File

Imports System.IO

' made by Andro (Robin en Dries)
' Version 2.3

Imports Excel = Microsoft.Office.Interop.Excel 'Size 454; 121

Public Class Form1
    Private ds As New DataSet("AllData")

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ds = DatabaseRepository.RetrieveAll

        'txtFile.Text = Path.GetFullPath( _
        '    Path.Combine(Application.StartupPath, "..\..")) & _
        '    "\Items.xlsx"
    End Sub

    ' Write into the Excel workbook.
    Private Sub btnWrite_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnWrite.Click
        ' Get the Excel application object.
        Dim excel_app As New Excel.ApplicationClass()

        ' Make Excel visible (optional).
        excel_app.Visible = True

        'File.Create("C:\Users\driesh\Documents\VbToExcel\items.xlsx")
        ' Open the workbook.
        Dim workbook As Excel.Workbook = _
        excel_app.Workbooks.Open(Filename:="C:\Users\driesh\Documents\VbToExcel\items.xlsx")

        ' See if the worksheet already exists.
        Dim sheet_name As String = DateTime.Now.ToString("MM-dd-yy")

        Dim sheet As Excel.Worksheet = FindSheet(workbook, sheet_name)
        If (sheet Is Nothing) Then
            ' Add the worksheet at the end.
            sheet = DirectCast(workbook.Sheets.Add( _
                After:=workbook.Sheets(workbook.Sheets.Count), _
                Count:=1, _
                Type:=Excel.XlSheetType.xlWorksheet), Excel.Worksheet)
            sheet.Name = sheet_name
        End If

        ' Add some data to individual cells.
        sheet.Cells(1, 1) = "Test 1"
        sheet.Cells(1, 2) = "Test 2"
        sheet.Cells(1, 3) = "Test 3"

        Dim jj As Integer = 0

        For i = 1 To ds.Tables.Count
            If i = 17 Then Exit For
            Dim table As DataTable = ds.Tables(i - 1)
            For j = 0 To table.Rows.Count - 1
                For k = 0 To table.Columns.Count - 1
                    sheet.Cells((j + 1) + jj, (k + 1)) = table.Rows(j)(k).ToString
                Next
            Next
            jj += table.Rows.Count + 1
        Next

        ' Make that range of cells bold and red.
        'Dim header_range As Excel.Range = sheet.Range("A1", "C1")
        'header_range.Font.Bold = True
        'header_range.Font.Color = _
        '    System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red)
        'header_range.Interior.Color = _
        '    System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Pink)

        ' Add some data to a range of cells.
        'Dim values(,) As Integer = _
        '{ _
        '    {2, 4, 6}, _
        '    {3, 6, 9}, _
        '    {4, 8, 12}, _
        '    {5, 10, 15} _
        '}
        'Dim value_range As Excel.Range = sheet.Range("A2", "C5")
        'value_range.Value2 = values

        ' Save the changes and close the workbook.
        'workbook.Close(SaveChanges:=True)

        ' Close the Excel server.
        'excel_app.Quit()

        MessageBox.Show("Done")
    End Sub

    ' Return the worksheet with the given name.
    Private Function FindSheet(ByVal workbook As Excel.Workbook, ByVal sheet_name As String) As Excel.Worksheet
        For Each sheet As Excel.Worksheet In workbook.Sheets
            If (sheet.Name = sheet_name) Then Return sheet
        Next sheet

        Return Nothing
    End Function
End Class