OpenXML Document in VB.NET Multiple Sheets RRS feed

  • Question

  • Hi,

    I'm successfully creating a spreadsheet with one sheet from a dataview.

    Now I'm trying to create multiple sheets from multiple dataviews.

    I can't quite get it to add multiple sheets.

    		Dim dv1 As DataView
    		Dim sql() As String
    		sql = Split(dv(x)("ps_sql"), ";")
    		' create the workbook
    		Dim Spreadsheet = SpreadsheetDocument.Create((TextBox1.Text + dv(x)("ps_filename").ToString().Replace(".", "_" + datestamp + ".")).ToString(), SpreadsheetDocumentType.Workbook)
    		For sh = 0 To sql.Count - 1
    			If sql(sh) <> "" Then
    				Spreadsheet.WorkbookPart.Workbook = New Workbook()
    				Spreadsheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
    				Spreadsheet.WorkbookPart.WorksheetParts.Last().Worksheet = New Worksheet()
    				dv1 = GetData(sql(sh), connectionString)
    				' create sheet data
    				Spreadsheet.WorkbookPart.WorksheetParts.Last().Worksheet.AppendChild(New SheetData())
    				' create header row
    				Dim headerRow As New Row()
    				For Each column As DataColumn In dv1.Table.Columns
    					Dim cell = New Cell()
    					cell.DataType = CellValues.String
    					cell.CellValue = New CellValue(column.ColumnName)
    				' create data rows
    				For Each dsrow As DataRow In dv1.Table.Rows
    					Dim newRow = New Row()
    					For Each col In dsrow.Table.Columns
    						Dim cell = New Cell()
    						Select Case col.DataType.ToString
    							Case "System.Decimal"
    								cell.DataType = CellValues.Number
    							Case "System.Int32"
    								cell.DataType = CellValues.Number
    							Case "System.String"
    								cell.DataType = CellValues.String
    							Case "System.DateTime"
    								cell.DataType = CellValues.Date
    						End Select
    						cell.CellValue = New CellValue(dsrow(col).ToString())
    				' save worksheet
    				' create the worksheet to workbook relation
    				Spreadsheet.WorkbookPart.Workbook.AppendChild(New Sheets())
    				Dim s = New Sheet()
    				s.Id = Spreadsheet.WorkbookPart.GetIdOfPart(Spreadsheet.WorkbookPart.WorksheetParts.Last())
    				s.SheetId = CType(sh, UInt32Value)
    				s.Name = "test" + sh.ToString
    				'Spreadsheet.WorkbookPart.Workbook.GetFirstChild(Of Sheets)().AppendChild(s)
    				' save workbook
    			End If

    Could someone kindly point me in the right direction please?

    Many Thanks


    • Edited by TedFire Sunday, June 3, 2018 3:04 PM
    Sunday, June 3, 2018 2:59 PM

All replies

  • Hello TedFire,

    >>For sh = 0 To sql.Count - 1

    I can see that you had tried to loop to create multiple sheets. In your loop, I find below two line code.

    Spreadsheet.WorkbookPart.Workbook = New Workbook()
    Spreadsheet.WorkbookPart.Workbook.AppendChild(New Sheets())

    Please notes that one SpreadsheetDocument only has one Workbook and One Sheets. So if you loop to create them times, it will cause error or some unexpected result. 

    I would suggest you move them out of the loop and run before the loop starts.

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Monday, June 4, 2018 6:11 AM