public void ReadTemplateIntoTableTest()
{
var methodName = DiagnosticsHelper.GetCallingMethodName(1);
var dataSet = new DataSet();
//const string testDataFileName = "CleverLenderLtd Originations Data Feed Template.xlsx.lnk"; // MK: mind the '*.lnk' here!!!...
const string testDataFileName = "CleverLenderLtd PBTL Data Feed Template.xlsx.lnk"; // MK: mind the '*.lnk' here!!!...
ReadTemplate(dataSet, testDataFileName);
var dataTable = dataSet.Tables["ACCOUNTS"];
Assert.AreEqual((dataTable.Columns[0].ToString()).ToUpper(), "A_ApplicationID".ToUpper(), "In " + methodName + " call");
}
public void ReadTemplateBasedExcelAsDataTablesTest()
{
var methodName = DiagnosticsHelper.GetCallingMethodName(1);
//const string testDataFileName = "CleverLenderLtd Originations Data Feed Template.xlsx.lnk"; // MK: mind the '*.lnk' here!!!...
const string testTemplateFileName = "CleverLenderLtd PBTL Data Feed Template.xlsx.lnk"; // MK: mind the '*.lnk' here!!!...
//const string testDataFileName = "ExcelHelperTestData.xlsx";
const string testDataFileName = "CleverLenderLtd_PBTL_08-07-2016.xlsx";
var dataSet = new DataSet();
//var dictionaryTables = new DataSet();
var folderPath = ReflectionHelper.GetExecutingAssemblyFolder(Assembly.GetExecutingAssembly());
var testDataFullFileName = Path.Combine(folderPath, testDataFileName);
if (!File.Exists(testDataFullFileName))
{
Assert.Inconclusive("The test data file {0} does not exist in " + methodName + " call", testDataFullFileName);
}
ReadTemplate(dataSet, testTemplateFileName);
//ReadDictionary(dictionaryTables, testDictionaryFileName);
var supportedSheetNames = new List <string> {
"ACCOUNTS", "PROPERTIES", "CONTACTS"
};
var errorList = ExcelHelper.ReadTemplateBasedExcelAsDataTables(dataSet, testDataFullFileName, supportedSheetNames);
Assert.IsTrue(errorList.Count == 0, "There is unexpected errors in " + methodName + " call!..");
var dataTable = dataSet.Tables["ACCOUNTS"];
Assert.AreEqual((dataTable.Rows[0]).ItemArray[0].ToString(), "100065143", "In " + methodName + " call");
Assert.AreEqual((dataTable.Rows[1]).ItemArray[0].ToString(), "100071200", "In " + methodName + " call");
var dataTable2 = dataSet.Tables["CONTACTS"];
Assert.AreEqual((dataTable2.Rows[0]).ItemArray[7].ToString(), "09/04/1966", "In " + methodName + " call");
Assert.AreEqual((dataTable2.Rows[1]).ItemArray[7].ToString(), "10/07/1989", "In " + methodName + " call");
}
public void ReadDictionaryIntoTableTest()
{
var methodName = DiagnosticsHelper.GetCallingMethodName(1);
const string testDataFileName = "CleverLenderLtd Originations Data Feed Dictionary.xlsx.lnk"; // MK: mind the '*.lnk' here!!!...
var dataSet = new DataSet();
ReadDictionary(dataSet, testDataFileName);
var dataTable = dataSet.Tables["ACCOUNTS"];
Assert.AreEqual((dataTable.Columns[0].ToString()).ToUpper(), DictionaryColumnName.ToUpper(), "In " + methodName + " call");
Assert.AreEqual((dataTable.Columns[1].ToString()).ToUpper(), DictionaryDataType.ToUpper(), "In " + methodName + " call");
Assert.AreEqual(((dataTable.Rows[0]).ItemArray[0].ToString()).Trim().ToUpper(), "ApplicationID".Trim().ToUpper(), "In " + methodName + " call");
Assert.AreEqual(((dataTable.Rows[0]).ItemArray[1].ToString()).Trim().ToUpper(), "INT".Trim().ToUpper(), "In " + methodName + " call");
Assert.AreEqual(((dataTable.Rows[1]).ItemArray[0].ToString()).Trim().ToUpper(), "Further Advance Flag".Trim().Replace(" ", "").ToUpper(), "In " + methodName + " call");
Assert.AreEqual(((dataTable.Rows[1]).ItemArray[1].ToString()).Trim().ToUpper(), "Y/N".Trim().ToUpper(), "In " + methodName + " call");
}
public void GetCellValueTest()
{
var methodName = DiagnosticsHelper.GetCallingMethodName(1);
const string testDataFileName = "ExcelHelperTestData.xlsx";
const string sheetName1 = "DateTime Types Test";
const string sheetName2 = "Other Data Types Test";
var dataSet = new DataSet();
var folderPath = ReflectionHelper.GetExecutingAssemblyFolder(Assembly.GetExecutingAssembly());
var testDataFullFileName = Path.Combine(folderPath, testDataFileName);
if (!File.Exists(testDataFullFileName))
{
Assert.Inconclusive("The test data file {0} does not exist", testDataFullFileName);
}
// I am checking if the file is open from someone else
try
{
SpreadsheetDocument workbook = SpreadsheetDocument.Open(testDataFullFileName, false);
workbook.Close();
}
catch
{
Assert.Inconclusive("The test data file {0} is open from a differnet resource", testDataFullFileName);
}
var dataTable1 = new DataTable(sheetName1);
var dataTable2 = new DataTable(sheetName2);
dataTable1.Columns.Add("Value");
dataTable1.Columns.Add("CellStyleIndex");
dataTable1.Columns.Add("numberFormatId");
dataTable1.Columns.Add("formatCode");
dataTable2.Columns.Add("Value");
dataTable2.Columns.Add("CellStyleIndex");
dataTable2.Columns.Add("numberFormatId");
dataTable2.Columns.Add("formatCode");
dataSet.Tables.Add(dataTable1);
dataSet.Tables.Add(dataTable2);
var errorList = ReadTemplateBasedExcelAsDataTablesTest(dataSet, testDataFullFileName);
Assert.IsTrue(errorList.Count == 0, "There is unexpected errors in " + methodName + " call!..");
Assert.AreEqual((dataTable1.Rows[0]).ItemArray[0].ToString(), "09/04/1966", "In " + methodName + " call");
Assert.AreEqual((dataTable1.Rows[1]).ItemArray[0].ToString(), "10/07/1989", "In " + methodName + " call");
Assert.AreEqual((dataTable1.Rows[2]).ItemArray[0].ToString(), "09/07/1972", "In " + methodName + " call");
Assert.AreEqual((dataTable1.Rows[3]).ItemArray[0].ToString(), "21/08/1965", "In " + methodName + " call");
Assert.AreEqual((dataTable1.Rows[4]).ItemArray[0].ToString(), "08/11/1964", "In " + methodName + " call");
Assert.AreEqual((dataTable1.Rows[5]).ItemArray[0].ToString(), "11/12/1955", "In " + methodName + " call");
Assert.AreEqual((dataTable1.Rows[6]).ItemArray[0].ToString(), "24/11/1962", "In " + methodName + " call");
Assert.AreEqual((dataTable1.Rows[7]).ItemArray[0].ToString(), "25/11/1962", "In " + methodName + " call");
Assert.AreEqual((dataTable1.Rows[8]).ItemArray[0].ToString(), "08/07/2016", "In " + methodName + " call");
Assert.AreEqual((dataTable1.Rows[9]).ItemArray[0].ToString(), "08/07/2016", "In " + methodName + " call");
Assert.AreEqual((dataTable2.Rows[0]).ItemArray[0].ToString(), "100065143", "In " + methodName + " call");
Assert.AreEqual((dataTable2.Rows[1]).ItemArray[0].ToString(), "N", "In " + methodName + " call");
Assert.AreEqual((dataTable2.Rows[2]).ItemArray[0].ToString(), "Y", "In " + methodName + " call");
Assert.AreEqual((dataTable2.Rows[3]).ItemArray[0].ToString(), "230580", "In " + methodName + " call");
Assert.AreEqual((dataTable2.Rows[4]).ItemArray[0].ToString(), "230581", "In " + methodName + " call");
Assert.AreEqual((dataTable2.Rows[5]).ItemArray[0].ToString(), "Homer Simpson", "In " + methodName + " call");
Assert.AreEqual((dataTable2.Rows[6]).ItemArray[0].ToString(), "126827.5", "In " + methodName + " call");
Assert.AreEqual((dataTable2.Rows[7]).ItemArray[0].ToString(), "", "In " + methodName + " call");
Assert.AreEqual((dataTable2.Rows[8]).ItemArray[0].ToString(), " ", "In " + methodName + " call");
Assert.AreEqual((dataTable2.Rows[9]).ItemArray[0].ToString(), "09890048", "In " + methodName + " call");
}