/// <summary>Parses the sheet definition values into a class.</summary> /// <param name="parseValue">The value that must be parsed.</param> /// <returns></returns> public pcExcelDef_Sheet SheetDef_Parse(string parseValue) { // {Sheet}->"Q22";{Data}->|A5|;|A10|->"Name or Nickname:";|A14|->"1";|A35|->"22";|K12|->"Total" // ============================================================================================ if (parseValue.Contains("{Sheet}->") == false) { "Error! '{Sheet}->' was not found.".zException_Show(); } if (parseValue.Contains(";{Data}->") == false) { "Error! ';{Data}->' was not found.".zException_Show(); } if (parseValue.Contains("|->") == false) { "Error! '|->' cell reference was not found.".zException_Show(); } var valueList = parseValue.zConvert_Str_ToListStr(";|"); if (valueList.Count < 3) { "Error! No cell references checks defined.".zException_Show(); } var result = new pcExcelDef_Sheet(); // SheetName ]================================= var dataCellAddress = valueList[0]; "}->".zVar_Next(ref dataCellAddress, true); var sheetName = ";{Data}->".zVar_Next(ref dataCellAddress, true); if (sheetName.Contains("\"") == false) { $"Error! Sheet ref '{sheetName}' does not contain quotes.".zException_Show(); // This is extra safety test } result.SheetName = sheetName.Replace("\"", ""); // DataCellAddress ]=========================== result.DataCellAddress = dataCellAddress.Replace("|", ""); for (int ii = 1; ii < valueList.Count; ii++) { var val = valueList[ii]; var address = val.zvar_Id("|->"); var value = val.zvar_Value("|->"); if (value.Contains("\"") == false) { $"Error! Cell value '{val}' does not contain quotes.".zException_Show(); } var cell = new pcExcelDef_Cell { CellAddress = address, CellValue = value.Replace("\"", "") }; result.Cells.Add(cell); } return(result); }
/// <summary>Test the Excel data file if it meets the sheets definition.</summary> /// <param name="data">The data.</param> /// <param name="sheetDef">The sheet definition.</param> /// <returns></returns> public bool DataIntegrity_Check(pcExcelData_ data, pcExcelDef_Sheet sheetDef) { foreach (pcExcelDef_Cell cell in sheetDef.Cells) { var value = data.Value_Get(cell.CellAddress); if (value != cell.CellValue) { return(false); } } return(true); }
/// <summary>Create a Dashboard from Excel input sheets.</summary> /// <param name="folder">The folder.</param> /// <param name="dashboardInputFile">The input excel file.</param> /// <param name="dashboardResultFile">The dashboard result file.</param> public void Dashboard_FromSheets(string folder, string dashboardInputFile, string dashboardResultFile = "") { #region Load config file and parse macros ]======================= var fileConfig = folder + dashboardInputFile; if (_lamed.lib.IO.File.Exists(fileConfig) == false) { $"Error! File '{fileConfig}' does not exists.".zException_Show(); // Test input } var fileCompile = fileConfig.Replace(".xlsx", "_Compile.xlsx"); if (dashboardResultFile == "") { dashboardResultFile = fileConfig.Replace(".xlsx", "_Result.xlsx"); } _lamed.lib.IO.File.Copy(fileConfig, fileCompile, true); // Copy config file to _Compile pcExcelData_ excelConfig = _lamed.lib.Excel.IO_Read.ExcelFile_LoadAsExcelData(fileCompile); // Load the file // Compile the refeneces string errorMsg; _lamed.lib.Excel.Macro.Compile(excelConfig, out errorMsg); // Get the reference _lamed.lib.Excel.Macro.ExcelFile_Merge(fileCompile, excelConfig, enExcel_MergeType.ExecuteMacro); // =========================================================================================== #endregion #region Checks & get input files // ================================================== // Check if "{Sheet}->" exists // {Sheet}->"Q22",{Data}->|A5|,|A10|->"Name or Nickname:",|A14|->"1",|A35|->"22",|K12|->"Total" string sheetDefStr; if (excelConfig.Find_First(out sheetDefStr, "{Sheet}->") == false) { "Error! Unable to find '{Sheet}->' in Excel sheet".zException_Show(); // Unit test needed for this line } pcExcelDef_Sheet sheetDef = _lamed.lib.Excel.Macro.MacroItem.SheetDef_Parse(sheetDefStr); List <string> filesGood = Dashbaord_FindSheetFiles(folder, sheetDef); // Get all Excel files from folder that meets search condition #endregion #region For all files apply macro references // ================================================== _lamed.lib.IO.File.Copy(fileCompile, dashboardResultFile, true); var startAddress = sheetDef.DataCellAddress; foreach (string fileData in filesGood) { var dataExcel = _lamed.lib.Excel.IO_Read.ExcelFile_LoadAsExcelData(fileData); _lamed.lib.Excel.Macro.ExcelFile_Merge(dashboardResultFile, dataExcel, enExcel_MergeType.InsertReferences, "", startAddress); startAddress = _lamed.lib.Excel.Adress.CellAddress_NextRow(startAddress); } #endregion }
/// <summary>Get all Excel files from folder that meets the sheet definition.</summary> /// <param name="folder">The folder.</param> /// <param name="sheetDef">The sheet definition.</param> /// <returns></returns> public List <string> Dashbaord_FindSheetFiles(string folder, pcExcelDef_Sheet sheetDef) { IEnumerable <string> files = _lamed.lib.IO.Search.Files(folder, "*.xlsx"); // Find Excel files that meets conditions var filesGood = new List <string>(); foreach (string fileInput in files) { pcExcelData_ dataTest = _lamed.lib.Excel.IO_Read.ExcelFile_LoadAsExcelData(fileInput); if (_lamed.lib.Excel.Macro.DataIntegrity_Check(dataTest, sheetDef)) { filesGood.Add(fileInput); } } return(filesGood); }
public void SheetDef_Parse_Test() { #region Test: {Sheet}->"Q22";{Data}->|A5|;|A10|->"Name or Nickname:";|A14|->"1";|A35|->"22";|K12|->"Total" // ====================================================================================== var input = "{Sheet}->\"Q22\";{Data}->|A5|;|A10|->\"Name or Nickname:\";|A14|->\"1\";|A35|->\"22\";|K12|->\"Total\""; pcExcelDef_Sheet sheetDef = _lamed.lib.Excel.Macro.MacroItem.SheetDef_Parse(input); Assert.False(sheetDef == null, "Class 'MsExcelDef_Sheet' is NULL."); Assert.Equal("Q22", sheetDef.SheetName); Assert.Equal("A5", sheetDef.DataCellAddress); Assert.Equal("A10", sheetDef.Cells[0].CellAddress); Assert.Equal("Name or Nickname:", sheetDef.Cells[0].CellValue); Assert.Equal("A14", sheetDef.Cells[1].CellAddress); Assert.Equal("1", sheetDef.Cells[1].CellValue); Assert.Equal("A35", sheetDef.Cells[2].CellAddress); Assert.Equal("22", sheetDef.Cells[2].CellValue); Assert.Equal("K12", sheetDef.Cells[3].CellAddress); Assert.Equal("Total", sheetDef.Cells[3].CellValue); #endregion }
public void SheetDef_Test() { #region Get Input // =================================== var excelFile = "General_Input.xlsx"; Config_Info.Config_File_Test(_Debug); // Test if file exists ===================================== string folderTestCases = Config_Info.Config_File_Test(_Debug, @"Excel_List/"); //string folderApplication; //string folderExcelTestCases; //pcTest_Configuration config; //_lamed.lib.Test.ConfigSettings(out folderApplication, out folderExcelTestCases, out config); var file = folderTestCases + excelFile; DebugLog("File: " + file, true); Assert.True(_lamed.lib.IO.File.Exists(file), $"File: '{file}' does not exist!"); // ============================= //var folder = folderExcelTestCases + @"Excel_list\"; var fileCompile = folderTestCases + excelFile; pcExcelData_ excelData = _lamed.lib.Excel.IO_Read.ExcelFile_LoadAsExcelData(fileCompile); #endregion #region Test Sheet definition string // =================================================== string sheetDefStr, sheetDefStr2; if (excelData.Find_First(out sheetDefStr, "{Sheet}->") == false) { "Error! Unable to find '{Sheet}->' in Excel sheet".zException_Show(); } Assert.Equal("{Sheet}->\"Q22\";{Data}->|A5|;|A10|->\"Name or Nickname:\";|A14|->\"1\";|A35|->\"22\";|K12|->\"Total\"", sheetDefStr); Assert.Equal(false, excelData.Find_First(out sheetDefStr2, "{Invalid search}->")); #endregion #region Test sheet definition // ================================================ pcExcelDef_Sheet sheetDef = _lamed.lib.Excel.Macro.MacroItem.SheetDef_Parse(sheetDefStr); Assert.Equal(sheetDef.SheetName, "Q22"); Assert.Equal(sheetDef.DataCellAddress, "A5"); Assert.Equal(sheetDef.Cells[0].CellAddress, "A10"); Assert.Equal(sheetDef.Cells[0].CellValue, "Name or Nickname:"); Assert.Equal(sheetDef.Cells[1].CellAddress, "A14"); Assert.Equal(sheetDef.Cells[1].CellValue, "1"); Assert.Equal(sheetDef.Cells[2].CellAddress, "A35"); Assert.Equal(sheetDef.Cells[2].CellValue, "22"); Assert.Equal(sheetDef.Cells[3].CellAddress, "K12"); Assert.Equal(sheetDef.Cells[3].CellValue, "Total"); #endregion #region Test found Excel files // ====================================== List <string> filesGood = _lamed.lib.Excel.Macro.Dashbaord_FindSheetFiles(folderTestCases, sheetDef); Assert.Equal(filesGood.Count, 8); Assert.Equal(_lamed.lib.IO.Parts.File(filesGood[0]), "Q22_Bruce.xlsx"); Assert.Equal(_lamed.lib.IO.Parts.File(filesGood[1]), "Q22_Charles.xlsx"); Assert.Equal(_lamed.lib.IO.Parts.File(filesGood[2]), "Q22_Danie.xlsx"); Assert.Equal(_lamed.lib.IO.Parts.File(filesGood[3]), "Q22_Erik.xlsx"); Assert.Equal(_lamed.lib.IO.Parts.File(filesGood[4]), "Q22_Henk.xlsx"); Assert.Equal(_lamed.lib.IO.Parts.File(filesGood[5]), "Q22_Jerrie.xlsx"); Assert.Equal(_lamed.lib.IO.Parts.File(filesGood[6]), "Q22_Joe.xlsx"); Assert.Equal(_lamed.lib.IO.Parts.File(filesGood[7]), "Q22_Leon.xlsx"); #endregion #region Exceptions // Sheet var sheetDefError1 = "{Sheet}-->\"Q22\";{Data}->|A5|;|A10|->\"Name or Nickname:\";|A14|->\"1\";|A35|->\"22\";|K12|->\"Total\""; var ex = Assert.Throws <InvalidOperationException>(() => _lamed.lib.Excel.Macro.MacroItem.SheetDef_Parse(sheetDefError1)); Assert.Equal("Error! '{Sheet}->' was not found.", ex.Message); // Data var sheetDefError2 = "{Sheet}->\"Q22\";{Data}-->|A5|;|A10|->\"Name or Nickname:\";|A14|->\"1\";|A35|->\"22\";|K12|->\"Total\""; ex = Assert.Throws <InvalidOperationException>(() => _lamed.lib.Excel.Macro.MacroItem.SheetDef_Parse(sheetDefError2)); Assert.Equal("Error! ';{Data}->' was not found.", ex.Message); // '|->' var sheetDefError3 = "{Sheet}->\"Q22\";{Data}->|A5|;|A10->\"Name or Nickname:\";|A14->\"1\";|A35->\"22\";|K12->\"Total\""; ex = Assert.Throws <InvalidOperationException>(() => _lamed.lib.Excel.Macro.MacroItem.SheetDef_Parse(sheetDefError3)); Assert.Equal("Error! '|->' cell reference was not found.", ex.Message); // ';|' var sheetDefError4 = "{Sheet}->\"Q22\";{Data}->|A5|; |A10|->\"Name or Nickname:\"; |A14|->\"1\"; |A35|->\"22\";|K12|->\"Total\""; ex = Assert.Throws <InvalidOperationException>(() => _lamed.lib.Excel.Macro.MacroItem.SheetDef_Parse(sheetDefError4)); Assert.Equal("Error! No cell references checks defined.", ex.Message); // Sheet name has not quotes var sheetDefError5 = "{Sheet}->Q22;{Data}->|A5|;|A10|->\"Name or Nickname:\";|A14|->\"1\";|A35|->\"22\";|K12|->\"Total\""; ex = Assert.Throws <InvalidOperationException>(() => _lamed.lib.Excel.Macro.MacroItem.SheetDef_Parse(sheetDefError5)); Assert.Equal("Error! Sheet ref 'Q22' does not contain quotes.", ex.Message); #endregion }