private static DataTable ExcelToDataTable(string fileName, string SheetName)
{
// Open file and return as Stream
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
using (System.IO.FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
using (ExcelDataReader.IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream))
{
//excelReader.IsFirstRowAsColumnNames = true;
//Return as dataset
// DataSet result = excelReader.AsDataSet();
DataSet result = excelReader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
//Get all the tables
DataTableCollection table = result.Tables;
// store it in data table
DataTable resultTable = table[SheetName];
//excelReader.Dispose();
//excelReader.Close();
// return
return(resultTable);
}
}
}
private static DataTable ExcelToDataTable(string fileName, string sheetName)
{
// Open file and return as Stream
using (FileStream stream = File.Open(fileName, FileMode.Open, FileAccess.Read))
{
using (ExcelDataReader.IExcelDataReader reader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream))
{
var result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
ConfigureDataTable = (data) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
});
//Get all the tables
var table = result.Tables;
// store it in data table
var resultTable = table[sheetName];
return(resultTable);
}
}
}