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 Init() { m_ValidSetting.FileName = System.IO.Path.Combine(m_ApplicationDirectory, "Small.xlsx"); m_ValidSetting.SheetName = "Sheet1"; m_ValidSetting.ColumnAdd(new Column() { Name = "EmpID", DataType = DataType.Integer }); }
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 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()); } } } } }