none
Writing strings to .xlsx file (VB.Net) RRS feed

  • Question

  • Hi all!

    I am not very good with the Opem XML API at all!  In fact, I am still learning .net too!  I need some help with writing some strings to an excel spreadsheet.  I have found sources that have allowed me to piece together code to create a new .xlsx file and create a new sheet (works great).

    But I now need to open it and write some data to it.  I have done a lot of searching and cannot seem to find any clear-cut answers on how to do it.  Everyone seems to have their own method and each one is more difficult (i.e. confusing) than the last! 

    So... here is what I need to do.

    I have a collection of objects (number of objects will vary).  

    Each object contains a series of strings... String1, String2, String3, String4, String5 (always 5 strings)

    I need all the strings in each object to be writen to the same row, but each string will be in a seperate column.

     

    So ObjectA strings might be written as follows:

    Row1, ColA: String1

    Row1, ColB: String2

    Row1, ColC: String3

    Row1, ColD: String4

    Row1, ColE: String5

     

     

    ObjectB strings might be written as follows:

    Row2, ColA: String1

    Row2, ColB: String2

    Row2, ColC: String3

    Row2, ColD: String4

    Row2, ColE: String5

     

    etc...

     

    I can open the file using something like:

     

    Using spreadSheet As SpreadsheetDocument = SpreadsheetDocument.Open(xlsxFilename, True)
    

     


    But I cannot figure out how to easily write to the file and then save it.

    Can anyone provide me with some very simple examples of how to do this?

    Thanks!

     


    • Edited by Rob Outman Friday, December 16, 2011 11:27 PM
    Friday, December 16, 2011 11:27 PM

Answers

  • Hi Dull_Blades,

    Thanks for posting in the MSDN Forum.

    I hope this snippet can help you. This snippet only work with the workbook which hasn't had string values in it.

    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.WorksheetParts.Last()
                    objWorksheet = objWorkSheetPart.Worksheet
                    objSeetData = objWorksheet.Descendants(Of SheetData)() _
    .FirstOrDefault()
                    objShareStringTablePart = objWorkbookPart.SharedStringTablePart
                    If objShareStringTablePart Is Nothing Then
                        objShareStringTablePart = objWorkbookPart.AddNewPart _
    (Of SharedStringTablePart)()
                        objShareStringTable = New SharedStringTable()
                        objShareStringTablePart.SharedStringTable = _
    objShareStringTable
                    Else
                        objShareStringTable = objShareStringTablePart. _
    SharedStringTable
                    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)
                            ip = ip + 1
                            objCell.Append(objCellValue)
                            objRow.Append(objCell)
                        Next
                        objSeetData.Append(objRow)
                    Next
                    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
    

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, December 19, 2011 11:36 AM
    Moderator

All replies

  • Hi Dull_Blades,

    Thanks for posting in the MSDN Forum.

    I hope this snippet can help you. This snippet only work with the workbook which hasn't had string values in it.

    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.WorksheetParts.Last()
                    objWorksheet = objWorkSheetPart.Worksheet
                    objSeetData = objWorksheet.Descendants(Of SheetData)() _
    .FirstOrDefault()
                    objShareStringTablePart = objWorkbookPart.SharedStringTablePart
                    If objShareStringTablePart Is Nothing Then
                        objShareStringTablePart = objWorkbookPart.AddNewPart _
    (Of SharedStringTablePart)()
                        objShareStringTable = New SharedStringTable()
                        objShareStringTablePart.SharedStringTable = _
    objShareStringTable
                    Else
                        objShareStringTable = objShareStringTablePart. _
    SharedStringTable
                    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)
                            ip = ip + 1
                            objCell.Append(objCellValue)
                            objRow.Append(objCell)
                        Next
                        objSeetData.Append(objRow)
                    Next
                    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
    

    Have a good day,

    Tom


    Tom Xu [MSFT]
    MSDN Community Support | Feedback to us

    Monday, December 19, 2011 11:36 AM
    Moderator
  • Thanks Tom!

     

    This is perfect! I had to do some manipulation to get it to work with my upatates, but this really helps me understand how to write to a spreadsheet now!

     

    Thanks again!

    Monday, December 19, 2011 10:24 PM
  • A hard read without a single comment...

    Friday, September 20, 2013 5:37 PM