/// <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
}
