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