C# 如何遍历 Excel 工作表，仅从特定列中提取数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16156951/
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
How to iterate through Excel Worksheets only extracting data from specific columns
提问by RobHurd
How do you iterate through an excel workbook with multiple worksheets only extracting data from say columns "C", "E" & "F"?
您如何遍历包含多个工作表的 Excel 工作簿，仅从“C”、“E”和“F”列中提取数据？
Here is the code I have thus far:
这是我迄今为止的代码：
public static string ExtractData(string filePath)
{
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workBook = excelApp.Workbooks.Open(filePath);
string data = string.Empty;
int i = 0;
foreach (Excel.Worksheet sheet in workBook.Worksheets)
{
data += "******* Sheet " + i++.ToString() + " ********\n";
//foreach (Excel.Range row in sheet.UsedRange.Rows)
//{
// data += row.Range["C"].Value.ToString();
//}
foreach (Excel.Range row in sheet.UsedRange.Rows)
{
foreach (Excel.Range cell in row.Columns)
{
data += cell.Value + " ";
}
data += "\n";
}
}
excelApp.Quit();
return data;
}
Thank you very much for your time, any help is appreciated.
非常感谢您的时间，任何帮助表示赞赏。
回答by Mitja Bezen?ek
You could use something like this to get Column C for example:
例如，您可以使用类似的方法来获取 C 列：
var numberOfRows = sheet.UsedRange.Columns[3, Type.Missing].Rows.Count;
var values = sheet.Range["C1:C" + numberOfRows].Value2;
numberOfRows holds the number of rows in the worksheet (I think it doesn't skip the blank rows at the top tho, not sure). After that you select a range from C1 to CN and get the Value2 which contains the values. Mind that the values array is actually a two dimensional array. You can now easily do a for loop to get the items:
numberOfRows 保存工作表中的行数（我认为它不会跳过顶部的空白行，不确定）。之后，您选择从 C1 到 CN 的范围并获得包含这些值的 Value2。注意 values 数组实际上是一个二维数组。您现在可以轻松执行 for 循环来获取项目：
for (int i = 1; i <= values.Length; i++){
sb.Append(values[i, 1] + " ");
}
This could be optimized in case columns are next to each other and such, but the above code should get you started.
这可以在列彼此相邻的情况下进行优化，但上面的代码应该可以帮助您入门。
回答by Zack Kay
Editing your method, here's something should do what you're looking for:
编辑你的方法，这里有一些东西应该做你正在寻找的东西：
public static string ExtractData(string filePath)
{
Excel.Application excelApp = new Excel.Application();
Excel.Workbook workBook = excelApp.Workbooks.Open(filePath);
int[] Cols = { 3, 5, 6 }; //Columns to loop
//C, E, F
string data = string.Empty;
int i = 0;
foreach (Excel.Worksheet sheet in workBook.Worksheets)
{
data += "******* Sheet " + i++.ToString() + " ********\n";
foreach (Excel.Range row in sheet.UsedRange.Rows)
{
foreach (int c in Cols) //changed here to loop through columns
{
data += sheet.Cells[row.Row, c].Value2.ToString() + " ";
}
data += "\n";
}
}
excelApp.Quit();
return data;
}
I've created a int array to indicate which columns you'd like to read from, and then on each row we just loop through the array.
我创建了一个 int 数组来指示您想要读取的列，然后在每一行上我们只是循环遍历数组。
HTH, Z
HTH, Z
