c# - 想要使用 selenium c# 从同一个 Excel 文件中的不同 Excel 工作表中读取数据

Question

已使用 ExcelDataReader NuGET 包。

这是设置它的类-

    private static DataTable ExcelToDataTable(string fileName)
    {
        //open file and returns as Stream
        FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
        //Createopenxmlreader via ExcelReaderFactory
        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //.xlsx
                                                                                       //Set the First Row as Column Name
        excelReader.IsFirstRowAsColumnNames = true;
        //Return as DataSet
        DataSet result = excelReader.AsDataSet();
        //Get all the Tables
        DataTableCollection table = result.Tables;
        //Store it in DataTable
        DataTable resultTable = table["Sheet1"];
        //return
        return resultTable;
    }
   static List<Datacollection> dataCol = new List<Datacollection>();

public static void PopulateInCollection(string fileName)
    {
        DataTable table = ExcelToDataTable(fileName);

        //Iterate through the rows and columns of the Table
        for (int row = 1; row <= table.Rows.Count; row++)
        {
            for (int col = 0; col < table.Columns.Count; col++)
            {
                Datacollection dtTable = new Datacollection()
                {
                    rowNumber = row,
                    colName = table.Columns[col].ColumnName,
                    colValue = table.Rows[row - 1][col].ToString()
                };
                //Add all the details for each row
                dataCol.Add(dtTable);
            }
        }
    }
    public static string ReadData(int rowNumber, string columnName)
    {
        try
        {
            //Retriving Data using LINQ to reduce much of iterations
            string data = (from colData in dataCol
                           where colData.colName == columnName && colData.rowNumber == rowNumber
                           select colData.colValue).SingleOrDefault();

            //var datas = dataCol.Where(x => x.colName == columnName && x.rowNumber == rowNumber).SingleOrDefault().colValue;
            return data.ToString();
        }
        catch (Exception)
        {
            return null;
        }
    }


}

public class Datacollection
{
    public int rowNumber { get; set; }
    public string colName { get; set; }
    public string colValue { get; set; }
}

}

---

它在代码中调用 -

   //setting the excel file location
ExcelLib.PopulateInCollection(@"C:\Users\User1\Documents\data.xlsx");
//reading data from row 1 & column named middlename
xrmApp.Entity.SetValue("middlename", ExcelLib.ReadData(1, "middlename"));

我希望能够以不同的方法使用此代码，并且对于每种方法我需要不同的数据。无论如何，他们也是如此，我可以在代码中添加一个工作表编号参数，这样我就可以指定在读取行号和列号时要查看的工作表。

谢谢！

标签： c#visual-studioseleniumnuget-packageexceldatareader

Answer 1

根据您的描述，您想在同一个工作表中读取不同工作表中的数据

excel文件。

我建议您可以将 excel 文件转换为数据集，然后从

数据集。

像下面的代码：

在此之前，请同时安装 nuget 包 ExcelDataReader 和 ExcelDataReader.DataSet。

 class Program
 {
    static void Main(string[] args)
    {
        DataSet set = ExcelToDataSet("D:\\test.xlsx");
        DataTable table = set.Tables[0];   // first sheet
        string data = ReadData(table, 0, "TeacherName");// the first row and colunname is teachername
        Console.WriteLine(data);
        table = set.Tables[1];             //second sheet
        data = ReadData(table, 0, "StuName");// the first row and colunname is stuname
        Console.WriteLine(data);
        Console.ReadKey();

    }

    private static DataSet ExcelToDataSet(string fileName)
    {
        //open file and returns as Stream
        FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read);
        //Createopenxmlreader via ExcelReaderFactory
        IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); //.xlsx
                                                                                       //Set the First Row as Column Name
        DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
        {
            ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
            {
                UseHeaderRow = true
            }
        });
        //Get all the Tables
        //return
        return result;
    }

    static string ReadData(DataTable table,int row,string columnname)
    {
        return table.Rows[row].Field<string>(columnname).ToString();
    }


}

我创建了以下excel：

第一张：

第二张：

如果我运行上面的代码，你会得到两张表的第一行和第一列的值。