Read Excel First Column using C# into Array

Question

I'm trying to read in the values of the first column into an array. What's the best way to do that? Below is the code I have so far. Basically I'm trying to get the range of data for that column so I can pull the cell values into a system array.

Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();

if (xlsApp == null)
{
    Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
    return null;
}

//xlsApp.Visible = true;

Workbook wb = xlsApp.Workbooks.Open(filename, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true);
Sheets sheets = wb.Worksheets;
Worksheet ws = (Worksheet)sheets.get_Item(1);

//***Breaks Here***
ListColumn column = ws.ListObjects[1].ListColumns[1];
Range range = column.DataBodyRange;
System.Array myvalues = (System.Array)range.Cells.Value;

ListColumn column = ws.ListObjects[1].ListColumns[1]; throws an error — Brantley Blanchard
– Brantley Blanchard, Commented Dec 14, 2012 at 21:26

Brantley Blanchard · Accepted Answer · 2012-12-14 22:05:45Z

9

Here is what I ended up using to get it to work. Once you know that Columns actually returns a range, storing it that way seems to compile and run fine. Here is the working method in my ExcelReader class. I plan to use this for test driven data on WebDriver.

    public static string[] FirstColumn(string filename)
    {
        Microsoft.Office.Interop.Excel.Application xlsApp = new Microsoft.Office.Interop.Excel.Application();

        if (xlsApp == null)
        {
            Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
            return null;
        }

        //Displays Excel so you can see what is happening
        //xlsApp.Visible = true;

        Workbook wb = xlsApp.Workbooks.Open(filename,
            0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true);
        Sheets sheets = wb.Worksheets;
        Worksheet ws = (Worksheet)sheets.get_Item(1);

        Range firstColumn = ws.UsedRange.Columns[1];
        System.Array myvalues = (System.Array)firstColumn.Cells.Value;
        string[] strArray = myvalues.OfType<object>().Select(o => o.ToString()).ToArray(); 
        return strArray;
    }

edited Dec 14, 2012 at 22:05

answered Dec 14, 2012 at 21:42

Brantley Blanchard

1,2183 gold badges15 silver badges24 bronze badges

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

1 Comment

user1647667 Over a year ago

is it possible if I combine 2 column and add comma(,) in between? (Column1, Column 2)

Sid Holland · Accepted Answer · 2012-12-14 21:43:02Z

2

First, I'd work out how many rows are actually being used:

Excel.Range allCellsInColumn = xlWorksheet.Range["A:A"];
Excel.Range usedCells = allCellsInColumn.Find("*", Missing.Value, Missing.Value, Missing.Value,
    Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, Missing.Value, Missing.Value);

Once you have that you can retrieve the values:

System.Array values = usedCells.Values;

Once you have the values in the array you can skip over the elements with nothing in them. I don't think there is a way of retrieving just the cells with something in them without looping through them one at a time, which is very time consuming in Interop.

answered Dec 14, 2012 at 21:43

Sid Holland

2,9313 gold badges30 silver badges46 bronze badges

Comments

D Stanley · Accepted Answer · 2012-12-14 21:29:28Z

0

Is your data in a list? Your code seems to be looking for an Excel List which may not be present. If not you can just get the entire first column (A:A) into a Range and get it's Value:

Range firstCol = ws.Range("A:A");
System.Array values = range.Value as System.Array;

answered Dec 14, 2012 at 21:29

D Stanley

153k12 gold badges189 silver badges257 bronze badges

1 Comment

Brantley Blanchard Over a year ago

That works but ideally I'd like to only get active cells. Any way to do that? Range range = ws.Columns[1]; That also seems to work

Collectives™ on Stack Overflow

Read Excel First Column using C# into Array

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related