I have a xlsx file like (it is the first sheet)
我有一个xlsx文件(这是第一张)
Postcode Sales_Rep_ID Sales_Rep_Name Year Value
2121 456 Jane 2011 $84,219
2092 789 Ashish 2012 $28,322
2128 456 Janet 2013 $81,879
2073 123 John 2011 $44,491
How do I get a particular value of the column named Year whose say, Sales_Rep_Name value is Janet? I'm stuck at the below code and can't figure how to do it?
如何获取名为Year的列的特定值,其中Sales_Rep_Name值为Janet?我被困在下面的代码,无法想象如何做到这一点?
FileStream stream = File.Open(@"C:\Users\Bumba\Downloads\Sample-Sales-Data.xlsx", FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration() {
ConfigureDataTable = (_) => new ExcelDataTableConfiguration() {
UseHeaderRow = true
}
});
foreach (DataTable element in result.Tables) {
Console.WriteLine(element.Columns["Year"].ToString());
//what to do??????
}
excelReader.Close();
Console.ReadLine();
Someone help....
1 个解决方案
#1
3
The easiest option is just to enumerate table (sheet) rows and compare the value in Sales_Rep_Name
column against required value ("Janet"):
最简单的选择是枚举表(表)行并将Sales_Rep_Name列中的值与所需值(“Janet”)进行比较:
foreach (DataTable table in result.Tables)
{
foreach (DataRow row in table.Rows)
{
var salesRepName = (string)row["Sales_Rep_Name"];
if (String.Equals(salesRepName, "Janet"))
{
var year = (int)(double)row["Year"];
Console.WriteLine($"Janet's year is {year}");
}
}
}
You could also use DataTable.Select(string filterExpression)
method to match required rows:
您还可以使用DataTable.Select(string filterExpression)方法来匹配所需的行:
foreach (DataTable table in result.Tables)
{
foreach (DataRow row in table.Select("Sales_Rep_Name = 'Janet'"))
{
var year = (int)(double)row["Year"];
Console.WriteLine($"Janet's year is {year}");
}
}
The second choice will work faster (when the data is big), however the first one is more flexible and allows to use more complex filters.
第二种选择将更快(当数据很大时),但第一种选择更灵活,允许使用更复杂的过滤器。
#1
3
The easiest option is just to enumerate table (sheet) rows and compare the value in Sales_Rep_Name
column against required value ("Janet"):
最简单的选择是枚举表(表)行并将Sales_Rep_Name列中的值与所需值(“Janet”)进行比较:
foreach (DataTable table in result.Tables)
{
foreach (DataRow row in table.Rows)
{
var salesRepName = (string)row["Sales_Rep_Name"];
if (String.Equals(salesRepName, "Janet"))
{
var year = (int)(double)row["Year"];
Console.WriteLine($"Janet's year is {year}");
}
}
}
You could also use DataTable.Select(string filterExpression)
method to match required rows:
您还可以使用DataTable.Select(string filterExpression)方法来匹配所需的行:
foreach (DataTable table in result.Tables)
{
foreach (DataRow row in table.Select("Sales_Rep_Name = 'Janet'"))
{
var year = (int)(double)row["Year"];
Console.WriteLine($"Janet's year is {year}");
}
}
The second choice will work faster (when the data is big), however the first one is more flexible and allows to use more complex filters.
第二种选择将更快(当数据很大时),但第一种选择更灵活,允许使用更复杂的过滤器。