public void ExcelDataReaderRead() { using (ExcelFileReaderInterop test = new ExcelFileReaderInterop()) { test.Open(m_ValidSetting, CancellationToken.None, true); test.Read(); // 1 German Fluent TRUE 01/01/2001 Assert.AreEqual("1", test.GetString(0)); Assert.AreEqual(1d, test.GetDouble(0)); // The natural Excel format is double But m_ValidSetting defines it as int, so an int should // be returned Assert.AreEqual(1, (int)test.GetValue(0)); Assert.IsFalse(test.IsDBNull(0)); Assert.AreEqual("German", test.GetString(1)); Assert.AreEqual("Fluent", test.GetString(2)); Assert.AreEqual("True", test.GetString(3), true); Assert.IsTrue(test.GetBoolean(3)); Assert.IsInstanceOfType(test.GetValue(3), typeof(Boolean)); Assert.AreEqual(string.Format(System.Globalization.CultureInfo.CurrentCulture, "{0:d}", new DateTime(2001, 01, 01)), test.GetString(4)); Assert.IsInstanceOfType(test.GetValue(4), typeof(DateTime)); } }
public void ExcelDataReaderReadTreatTextNullAsNull() { ExcelFile setting = new ExcelFile(); setting.FileName = System.IO.Path.Combine(m_ApplicationDirectory, "BasicExcel.xls"); setting.SheetName = "Basic"; // setting.TreatTextNullAsNull = true; using (ExcelFileReaderInterop test = new ExcelFileReaderInterop()) { test.Open(setting, CancellationToken.None, true); test.Read(); Assert.AreEqual(1, test.GetInt32(0)); Assert.AreEqual("German", test.GetString(1)); Assert.AreEqual(new DateTime(1901, 1, 5), test.GetDateTime(2)); Assert.AreEqual(276d, test.GetDouble(3)); Assert.AreEqual(0.94, test.GetDouble(4)); Assert.AreEqual(true, test.GetBoolean(5)); test.Read(); test.Read(); Assert.AreEqual(4, test.GetInt32(0)); Assert.IsTrue(test.IsDBNull(1)); Assert.IsTrue(string.IsNullOrEmpty(test.GetString(1))); Assert.AreEqual(DBNull.Value, test.GetValue(1)); Assert.IsTrue(test.IsDBNull(2)); Assert.AreEqual(true, test.GetBoolean(5)); test.Read(); test.Read(); Assert.IsTrue(string.IsNullOrEmpty(test.GetString(1))); Assert.AreEqual(DBNull.Value, test.GetValue(1)); } }
public void ExcelDataReaderRead_GetSheets() { string[] sheets; using (IExcelFileReader reader = new ExcelFileReaderInterop()) { reader.Open(new ExcelFile() { FileName = System.IO.Path.Combine(m_ApplicationDirectory, "Small.xlsx") }, CancellationToken.None, false); sheets = reader.Sheets; } Assert.AreEqual(3, sheets.Length); bool contains1 = false; bool contains2 = false; foreach (string sheetname in sheets) { if (sheetname.Equals("Sheet1", StringComparison.OrdinalIgnoreCase)) { contains1 = true; } if (sheetname.Equals("No2", StringComparison.OrdinalIgnoreCase)) { contains2 = true; } } Assert.IsTrue(contains1); Assert.IsTrue(contains2); }
public void ExcelDataReaderRangeTrim() { m_ValidSetting.SheetName = "Trimming"; m_ValidSetting.SheetRange = "B4:D9"; m_ValidSetting.TrimmingOption = TrimmingOption.All; using (ExcelFileReaderInterop test = new ExcelFileReaderInterop()) { test.Open(m_ValidSetting, CancellationToken.None, true); test.Read(); // 1 German Fluent TRUE 01/01/2001 Assert.AreEqual("1", test.GetString(0)); // The natural Excel format is double But m_ValidSetting defines it as int, so an int should // be returned Assert.AreEqual(1, (int)test.GetValue(0)); Assert.IsFalse(test.IsDBNull(0)); Assert.AreEqual("German", test.GetValue(1)); Assert.AreEqual("Fluent", test.GetValue(2)); test.Read(); test.Read(); Assert.AreEqual("4", test.GetString(0)); Assert.AreEqual(4, (int)test.GetValue(0)); Assert.AreEqual("German", test.GetValue(1)); } }
public void ExcelDataReaderGetSchemaTable_DataConversion() { ExcelFile setting = new ExcelFile(); setting.FileName = System.IO.Path.Combine(m_ApplicationDirectory, "BasicExcel.xls"); setting.SheetName = "Basic"; Column columnFormat = new Column() { Name = "IsNativeLang", DataType = DataType.Boolean }; setting.ColumnAdd(columnFormat); using (ExcelFileReaderInterop test = new ExcelFileReaderInterop()) { test.Open(m_ValidSetting, CancellationToken.None, true); var dt = test.GetSchemaTable(); foreach (System.Data.DataRow dataRow in dt.Rows) { if (columnFormat.Name.Equals((string)dataRow[System.Data.Common.SchemaTableColumn.ColumnName], StringComparison.OrdinalIgnoreCase)) { Assert.ReferenceEquals(dataRow[System.Data.Common.SchemaTableColumn.DataType], columnFormat.DataType.GetNetType()); } } } }
public void ExcelDataReaderCreateOK() { using (ExcelFileReaderInterop test = new ExcelFileReaderInterop()) { test.Open(m_ValidSetting, CancellationToken.None, true); Assert.IsFalse(test.IsClosed); } }
public void ExcelDataReaderRead_GetSheets2() { string[] sheets; using (IExcelFileReader reader = new ExcelFileReaderInterop()) { reader.Open(new ExcelFile() { FileName = System.IO.Path.Combine(m_ApplicationDirectory, "BasicExcel.xls") }, CancellationToken.None, false); sheets = reader.Sheets; } Assert.AreEqual(1, sheets.Length); Assert.AreEqual("Basic", sheets[0]); }
public void SaveSheetAsTextWithRange() { ExcelFile file = new ExcelFile(); file.FileName = System.IO.Path.Combine(m_ApplicationDirectory, "DateTimeCombineExcel.xlsx"); file.SheetName = "Combinations"; file.SheetRange = "A12:D24"; file.ShowProgress = false; using (ExcelFileReaderInterop reader = new ExcelFileReaderInterop()) { reader.Open(file, System.Threading.CancellationToken.None, false); reader.SaveSheetAsText(null, null, m_Output); } Assert.IsTrue(File.Exists(m_Output)); }
public void OpenColoseWithRange() { ExcelFile file = new ExcelFile(); file.FileName = System.IO.Path.Combine(m_ApplicationDirectory, "DateTimeCombineExcel.xlsx"); file.SheetName = "Combinations"; file.SheetRange = "A12:D24"; file.ShowProgress = false; using (ExcelFileReaderInterop reader = new ExcelFileReaderInterop()) { reader.Open(file, System.Threading.CancellationToken.None, true); Assert.IsFalse(reader.IsClosed); reader.Close(); Assert.IsTrue(reader.IsClosed); } }
public void ExcelDataReaderCreate_SheetWrong() { m_ValidSetting.SheetName = "****"; using (ExcelFileReaderInterop test = new ExcelFileReaderInterop()) { try { test.Open(m_ValidSetting, CancellationToken.None, true); } catch (ApplicationException) { } catch (Exception) { Assert.Fail("Wrong Exception Type"); } } }
public void SaveSheetAsTextWithRangeAndProcess() { ExcelFile file = new ExcelFile(); file.FileName = System.IO.Path.Combine(m_ApplicationDirectory, "DateTimeCombineExcel.xlsx"); file.SheetName = "Combinations"; file.SheetRange = "B1:B5"; file.ShowProgress = false; using (ExcelFileReaderInterop reader = new ExcelFileReaderInterop()) { using (FormProcessDisplay disp = new FormProcessDisplay("Test", null, null)) { reader.ProcessDisplay = disp; reader.Open(file, System.Threading.CancellationToken.None, true); reader.SaveSheetAsText(disp, null, m_Output); } } Assert.IsTrue(File.Exists(m_Output)); }
public void OpenwithoutRangeProcess() { ExcelFile file = new ExcelFile(); file.FileName = System.IO.Path.Combine(m_ApplicationDirectory, "DateTimeCombineExcel.xlsx"); file.SheetName = "Combinations"; file.ShowProgress = true; file.HasFieldHeader = true; using (ExcelFileReaderInterop reader = new ExcelFileReaderInterop()) { using (FormProcessDisplay disp = new FormProcessDisplay("Test", null, null)) { reader.ProcessDisplay = disp; reader.Open(file, System.Threading.CancellationToken.None, true); Assert.IsFalse(reader.IsClosed); reader.Close(); Assert.IsTrue(reader.IsClosed); } } }
public void ExcelFileReaderOpenWrongSheetName() { try { ExcelFile file = new ExcelFile(); file.FileName = System.IO.Path.Combine(m_ApplicationDirectory, "DateTimeCombineExcel.xlsx"); file.SheetName = "Combin ations"; file.SheetRange = null; file.ShowProgress = false; using (ExcelFileReaderInterop reader = new ExcelFileReaderInterop()) { reader.Open(file, System.Threading.CancellationToken.None, false); reader.SaveSheetAsText(null, null, m_Output); } } catch (Exception) { return; } Assert.Fail("Expected Exception"); }
public void ExcelDataReader_ReadDateTime() { ExcelFile setting = new ExcelFile(); setting.FileName = System.IO.Path.Combine(m_ApplicationDirectory, "DateTimeCombineExcel.xlsx"); setting.SheetName = "Combinations"; setting.ColumnAdd(new Column() { Name = "Date", DataType = DataType.DateTime, DateFormat = @"dd/MM/yyyy HH:mm;HH:mm", TimePart = @"Time", TimePartFormat = @"HH:mm;dd/MM/yyyy HH:mm", DateSeparator = ".", TimeSeparator = ":" }); setting.ColumnAdd(new Column() { Name = "Result", DataType = DataType.DateTime }); using (ExcelFileReaderInterop test = new ExcelFileReaderInterop()) { test.Open(setting, CancellationToken.None, true); Assert.IsFalse(test.IsClosed, "Can not open file"); int row = 0; while (test.Read()) { row++; if (test.GetString(0) == null && test.GetString(1) == null) { Assert.AreEqual(DBNull.Value, test.GetValue(2)); } else { var val = test.GetValue(2); var res = test.GetValue(4); if (val != DBNull.Value && res != DBNull.Value) { Assert.AreEqual((DateTime)res, (DateTime)val, "Wrong result for row " + row.ToString()); } } } } }
public void ExcelDataReaderGetSchemaTable_NoDataConversion() { ExcelFile setting = new ExcelFile(); setting.FileName = System.IO.Path.Combine(m_ApplicationDirectory, "BasicExcel.xls"); setting.SheetName = "Basic"; using (ExcelFileReaderInterop test = new ExcelFileReaderInterop()) { test.Open(setting, CancellationToken.None, true); var dt = test.GetSchemaTable(); foreach (System.Data.DataRow dataRow in dt.Rows) { if (((string)dataRow[System.Data.Common.SchemaTableColumn.ColumnName]).Equals("IsNativeLang", StringComparison.OrdinalIgnoreCase)) { Assert.ReferenceEquals(dataRow[System.Data.Common.SchemaTableColumn.DataType], typeof(bool)); } } } }
public void ExcelDataReader_ReadDate() { ExcelFile setting = new ExcelFile(); setting.FileName = System.IO.Path.Combine(m_ApplicationDirectory, "DateTimeCombineExcel.xlsx"); setting.SheetName = "Combinations"; setting.ColumnAdd(new Column() { Name = "Date", DataType = DataType.DateTime, DateFormat = @"dd/MM/yyyy HH:mm;HH:mm", DateSeparator = ".", TimeSeparator = ":" }); setting.TreatTextAsNull = string.Empty; using (ExcelFileReaderInterop test = new ExcelFileReaderInterop()) { test.Open(setting, System.Threading.CancellationToken.None, true); Assert.IsFalse(test.IsClosed); int row = 0; while (test.Read()) { row++; if (test.GetString(0) == null || test.GetString(0).Equals("Null", StringComparison.OrdinalIgnoreCase)) { Assert.AreEqual(DBNull.Value, test.GetValue(2)); } else { var val = test.GetValue(2); Assert.IsNotNull(val); if (!test.GetValue(0).ToString().Equals("Null", StringComparison.OrdinalIgnoreCase)) { Assert.IsInstanceOfType(val, typeof(DateTime), "Did not return DateTime for row " + row.ToString()); } } } } }