在Excel(xlsx)文件中用OpenXml SDK 添加一个新的Worksheet并写入字符串
实现环境: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
征诛志异,三让两家王朝;功同开辟,一桮万古江南。