Generating an Excel Spreadsheet from VB.NET

Download the Source Files

It seems quite often that I need to programmatically create an Excel spreadsheet with data from a database or some other location. I usually just create a CSV (comma separated value) file and dump the results.

There are many times when it would be much nicer to create a real Excel file where I could add formatting and additional worksheets.

I have a list of files that I need dumped to a spreadsheet on a regular basis with each different directory being located in a new worksheet. I went looking for a nice simple way of creating this from a .NET program, and although there were quite a few sites that talked about this, none of them gave a straight forward demonstration.

So I figured I would place this nice basic code into a tutorial for anyone who needs to create an Excel spreadsheet with multiple worksheets.

The first thing that we need to do is create a simple WinForm project.


I named my project GenerateFileList, but you can name your’s whatever you would like. On the form, I created two simple controls: a textbox that I named txtPath and a button I named btnGenerateList. The textbox will be used to set which folder path you want to generate the file list from.



After we finish setting up our basic form, the next thing we need to do is add a reference to the Microsoft Excel Interop Library which can be found in the COM components tab.


This reference gives us a nice little framework to work with Excel files.

In our code behind file, we need references to two assemblies: the Excel assembly that we just added a reference to and System.IO since we will be working with files and folders.

Imports Microsoft.Office.Interop
Imports System.IO

I declare one global variable that is the workbook. I placed this in the global scope so that I don’t have to pass it around the functions (I’m a lazy programmer) and just access it from anywhere.

Dim wb As Excel.Workbook

When creating an Excel spreadsheet programmatically, you basically have 3 object types you will create: the Excel Application, the Excel Workbook, and the Excel Worksheet.

First lets create our function that will be called when the user click the button.

    Private Sub btnGenerateList_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenerateList.Click
        Dim ex As New Excel.Application
        wb = ex.Workbooks.Add
        'Check to make sure the directory given exists.
        If Directory.Exists(Me.txtPath.Text) Then
            'Prompt the user of where to save the file
            Dim dialog As New SaveFileDialog
            Dim result As DialogResult = dialog.ShowDialog
            'Check to make sure the user didn't click cancel.
            If result = Windows.Forms.DialogResult.OK Then
                'Recursivly build the folder structure.
                Me.BuildFolder(New DirectoryInfo(Me.txtPath.Text))
                wb.SaveAs(dialog.FileName)
                'Show the spreadsheet.
                ex.Visible = True
                wb.Activate()
            End If
        End If
    End Sub

The first thing we do in this code is create a new instance of the Excel Application so that we can create our workbook. Once we have the Application, we can create our new workbook with the Workbooks.Add function. We assign the newly created workbook to our global variable.

Next we check to make sure the directory the user entered is a valid directory. I should probably add a messagebox alert here to inform the user if they entered an invalid path, but I’m still a lazy programmer.

If the directory path is valid, we prompt the user to identify where they would like the new spreadsheet saved. We use the SaveFileDialog to allow the user to make their selection. .NET sure makes some tasks exceptionally easy. If the user cancels out of the dialog, we don’t do anything. If they did select a file location, then we call our BuildFolder function that will recursively call itself for each sub-directory.

Our BuildFolder function takes one DirectoryInfo parameter that will be created from the path in the textbox.

    ''' <summary>
    ''' Creates a worksheet with the file names in the directory and recurses sub-directories.
    ''' </summary>
    ''' <param name="Directory">The DirectoryInfo to create the file list from.</param>
    ''' <remarks></remarks>
    Protected Sub BuildFolder(ByVal Directory As DirectoryInfo)
        'Check to make sure there are files in the directory
        If Directory.GetFiles.Length > 0 Then
            'Create a new worksheet and set its name to the directory
            Dim sheet As Excel.Worksheet = wb.Worksheets.Add
            sheet.Name = CheckName(Directory.Name)
            Dim files As FileInfo() = Directory.GetFiles
            Dim f As FileInfo
            'Create an integer that we will use to move through the cells.
            Dim i As Integer = 1
            For Each f In files
                'Write the file name to the cell
                sheet.Cells(i, 1) = f.Name
                i += 1
            Next
        End If
        Dim dirs As DirectoryInfo() = Directory.GetDirectories
        Dim d As DirectoryInfo
        'Recurse all subfolders
        For Each d In dirs
            BuildFolder(d)
        Next
    End Sub

When BuildFolder is called, we first check to make sure the directory has files. If it doesn’t have any files in it, we don’t want to create a new worksheet for it.

If the directory does have files we first create a new Excel Worksheet with the Workbook.Worksheets.Add function.

We are going to name our worksheet the same name as the directory, but since Excel doesn’t allow us to have more that one worksheet with the same name; we need to check that the name hasn’t already been used on another worksheet in the workbook.

This is accomplished with the CheckName function.

    ''' <summary>
    ''' Checks to make sure the worksheet name doesn't already exist in the workbook.
    ''' </summary>
    ''' <param name="Name"></param>
    ''' <returns>A valid worksheet name.</returns>
    ''' <remarks></remarks>
    Protected Function CheckName(ByVal Name As String) As String
        Dim s As Excel.Worksheet
        Dim exists As Boolean = False
        For Each s In wb.Worksheets
            If UCase(s.Name) = UCase(Name) Then
                'Change the name to include a (1) and check again.
                Name = CheckName(Name & "(1)")
            End If
        Next
        Return Name
    End Function

This function could be much more elegant, but it’s quick and dirty and gets the job done. Since the file list that I’m trying to create doesn’t have very many directories with the same name, I’m going to leave it at this.

Basically we just loop through each of the existing worksheets in the workbook and if a name already exists, we append “(1)” to the end and recursively call the function again to make sure that’s not in use. Preferably we would append a incremental number to the end rather than just continuing to add “(1)”, but once again, I’m a lazy programmer.

We know the name coming back is valid so we assign it to the name of the new worksheet. I’m not sure if Excel allows special characters for worksheets or not, but I’m willing to bet that any character that’s valid for a file name is also valid for a worksheet name.

Next, returning to our BuildFolder function, we get the list of files in the directory and loop through adding the file name to a new row cell each time. We use our integer to move to the next line as we loop through the file set.

The Excel library is has odd behavior from a programmer point of view. Notice that we assign the value of our cell with:

sheet.Cells(i, 1) = f.Name

First oddity is that numbering starts at 1 instead of 0. I guess this makes sense as that’s the way the cells are numbered when you open the application. However for the life of me, I can’t figure out why the decided to put the Y coordinate first when every other piece of programming code in the world uses the point format (x, y). I guess some people just like to be different.

After we list all of our files in the worksheet, we get a list of any sub-directories and recursively call our BuildFolder function to create a worksheet file list for each of them.

Back in the button click function after our recursion is finished, we are ready to save our new Excel spreadsheet.

                wb.SaveAs(dialog.FileName)
                'Show the spreadsheet.
                ex.Visible = True
                wb.Activate()

We will use the file name that we got from the SaveFileDialog open up excel and add our workbook as the active file.

I hope this gave you a nice introduction to creating Excel spreadsheets programmatically with VB.NET and you even got a nice little overview of a function to recurse sub-directories. I’m sure you will want to do much more formatting and the like, but once you have your worksheet object, the Intellisense guides you pretty well accomplishing a lot of the more advanced tasks.