实现环境:Visual Studio 2010, OpenXml SDK 2.0.5022
这个代码实现了向一个已有的xlsx文件中添加一个新的Worksheet,并在这个Sheet中写入三行字符串“ObjectA1"~"ObjectC5"
Imports DocumentFormat.OpenXml.Packaging
Imports DocumentFormat.OpenXml.Spreadsheet
Public Class Form1
Private colStrings As List(Of String)
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
Dim objOpenFileDialog As OpenFileDialog = New OpenFileDialog()
Dim strFilePath As String
Dim objWorkbookPart As WorkbookPart
Dim objWorkbook As Workbook
Dim objWorkSheetPart As WorksheetPart
Dim objWorksheet As Worksheet
Dim objSeetData As SheetData
Dim objShareStringTablePart As SharedStringTablePart
Dim objShareStringTable As SharedStringTable
objOpenFileDialog.Filter = "Excel Document (*.xlsx)|*.xlsx"
objOpenFileDialog.ShowDialog()
strFilePath = objOpenFileDialog.FileName
If strFilePath.Length > 0 Then
GenectStringcollection()
Using objSpreadsheetDocument = SpreadsheetDocument.Open _
(strFilePath, True)
objWorkbookPart = objSpreadsheetDocument.WorkbookPart
objWorkbook = objWorkbookPart.Workbook
objWorkSheetPart = objWorkbookPart.AddNewPart _
(Of WorksheetPart)()
objWorksheet = New Worksheet()
objWorkSheetPart.Worksheet = objWorksheet
objSeetData = New SheetData()
objWorksheet.Append(objSeetData)
objShareStringTablePart = objWorkbookPart.SharedStringTablePart
Dim id As Integer = 0
If objShareStringTablePart Is Nothing Then
objShareStringTablePart = objWorkbookPart.AddNewPart _
(Of SharedStringTablePart)()
objShareStringTable = New SharedStringTable()
objShareStringTablePart.SharedStringTable = _
objShareStringTable
Else
objShareStringTable = objShareStringTablePart. _
SharedStringTable
id = UInt32.Parse(objShareStringTable.Count)
End If
Dim ip As Integer = 0
For i As Integer = 0 To 2
Dim objRow As New Row()
objRow.RowIndex = i + 1
For j As Integer = 0 To 4
Dim objShareStringItem As New SharedStringItem()
Dim objText = New Text()
objText.Text = colStrings(ip)
objShareStringItem.Append(objText)
objShareStringTable.Append(objShareStringItem)
Dim strref As String = String.Empty
Select Case j
Case 0
strref = "A"
Case 1
strref = "B"
Case 2
strref = "C"
Case 3
strref = "D"
Case 4
strref = "E"
End Select
strref = strref & CStr(i + 1)
Dim objCell As New Cell()
objCell.CellReference = strref
objCell.DataType = CellValues.SharedString
Dim objCellValue As New CellValue()
objCellValue.Text = CStr(ip + id)
ip = ip + 1
objCell.Append(objCellValue)
objRow.Append(objCell)
Next
objSeetData.Append(objRow)
Next
Dim newSheet As New Sheet()
newSheet.Id = objWorkbookPart.GetIdOfPart(objWorkSheetPart)
newSheet.Name = "MySheet"
newSheet.SheetId = objWorkbook.Sheets.Descendants(Of Sheet)() _
.Count() + 1
objWorkbook.Sheets.Append(newSheet)
objWorkbook.Save()
End Using
End If
End Sub
Private Sub GenectStringcollection()
colStrings = New List(Of String)()
For i As Integer = 1 To 3
Dim str As String = String.Empty
Select Case i
Case 1
str = "ObjectA"
Case 2
str = "ObjectB"
Case 3
str = "ObjectC"
End Select
For j As Integer = 1 To 5
colStrings.Add(str & CStr(j))
Next
Next
End Sub
End Class
相关资源: http://download.csdn.net/detail/tx_officedev/3942509