private static void DataTableBulkCopySample() { var dt = new DataTable(); using (var reader = new ExcelDataReader(@"test.xlsx")) dt.Load(reader); Console.WriteLine("Read DataTable done: " + dt.Rows.Count); DataHelper.CreateTableIfNotExists(ConnectionString, TableName, dt.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray()); Console.WriteLine("Create table done."); using (var bulkCopy = new SqlBulkCopy(ConnectionString)) { bulkCopy.DestinationTableName = TableName; foreach (DataColumn dc in dt.Columns) bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName); bulkCopy.WriteToServer(dt); } Console.WriteLine("Copy data to database done (DataTable)."); }
public void CanReadExcel() { IDataTable table; using (var excel = new ExcelDataReader(GetFileStream("testdata.xlsx"), true)) { table = LightweightDataTable.FromDataReader(excel); } VerifyDataTable(table); }
private static void DataReaderBulkCopySample() { using (var reader = new ExcelDataReader(@"test.xlsx")) { var cols = Enumerable.Range(0, reader.FieldCount).Select(i => reader.GetName(i)).ToArray(); DataHelper.CreateTableIfNotExists(ConnectionString, TableName, cols); Console.WriteLine("Create table done."); using (var bulkCopy = new SqlBulkCopy(ConnectionString)) { // MSDN: When EnableStreaming is true, SqlBulkCopy reads from an IDataReader object using SequentialAccess, // optimizing memory usage by using the IDataReader streaming capabilities bulkCopy.EnableStreaming = true; bulkCopy.DestinationTableName = TableName; foreach (var col in cols) bulkCopy.ColumnMappings.Add(col, col); bulkCopy.WriteToServer(reader); } Console.WriteLine("Copy data to database done (DataReader)."); } }
static void Main(string[] args) { var dt = new DataTable(); using (var reader = new ExcelDataReader(@"data.xlsx")) dt.Load(reader); Console.WriteLine("done: " + dt.Rows.Count); Console.ReadKey(); }
public static List <int> GetIdCityByExcel(this HttpPostedFileBase file, string fileLocation) { //Arquivo valido para tratamento if (file.FileName.Contains(".csv") || file.FileName.Contains(".xlsx")) { string columName = "City"; var names = columName.Split(';'); if (System.IO.File.Exists(fileLocation)) { System.IO.File.Delete(fileLocation); } var dt = new DataTable(); file.SaveAs(fileLocation); //Caso for csv if (fileLocation.Contains(".csv")) { dt = DataTableHandle.GetDataTabletFromCsvFile(fileLocation); } if (fileLocation.Contains(".xlsx")) { using (var reader = new ExcelDataReader(fileLocation)) dt.Load(reader); } var lista = new List <int>(); var cidades = Business.City.GetCities().ToList(); foreach (var s in dt.AsEnumerable()) { int data; try { data = Convert.ToInt32(s[names[0].Trim()].ToString()); if (!cidades.Any(w => w.idCity == data)) { continue; } } catch { //supressed continue; } lista.Add(data); } //Atualiza as ordens return(lista); } return(null); }
public void OpenPathHdrDefaultSheetTest() { var dt = new DataTable(); using (var reader = new ExcelDataReader(@"test.xlsx")) dt.Load(reader); Assert.AreEqual(7, dt.Columns.Count); Assert.AreEqual(5, dt.Rows.Count); }
public void OpenPathNoHdrSheetNameTest() { var dt = new DataTable(); using (var reader = new ExcelDataReader(@"test.xlsx", "Second", false)) dt.Load(reader); Assert.AreEqual(7, dt.Columns.Count); Assert.AreEqual(6, dt.Rows.Count); }
public void Read_ShouldReturnFailedResultWithExpectedMessageWhenFileNotFound() { var path = TestUtils.BuildPathFor(@"DataReadersTestCases\NotExistedFile.xlsx"); var reader = new ExcelDataReader(); var result = reader.Read(path); result.Data.Should().BeEmpty(); result.ErrorMessage.Should().ContainEquivalentOf("Could not find file"); }
public void Read_ShouldReturnFailedResultWithExpectedMessageWhenValuesInWrongFormat() { var path = TestUtils.BuildPathFor(@"DataReadersTestCases\ExcelDataReaderTestCase2.xlsx"); var reader = new ExcelDataReader(); var result = reader.Read(path); result.Data.Should().BeEmpty(); result.ErrorMessage.Should().ContainEquivalentOf("string was not in a correct format"); }
public void OpenPathEmptyRowsTest() { var dt = new DataTable(); using (var reader = new ExcelDataReader(@"NoDataReturned.xlsx", "ContractIBNR")) dt.Load(reader); Assert.AreEqual(5, dt.Columns.Count); Assert.AreEqual(1, dt.Rows.Count); }
public void DataReaderTest() { using (var reader = new ExcelDataReader(@"test.xlsx")) { Assert.AreEqual(0, reader.Depth); Assert.AreEqual(-1, reader.RecordsAffected); Assert.AreEqual(7, reader.FieldCount); Assert.AreEqual(5, reader.GetOrdinal("ColDecimal")); Assert.AreEqual(-1, reader.GetOrdinal("InvalidColName")); } }
protected override void ReadAll() { do { int rowNr = 0; while (ExcelDataReader.Read()) { if (ExcelDataReader.VisibleState != "visible") { continue; } if (HasSheetName && ExcelDataReader.Name != SheetName) { continue; } rowNr++; if (HasRange && rowNr > Range.EndRowIfSet) { break; } if (HasRange && rowNr < Range.StartRow) { continue; } try { TOutput row = ParseDataRow(); if (row == null && IgnoreBlankRows) { continue; } else if (row == null && !IgnoreBlankRows) { break; } else if (row != null) { Buffer.SendAsync(row).Wait(); LogProgress(); } } catch (Exception e) { if (!ErrorHandler.HasErrorBuffer) { throw e; } ErrorHandler.Send(e, $"File: {Uri} -- Sheet: {SheetName ?? ""} -- Row: {rowNr}"); } } } while (ExcelDataReader.NextResult()); }
private TOutput ParseDataRow() { TOutput row = (TOutput)Activator.CreateInstance(typeof(TOutput)); bool emptyRow = true; for (int col = 0, colNrInRange = -1; col < ExcelDataReader.FieldCount; col++) { if (HasRange && col > Range.EndColumnIfSet) { break; } if (HasRange && (col + 1) < Range.StartColumn) { continue; } colNrInRange++; emptyRow &= ExcelDataReader.IsDBNull(col); object value = ExcelDataReader.GetValue(col); if (TypeInfo.IsDynamic) { var r = row as IDictionary <string, Object>; if (HasHeaderData) { r.Add(HeaderColumns[colNrInRange], value); } else { r.Add("Column" + (colNrInRange + 1), value); } } else { PropertyInfo propInfo = null; if (HasHeaderData && TypeInfo.ExcelColumnName2PropertyIndex.ContainsKey(HeaderColumns[colNrInRange])) { propInfo = TypeInfo.Properties[TypeInfo.ExcelColumnName2PropertyIndex[HeaderColumns[colNrInRange]]]; } else if (TypeInfo.ExcelIndex2PropertyIndex.ContainsKey(colNrInRange)) { propInfo = TypeInfo.Properties[TypeInfo.ExcelIndex2PropertyIndex[colNrInRange]]; } propInfo?.TrySetValue(row, TypeInfo.CastPropertyValue(propInfo, value?.ToString())); } } if (emptyRow) { return(default(TOutput)); } else { return(row); } }
public void test_read_excel_should_report_missing_column_error() { ExcelDataReader <InvoiceData> processor = new ExcelDataReader <InvoiceData>(); processor.Source = this.getFilePath("invoice_missing_column.xlsx"); var process_result = processor.ReadData(); Assert.IsFalse(process_result); Assert.AreEqual(0, processor.Data.Count()); Assert.AreEqual(1, processor.Errors.Count); Assert.AreEqual("column Circuit Name is not found", processor.Errors[0].GetError("Circuit Name").Message); }
public void OpenStreamEmptySheetTest() { var dt = new DataTable(); using (var sr = File.OpenRead(@"test.xlsx")) { using (var reader = new ExcelDataReader(sr, "Third", false)) dt.Load(reader); } Assert.AreEqual(0, dt.Columns.Count); Assert.AreEqual(0, dt.Rows.Count); }
protected bool IsRowEmpty(ExcelDataReader reader, decimal count, IEnumerable <int> excludedColumns) { var itemArray = new object[reader.FieldCount]; reader.GetValues(itemArray); var emptyRowFound = itemArray.Where((r, d) => excludedColumns.Contains(d)).All(v => v == DBNull.Value); if (emptyRowFound) { } return(emptyRowFound); }
public void OpenStreamHdrSheetIndexTest() { var dt = new DataTable(); using (var sr = File.OpenRead(@"test.xlsx")) { using (var reader = new ExcelDataReader(sr, 1, false)) dt.Load(reader); } Assert.AreEqual(7, dt.Columns.Count); Assert.AreEqual(6, dt.Rows.Count); }
private DataTable ReadFile(string _path) { DataTable dtShifts = new DataTable(); try { using (var reader = new ExcelDataReader(_path)) dtShifts.Load(reader); } catch (Exception ex) { } return(dtShifts); }
static void Main(string[] args) { Console.WriteLine("Type path of excel file:\n"); string path = @Console.ReadLine(); var dataReader = new ExcelDataReader(path); var dictionary = dataReader.getDataEntry(); foreach (KeyValuePair <string, DataEntry> pair in dictionary) { Console.WriteLine("{0} {1}", pair.Key, pair.Value.Hours + " " + pair.Value.Price); } Console.ReadLine(); }
private void MainForm_Shown(object sender, EventArgs e) { Thread t = new Thread(() => { using (var openFileDialog = new OpenFileDialog()) { openFileDialog.CheckFileExists = true; openFileDialog.Multiselect = false; openFileDialog.Filter = @"Excel Files|*.xls;*.xlsx"; openFileDialog.Title = "Select Excel File With Data"; if (openFileDialog.ShowDialog() == DialogResult.OK) { var fileName = openFileDialog.FileName; //todo add Unity Container using (IDataReader excelDataReader = new ExcelDataReader()) { if (excelDataReader is INotifyChanges) { ((INotifyChanges)excelDataReader).SendNotification += SendNotification; } excelDataReader.ReaderDetails = fileName; var response = excelDataReader.ReadData(); if (response.Success) { WritePlayers(response.ReturnValue as IEnumerable <PlayersGroup>); SendNotification(null, "Work Completed."); } else { foreach (var exception in response.Errors) { Logger.Error(exception); } SendNotification(null, string.Format(Resources.SplashForm_Errors_Log, response.Errors.Count)); } } } SystemSounds.Beep.Play(); Thread.Sleep(500); MainForm.Finished = true; } }); t.IsBackground = true; t.SetApartmentState(ApartmentState.STA); t.Start(); }
public static DataSet Parse(string fileName) { // string cons = "Provider=Microsoft.ACE.OLEDB.12.0; " + "data source='" + fileName + "';" + "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" "; FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.Read); try { var spreadsheet = new ExcelDataReader(fs); fs.Close(); return(spreadsheet.WorkbookData); } catch (Exception ex) { fs.Close(); fs.Dispose(); throw ex; } //foreach (var sheetName in GetExcelSheetNames(cons)) //{ // using (OleDbConnection con = new OleDbConnection(cons)) // { // try // { // var dataTable = new DataTable(); // string query = string.Format("SELECT * FROM [{0}]", sheetName); // con.Open(); // OleDbDataAdapter adapter = new OleDbDataAdapter(query, con); // adapter.Fill(dataTable); // data.Tables.Add(dataTable); // con.Close(); // con.Dispose(); // } // catch (Exception ex) // { // con.Close(); // con.Dispose(); // throw ex; // } // } //} }
public void GetField() { //var filePath = @"D:\Users\Scott\Source\github\cadru\tests\Cadru.Data.Tests\Plan Assignment DELETE File 20170615.xlsx"; var filePath = @"Adjustments Table Upload 20170616.xlsx"; using (var stream = File.OpenRead(filePath)) { using (var reader = new ExcelDataReader(stream)) { reader.FirstRowAsHeader = true; reader.Read(); var sheetName = reader.CurrentSheetName; reader.Read(); var empty = this.IsRowEmpty(reader, 0, Enumerable.Empty <int>()); var fiscalYear = reader.Field <int>(0); var empId = reader.Field <string>(1); var startDate = reader.Field <DateTime>(2); var status = reader.Field <int?>(6); var monthlyRate = reader.Field <decimal?>(7); var orgUnit = reader.Field <string>(10); var s1 = reader.ToDelimitedString(); var s2 = reader.ToDelimitedString(true); var action = "I"; var index = -1; if (reader.FieldNames.Contains("ImportAction")) { index = reader.GetOrdinal("ImportAction"); } else if (reader.FieldNames.Contains("Action")) { index = reader.GetOrdinal("Action"); } if (index != -1) { action = reader.GetString(index) ?? "I"; } reader.Close(); } } }
private void ParseHeader() { for (int col = 0, colNrInRange = -1; col < ExcelDataReader.FieldCount; col++) { if (HasRange && col > Range.EndColumnIfSet) { break; } if (HasRange && (col + 1) < Range.StartColumn) { continue; } colNrInRange++; string value = Convert.ToString(ExcelDataReader.GetValue(col)); HeaderColumns.Add(value); } IsHeaderRead = true; }
private static IEnumerable <TestCaseData> GetData(string path, string sheetName) { var testcases = ExcelDataReader.New().FromFileSystem(path).AddSheet(sheetName).GetTestCases(delegate(string sheet, System.Data.DataRow row, int rowNum) { var testDataArgs = new Dictionary <string, string>(); foreach (System.Data.DataColumn column in row.Table.Columns) { testDataArgs[column.ColumnName] = Convert.ToString(row[column]); } string testName = sheet + " - " + row.ItemArray[0]; return(new TestCaseData(testDataArgs).SetName(testName)); }); foreach (TestCaseData testCaseData in testcases) { yield return(testCaseData); } }
public DataSet ProcessFileForStaging(string FilePath, string ConnectionStr, string StagingTableName, string StoredProcedure, int SqlCommandTimeOut = 30, List <SqlBulkCopyColumnMapping> ColumnMappings = null, string[] IgnoreColumns = null) { DataTable dt = new DataTable(); if (Path.GetExtension(FilePath).ToLower() == ".csv") { dt = ParseCSVFile(FilePath); } else { using (ExcelDataReader r = new ExcelDataReader(FilePath)) { dt.Load(r); } } return(ProcessStagingTableFile(dt, ConnectionStr, StagingTableName, StoredProcedure, SqlCommandTimeOut, ColumnMappings, IgnoreColumns)); }
public void test_read_excel_should_report_data_errors() { ExcelDataReader <InvoiceData> processor = new ExcelDataReader <InvoiceData>(); processor.Source = this.getFilePath("invoice_invalid_data.xlsx"); var process_result = processor.ReadData(); Assert.IsFalse(process_result); Assert.AreEqual(4, processor.Data.Count()); Assert.AreEqual(1, processor.Errors.Count); var error = processor.Errors[0]; Assert.AreEqual(3, error.Count); Assert.AreEqual("unable to convert 'today' to datetime value", error.GetError("Invoice From").Message); Assert.AreEqual("unable to convert 'tomorrow' to datetime value", error.GetError("Invoice To").Message); Assert.AreEqual("unable to convert '300 Million' to number value", error.GetError("Termination Charge").Message); }
private bool ValidateAPFile(string fpath) { bool valid = true; try { List <DispositionRow> rows = ExcelDataReader.GetResearchRows(fpath); int zeroRecords = rows.Count(r => r.InterviewRecordID == 0); valid = (zeroRecords == 0); } catch (Exception e) { valid = false; } return(valid); // return true; }
private bool ValidateMDFile(string fpath) { bool valid = true; try { List <ModificationRow> rows = ExcelDataReader.GetModificationRows(fpath); int zeroRecords = rows.Count(r => r.InterviewRecordID == 0); valid = (zeroRecords != 0); } catch { valid = false; } return(valid); }
public static DataTable GenerateTableFromExcel(string pathToExcel) { //var path = @"s:\OneClickDb\PLC1.xlsx"; var dt = new DataTable(); try { using (var reader = new ExcelDataReader(pathToExcel)) { dt.Load(reader); return(dt); } } catch (Exception e) { Console.WriteLine(e); return(null); } }
public void test_read_excel_should_success() { ExcelDataReader <InvoiceData> processor = new ExcelDataReader <InvoiceData>(); processor.Source = this.getFilePath("invoice.xlsx"); var process_result = processor.ReadData(); Assert.IsTrue(process_result); Assert.AreEqual(5, processor.Data.Count()); Assert.AreEqual(5, processor.TotalRecords); var first_row = processor.Data.First(); Assert.AreEqual("Circuit #1", first_row.CircuitName); Assert.AreEqual("CNY", first_row.CurrencyCode); Assert.AreEqual("2019-08-01 00:00:00", first_row.BillingPeriod.ToString("yyyy-MM-dd HH:mm:ss")); Assert.AreEqual(360.33, first_row.TerminationCharge); Assert.AreEqual(4000.56m, first_row.AccessInstallationCharge); Assert.AreEqual(872.18m, Math.Round(first_row.VAT, 2)); }
private bool ValidateQBFile(string fpath) { bool valid = true; try { var checks = ExcelDataReader.GetQuickbooksChecks(fpath); var clr = checks.Count(c => c.Clr.Equals("Unknown")); valid = clr == 0; } catch (Exception e) { valid = false; } return(valid); // return true; }
private void ProcessFile(FileItem item, bool alreadyOpened = false) => this.MainWindow.Waiting(async(statusBar, callback) => { if (!alreadyOpened) { sourceList.Add(item); } if (FileContent.IsOpened(item)) { callback(); return; } ExcelDataReader reader = container.Get <ExcelDataReader>().Setup(r => { r.Initialize(item.Location, msg => this.MainWindow.Output(msg)); }); ExcelData data = await reader.Read(); FileContent.Add(item, data); callback(); });
public object Get(string dob, string forceUpdate, string callback) { ExcelDataReader e = new ExcelDataReader(); DateTime validDOB; DateTime.TryParse(dob, out validDOB); string spPath = ConfigurationManager.AppSettings["PathToSP"]; if (validDOB.Year < 2) return ConvertToHttpResponse("bad date of birth. dob= " + dob + ", validDOB= " + validDOB.ToShortDateString()); string spDate = Convert.ToDateTime(dob).ToString("yyyy.MM"); string spDateToday = DateTime.Now.ToString("yyyy.MM"); string pathToFile = System.AppDomain.CurrentDomain.BaseDirectory + @"Data\ie_data.xls"; var client = new WebClient(); if (!File.Exists(pathToFile)){ client.DownloadFile(spPath, pathToFile); } DateTime fileCreatedDate = File.GetLastWriteTime(pathToFile); try { //Download the xls file on web if (((DateTime.Now - fileCreatedDate).TotalDays > 28) || forceUpdate == "true") { File.Delete(pathToFile); client.DownloadFile(spPath, pathToFile); } } catch (Exception) { //email error } // Read file DataSet spData = e.convertExelDataDataSet("/Data/ie_data.xls"); DataSet stampData = e.convertExelDataDataSet("/Data/stamps.xlsx"); //Querys DataRow sp_dob = spData.Tables[0].Rows.Cast<DataRow>() .Where(row => row["Column1"].ToString() == spDate) .FirstOrDefault(); DataRow sp_today = spData.Tables[0].Rows.Cast<DataRow>() .Where(row => row["Column1"] != DBNull.Value) .Last(); DataRow stamp_dob = stampData.Tables[0].Rows.Cast<DataRow>() .Where(row => Convert.ToInt32(row["Column2"]) >= validDOB.Year - 2) .FirstOrDefault(); DataRow stamp_today = stampData.Tables[0].Rows.Cast<DataRow>() .Where(row => row["Column2"] != DBNull.Value ) .Last(); //load object Dictionary<string, string> payload = new Dictionary<string, string>(); payload.Add("dob", dob); payload.Add("dobSP", (sp_dob != null) ? sp_dob["Column2"].ToString() : ""); payload.Add("latestSP", (sp_today != null) ? sp_today["Column2"].ToString() : ""); payload.Add("dobStampPrice", stamp_dob["Column3"].ToString()); payload.Add("latestStampPrice", stamp_today["Column3"].ToString()); payload.Add("dobStampImage", stamp_dob["Column4"].ToString()); payload.Add("latestStampImage", stamp_today["Column4"].ToString()); payload.Add("dataUpdated", fileCreatedDate.ToString()); //return ConvertToHttpResponse(JsonConvert.SerializeObject(payload)); string serialized = JsonConvert.SerializeObject(payload); return (String.IsNullOrEmpty(callback)) ? serialized : callback + "(" + serialized + ")"; }
public string Get() { ExcelDataReader e = new ExcelDataReader(); DataSet data = e.convertExelDataDataSet("/Data/ie_data.xls"); return JsonConvert.SerializeObject(data); }
public static IDataTable FromFile( string fileName, Stream stream, long contentLength, CsvConfiguration configuration, int skip = 0, int take = int.MaxValue) { Guard.ArgumentNotEmpty(() => fileName); Guard.ArgumentNotNull(() => stream); Guard.ArgumentNotNull(() => configuration); if (contentLength == 0) { throw Error.Argument("fileName", "The posted file '{0}' does not contain any data.".FormatInvariant(fileName)); } IDataReader dataReader = null; try { var fileExt = System.IO.Path.GetExtension(fileName).ToLowerInvariant(); switch (fileExt) { case ".xlsx": dataReader = new ExcelDataReader(stream, true); // TODO: let the user specify if excel file has headers break; default: dataReader = new CsvDataReader(new StreamReader(stream), configuration); break; } var table = LightweightDataTable.FromDataReader(dataReader, skip, take); if (table.Columns.Count == 0 || table.Rows.Count == 0) { throw Error.InvalidOperation("The posted file '{0}' does not contain any columns or data rows.".FormatInvariant(fileName)); } return table; } catch (Exception ex) { throw ex; } finally { if (dataReader != null) { if (!dataReader.IsClosed) { dataReader.Dispose(); } dataReader = null; } } }
private DataTable ReadPaymentsFromFile(Mapper mapper) { string fileName = Path.GetFileName(txtExcelFilename.Text); DataTable dt = new DataTable(); // check for Excel office version -- old versions if (fileName.ToLower().EndsWith(".xls")) { FileStream stream = new FileStream(txtExcelFilename.Text, FileMode.Open); ExcelDataReader excelReader = new ExcelDataReader(stream); DataSet ds = excelReader.GetDataSet(); dt = ds.Tables[0]; //this stores the mapped column names to the column names in Excel for (int i = 0; i < ds.Tables[0].Columns.Count; i++) { if (mapper.ContainsKey(ds.Tables[0].Rows[0][i].ToString())) { dt.Columns[i].ColumnName = (string)ds.Tables[0].Rows[0][i]; } } stream.Close(); } else { // Office 2007 file parser string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + txtExcelFilename.Text + ";" + "Extended Properties=\"Excel 12.0;HDR=YES;\""; // if you don't want to show the header row (first row) // use 'HDR=NO' in the string string oleSql = "SELECT * FROM [{0}]"; DataSet ds = new DataSet(); using (OleDbConnection oleCnn = new OleDbConnection(connectionString)) { oleCnn.Open(); DataTable schema = oleCnn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string sheet1Name = schema.Rows[0]["TABLE_NAME"].ToString(); oleSql = String.Format(oleSql, sheet1Name); using (OleDbDataAdapter oleDA = new OleDbDataAdapter(oleSql, oleCnn)) { oleDA.Fill(ds); } } dt = ds.Tables[0]; } return dt; }
public void ExcelReaderThrowsIfNotInitialized() { using (var excel = new ExcelDataReader(GetFileStream("testdata.xlsx"), true)) { ExceptionAssert.Throws<InvalidOperationException>(() => { var value = excel["Name"]; }); } }