I am trying to read data from multiple sheets in a single excel file and in the process I have been having one issue or the other. The current problem i have now is on 'Object reference not set to an instance of an object' which I get while performing a test.
I have tried using OLEDB connection but to no avail. I'm still new in coding and would appreciate any help.
// singleton object of public class ExcelDataContext { // creating an object of ExcelDataContext private static readonly ExcelDataContext Instance = new ExcelDataContext();
// no instantiated available
private static DataTable GetExcelDataContext(string filename)
{
FileStream stream = File.Open(filename, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
foreach (DataTable sheet in result.Tables)
{
DataTableCollection tableCollection = result.Tables;
if (sheet.Equals(tableCollection["loginData"]))
{
DataTable loginDataTable = GetInstance().Sheets["loginData"];
return loginDataTable;
}
if (sheet.Equals(tableCollection["homeData"]))
{
DataTable homeDataTable = GetInstance().Sheets["homeData"];
return homeDataTable;
}
excelReader.Close();
}
return new DataTable();
}
// accessing to ExcelDataContext singleton
public static ExcelDataContext GetInstance()
{
return Instance;
}
// the dataset of Excel
public DataTableCollection Sheets => null;
public class DataCollection
{
public int RowNumber { get; set; }
public string ColName { get; set; }
public string ColValue { get; set; }
}
static readonly List<DataCollection> DataCollections = new List<DataCollection>();
public static void PopulateInCollection(string filename)
{
DataTable dataTable = GetExcelDataContext(filename);
for (int row = 1; row <= dataTable.Rows.Count; row++) //row =1 because we are not using header name
{
for (int col = 0; col < dataTable.Columns.Count; col++)
{
DataCollection collection = new DataCollection()
{
RowNumber = row,
ColName = dataTable.Columns[col].ColumnName, //return column name
ColValue = dataTable.Rows[row - 1][col].ToString()
};
DataCollections.Add(collection);
}
}
}
public static string ReadData(int rowNumber, string columnName)
{
try
{
string data =
(from colData in DataCollections
where colData.ColName == columnName && colData.RowNumber == rowNumber
select colData.ColValue).SingleOrDefault();
return data;
}
catch (Exception)
{
return null;
}
}
}
i am expecting to be able to get the data from each excel sheet.
