使用C#将Excel第一列读入数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13886330/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Read Excel First Column using C# into Array
提问by Brantley Blanchard
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;
采纳答案by Brantley Blanchard
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.
这是我最终用来让它工作的东西。一旦您知道 Columns 实际上返回了一个范围，那么以这种方式存储它似乎可以正常编译和运行。这是我的 ExcelReader 类中的工作方法。我计划将其用于 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;
}
回答by D Stanley
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:
您的数据是否在列表中？您的代码似乎正在寻找可能不存在的 Excel 列表。如果没有，您可以将整个第一列 ( A:A) 放入范围并获取其值：
Range firstCol = ws.Range("A:A");
System.Array values = range.Value as System.Array;
回答by Sid Holland
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.
一旦您拥有数组中的值，您就可以跳过其中没有任何内容的元素。我不认为有一种方法可以只检索包含某些内容的单元格，而无需一次一个地循环遍历它们，这在 Interop 中非常耗时。
