Reading the first column of Excel using C # in an array

Question

I am trying to read the values ​​of the first column in an array. What is the best way to do this? Below is the code that I have. Basically I am trying to get the data range for this column so that I can output the cell values ​​to the 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; 

Answer 1

Here is what I used to make it work. Once you know that a column is actually returning a range, saving it this way seems to compile and execute fine. Here is the working method in the ExcelReader class. I plan to use this for test data in 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; } 

Answer 2

First, I would determine how many lines are actually 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); 

After that, you can get the values:

 System.Array values = usedCells.Values; 

Once you have the values ​​in the array, you can skip elements that have nothing. I don’t think there is a way to get only cells with something in them, without looping them one at a time, which is very laborious in Interop.

Answer 3

Is your data listed? It seems your code is looking for an Excel list that might be missing. If not, you can just get the entire first column ( A:A ) in the range and get it. Value:

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