Import data from excel into a two-dimensional array

Question

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.

Marco · Accepted Answer · 2012-04-12 08:52:10Z

1

I think you could use

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

or

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

answered Apr 12, 2012 at 8:52

Marco

57.7k15 gold badges135 silver badges159 bronze badges

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

1 Comment

Maddy Over a year ago

Marco, object dataArray = excelCell.Value;, how can I use index on this? If I want to get specific data like [1,1] or [2,1], is there a way to index it?

pabdulin · Accepted Answer · 2012-04-12 09:44:48Z

0

The error says all, it cant convert a value of string type (which is a data in a cell obviously) to two-dimetional array. Why are you trying to cast it so? There is no need for array then you reading a single cell. But if your range contains more then one cell, then returned result will be, indeed, two-dimentional array.

Then you need something like this:

object[,] result;
object rawData = excelCell.get_Value(Missing.Value);
if(rawData.GetType().IsArray())
{
    result = rawData;
}
else
{
    result = CreateArrayFromValue(rawData);
}

return result;

of course you need to implement CreateArrayFromValue yourself.

edited Apr 12, 2012 at 9:44

answered Apr 12, 2012 at 8:53

pabdulin

35.6k9 gold badges66 silver badges102 bronze badges

3 Comments

Snuge Over a year ago

I am trying to import an initially unknown number rows and columns. I'm going to use this code multiple times in my program, but the range will be different with each new application.

Snuge Over a year ago

I've found a working solution, but it wouldn't let me post it.

Maddy Over a year ago

@Snuge, anyway you can share the code or tell me what you did? I am stuck at the same point.

Collectives™ on Stack Overflow

Import data from excel into a two-dimensional array

2 Answers 2

1 Comment

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related