/// <summary>Gets the cell value</summary>
/// <param name="excelData">The excel data.</param>
/// <param name="col">The col.</param>
/// <param name="row">The row.</param>
/// <returns></returns>
public string Value_Get(pcExcelData_ excelData, int col, int row)
{
var row1 = excelData.Row(row);
var result = row1[col - 1];
return(result.Trim());
}
public void csvLoadFile_Test()
{
// Load a sample set
var data = new pcExcelData_();
var lines = new List <string>();
lines.Add("A1,B1,C1,D1");
lines.Add("A2,B2,C2,D2");
lines.Add("A3,B3,C3,D3");
lines.Add("A4,B4,C4,D4");
data.csvLoadFromLines(lines.ToArray());
#region Test (1,1); A1
// ====================
string cell1 = data.Value_Get(1, 1);
Assert.Equal("A1", cell1);
string cell2 = data.Value_Get("A1");
Assert.Equal("A1", cell2);
#endregion
#region Test C2; D3, D4
// ====================
Assert.Equal("C2", data.Value_Get("C2"));
Assert.Equal("D3", data.Value_Get("D3"));
Assert.Equal("D4", data.Value_Get("D4"));
#endregion
}
public void Value_Set(pcExcelData_ excelData, string cellRef = "A1", string value = "")
{
int col, row;
LamedalCore_.Instance.lib.Excel.Adress.ColRow_AsInt(out col, out row, cellRef);
Value_Set(excelData, col, row, value);
}
private readonly LamedalCore_ _lamed = LamedalCore_.Instance; // system library
/// <summary>Set the Cell value for the worksheet.</summary>
/// <param name="excelData">The excel data.</param>
/// <param name="cellName">The cell name</param>
/// <returns>Cell</returns>
public Cell WorkSheet_Cell(pcExcelData_ excelData, string cellName)
{
Worksheet sheet = Worksheet_FromExcelData(excelData);
Cell result = sheet.Cells[cellName];
return(result);
}
/// <summary>Compares two data sheets and return the differences.</summary>
/// <param name="input1">The input1.</param>
/// <param name="result1">The result1.</param>
/// <param name="returnValue">The return value.</param>
/// <returns></returns>
public List <string> CompareDataSheet(pcExcelData_ input1, pcExcelData_ result1, enExcel_FindReturnValue returnValue = enExcel_FindReturnValue.CellAddress)
{
var result = new List <string>();
var rowNo = 1;
foreach (List <string> row in input1.Rows)
{
var colNo = 1;
foreach (string value in row)
{
var resultValue = result1.Value_Get(colNo, rowNo);
if (value != resultValue)
{
var address = _lamed.lib.Excel.Adress.CellAddress(colNo, rowNo);
var valueDiff = $"Value '{value}' != '{resultValue}'";
switch (returnValue)
{
case enExcel_FindReturnValue.CellAddress: result.Add(address); break;
case enExcel_FindReturnValue.CellValue: result.Add(valueDiff); break;
case enExcel_FindReturnValue.CellAddressAndValue: result.Add(address + " -> " + valueDiff); break;
default: throw new Exception($"Argument '{nameof(returnValue)}' error.");
}
}
colNo++;
}
rowNo++;
}
return(result);
}
/// <summary>Execute the Macros in the config file.</summary>
/// <param name="dataSource">The data source.</param>
/// <param name="dataConfig">The data configuration.</param>
public void Execute_Data(pcExcelData_ dataSource, pcExcelData_ dataConfig)
{
foreach (List <string> row in dataConfig.Rows)
{
Execute_Row(dataSource, row);
}
}
/// <summary>Compares two data sheets and return the differences.</summary>
/// <param name="inputFile">The file input.</param>
/// <param name="inputSheet">The input sheet.</param>
/// <param name="resultFile">The result file.</param>
/// <param name="resultSheet">The result sheet.</param>
/// <param name="returnValue">The return value.</param>
/// <returns></returns>
public List <string> CompareDataSheet(string inputFile, string inputSheet, string resultFile, string resultSheet, enExcel_FindReturnValue returnValue = enExcel_FindReturnValue.CellAddress)
{
pcExcelData_ input1 = ExcelFile_LoadAsExcelData(inputFile, inputSheet);
pcExcelData_ result1 = ExcelFile_LoadAsExcelData(resultFile, resultSheet);
return(_lamed.lib.Excel.Data.CompareDataSheet(input1, result1));
}
/// <summary>Set the column width of the sheet.</summary>
/// <param name="excelData">The excel data.</param>
/// <param name="colName">Name of the col.</param>
/// <param name="colWidth">Width of the col.</param>
public void WorkSheet_ColumnWidth(pcExcelData_ excelData, string colName, double colWidth)
{
var sheet = Worksheet_FromExcelData(excelData);
var colNo = _lamed.lib.Excel.Adress.ColName_2Int(colName);
sheet.ColumnWidths[colNo - 1] = colWidth;
}
public void ExcelFile_LoadAsExcelData_Test()
{
var excelInputFile = "Sheet_EqualTest.xlsx";
Config_Info.Config_File_Test(_Debug);
// Setup part
// Test if file exists =====================================
string folderTestCases = Config_Info.Config_File_Test(_Debug, @"Excel/");
var file_input = folderTestCases + excelInputFile;
// Test if files exists
Assert.True(_lamed.lib.IO.Folder.Exists(folderTestCases), folderTestCases);
Assert.True(_lamed.lib.IO.File.Exists(file_input), file_input);
// Valid sheets
pcExcelData_ input1 = _lamed.lib.Excel.IO_Read.ExcelFile_LoadAsExcelData(file_input, "Sheet1");
pcExcelData_ input2 = _lamed.lib.Excel.IO_Read.ExcelFile_LoadAsExcelData(file_input, "Sheet2");
pcExcelData_ input3 = _lamed.lib.Excel.IO_Read.ExcelFile_LoadAsExcelData(file_input, "Sheet3");
// Invalid sheets
var ex = Assert.Throws <InvalidOperationException>(() => _lamed.lib.Excel.IO_Read.ExcelFile_LoadAsExcelData(file_input, "Sheet334"));
Assert.Equal("Error! Worksheet with name 'Sheet334' was not found!", ex.Message);
}
/// <summary>Gets the cell value</summary>
/// <param name="excelData">The excel data.</param>
/// <param name="cellName">Name of the cell.</param>
/// <returns></returns>
public string Value_Get(pcExcelData_ excelData, string cellName)
{
int col, row;
LamedalCore_.Instance.lib.Excel.Adress.ColRow_AsInt(out col, out row, cellName);
return(Value_Get(excelData, col, row));
}
/// <summary>Test all the References in the sheet.</summary>
/// <param name="data">The data.</param>
/// <param name="resultMsg">The error MSG.</param>
/// <returns></returns>
public bool DataIntegrity_Check(pcExcelData_ data, out string resultMsg)
{
var result = true;
resultMsg = "";
List <string> reflines = _lamed.lib.Excel.Adress.Find(data, "|->"); // Search for |??|-> pattern
// Parse the pattern & test
foreach (string refline in reflines)
{
if (refline.Contains("{Sheet}->"))
{
continue; // Skip sheet import definitions
}
string cellAddress, cellValue;
MacroItem.DataIntegrity_CellParser(refline, out cellAddress, out cellValue);
string msg;
if (MacroItem.DataIntegrity_Check(data, cellAddress, cellValue, out msg) == false)
{
result = false;
}
if (resultMsg != "")
{
resultMsg += "".NL();
}
resultMsg += msg;
}
return(result);
}
/// <summary>Set the Cell value for the worksheet.</summary>
/// <param name="excelData">The excel data.</param>
/// <param name="col">The col.</param>
/// <param name="row">The row.</param>
/// <param name="value">The value.</param>
/// <param name="bold">The bold.</param>
/// <param name="underline">The underline.</param>
/// <param name="italic">The italic.</param>
/// <param name="fontSize">Size of the font.</param>
/// <param name="webLink">The web link.</param>
/// <param name="textColor">Color of the text.</param>
/// <param name="columnWidth">Width of the column.</param>
/// <param name="fontName">Name of the font.</param>
/// <param name="border">The border.</param>
/// <returns></returns>
public Cell WorkSheet_CellSet(pcExcelData_ excelData, int col, int row, object value, bool?bold = null, bool?underline = null,
bool?italic = null, int?fontSize = null, string webLink = null, Color?textColor = null, int?columnWidth = null,
string fontName = null, enExcel_CellBorder?border = null)
{
string cellRef = _lamed.lib.Excel.Adress.CellAddress(col, row);
return(WorkSheet_CellSet(excelData, cellRef, value, bold, underline, italic, fontSize, webLink, textColor, columnWidth, fontName, border));
}
/// <summary>Load the CSV file.</summary>
/// <param name="csvFilename">The CSV filename.</param>
/// <returns></returns>
public pcExcelData_ csvLoadFromFile(string csvFilename)
{
// Read the CSV file and populate the data structure
var result = new pcExcelData_();
string[] lines = _lamed.lib.IO.RW.File_Read2StrArray(csvFilename);
_lamed.lib.Excel.Csv.DataRows_FromCsvLines(result.Rows, lines);
return(result);
}
public void MacroCompile_UpDownTest()
{
#region Input
var data = new pcExcelData_();
var lines = new List <string>();
// ========================
// |A1|, |^|
// |A2|, |^|
// |V| , |C3|, Test
// |V| , |D4|
// |C3|->Test
// ========================
lines.Add("|A1|,|^|,|^|");
lines.Add("|A2|,|^|,|^|");
lines.Add("|V|,|C3|,|B2|,Test");
lines.Add("|V|,|D4|,|C3|");
lines.Add("|D3|->Test");
data.csvLoadFromLines(lines.ToArray());
string errMsg;
Assert.True(_lamed.lib.Excel.Macro.DataIntegrity_Check(data, out errMsg), errMsg);
// Test the column values
var cols = data.Columns;
var col0 = "|A1|,|A2|,|V|,|V|,|D3|->Test".zConvert_Str_ToListStr(",");
var col1 = "|^|,|^|,|C3|,|D4|,".zConvert_Str_ToListStr(",");
var col2 = "|^|,|^|,|B2|,|C3|,".zConvert_Str_ToListStr(",");
var col3 = ",,Test,,".zConvert_Str_ToListStr(",");
Assert.Equal(col0, cols[0]);
Assert.Equal(col1, cols[1]);
Assert.Equal(col2, cols[2]);
Assert.Equal(col3, cols[3]);
#endregion
#region Result
var resultData = new pcExcelData_();
var resultLine = new List <string>();
// ========================
// |A1|,|A1|
// |A2|,|B2|
// |A3|,|C3|, 100
// |A4|,|D4|
// |C3|->Test
// ========================
resultLine.Add("|A1|,|A1|,|??|");
resultLine.Add("|A2|,|B2|,|A1|");
resultLine.Add("|A3|,|C3|,|B2|,Test");
resultLine.Add("|A4|,|D4|,|C3|");
resultLine.Add("|D3|->Test");
resultData.csvLoadFromLines(resultLine.ToArray());
Assert.True(_lamed.lib.Excel.Macro.DataIntegrity_Check(data, out errMsg), errMsg);
#endregion
string errorMsg2, errorMsg1;
_excel.Macro.Compile(data, out errorMsg1); // Do the conversion
Assert.True(_lamed.lib.IO.Json.Object_IsEqual(resultData, data, out errorMsg2), errorMsg2);
}
/// <summary>Compile the |V0| macro code</summary>
/// <param name="data">The data.</param>
/// <param name="addressList">The address list.</param>
public void Compile_Down0Calculate(pcExcelData_ data, List <string> addressList)
{
foreach (string cell in addressList)
{
int col, row;
_lamed.lib.Excel.Adress.ColRow_AsInt(out col, out row, cell);
var value1 = data.Value_Get(col, row - 1);
data.Value_Set(cell, value1);
}
}
/// <summary>
/// Return the Worksheet for the excel data
/// </summary>
/// <param name="excelData"></param>
/// <returns></returns>
public Worksheet Worksheet_FromExcelData(pcExcelData_ excelData)
{
var sheet = excelData.Worksheet;
if (sheet == null)
{
"Error! There is no worksheet in excelData.".zException_Show(); // Unit test needed for this
}
return(sheet);
}
/// <summary>Creates a new Worksheet.</summary>
/// <param name="data">The data.</param>
/// <param name="workbook">The workbook.</param>
/// <param name="sheetName">Name of the sheet.</param>
/// <param name="orientation">The orientation.</param>
internal void WorkSheet_New(pcExcelData_ data, Workbook workbook, string sheetName, enExcel_Orientation orientation = enExcel_Orientation.Portrait)
{
var sheet = new Worksheet(sheetName);
workbook.Add(sheet);
sheet.PageSetup.Orientation = (enOrientation)orientation;
sheet.PageSetup.PrintRepeatRows = 2;
sheet.PageSetup.PrintRepeatColumns = 2;
data._sheet = sheet;
}
/// <summary>Checks the data integrity of the sheet.</summary>
/// <param name="data">The data.</param>
/// <param name="cellAddress">The cell address.</param>
/// <param name="cellValue">The cell value.</param>
/// <param name="resultMsg">The error MSG.</param>
/// <returns></returns>
public bool DataIntegrity_Check(pcExcelData_ data, string cellAddress, string cellValue, out string resultMsg)
{
var testValue = data.Value_Get(cellAddress);
if (cellValue != testValue)
{
resultMsg = $"Reference check address: '{cellAddress}' == '{testValue}' (Error! Should be '{cellValue}').";
return(false);
}
resultMsg = $"Reference check address: '{cellAddress}' == '{testValue}' (Ok).";
return(true);
}
/// <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>From the cell list calculate a new value from the top and left cell name</summary>
/// <param name="data">The data.</param>
/// <param name="addressList">The address list.</param>
public void Compile_DownLeftCalculate(pcExcelData_ data, List <string> addressList)
{
// '|V>|'
foreach (string cell in addressList)
{
int col, row;
_lamed.lib.Excel.Adress.ColRow_AsInt(out col, out row, cell);
var value1 = data.Value_Get(col, row - 1);
var value2 = data.Value_Get(col - 1, row);
var value = Compile_CellMergeValues(value1, value2);
data.Value_Set(cell, value);
}
}
///// <summary>Gets all the cells that contain's |V>|.</summary>
///// <param name="data">The data.</param>
///// <returns></returns>
//public List<string> Compile_DownLeftCells(ExcelData_ data)
//{
// return Find(data, "|V>|", enExcelCompare.Equal, enExcelFindReturnValue.CellAddress);
// //var result = new List<string>();
// //var rowNo = 1;
// //foreach (List<string> row in data.Rows)
// //{
// // var colNo = 1;
// // foreach (string cell in row)
// // {
// // if (cell == "|V>|")
// // {
// // var address = CellAddress(colNo, rowNo, false); // Todo: fix this macro to do everything
// // result.Add(address);
// // }
// // colNo++;
// // }
// // rowNo++;
// //}
// //return result;
//}
/// <summary>Finds the first occurance.</summary>
/// <param name="data">The data.</param>
/// <param name="result">The result.</param>
/// <param name="findValue">The find value.</param>
/// <param name="compare">The compare formula to use.</param>
/// <param name="returnType">Type of the return.</param>
/// <returns></returns>
public bool Find_First(pcExcelData_ data, out string result, string findValue = "|->", enExcel_Compare compare = enExcel_Compare.Contains,
enExcel_FindReturnValue returnType = enExcel_FindReturnValue.CellValue)
{
result = "";
var findList = Find(data, findValue, compare, returnType);
if (findList.Count == 0)
{
return(false);
}
result = findList[0];
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
}
public void ArrowLeft_Test()
{
var excelFile = "TestArrowLeft.xlsx";
Config_Info.Config_File_Test(_Debug);
// Test if file exists =====================================
string folderTestCases = Config_Info.Config_File_Test(_Debug, @"Excel/");
//string folderApplication;
//string folderTestCases;
//pcTest_Configuration config;
//_lamed.lib.Test.ConfigSettings(out folderApplication, out folderTestCases, out config);
var file = folderTestCases + excelFile;
DebugLog("File: " + file, true);
Assert.True(_lamed.lib.IO.File.Exists(file), $"File: '{file}' does not exist!");
// Load the Test ==========================================
pcExcelData_ input = _lamed.lib.Excel.IO_Read.ExcelFile_LoadAsExcelData(file, "Input");
pcExcelData_ result = _lamed.lib.Excel.IO_Read.ExcelFile_LoadAsExcelData(file, "Result");
// =========================================================
// Test sheet integrity ====================================
string resultMsg1, resultMsg2;
if (_lamed.lib.Excel.Macro.DataIntegrity_Check(input, out resultMsg1) == false)
{
throw new Exception(resultMsg1);
}
if (_lamed.lib.Excel.Macro.DataIntegrity_Check(result, out resultMsg2) == false)
{
throw new Exception(resultMsg2);
}
var msg = "Input:".NL() + resultMsg1.NL(2) + "Result:".NL() + resultMsg2;
DebugLog(msg);
// ============================================================
// Execute macro and test the results =========================
string resultMsg;
_lamed.lib.Excel.Macro.Compile(input, out resultMsg);
if (_lamed.lib.IO.Json.Object_IsEqual(result.Normalize(), input.Normalize(), out resultMsg) == false)
{
resultMsg.zException_Show();
}
// ============================================================
}
/// <summary>Set the Cell value for the worksheet.</summary>
/// <param name="excelData">The excel data.</param>
/// <param name="cellName">Name of the cell.</param>
/// <param name="value">The value.</param>
/// <param name="bold">The bold.</param>
/// <param name="underline">The underline.</param>
/// <param name="italic">The italic.</param>
/// <param name="fontSize">Size of the font.</param>
/// <param name="webLink">The web link.</param>
/// <param name="textColor">Color of the text.</param>
/// <param name="columnWidth">Width of the column.</param>
/// <param name="fontName">Name of the font.</param>
/// <param name="border">The border.</param>
/// <returns></returns>
public Cell WorkSheet_CellSet(pcExcelData_ excelData, string cellName, object value, bool?bold = null, bool?underline = null,
bool?italic = null, int?fontSize = null, string webLink = null, Color?textColor = null, int?columnWidth = null,
string fontName = null, enExcel_CellBorder?border = null)
{
Cell result = WorkSheet_Cell(excelData, cellName);
result.Value = value;
if (bold != null)
{
result.Bold = (bool)bold;
}
if (underline != null)
{
result.Underline = (bool)underline;
}
if (italic != null)
{
result.Italic = (bool)italic;
}
if (fontSize != null)
{
result.FontSize = (int)fontSize;
}
if (webLink != null)
{
result.Hyperlink = webLink;
}
if (textColor != null)
{
result.TextColor = (Color)textColor;
}
if (fontName != null)
{
result.FontName = fontName;
}
if (columnWidth != null)
{
WorkSheet_ColumnWidth(excelData, cellName, (int)columnWidth);
}
//if (border.HasValue)
if (border != null)
{
int borderValue = (int)border.Value;
result.Border = (enCellBorder)borderValue;
}
return(result);
}
/// <summary>Sets the value of a cell</summary>
/// <param name="col">The col.</param>
/// <param name="row">The row.</param>
/// <param name="value">The value.</param>
public void Value_Set(pcExcelData_ excelData, int col, int row, string value)
{
// Add the rows if required
while (excelData.Row_Count < row)
{
excelData.Rows.Add(new List <string>());
}
var rowList = excelData.Rows[row - 1];
while (rowList.Count < col)
{
rowList.Add("");
}
excelData.Rows[row - 1][col - 1] = value;
}
/// <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 MacroCompile_UpDown0_Test()
{
#region Input
var data = new pcExcelData_();
var lines = new List <string>();
// ========================
// |A1|, |^0|,|^0|
// |A2|, |^0|,|^0|
// |V0|, |C3|,|B2|, Test
// |V0|, |D4|,|C3|
// |C3|->Test
// ========================
lines.Add("|A1|,|^0|,|^0|");
lines.Add("|A2|,|^0|,|^0|");
lines.Add("|V0|,|C3|,|B2|,Test");
lines.Add("|V0|,|D4|,|C3|");
lines.Add("|D3|->Test");
data.csvLoadFromLines(lines.ToArray());
string errMsg;
Assert.True(_lamed.lib.Excel.Macro.DataIntegrity_Check(data, out errMsg), errMsg);
#endregion
#region Result
var resultData = new pcExcelData_();
var resultLine = new List <string>();
// ========================
// |A1|,|A1|, |B2|
// |A2|,|B2|, |B2|
// |A2|,|C3|, |B2|, 100
// |A2|,|D4|, |C3|
// |C3|->Test
// ========================
resultLine.Add("|A1|,|C3|,|B2|");
resultLine.Add("|A2|,|C3|,|B2|");
resultLine.Add("|A2|,|C3|,|B2|,Test");
resultLine.Add("|A2|,|D4|,|C3|");
resultLine.Add("|D3|->Test");
resultData.csvLoadFromLines(resultLine.ToArray());
Assert.True(_lamed.lib.Excel.Macro.DataIntegrity_Check(data, out errMsg), errMsg);
#endregion
string errorMsg2, errorMsg1;
_excel.Macro.Compile(data, out errorMsg1); // Do the conversion
Assert.True(_lamed.lib.Test.ObjectsAreEqual(resultData, data, out errorMsg2), errorMsg2);
}
public void DataIntegrity_Check_Test()
{
#region Input
string errorMsg;
var data = new pcExcelData_();
var lines = new List <string>();
// ========================
// Test1
// 1 , 2 , |>| , |>|
// |A2| , |C4| , |>| , |>|, Test2
// Test , |B3| , |C3| , |>|
// |<| , |<| , 10 , 15
// , , Test3
// |A1|->Test1
// |E3|->Test2
// |C6|->Test3
// ========================
lines.Add("Test1");
lines.Add("1,2,|>|,|>|");
lines.Add("|A2|,|C4|,|>|,|>|,Test2");
lines.Add("Test,|B3|,|C3|,|>|");
lines.Add("|<|,|<|,10,15");
lines.Add(",,Test3");
lines.Add("|A1|->\"Test1\"");
lines.Add("|E3|->\"Test2\"");
lines.Add("|C6|->Test3");
data.csvLoadFromLines(lines.ToArray());
#endregion
// Test the ref points
var reflines = _lamed.lib.Excel.Adress.Find(data, "|->", enExcel_Compare.Contains, enExcel_FindReturnValue.CellValue);
Assert.Equal("|A1|->\"Test1\"", reflines[0]);
Assert.Equal("|E3|->\"Test2\"", reflines[1]);
Assert.Equal("|C6|->Test3", reflines[2]);
string cellAddress, cellValue;
_lamed.lib.Excel.Macro.MacroItem.DataIntegrity_CellParser("|A1|->\"Test1\"", out cellAddress, out cellValue);
Assert.Equal("A1", cellAddress);
Assert.Equal("Test1", cellValue);
Assert.True(_lamed.lib.Excel.Macro.MacroItem.DataIntegrity_Check(data, "A1", "Test1", out errorMsg));
Assert.False(_lamed.lib.Excel.Macro.MacroItem.DataIntegrity_Check(data, "A1", "Test2", out errorMsg));
Assert.Equal("Reference check address: 'A1' == 'Test1' (Error! Should be 'Test2').", errorMsg);
// Test all integrity points
Assert.True(_lamed.lib.Excel.Macro.DataIntegrity_Check(data, out errorMsg), errorMsg);
}
public void SimpleTest()
{
var data = new pcExcelData_();
data.WorkSheet_New("TestSheet", enExcel_Orientation.Landscape, "The Author", "Workbook Title");
//sheet.PageSetup.Orientation = Orientation.Landscape;
//sheet.PageSetup.PrintRepeatRows = 2;
//sheet.PageSetup.PrintRepeatColumns = 2;
data.WorkSheet_ColumnWidth(1, 24.6);
data.WorkSheet_ColumnWidth("A", 24.6);
//_lamed.lib.Excel.WorkSheet.WorkSheet_ColumnWidth();
//sheet.ColumnWidths[0] = 24.6;
data.WorkSheet_CellSet("A1", "Test", fontName: "Arial Black");
//sheet.Cells["A1"] = "Test";
//sheet.Cells["A1"].FontName = "Arial Black";
data.WorkSheet_CellSet(2, 1, "Another Test", border: enExcel_CellBorder.Bottom | enExcel_CellBorder.Right);
//sheet.Cells[0, 1] = "Another Test";
//sheet.Cells[0, 1].Border = CellBorder.Bottom | CellBorder.Right;
data.WorkSheet_CellSet(2, 1, "Bold Red", bold: true, textColor: Color.Red);
//sheet.Cells[0, 2] = "Bold Red";
//sheet.Cells[0, 2].Bold = true;
//sheet.Cells[0, 2].TextColor = Color.Red;
data.WorkSheet_CellSet(3, 1, "BIU Big Blue", bold: true, underline: true, italic: true,
textColor: Color.Blue, fontSize: 18, webLink: "https://github.com/mstum/Simplexcel", columnWidth: 40);
data.WorkSheet_ColumnWidth(3, 40);
data.WorkSheet_CellSet(4, 1, 13);
data.WorkSheet_CellSet(5, 1, 13.58);
data.WorkSheet_CellSet(2, 1, "Orange", bold: true, italic: true, textColor: Color.Orange, fontSize: 18);
data.Workbook_Save(@"testCompressed.xlsx");
//_lamed.lib.Command.Execute_Explorer();
// Exceptions
data.Workbook_Close();
Assert.Throws <InvalidOperationException>(() => data.WorkSheet_CellSet(4, 1, 13));
}
public void MacroCompile_DownLeftTest()
{
#region Input
string errorMsg;
var data = new pcExcelData_();
var lines = new List <string>();
// ========================
// |B17|, |AB17|, |AA17|
// |B27|, |AB27|, |AA27|
// |B30|, |V>| , |V>|
// |B44|, |V>| , |V>|
// ========================
lines.Add("|B17|,|AB17|,|AA17|");
lines.Add("|B27|,|AB27|,|AA27|");
lines.Add("|B30|,|V>|,|V>|");
lines.Add("|B44|,|V>|,|V>|");
data.csvLoadFromLines(lines.ToArray());
List <string> addressList = _lamed.lib.Excel.Adress.Find(data, "|V>|", enExcel_Compare.Equal, enExcel_FindReturnValue.CellAddress);
var addressList_Test = "B3,C3,B4,C4".zConvert_Str_ToListStr(",");
Assert.True(_lamed.lib.IO.Json.Object_IsEqual(addressList_Test, addressList, out errorMsg), errorMsg);
//Compile_DownLeftCalculate(data, addressList);
#endregion
#region Result
var resultData = new pcExcelData_();
var resultLine = new List <string>();
// ========================
// |B17|,|AB17|,|AA17|
// |B27|,|AB27|,|AA27|
// |B30|,|AB30|,|AA30|
// |B44|,|AB44|,|AA44|
// ========================
resultLine.Add("|B17|,|AB17|,|AA17|");
resultLine.Add("|B27|,|AB27|,|AA27|");
resultLine.Add("|B30|,|AB30|,|AA30|");
resultLine.Add("|B44|,|AB44|,|AA44|");
resultData.csvLoadFromLines(resultLine.ToArray());
#endregion
_excel.Macro.Compile(data, out errorMsg); // Do the conversion
Assert.True(_lamed.lib.IO.Json.Object_IsEqual(resultData, data, out errorMsg), errorMsg);
}
