none
OpenXML Document in VB.NET Multiple Sheets

    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)
    		Spreadsheet.AddWorkbookPart()
    
    		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)
    					headerRow.AppendChild(cell)
    				Next
    				Spreadsheet.WorkbookPart.WorksheetParts.Last().Worksheet.Last().AppendChild(headerRow)
    
    				' 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()
    						'MsgBox(col.columnname)
    						'MsgBox(col.datatype.ToString)
    						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())
    						newRow.AppendChild(cell)
    					Next
    					Spreadsheet.WorkbookPart.WorksheetParts.Last().Worksheet.Last().AppendChild(newRow)
    				Next
    
    				' save worksheet
    				Spreadsheet.WorkbookPart.WorksheetParts.Last().Worksheet.Save()
    
    				' 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)
    				Spreadsheet.WorkbookPart.Workbook.Sheets.AppendChild(s)
    
    				' save workbook
    				Spreadsheet.WorkbookPart.Workbook.Save()
    
    			End If
    		Next
    		Spreadsheet.Close()

    Could someone kindly point me in the right direction please?

    Many Thanks

    Ted


    • Modifié TedFire dimanche 3 juin 2018 15:04
    dimanche 3 juin 2018 14:59

Toutes les réponses

  • 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,

    Terry


    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 MSDNFSF@microsoft.com.


    lundi 4 juin 2018 06:11
    Modérateur