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 } } }