vb.net writing Array of Arrays to excel Range worksheet

Question

I have a windows form that asks user for some inputs. Creates a 2 d array. Then opens a new excel workbook and writes the array to cells in that workbook. Everything seem to be working as I would expect, except for the values being placed in the cells is "System.Int64[][]" instead of the values from the array.

Below is the code starting where i create my 2d array (a function call). Any help is appreciated. Thanks

'Get Final Array
    Dim fArray As Long()()
    fArray = getFArray(numVars, numCombos, stepNumsArray, jArray)
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Dim xlApp As New excel.Application
    Try
        Dim xlBook As excel.Workbook = xlApp.Workbooks.Add
        Dim xlWS1 As excel.Worksheet = CType(xlBook.Worksheets(1), excel.Worksheet)
        xlApp.Visible = True
        Dim R As excel.Range
        R = xlWS1.Cells.Range("A2:C13")
        R.Value = fArray.ToString

        xlWS1.SaveAs(Environment.GetFolderPath(Environment.SpecialFolder.Desktop) & "\" & "Test.xlsx")
    Catch ex As Exception
        MsgBox("Export to Excel Error: " & ex.Message)
    Finally
        xlApp.Workbooks.Close()
        xlApp.Quit()
        xlApp = Nothing

    End Try

The default implementation of `Object.ToString()` is to output the `Type.FullName` of the object. — Sam Axe, Mar 27 '17 at 21:05
I have no clue about the Excel API, but you could try assigning the array directly to the value: `R.Value = fArray`. Failing that you may need to loop over the cells in your range and set the individually. — Sam Axe, Mar 27 '17 at 21:07
http://stackoverflow.com/questions/536636/write-array-to-excel-range — Sam Axe, Mar 27 '17 at 21:09

score 0 · Answer 1 · answered Mar 27 '17 at 21:44

The main issues are the use of jagged array instead of rectangular array, using .ToString on array, and using Long instead of Double (Excel uses Double for all numeric types). Once those are fixed:

Dim fArray(11, 2) As Double
' ...
xlWS1.Range("A2:C13").Value2 = fArray

vb.net writing Array of Arrays to excel Range worksheet

1 Answers1