How to add multiple worksheets to an Excel WorkBook using VB.NET?

Article
2010-05-10

Question

_{Monday, May 10, 2010 7:44 PM}

I wrote code that creates an Excel workbook wit a single worksheet (see below). How can I update this to add multiple worksheets to the workbook?

  ''' <summary>
  ''' Create a new XLS Excel spreadsheet based on a passed CSV (comma separated values) file.
  ''' </summary>
  ''' <param name="CSV_Path"></param>
  ''' <param name="XLS_Path"></param>
  ''' <returns>True = OK, Fale = failed</returns>
  ''' <remarks>Convert "insert blank line" to blank.</remarks>
  Public Function CreateXLS_FromCSV(ByVal CSV_Path As MemoryStream, ByVal XLS_Path As String) As Boolean

    Try
      ' Remove the XLX file if it already exists.
      File.Delete(XLS_Path)

      Dim myApplication As Excel.Application
      Dim myWorkBook As Excel.Workbook
      Dim myWorkSheet As Excel.Worksheet
      Dim misValue As Object = System.Reflection.Missing.Value

      ' Create the spreadsheet
      myApplication = New Excel.Application
      myWorkBook = myApplication.Workbooks.Add(misValue)
      myWorkSheet = myWorkBook.Sheets("Sheet1")

      ' Copy the data from the CSV file to the spreadsheet.
      Dim typeCode As TypeCode = Type.GetTypeCode(CSV_Path.GetType())

      ' Reset position to beginning
      CSV_Path.Position = 0
      'CSV_Path.Seek(0, SeekOrigin.Begin)  'reset file pointer. Alternative method.

      Using myTextFieldParser As New Microsoft.VisualBasic.FileIO.TextFieldParser(CSV_Path)
        myTextFieldParser.TextFieldType = FileIO.FieldType.Delimited
        myTextFieldParser.SetDelimiters(",")
        myTextFieldParser.HasFieldsEnclosedInQuotes = False 'treat double quotes like other characters
        Dim currentRow As String()

        Dim RowNumber As Integer = 0
        Dim ColNumber As Integer = 0
        Dim Rowlength As Integer = 0
        Dim currentField As String

        While Not myTextFieldParser.EndOfData
          Try
            RowNumber += 1
            currentRow = myTextFieldParser.ReadFields()

            ColNumber = 0
            For Each currentField In currentRow
              If currentField = "insert blank line" Then
                currentField = " "
              End If
              ColNumber += 1
              myWorkSheet.Cells(RowNumber, ColNumber) = currentField
            Next

          Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
            Console.WriteLine("Line " & ex.Message & "is not valid and will be skipped.")
          End Try
        End While
      End Using

      ' Save and close the new populated spreadsheet
      myWorkBook.SaveAs(XLS_Path, Excel.XlFileFormat.xlExcel8)
      myWorkBook.Close()
      myApplication.Quit()
    
    Catch ex As Exception
      'Console.WriteLine(ex.Message)
      Return False
    End Try

    Return True
  End Function

All replies (6)

_{Monday, May 10, 2010 8:36 PM ✅Answered | 2 votes}

When you create a new workbook, 3 sheets are created by default. (Just like if you go into excel, you notice right away there are 3 sheets at the bottom, not just 1).

So you already have this code in your project:

myWorkSheet = myWorkBook.Sheets("Sheet1")

Setting myWorkSheet to myWorkBook.Sheets("Sheet2") will make myWorkSheet reference sheet2 instead of sheet1.

Or you can create a second variable if you still need myWorkSheet to reference the first sheet.

Dim mySecondSheet = myWorkBook.Sheets("Sheet2")

Matt Kleinwaks - MSMVP MSDN Forums Moderator - www.zerosandtheone.com

_{Tuesday, May 11, 2010 7:01 PM ✅Answered | 1 vote}

That was what I needed to know -- 3 sheets are created by default, and you have to add more sheets. I added the 6 sheets that I needed and removed the 3 default sheets. Here is the code:

 ''' <summary>
 ''' Create a new XLS Excel spreadsheet based on a passed CSV (comma separated values) file.
 ''' Add multiple sheets to the Excel workbook.
 ''' </summary>
 ''' <param name="CSV_Path_List"></param>
 ''' <param name="XLS_Path"></param>
 ''' <returns>True = OK, Fale = failed</returns>
 ''' <remarks>Convert "insert blank line" to blank.</remarks>
 Public Function CreateXLS_FromCSV_MultipleSheets(ByVal CSV_Path_List As List(Of XLSheet), ByVal XLS_Path As String) As Boolean

  Try
   ' Remove the XLS file if it already exists.
   File.Delete(XLS_Path)

   Dim myApplication As Excel.Application
   Dim myWorkBook As Excel.Workbook
   Dim myWorkSheet As Excel.Worksheet
   Dim misValue As Object = System.Reflection.Missing.Value

   ' Create the spreadsheet
   myApplication = New Excel.Application
   myWorkBook = myApplication.Workbooks.Add(misValue)

   ' Add 6 sheets to the WorkBook.
   myWorkBook.Sheets.Add(, , 6)

   ' Remove the default sheets. 
   ' Must be done after adding more because there must be at least one sheet.
   myWorkSheet = myWorkBook.Sheets("Sheet1")
   myWorkSheet.Delete()
   myWorkSheet = myWorkBook.Sheets("Sheet2")
   myWorkSheet.Delete()
   myWorkSheet = myWorkBook.Sheets("Sheet3")
   myWorkSheet.Delete()

   Try
    '  Add sheets to the WorkBook.
    Dim SheetIndex As Integer = 1
    For Each myCSV_Path As XLSheet In CSV_Path_List

     'Add another worksheet
     'MessageBox.Show("SheetIndex: " + SheetIndex.ToString)
     'myWorkBook.Sheets.Add()
     myWorkSheet = myWorkBook.Sheets(SheetIndex)
     myWorkSheet.Name = myCSV_Path.SheetName
     SheetIndex += 1

     ' Copy the data from the CSV file to the spreadsheet.
     Dim typeCode As TypeCode = Type.GetTypeCode(CSV_Path_List.GetType())

     ' Reset position to beginning
     myCSV_Path.SheetMemoryStream.Position = 0

     Using myTextFieldParser As New Microsoft.VisualBasic.FileIO.TextFieldParser(myCSV_Path.SheetMemoryStream)
      myTextFieldParser.TextFieldType = FileIO.FieldType.Delimited
      myTextFieldParser.SetDelimiters(",")
      'myTextFieldParser.HasFieldsEnclosedInQuotes = False 'treat double quotes like other characters
      myTextFieldParser.HasFieldsEnclosedInQuotes = True 'treat double quotes like other characters
      Dim currentRow As String()

      Dim RowNumber As Integer = 0
      Dim ColNumber As Integer = 0
      Dim Rowlength As Integer = 0
      Dim currentField As String

      While Not myTextFieldParser.EndOfData
       Try
        RowNumber += 1
        currentRow = myTextFieldParser.ReadFields()

        ColNumber = 0
        For Each currentField In currentRow
         If currentField = "insert blank line" Then
          currentField = " "
         End If
         ColNumber += 1
         myWorkSheet.Cells(RowNumber, ColNumber) = currentField
        Next

       Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
        Console.WriteLine("Line " & ex.Message & "is not valid and will be skipped.")
       End Try
      End While
     End Using
    Next
   Catch ex As Exception
    MessageBox.Show("Ignore Sheet: ")
    ' ignore this sheet
   End Try


   '  Save and close the new populated spreadsheet
   myWorkBook.SaveAs(XLS_Path, Excel.XlFileFormat.xlExcel8)
   myWorkBook.Close()
   myApplication.Quit()

  Catch ex As Exception
   MessageBox.Show(ex.Message)
   'Console.WriteLine(ex.Message)
   Return False
  End Try

  Return True
 End Function

Imports System.IO

Public Class XLSheet

 Private _SheetName As String
 Public Property SheetName() As String
  Get
   Return _SheetName
  End Get
  Set(ByVal value As String)
   _SheetName = value
  End Set
 End Property


 Private _SheetMemoryStream As MemoryStream
 Public Property SheetMemoryStream() As MemoryStream
  Get
   Return _SheetMemoryStream
  End Get
  Set(ByVal value As MemoryStream)
   _SheetMemoryStream = value
  End Set
 End Property

End Class

_{Wednesday, May 12, 2010 11:05 AM}

Note that the number of sheets in a new workbook is user-selectable from 1 to 255 (in Tools->Options...->General).

_{Wednesday, May 12, 2010 2:19 PM}

Andrew, are you talking about a person manulally creating a spreadsheet? That won't apply in this case, because a .NET program creates the spreadsheet. Is there a way to specify the number of sheets when creating a workbook via a .NET program?

_{Wednesday, May 12, 2010 2:34 PM | 1 vote}

My guess would be creating a speadsheet via code will use whatever the default number of sheets is set in the actual excel program, since you technically are just automating that excel installation. I have a feeling a very very small percent of people who have excel installed adjust from the 3 sheet default.Matt Kleinwaks - MSMVP MSDN Forums Moderator - www.zerosandtheone.com

_{Wednesday, June 13, 2012 2:50 PM}

how to rename the sheet name instead of sheet1 by default?

Share via

How to add multiple worksheets to an Excel WorkBook using VB.NET?

Question

All replies (6)

Additional resources