CODEWITHSUNDEEP
c#excel
adamj537
adamj537

Reputation: 853

C# Excel Read to Multidimensional Array

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.

Upvotes: 2

Views: 2299

Answers (2)

adamj537
adamj537

Reputation: 853

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;
}

Upvotes: 2

steveo40
steveo40

Reputation: 931

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.

Upvotes: 1

Related Questions