private static DataTable ParseDataTableFromStream(Stream stream)
{
var config = new ExcelDataSetConfiguration
{
ConfigureDataTable = tableReader => new ExcelDataTableConfiguration {
UseHeaderRow = true
}
};
using (var reader = ExcelReaderFactory.CreateCsvReader(stream))
{
var dataSet = reader.AsDataSet(config);
return(dataSet.Tables[0]);
}
}
static void ExcelReader()
{
try
{
TimeZoneInfo Pacific_Standard_Time = TimeZoneInfo.FindSystemTimeZoneById(timeZone);
DateTime dateTime_Pacific = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, Pacific_Standard_Time);
var dateString = dateTime_Pacific.ToString("yyyyMMdd");
DirectoryInfo dir = new DirectoryInfo(filePath);
string partialName = $"{fileName}-{dateString}";
FileInfo[] file = dir.GetFiles(partialName + "*.csv", SearchOption.TopDirectoryOnly).OrderByDescending(p => p.CreationTimeUtc).ToArray();
if (file == null || file.Length <= 0)
{
}
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
FileStream stream = File.Open(file[0].FullName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateCsvReader(stream);
ExcelDataSetConfiguration excelDataSetConfiguration = new ExcelDataSetConfiguration
{
ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
};
var dataSet = excelReader.AsDataSet(excelDataSetConfiguration);
var dataTable = dataSet.Tables[0];
IMapper mapper = config.CreateMapper();
List <DataRow> rows = new List <DataRow>(dataTable.Rows.OfType <DataRow>());
List <DATRateData> result = mapper.Map <List <DataRow>, List <DATRateData> >(rows);
using (var context = new DownloadContext())
{
context.DatRatesData.AddRange(result);
context.SaveChanges();
}
excelReader.Close();
}
catch (Exception ex)
{
throw new Exception($"ExcelReader: {ex.Message}");
}
}
public SelectPeriodForm()
{
InitializeComponent();
var r = openFileDialog.ShowDialog();
if (r != DialogResult.OK)
{
Close();
return;
}
FileName = openFileDialog.FileName;
using (var stream = File.Open(openFileDialog.FileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
IExcelDataReader reader;
reader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream);
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = reader.AsDataSet(conf);
int i = 1;
tableSelectGrid.Rows.Clear();
foreach (DataTable table in dataSet.Tables)
{
tableSelectGrid.Rows.Add(i, table.TableName);
i++;
}
var patterns = JsonConvert.DeserializeObject <List <Pattern> >(Properties.Settings.Default.Patterns);
patternsBox.Items.Clear();
if (patterns == null)
{
return;
}
i = 0;
foreach (var p in patterns)
{
patternsBox.Items.Add(p.Name);
i++;
}
}
}
public static void ReadFile()
{
using (var stream = File.OpenRead(PortablePath.Combine(path, file)))
{
var reader = ExcelReaderFactory.CreateReader(stream);
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
dataSet = reader.AsDataSet(conf);
reader.Close();
}
}
public LetterChart2()
{
EndMonth.Add(January);
EndMonth.Add(February);
EndMonth.Add(March);
EndMonth.Add(April);
EndMonth.Add(May);
EndMonth.Add(June);
EndMonth.Add(July);
EndMonth.Add(August);
EndMonth.Add(September);
EndMonth.Add(Oktober);
EndMonth.Add(November);
EndMonth.Add(December);
string path = HostingEnvironment.ApplicationPhysicalPath + currentFile.GetCurrentFile().Path2;
FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read);
IExcelDataReader dataReader = null;
if (path.EndsWith(".xls"))
{
dataReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (path.EndsWith(".xlsx"))
{
dataReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
for (int i = 1; i < 13; i++)
{
var temp = new DateTime(2017, i, 1, 00, 00, 00);
StartMonth.Add(temp);
}
ds = dataReader.AsDataSet(conf);
stream.Close();
stream.Dispose();
dataReader.Close();
}
public static async Task <IActionResult> Run(
[HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
ILogger log)
{
log.LogInformation("C# HTTP trigger function processed a request.");
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
IExcelDataReader excelReader = null;
MemoryStream ms = new MemoryStream();
await req.Body.CopyToAsync(ms);
try
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(ms);
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
DataSet dataSet = excelReader.AsDataSet(conf);
using (SqlConnection conn = new SqlConnection(System.Environment.GetEnvironmentVariable("SQLDB")))
{
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "sp_CodeCdt_Import";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
cmd.CommandTimeout = 9999;
SqlParameter sqlParam = cmd.Parameters.AddWithValue("@ImportTable", dataSet.Tables[0]);
sqlParam.SqlDbType = SqlDbType.Structured;
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
log.LogError(ex.Message);
return(new InternalServerErrorResult());
}
//string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
// parse query parameter
return(new OkObjectResult("Import Success"));
}
public DataTable ImportExcelData(string filePath)
{
using var stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
using var reader = ExcelReaderFactory.CreateReader(stream);
var config = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = reader.AsDataSet(config);
var dataTable = dataSet.Tables[0];
return(dataTable);
}
public dynamic GetDataTable(string fileName)
{
using (var reader = ExcelReaderFactory.CreateReader(File.Open(fileName, FileMode.Open, FileAccess.Read)))
{
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = reader.AsDataSet(conf);
var table = new DataTable();
//Console.WriteLine(dataSet.DataSetName);
return(dataSet.Tables);
}
}
public DataTableCollection Load(string fiPath)
{
using (FileStream stream = File.Open(fiPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
ExcelDataSetConfiguration _loadConfig = new ExcelDataSetConfiguration()
{
UseColumnDataType = false,
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
EmptyColumnNamePrefix = "Column",
UseHeaderRow = false
}
};
IExcelDataReader reader = null;
switch (Path.GetExtension(fiPath).ToLower().ToString())
{
case ".xls":
reader = ExcelReaderFactory.CreateBinaryReader(stream);
break;
case ".xlsx":
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
break;
case ".csv":
reader = ExcelReaderFactory.CreateCsvReader(stream);
break;
default:
reader = ExcelReaderFactory.CreateReader(stream);
break;
}
;
using (reader)
{
if (reader == null)
{
throw new ArgumentNullException(paramName: nameof(reader), message: "reader cannot be null");
}
using (DataSet ds = reader.AsDataSet(_loadConfig))
{
return(ds.Tables);
}
}
}
}
static DataSet ReadExcelFile(string file_name)
{
FileStream file = File.Open(file_name, FileMode.Open, FileAccess.Read);
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
IExcelDataReader excel_data_reader = ExcelReaderFactory.CreateReader(file);
ExcelDataSetConfiguration configuration = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
DataSet dataset = excel_data_reader.AsDataSet(configuration);
return(dataset);
}
static private List <XlsEnergyRecord> GetXlsEnergyRecords(IEnumerable <string> excelFilesList)
{
List <XlsEnergyRecord> xlsEnergyRecordsList = new List <XlsEnergyRecord>();
foreach (string excelFilePath in excelFilesList)
{
Console.WriteLine("Extracting data from: {0}.", excelFilePath);
int j = 0;
using (var stream = File.Open(excelFilePath, FileMode.Open, FileAccess.Read))
{
IExcelDataReader reader;
reader = ExcelReaderFactory.CreateReader(stream);
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = reader.AsDataSet(conf);
var dataTable = dataSet.Tables[recordTableIndex];
// Note that there's additional condition for for to exit.
for (int i = startRow; i < dataTable.Rows.Count && !dataTable.Rows[i][timeStartColumn].ToString().Equals(""); ++i)
{
xlsEnergyRecordsList.Add(
new XlsEnergyRecord(
dataTable.Rows[i][timeStartColumn].ToString(),
dataTable.Rows[i][timeFinishColumn].ToString(),
dataTable.Rows[i][driverColumn].ToString(),
dataTable.Rows[i][managerColumn].ToString(),
dataTable.Rows[i][trainColumn].ToString(),
dataTable.Rows[i][plannedTrainNumberColumn].ToString()
)
);
}
}
}
return(xlsEnergyRecordsList);
}
public string returnData(PathFile pathFile)
{
DataTable dt = new DataTable();
List <Dictionary <string, object> > parentRow = new List <Dictionary <string, object> >();
Dictionary <string, object> childRow;
FileInfo fileInfo = new FileInfo(pathFile.path);
using (var stream = File.Open(pathFile.path, FileMode.Open, FileAccess.Read))
{
IExcelDataReader reader;
reader = ExcelReaderFactory.CreateReader(stream);
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = reader.AsDataSet(conf);
dt = dataSet.Tables[0];
}
foreach (DataRow row in dt.Rows)
{
childRow = new Dictionary <string, object>();
foreach (DataColumn col in dt.Columns)
{
childRow.Add(col.ColumnName, row[col]);
}
parentRow.Add(childRow);
}
string result = JsonConvert.SerializeObject(parentRow);
if (fileInfo.Exists)
{
fileInfo.Delete();
}
return(result);
}
public static DataTable read_excel(string excel_path)
{
DataTable dt = new DataTable();
var file = new FileInfo(excel_path);
if (File.Exists(excel_path))
{
using (
var stream = File.Open(excel_path, FileMode.Open, FileAccess.Read))
{
IExcelDataReader reader;
if (file.Extension.Equals(".xls") || file.Extension.Equals(".XLS"))
{
reader = ExcelDataReader.ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (file.Extension.Equals(".xlsx") || file.Extension.Equals(".XLSX"))
{
reader = ExcelDataReader.ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
throw new Exception("Invalid FileName");
}
//// reader.IsFirstRowAsColumnNames
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = reader.AsDataSet(conf);
dt = dataSet.Tables[0];
}
}
else
{
dt = null;
}
return(dt);
}
//////////////////////////////////////////////////////////////////////
// //
// EXCEL HEADER TEXT ARRAY[] //
// //
//////////////////////////////////////////////////////////////////////
public string[] ExcelTableHeader()
{
var file = new FileInfo(filePath);
IExcelDataReader reader;
FileStream fs = File.Open(filePath, FileMode.Open, FileAccess.Read);
if (file.Extension.Equals(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(fs);
}
else if (file.Extension.Equals(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(fs);
}
else if (file.Extension.Equals(".csv"))
{
reader = ExcelReaderFactory.CreateCsvReader(fs);
}
else
{
throw new Exception("Invalid FileName");
}
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = reader.AsDataSet(conf);
var dt = dataSet.Tables[0];
reader.Close();
int i = 0;
string headertext = String.Empty;
headerarray = new string[dt.Columns.Count];
foreach (DataColumn column in dt.Columns)
{
headertext = column.ColumnName.ToLower();
headerarray[i] = headertext;
i++;
}
return(headerarray);
}
public List <HoursLogTableTemp> ProcessFile(string fullPath)
{
using (var stream = File.Open(fullPath, FileMode.Open, FileAccess.Read))
{
IExcelDataReader reader;
using (reader = ExcelReaderFactory.CreateReader(stream))
{
var config = new ExcelDataSetConfiguration
{
ConfigureDataTable = r => new ExcelDataTableConfiguration
{
UseHeaderRow = false
}
};
var dataSet = reader.AsDataSet(config);
var dataTable = dataSet.Tables;
var rows = dataTable[0].Rows;
var columns = dataTable[0].Columns;
var hoursLogged = new List <HoursLogTableTemp>();
for (var row = 0; row < rows.Count; row++)
{
var log = new HoursLogTableTemp
{
RowID = 0,
RowData = new List <string>()
};
var cell = new CellMapper();
for (var col = 0; col < columns.Count; col++)
{
log.RowID = row;
log.RowData.Add((rows[row][col]).ToString());
}
log.ColCount = columns.Count;
hoursLogged.Add(log);
}
return(hoursLogged);
}
}
}
private static DataSet ReadExcelFile(string fullExcelFileName, int headerRowIndex)
{
var columnHeaders = new HashSet <string>();
var dataTableConfiguration = new ExcelDataTableConfiguration()
{
UseHeaderRow = true,
ReadHeaderRow = (rowReader) =>
{
while (rowReader.Depth < headerRowIndex - 1)
{
rowReader.Read();
}
},
FilterColumn = (rowReader, columnIndex) =>
{
var header = rowReader.GetString(columnIndex);
if (!columnHeaders.Contains(header))
{
columnHeaders.Add(header);
return(true);
}
return(false);
},
FilterRow = (rowReader) =>
{
var isRowAfterHeaderRow = rowReader.Depth > headerRowIndex - 2;
var isFirstColumnPopulated = !string.IsNullOrWhiteSpace(rowReader.GetString(0));
return(isRowAfterHeaderRow && isFirstColumnPopulated);
},
};
var dataSetConfiguration = new ExcelDataSetConfiguration()
{
UseColumnDataType = true,
ConfigureDataTable = (tableReader) => dataTableConfiguration,
};
using (var stream = new FileStream(fullExcelFileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
return(reader.AsDataSet(dataSetConfiguration));
}
}
/*private void Import_To_Grid(string FilePath, string Extension, string isHDR)
*
* {
* string conStr = "";
* switch (Extension)
* {
* case "xls": //Excel 97-03
*
* conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
* break;
*
* case "xlsx": //Excel 07
*
* conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
* break;
* }
* conStr = String.Format(conStr, FilePath, isHDR);
*
* OleDbConnection connExcel = new OleDbConnection(conStr);
* OleDbCommand cmdExcel = new OleDbCommand();
* OleDbDataAdapter oda = new OleDbDataAdapter();
* DataTable DT = new DataTable();
* cmdExcel.Connection = connExcel;
* connExcel.Open();
*
* DataTable dtExcelSchema;
*
* dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
*
* string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
* connExcel.Close();
* connExcel.Open();
*
* cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
*
* oda.SelectCommand = cmdExcel;
* oda.Fill(DT);
* if (DT.Rows.Count > 0)
* {
* Grdprueba.DataSource = DT;
* }
* else { Grdprueba.DataSource = null; }
*
* Grdprueba.DataBind();
* connExcel.Close();
*
* }*/
protected void Import(string FilePath, string Extension)
{
try
{
FileStream stream = File.Open(FilePath, FileMode.Open, FileAccess.Read);
IExcelDataReader ExcelReader;
/*
* //1. Reading Excel file
* if (Path.GetExtension(FilePath).ToUpper() == ".XLS")
* { ExcelReader = ExcelReaderFactory.CreateBinaryReader(stream); }
* else
* { ExcelReader = ExcelReaderFactory.CreateOpenXmlReader(stream); }// XLSX
*/
//2. DataSet - The result of each spreadsheet will be created in the result.Tables
// DataSet result = ExcelReader.AsDataSet();
ExcelReader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream);
//// reader.IsFirstRowAsColumnNames
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
//3. DataSet - Create column names from first row
var dataSet = ExcelReader.AsDataSet(conf);
DataTable DT = dataSet.Tables[0];
if (DT.Rows.Count > 0)
{
Grdprueba.DataSource = DT;
}
else
{
Grdprueba.DataSource = null;
}
Grdprueba.DataBind();
}
catch (Exception ex) { }
}
public List <User> ReadUsers()
{
Users = new List <User>();
FileInfo file = new FileInfo(this.FilePath);
FileStream stream = File.Open(this.FilePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader;
if (file.Extension.Equals(".xls"))
{
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var _allFileData = excelReader.AsDataSet(conf);
var rows = _allFileData.Tables[0].Rows.Cast <DataRow>();
bool hasCompany = _allFileData.Tables[0].Columns.Count > 2;
foreach (var row in rows)
{
Users.Add(new User()
{
Name = row[0].ToString(),
Email = row[1].ToString(),
Company = hasCompany ? row[2].ToString() : string.Empty
});
}
return(Users);
}
public ActionResult Index(HttpPostedFileBase xls)
{
if (ModelState.IsValid)
{
if (xls != null && xls.ContentLength > 0)
{
Stream st = xls.InputStream;
IExcelDataReader reader = null;
if (xls.FileName.EndsWith(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(st);
}
else if (xls.FileName.EndsWith(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(st);
}
else
{
ModelState.AddModelError("File", "Este formato no es aceptado.");
return(View());
}
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
DataSet resultado = reader.AsDataSet();
reader.Close();
return(View(resultado.Tables[0]));
}
else
{
ModelState.AddModelError("File", "Suba un archivo.");
}
}
return(View());
}
public static DataSet ReadClinicalExcelData(string excelFilePath)
{
DataSet resultDS = new DataSet();
IExcelDataReader excelReader = null;
using (FileStream stream = new FileStream(excelFilePath, FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite))
{
var fileExtension = Path.GetExtension(excelFilePath);
switch (fileExtension.ToLower().Trim())
{
case ".xls":
// 1.Reading from a binary Excel file('97-2003 format; *.xls)
excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
break;
case ".xlsx":
//2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
break;
default:
excelReader = null;
break;
}
//3. DataSet - Create column names from first row
if (excelReader != null)
{
//excelReader.IsFirstRowAsColumnNames = true;
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
resultDS = excelReader.AsDataSet(conf);
excelReader.Close();
}
return(resultDS);
}
}
public static DataSet GetUploadedFileData(HttpPostedFileBase file, bool ignoreFirstRow = true)
{
if (file == null && file.ContentLength <= 1)
{
return(new DataSet());
}
else
{
var reader = file.FileName.ToLower().EndsWith(".xlsx", StringComparison.Ordinal) ? ExcelReaderFactory.CreateOpenXmlReader(file.InputStream) :
ExcelReaderFactory.CreateBinaryReader(file.InputStream);
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = !ignoreFirstRow
}
};
return(reader.AsDataSet(conf));
}
}
public DataTable ReadExcelData(String fileName, string sheetName)
{
System.IO.FileStream file = File.Open(fileName, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(file);
var conf = new ExcelDataSetConfiguration()
{
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
{
UseHeaderRow = true
}
};
DataSet result = excelReader.AsDataSet(conf);
DataTableCollection table = result.Tables;
DataTable resultTable = table[sheetName];
Console.WriteLine(resultTable);
return(resultTable);
}
private void LerRelatorio(string originalFileName)
{
using (var stream = File.Open(originalFileName, FileMode.Open, FileAccess.Read))
{
IExcelDataReader reader;
reader = ExcelReaderFactory.CreateReader(stream);
//reader.IsFirstRowAsColumnNames
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = reader.AsDataSet(conf);
var dataTable = dataSet.Tables[0];
}
}
private void ButtonStart_Click(object sender, EventArgs e)
{
try
{
var fileToRead = new FileInfo(TextBoxFileToRead.Text);
var isExcel = new[] { ".xls", ".xlsx" }.Contains(fileToRead.Extension, StringComparer.OrdinalIgnoreCase);
using (var stream = fileToRead.OpenRead())
{
var reader = isExcel
? ExcelReaderFactory.CreateReader(stream)
: ExcelReaderFactory.CreateCsvReader(stream);
using (reader)
{
var config = new ExcelDataSetConfiguration
{
ConfigureDataTable = tableReader => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var result = reader.AsDataSet(config);
if (result.Tables.Count != 1)
{
throw new Exception($"Cannot handle an Excel File with {result.Tables.Count} sheets");
}
var dataTable = result.Tables[0];
MessageBox.Show(string.Join("\n", dataTable.Columns.OfType <DataColumn>().Select(c => c.ColumnName)));
MessageBox.Show(dataTable.Rows.Count.ToString());
foreach (var row in dataTable.Rows.OfType <DataRow>())
{
MessageBox.Show(row["Cand Ballot Name Txt"] as string);
}
}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return;
}
}
public static System.Data.DataSet Excel2DataSet(string FileName, bool useHeaderRow = true)
{
System.Data.DataSet ds = null;
ExcelDataSetConfiguration erc = new ExcelDataSetConfiguration();
erc.ConfigureDataTable = (tableReder) => new ExcelDataTableConfiguration()
{
EmptyColumnNamePrefix = "Column",
UseHeaderRow = useHeaderRow//, ReadHeaderRow = (rowReader) => { rowReader.Read(); }
};
//using (var stream = File.Open(fn, FileMode.Open, FileAccess.Read))
//{
// ExcelDataSetConfiguration erc = new ExcelDataSetConfiguration();
// erc.ConfigureDataTable = (tableReder) => new ExcelDataTableConfiguration()
// {
// EmptyColumnNamePrefix = "Column",
// UseHeaderRow = true//, ReadHeaderRow = (rowReader) => { rowReader.Read(); }
// };
// using (var xlsReader = ExcelReaderFactory.CreateReader(stream))
// {
try
{
using (var stream = File.Open(FileName, FileMode.Open, FileAccess.Read))
{
using (var xlsReader = ExcelReaderFactory.CreateReader(stream))
//ds = new System.Data.DataSet();
ds = xlsReader.AsDataSet(erc);
}
}
catch (Exception ex)
{
_log.Fatal(ex); System.Windows.Forms.MessageBox.Show(ex.Message); Console.WriteLine(ex.Message);
}
return(ds);
}
public static DataTable ExcelToDataTable(string extension, System.IO.Stream stream)
{
IExcelDataReader reader;
if (extension.Equals(".xls"))
{
reader = ExcelReaderFactory.CreateBinaryReader(stream);
}
else if (extension.Equals(".xlsx"))
{
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
}
else
{
return(null);
}
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = reader.AsDataSet(conf);
if (dataSet.Tables.Count <= 0)
{
return(null);
}
var dataTable = dataSet.Tables[0];
if (dataTable.Rows.Count <= 0)
{
return(null);
}
return(dataTable);
}
public static DataSet GetExcelData(this string input)
{
try
{
using (var stream = File.Open(input, FileMode.Open, FileAccess.Read))
{
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
do
{
while (reader.Read())
{
// reader.GetDouble(0);
}
}while (reader.NextResult());
var options = new ExcelDataSetConfiguration
{
UseColumnDataType = true,
ConfigureDataTable = tableReader => new ExcelDataTableConfiguration
{
UseHeaderRow = true,
FilterRow = rowReader => true
}
};
var result = reader.AsDataSet(options);
result.DataSetName = input;
return(result);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
return(null);
}
public void ReadExcelSheet()
{
FileStream fileStream = new FileStream(@"file location", FileMode.Open, FileAccess.Read);
IExcelDataReader reader = ExcelReaderFactory.CreateOpenXmlReader(fileStream);
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
DataTable resultSet = reader.AsDataSet(conf).Tables["SheetName"];
for (int i = 0; i < resultSet.Rows.Count; i++)
{
var col = resultSet.Rows[i];
for (int j = 0; j < col.ItemArray.Length; j++)
{
var cellValue = col.ItemArray[j];
}
}
}
private static DataTable ImportToDataTable_Libraries(string filePath)
{
DataTable table;
using (FileStream fileStream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
IExcelDataReader excelDataReader = ExcelReaderFactory.CreateReader(fileStream);
ExcelDataSetConfiguration configuration = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
DataSet result = excelDataReader.AsDataSet(configuration);
table = result.Tables[0];
}
return(table);
}
public PageMetaDetails(string pageUrl)
{
//string currentDir = System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath;
var filePath = System.Web.Hosting.HostingEnvironment.ApplicationPhysicalPath + "book1.xlsx";
using (var stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
{
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
// 1. Use the reader methods
var conf = new ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
// 2. Use the AsDataSet extension method
var dataSet = reader.AsDataSet(conf);
// Now you can get data from each sheet by its index or its "name"
var dataTable = dataSet.Tables[0];
for (var x = 0; x < dataTable.Rows.Count; x++)
{
if (pageUrl == dataTable.Rows[x][1].ToString())
{
metaDescription = dataTable.Rows[x][4].ToString();
metaKeyword = dataTable.Rows[x][3].ToString();
title = dataTable.Rows[x][2].ToString();
break;
}
}
// The result of each spreadsheet is in result.Tables
}
}
}
