In my project I have Test Data folder and inside that I have my xlsx file.
I am trying to get the project path folder so it read from their instead of hard coding the path. But it always goes and reads form project/bin/debug path. Here is my Excel utility. In my class I am Initializing the ExcelUtil class and using the ReadData method
public class ExcelUtil
{
public static void InitializeExcel()
{
string exeDir = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().Location);
File.OpenRead(System.IO.Path.Combine(exeDir, "Data.xlsx"));
}
public static DataTable ExcelToDataTable(string fileName)
{
//Open the file
using (var stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
//read the excel file
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
//using anaysome method
ConfigureDataTable = (data) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
//Storing in DataCollection
DataTableCollection table = result.Tables;
DataTable resultTable = table["Sheet1"];
return resultTable;
}
}
}
//storing the Data from excel in List type of othe custom class
public static List<DataCollection> datacol = new List<DataCollection>();
//Method populates the data into the collection
public static void PopulateInCollection(string fileName)
{
DataTable table = ExcelToDataTable(fileName);
//Iterating through the rows and columns
for (int row = 1; row <= table.Rows.Count; row++)
{
for (int col = 0; col < table.Columns.Count; col++)
{
DataCollection dTable = 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(dTable);
}
}
}
//Method read data from excel using rownum and colname
public static string ReadData(int rowNumber, string columnName)
{
try
{
//Retriving data using LINQO to reduce much iterations
string data = (from colData in datacol
where colData.ColName == columnName && colData.RowNumber == rowNumber
select colData.ColValue).SingleOrDefault();
return data.ToString();
}
catch (Exception e)
{
Console.WriteLine(e.Message);
return null;
}
}
}
//Custom class to hold rowsnum and columnnum adn val data
public class DataCollection
{
public int RowNumber { get; set; }
public string ColName { get; set; }
public string ColValue { get; set; }
}
}
