VB.NET多页表中的OpenXML文档 [英] OpenXML Document in VB.NET Multiple Sheets
问题描述
我已成功创建一个包含数据视图中一张的电子表格。
现在我正在尝试从多个数据视图创建多个工作表。
我无法添加多个工作表。
< pre class ="prettyprint lang-vb"> Dim dv1 As DataView
Dim sql()As String
sql = Split(dv(x)(" ps_sql")," ;; ")
'创建工作簿
Dim Spreadsheet = SpreadsheetDocument.Create((TextBox1.Text + dv(x)(" ps_filename")。ToString()。Replace("。"," _" + datestamp +"。"))。ToString(),SpreadsheetDocumentType.Workbook)
Spreadsheet.AddWorkbookPart()
对于sh = 0到sql.Count - 1
如果sql(sh)<> ""然后
Spreadsheet.WorkbookPart.Workbook = New Workbook()
Spreadsheet.WorkbookPart.AddNewPart(Of WorksheetPart)()
Spreadsheet.WorkbookPart.WorksheetParts.Last()。Worksheet = New Worksheet()
dv1 = GetData(sql(sh),connectionString)
'创建工作表数据
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)
'创建数据行
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
结束选择
cell.CellValue =新CellValue(dsrow(col).ToString())
newRow.AppendChild(cell )
下一个
Spreadsheet.WorkbookPart.WorksheetParts.Last()。Worksheet.Last()。AppendChild(newRow)
下一个
'保存工作表
Spreadsheet.WorkbookPart.WorksheetParts.Last()。Worksheet.Save()
'创建工作表到工作簿关系
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
Spreadsheet.WorkbookPart.Workbook.Sheets.AppendChild
'保存工作簿
Spreadsheet.WorkbookPart.Workbook.Save()
结束如果
下一个
Spreadsheet.Close()
有人可以指点我正确的方向吗?
非常感谢
Ted
你好TedFire,
>>对于sh = 0对于sql.Count - 1
我可以看到你试图循环创建多个工作表。在你的循环中,我在下面找到两行代码。
Spreadsheet.WorkbookPart.Workbook = New Workbook()
Spreadsheet .WorkbookPart.Workbook.AppendChild(New Sheets())请注意,一个SpreadsheetDocument只有一个Workbook和One Sheets。因此,如果你循环创建它们的时间,它将导致错误或一些意外的结果。
我建议你将它们移出循环并在循环开始之前运行。
最好的问候,
Terry
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
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
这篇关于VB.NET多页表中的OpenXML文档的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!