locked
Writing a dictionary to an excel worksheet RRS feed

  • Question

  • I have created a dictionary in my program. Now I need to write that dictionary into excel. Each dictionary item should be a different row with each value in a different column. The number of dictionary items (and therefore, rows) can change depending on user input. The number of columns will always be the same. This is the best I've come up with so far.

    (This is my dictionary that I get from another function)

    AllMarginResults = SomeFunction()

    (Convert the dictionary to an array)

    Dim DictAsArray = AllMarginResults.Values.ToArray()

    (Put array in Excel)

    Dim ArrayToExcel As Excel.Range = ("A1")

    ArrayToExcel.Resize(UBound(DictAsArray, AllMarginResults.Count), UBound(DictAsArray, 24)).Value = DictAsArray

    When I run this, I get an error that says: "An unhandled exception of type 'System.InvalidCastException' occurred in STA.exe" and "Additional information: Unable to cast object of type 'System.String' to type 'Microsoft.Office.Interop.Excel.Range'."

    Can anyone help me figure out how to fix this? Or maybe I'm doing it in a silly way. The end goal is to have a dictionary with all its values displayed in excel. If someone has a better idea, I'm open to that as well.

    Thank you!

    Monday, August 28, 2017 3:50 PM

Answers

  • Okay, lets take a different route but only works for .xlsx, not .xls.

    There is a free library called SpreadSheetLight which I wrote several code samples for found here along with how to install via Nuget right inside of Visual Studio.

    Simple example where I use a class Person in a Dictionary

    Public Class Person
        Public Property Id As Integer
        Public Property FirstName As String
        Public Property LastName As String
    End Class

    Code to write data

    Public Sub DictExample(
        ByVal pSender As Dictionary(Of String, Person),
        ByVal pFileName As String,
        ByVal pSheetName As String)
    
        Using sl As New SLDocument(pFileName, pSheetName)
            Dim stats As SLWorksheetStatistics = sl.GetWorksheetStatistics
            Dim p As Person
    
            Dim rowIndex As Integer = stats.EndRowIndex + 1
    
            For Each kvp In pSender
                p = kvp.Value
                sl.SetCellValueNumeric(rowIndex, 1, p.Id.ToString)
                sl.SetCellValue(rowIndex, 2, p.FirstName)
                sl.SetCellValue(rowIndex, 3, p.LastName)
                rowIndex += 1
            Next
    
            sl.Save()
        End Using
    
    End Sub

    Do it (say from a button click event)

    Dim ops As New Operations
    
    Dim dic As New Dictionary(Of String, Person)
    dic.Add("Karen", New Person With {.Id = 10, .FirstName = "Karen", .LastName = "Payne"})
    dic.Add("Mary", New Person With {.Id = 20, .FirstName = "Mary", .LastName = "Jones"})
    ops.DictExample(dic, IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "DictDemo.xlsx"), "Sheet1")
    Excel need not be installed for the above to work :-) Now if I got something a tad off it should be easy to adapt to what you are after.

    Results from running the code three times in a row


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by kabgei Monday, August 28, 2017 7:36 PM
    Monday, August 28, 2017 7:29 PM

All replies

  • Hi

    Dictionary(Of ????, ?????)

    please fill in the missing items.

    Option Explicit On
    Option Strict On
    Public Class Form1
        Dim AllMarginResults As New Dictionary(Of String, String)
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            AllMarginResults = SomeFunction()
    
            ' this gets the VALUES from the dictionary
            Dim DictKeysAsArray = AllMarginResults.Keys.ToArray()
    
            ' this gets the KEYS from the dictionary
            Dim DictValuesAsArray = AllMarginResults.Values.ToArray()
    
            ' this gets both the KEYS and VALUES
            Dim Darray() = AllMarginResults.ToArray
    
        End Sub
        Function SomeFunction() As Dictionary(Of String, String)
            Dim dic As New Dictionary(Of String, String)
            With dic
                .Add("One", "1")
                .Add("Two", "2")
                .Add("Three", "3")
                .Add("Four", "4")
                .Add("Five", "5")
                .Add("Six", "6")
            End With
            Return dic
        End Function
    End Class
    



    Regards Les, Livingston, Scotland






    • Edited by leshay Monday, August 28, 2017 4:26 PM
    Monday, August 28, 2017 3:57 PM
  • It would be dictionary(of string, DictionaryProperties) where DictionaryProperties is a list of variables defined as string or double. Is that what you meant?
    Monday, August 28, 2017 5:51 PM
  • Hello,

    See my code sample. Look at code module named OpenWorkSheet.vb, sub named OpenExcelWriteData. There is more code than you might except as I handle the proper disposal of all objects which most code samples fail to do.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, August 28, 2017 6:21 PM
  • I think I should clarify a little what I'm trying to do. I apologize for any confusion (by me or anyone else) as I am still kind of a novice.

    This is a snippet of what my basic code looks like. There are 23 values for each dictionary key. I've only listed a few for simplicity.

    ----------------------------------------------------------------------------

    Module Calculate

        Private STResults as New Dictionary(Of String, AllResults)  **AllResults is a class of properties**

        Function GetData

            (many calculations are made that aren't really important for the question, but that is where the values to go in the dictionary come from)

            Dim Result As New AllResults

            Result.PartNum = PartNum

            Result.Thickness = Thickness

            Result.Height = Height

            Result.Width = Width

            Result.Load = Load

            STResults.Add(Result.PartNum, Result)

            Return STResults

        End Function

        Sub EnterDictionaryInExcel

            (There is some code that opens the excel sheet and writes headers to it)

            AllMarginResults = GetData()

            (So here is where somehow I want to load my dictionary into excel)

        End Sub

    For example one dictionary item will have:

    PartNum = 11-223

    Thickness = 0.2

    Height = 11

    Width = 5

    Load = 100

    In excel, PartNum should go in column A, thickness in B, etc., Load in E. Then it should go to the next row and enter another dictionary item. There could be as many as 30 items or fewer.

    I'm hoping this didn't make things more confusing. The only thing I'm having trouble with is entering each dictionary item in a separate row in Excel. I'm wondering if I should rename my key to a numerical index and then I can tell it to put it in a row that's equivalent to the key.

    Thanks for your help!

             

             


    • Edited by kabgei Monday, August 28, 2017 6:59 PM
    Monday, August 28, 2017 6:58 PM
  • Okay, lets take a different route but only works for .xlsx, not .xls.

    There is a free library called SpreadSheetLight which I wrote several code samples for found here along with how to install via Nuget right inside of Visual Studio.

    Simple example where I use a class Person in a Dictionary

    Public Class Person
        Public Property Id As Integer
        Public Property FirstName As String
        Public Property LastName As String
    End Class

    Code to write data

    Public Sub DictExample(
        ByVal pSender As Dictionary(Of String, Person),
        ByVal pFileName As String,
        ByVal pSheetName As String)
    
        Using sl As New SLDocument(pFileName, pSheetName)
            Dim stats As SLWorksheetStatistics = sl.GetWorksheetStatistics
            Dim p As Person
    
            Dim rowIndex As Integer = stats.EndRowIndex + 1
    
            For Each kvp In pSender
                p = kvp.Value
                sl.SetCellValueNumeric(rowIndex, 1, p.Id.ToString)
                sl.SetCellValue(rowIndex, 2, p.FirstName)
                sl.SetCellValue(rowIndex, 3, p.LastName)
                rowIndex += 1
            Next
    
            sl.Save()
        End Using
    
    End Sub

    Do it (say from a button click event)

    Dim ops As New Operations
    
    Dim dic As New Dictionary(Of String, Person)
    dic.Add("Karen", New Person With {.Id = 10, .FirstName = "Karen", .LastName = "Payne"})
    dic.Add("Mary", New Person With {.Id = 20, .FirstName = "Mary", .LastName = "Jones"})
    ops.DictExample(dic, IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "DictDemo.xlsx"), "Sheet1")
    Excel need not be installed for the above to work :-) Now if I got something a tad off it should be easy to adapt to what you are after.

    Results from running the code three times in a row


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by kabgei Monday, August 28, 2017 7:36 PM
    Monday, August 28, 2017 7:29 PM