C# – Import data from excel into a two-dimensional array

cexceloffice-interop

This is my first console application project in C# and I'm attempting to import the used area of an excel sheet into a Two-Dimensional Array. This may not be the most efficient way to do this, but this is the code I have so far.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection; 

Excel.Application excelApp = new Excel.Application();
        excelApp.Visible = true;
        string workbookPath = "C:/Users/Snuge/Desktop/CourseNumbersNoWSReg.xlsx";
        Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath,
                0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "",
                true, false, 0, true, false, false);

        // This selectes the used range of the excel workbook and enters it in
        // a two dimentional array
        try
        {
            // Get a reference to the first sheet of the workbook.
            Excel.Sheets excelSheets = excelWorkbook.Worksheets;
            string currentSheet = "Sheet1";
            Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

            // write out to console for debugging
            Console.WriteLine("excelWorksheet is " + excelWorksheet);

            // Get a range of data.
            Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("A3", Missing.Value);

            // write out to console for debugging
            Console.WriteLine("excelCell is " + excelCell);

            // write out to console for debugging
            Console.WriteLine("Creating string[,] array. . . ");
            // Retrieve the data from the range.
            Object[,] dataArray;
            // write out to console for debugging
            Console.WriteLine("String[,] array created. . . ");

            dataArray = (System.Object[,])excelCell.get_Value(Missing.Value);

            // write out to console for debugging
            Console.WriteLine("Counting rows and columns. . . ");
            // Determine the dimensions of the array.
            int iRows;
            int iCols;
            iRows = dataArray.GetUpperBound(0);
            iCols = dataArray.GetUpperBound(1);

            // write out to console for debugging
            Console.WriteLine("Printing array. . . ");
            // Print the data of the array.
            for (int rowCounter = 1; rowCounter <= iRows; rowCounter++)
            {
                // write out to console for debugging
                Console.WriteLine("row " + rowCounter);
                for (int colCounter = 1; colCounter <= iCols; colCounter++)
                {

                    // Write the next value to the console.
                    Console.WriteLine("col " + colCounter + "= " + dataArray[rowCounter, colCounter].ToString() + ", ");
                }
                // Write in a new line.
                Console.WriteLine("\n");
            }                
        }

        catch (Exception theException)
        {
            // Create error message
            String errorMessage;
            errorMessage = "Error: ";
            errorMessage = String.Concat(errorMessage, theException.Message);
            errorMessage = String.Concat(errorMessage, " Line: ");
            errorMessage = String.Concat(errorMessage, theException.Source);
            // Display error message
            MessageBox.Show(errorMessage, "Error");
        }

I inserted the Console.Writeline(); for debugging purposes. The correct Excel workbook opens and the output I obtain in the command prompt is

excelWorksheet is System.__ComObject 
excelCell is System.__ComObject 
Creating string[,] array. . . 
String[,] array created. . .

Then a message box pops up with the message

"Error: Cannot convert type 'string' to object[*,*] Line: Anonymously Hosted DynamicMethods Assembly".    

This line of code gives me an error and i don't know why.

dataArray = (System.Object[,])excelCell.get_Value(Missing.Value);

Could somebody provide me with a solution for this problem?

Also, is there code to display a value instead of "System.__ComObject" in the command prompt?

I'm using Excel 2007 and I've added the Microsoft Excel 12.0 Object Library reference.

Best Answer

I think you could use

// This value "should" be boxed into two-dimensional array
object dataArray = excelCell.Value;

or

var dataArray = (object[,])excelCell.Value2;
Related Topic