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());
}
}
}
}
}