C# Excel Read to Multidimensional Array

Question

I am writing a C# class to automate reading and writing data in an Excel spreadsheet, where that data can be of type double, int, or string. I've gotten the following method for writing data to a spreadsheet to work (using Microsoft.Office.Interop.Excel):

    public void WriteMultiCellValues<T>(int row, int column, T[,] values)
    {
        // Create an Excel Range object encompassing the workbook's starting cell.
        Range range = (Range)_worksheet.Cells[row, column];

        // Get the width and height of the "values" multidimensional array.
        int rows = values.GetLength(0);
        int columns = values.GetLength(1);

        // Resize the range to be the same size as the values array.
        range = range.get_Resize(rows, columns);

        // Write the range data to Excel.
        range.set_Value(value: values);
    }

I would like to make an analogue method which reads from a spreadsheet to a multidimensional array. But this does not work:

    public T[,] ReadMultiCellValues<T>(int row, int column, int numRows, int numColumns)
    {
        // Create an Excel Range object encompassing the workbook's starting cell.
        Range range = (Range)_worksheet.Cells[row, column];

        // Resize the range to the desired dimensions.
        range = range.get_Resize(numRows, numColumns);

        // Read the cells' values from the spreadsheet.
        return (T[,])range.Value2;
    }

When run, the return statement fails because the object "range.Value2" can't be cast to an array. Does anyone know how to convert the object to a multidimensional array? Also, I'm open to solutions which don't use arrays if there is something more convenient, but it must work for data types of double, int, and string, and I would like the read and write methods to be analogous.

stackoverflow.com/questions/910400/…

Tim Williams
– Tim Williams

2018-05-03 04:27:05 +00:00
Commented May 3, 2018 at 4:27 — Tim Williams
– Tim Williams, Commented May 3, 2018 at 4:27

adamj537 · Accepted Answer · 2018-12-31 17:18:54Z

2

The answer from @steveo40 is correct. Here's the new, working, code:

public object[,] ReadMultiCellValues(int row, int column, int numRows, int numColumns)
{
    // Check for invalid arguments.
    ValidateCell(row, column);

    // Create an Excel Range object emcompassing the workbook's starting cell.
    Range range = (Range)_worksheet.Cells[row, column];

    // Resize the range to the desired dimensions.
    range = range.get_Resize(numRows, numColumns);

    // Read the cells' values from the spreadsheet.
    return (object[,])range.Value2;
}

answered Dec 31, 2018 at 17:18

adamj537

8838 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

steveo40 · Accepted Answer · 2018-05-03 08:32:15Z

1

This is because you are trying to cast values from an array that could contain strings, doubles, dates, etc to a single type. Instead, change the return type of your function from T[,] to a non-generic object [,] (and all the other bits in the function too, obviously). Then it should work.

answered May 3, 2018 at 8:32

steveo40

9315 silver badges11 bronze badges

1 Comment

adamj537 Over a year ago

This worked! Not sure how to post the working code, so I'll do so in a separate answer.

Collectives™ on Stack Overflow

C# Excel Read to Multidimensional Array

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related