public static void prjTb(System.Data.DataTable fnlCnsInput, System.Data.DataTable newPrmInput, InputOptionsPage projectionOptions, Excel.Workbook inputOwb)
{
oWB = inputOwb;
prjNme = projectionOptions.projName;
fnlCns = fnlCnsInput;
nwPrm = newPrmInput;
Tuple<List<string>, DataTable, List<double>, List<string>, DataTable, List<double>> projectionResultsData
= PrepareSummaryAndDetailResultsForOutput();
CheckIfResultsExist();
ProjectionSummaryTab projectionSummaryResults = new ProjectionSummaryTab(projectionResultsData, oWB, projectionNameOfSummaryTabs);
ProjectionRatesTab projectionRatesGrid = new ProjectionRatesTab(oWB, nwPrm, projectionNameOfSummaryTabs);
ProjectionDetailTab projectedDetailResults = new ProjectionDetailTab(fnlCns, oWB, projectionNameOfSummaryTabs);
ProjectionAssumptionResults projectedAssumptionResults = new ProjectionAssumptionResults(projectionOptions, oWB, projectionNameOfSummaryTabs);
FinalizeResults();
}
public MessageReport(string title, Excel.Range excelRange, string description, Criticity criticity)
{
this.title = title;
this.localisation = printRange(excelRange);
this.description = description;
this.criticity = criticity;
}
private static Excel.Range PutSampleData(Excel.Worksheet workSheet)
{
workSheet.Cells[2, 2].Value = "Date";
workSheet.Cells[3, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[4, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[5, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[6, 2].Value = DateTime.Now.ToShortDateString();
workSheet.Cells[2, 3].Value = "Columns1";
workSheet.Cells[3, 3].Value = 25;
workSheet.Cells[4, 3].Value = 33;
workSheet.Cells[5, 3].Value = 30;
workSheet.Cells[6, 3].Value = 22;
workSheet.Cells[2, 4].Value = "Column2";
workSheet.Cells[3, 4].Value = 25;
workSheet.Cells[4, 4].Value = 33;
workSheet.Cells[5, 4].Value = 30;
workSheet.Cells[6, 4].Value = 22;
workSheet.Cells[2, 5].Value = "Column3";
workSheet.Cells[3, 5].Value = 25;
workSheet.Cells[4, 5].Value = 33;
workSheet.Cells[5, 5].Value = 30;
workSheet.Cells[6, 5].Value = 22;
return workSheet.Range("$B2:$E6");
}
private static ExcelTestStruct findTablesInSheet(Excel.Worksheet wsCurrentTestSheet, SheetReport report)
{
Excel.ListObjects ListOfRanges = wsCurrentTestSheet.ListObjects;
SortedList listActionTables = new SortedList();
SortedList listCheckTables = new SortedList();
SortedList listDescrTables = new SortedList();
foreach (Excel.ListObject obj in ListOfRanges)
{
int range = obj.Range.Row;
String tableName = obj.Name;
if (tableName.StartsWith(PR_TEST_TABLE_ACTION_PREFIX))
listActionTables.Add(range, tableName);
else if (tableName.StartsWith(PR_TEST_TABLE_CHECK_PREFIX))
listCheckTables.Add(range, tableName);
else if (tableName.StartsWith(PR_TEST_TABLE_DESCRIPTION_PREFIX))
listDescrTables.Add(range, tableName);
else
{
report.add(new MessageReport("Unrecognised table type", obj.Range, String.Format("\"{0}\" is not an authorized name for table.", tableName), Criticity.Critical));
logger.Error(String.Format("\"{0}\" is not an authorized name for table.", tableName));
}
}
if(listActionTables.Count == 1 && listCheckTables.Count == 1 && listDescrTables.Count == 1)
{
ExcelTestStruct n = new ExcelTestStruct();
n.actionTableName = (string)listActionTables.GetByIndex(0);
n.testTableName = (string)listCheckTables.GetByIndex(0);
n.descrTableName = (string)listDescrTables.GetByIndex(0);
return n;
}
else
throw new NotImplementedException(String.Format("It is not currently possible to repair sheet \"{0}\"", wsCurrentTestSheet.Name));
}
public static string printRange(Excel.Range range)
{
string text = range.Address;
text = Regex.Replace(text, "\\$", "");
return "["+text+"]";
}
public ProjectionDetailTab(DataTable finalCns, Excel.Workbook oWB, string prjNme)
{
this.fnlCns = finalCns;
this.projectionNameOfSummaryTabs = prjNme;
this.shts = (Excel.Sheets)oWB.Sheets;
this.oSheet = (Excel.Worksheet)this.shts.Add();
SetupSheetNameAndLocationInWorkbook();
OutputAndFormatRateGridAndSubsidy();
FinalizeSheetName();
}
public ProjectionRatesTab(Excel.Workbook oWB, DataTable newInputPrm, string prjNme)
{
this.projectionNameOfSummaryTabs = prjNme;
this.shts = (Excel.Sheets)oWB.Sheets;
this.oSheet = (Excel.Worksheet)this.shts.Add();
this.nwPrm = newInputPrm;
SetupSheetNameAndLocationInWorkbook();
OutputAndFormatRateGridAndSubsidy();
FinalizeSheetName();
}
public ProjectionAssumptionResults(InputOptionsPage inputOptions, Excel.Workbook oWB, string prjNme)
{
this.inputOptions = inputOptions;
this.projectionNameOfSummaryTabs = prjNme;
this.shts = (Excel.Sheets)oWB.Sheets;
this.oSheet = (Excel.Worksheet)this.shts.Add();
SetupSheetNameAndLocationInWorkbook();
OutputAndFormatInputAssumptions();
FinalizeSheetName();
}
private void DumpArrayOfValues(string[,] Values, int StartRow, int StartColumn, Excel.Workbook xlwkbook, string WorksheetName)
{
Excel.Worksheet xlsheet = GetWorkSheetWithName(xlwkbook, WorksheetName);
for (int i = 0; i < Values.GetLength(0); i++)
{
for (int j = 0; j < Values.GetLength(1); j++)
{
int cRow = StartRow + i;
int cColumn = StartColumn + j;
Excel.Range CurrentRange = (Excel.Range)xlsheet.Cells[cRow, cColumn];
CurrentRange.Cells.Value = Values[i, j];
}
}
}
private List<string> GetWorksheetsWithPrefix(Excel.Workbook xlwkbook, string prefix)
{
List<string> wkshts = new List<string>();
foreach (var wksht in xlwkbook.Worksheets)
{
Excel.Worksheet thisWorksheet = (Excel.Worksheet)wksht;
if (thisWorksheet != null)
{
if (thisWorksheet.Name.StartsWith(prefix))
{
wkshts.Add(thisWorksheet.Name);
}
}
}
return wkshts;
}
public static CTestContainer parseTestsOfWorkbook(Excel.Sheets sheets, string filename)
{
logger.Info("Begin Analysis of selected sheets");
CTestContainer listOfTests = new CTestContainer();
WorkbookReport report = new WorkbookReport(filename);
foreach (Excel.Worksheet wsCurrentTestSheet in sheets)
{
logger.Debug(String.Format("Processing sheet \"{0}\".", wsCurrentTestSheet.Name));
SheetReport sheetReport = new SheetReport(wsCurrentTestSheet.Name);
try
{
ExcelTestStruct tableRefs = findTablesInSheet(wsCurrentTestSheet, sheetReport);
CTest result = TestSheetParser.parseTest(wsCurrentTestSheet.Name, wsCurrentTestSheet, tableRefs, sheetReport);
logger.Debug("Adding sheet to result list");
listOfTests.Add(result);
}
catch (Exception ex)
{
logger.Fatal("Sheet cannot be parsed : ", ex);
sheetReport.add(new MessageReport("Parsing error", "Sheet", "Sheet was not analysed. Message is : "+ ex.Message, Criticity.Critical));
}
report.add(sheetReport);
}
string URIFilename = Path.GetDirectoryName(Assembly.GetExecutingAssembly().CodeBase) + Path.DirectorySeparatorChar + "Report.html";
Uri uri = new Uri(URIFilename);
logger.Debug("Writing report in "+URIFilename);
report.printReport(uri.LocalPath);
if (report.NbrMessages > 0)
{
System.Diagnostics.Process.Start(URIFilename);
}
return listOfTests;
}
private static void AddColumnNames(Excel.Worksheet sheet)
{
string[] tempHeadingArray =
{
"FirstName",
"LastName",
"Income",
"Tax",
"Total Income"
};
var columnNameRange = sheet.get_Range(sheet.Cells[3, 1], sheet.Cells[3, tempHeadingArray.Length]);
columnNameRange.Style = "NewStyle";
columnNameRange.Font.Size = 10;
columnNameRange.Value = tempHeadingArray;
columnNameRange.UseStandardWidth = true;
columnNameRange.ColumnWidth = 14;
columnNameRange.WrapText = true;
}
public TestSheetParser(Excel.Worksheet sheet, string headerTableName, string actionsTableName, string checksTableName, SheetReport report)
{
this.report = report;
this.sheet = sheet;
if (!Regex.IsMatch(sheet.Name, PR_TEST_PREFIX + ".*"))
throw new FormatException(String.Format("Sheet name doesn't comply with naming rules (begins with \"{0}\").", PR_TEST_PREFIX));
try
{
logger.Debug(String.Format("Trying to retrieve action table \"{0}\".", actionsTableName));
loActionsTable = sheet.ListObjects[actionsTableName];
actionTableName = String.Format("'{0}'!{1}", sheet.Name, actionsTableName);
checkTableName = String.Format("'{0}'!{1}", sheet.Name, checksTableName);
logger.Debug(String.Format("Extracting columns for action table."));
lcActionsTableColumns = loActionsTable.ListColumns;
}
catch (Exception ex)
{
logger.Error(String.Format("Action table \"{0}\" retrieval has failed.", actionsTableName), ex);
throw new FormatException(String.Format("Action table \"{0}\" retrieval has failed.", actionsTableName));
}
try
{
logger.Debug(String.Format("Trying to retrieve test table \"{0}\".", checksTableName));
loChecksTable = sheet.ListObjects[checksTableName];
logger.Debug(String.Format("Extracting columns for checks table."));
lcChecksTableColumns = loChecksTable.ListColumns;
}
catch (Exception ex)
{
logger.Error(String.Format("Check table \"{0}\" retrieval has failed.", checksTableName), ex);
throw new FormatException(String.Format("Check table \"{0}\" retrieval has failed.", checksTableName));
}
if(lcActionsTableColumns.Count != lcChecksTableColumns.Count)
throw new FormatException(String.Format("Action ({0} columns) and check ({1} columns) tables has not same number of columns", lcActionsTableColumns.Count, lcChecksTableColumns.Count));
}
private Excel.Worksheet GetWorkSheetWithName(Excel.Workbook workBook, string WorksheetName, bool CreateNewIfNotFound = true)
{
foreach (var wksht in workBook.Worksheets)
{
Excel.Worksheet thisWorksheet = (Excel.Worksheet)wksht;
if (thisWorksheet != null)
{
if (thisWorksheet.Name == WorksheetName)
{
return thisWorksheet;
}
}
}
if (CreateNewIfNotFound == true)
{
Excel.Worksheet newWorksheet = (Excel.Worksheet)workBook.Worksheets.Add();
newWorksheet.Name = WorksheetName;
return newWorksheet;
}
throw new Exception("Worksheet (tab) with the specified name not found in Excel file for input values. Please check input.");
}
private void NewWorkbookEventHandler(Excel.Workbook wb)
{
//ribbon.Invalidate();
}
public static CTest parseTest(string title, Excel.Worksheet sheet, WorkbookParser.ExcelTestStruct tableRefs, SheetReport report)
{
logger.Info(String.Format("Beginning Analysis of sheet {0}, using arrays {1} and {2}", sheet.Name, tableRefs.actionTableName, tableRefs.testTableName));
TestSheetParser analyser = new TestSheetParser(sheet, tableRefs.descrTableName, tableRefs.actionTableName, tableRefs.testTableName, report);
logger.Debug("Sheet passed validity tests successfully");
CTest test = null;
test = analyser.parseAsTest(title);
return test;
}
private TableColumnsStructure checkAndDetermineTablecolumns(Excel.ListColumns lcActionsTableColumns)
{
TableColumnsStructure tableStructure = new TableColumnsStructure();
for (int CurrentColumn = 1; CurrentColumn < 5; CurrentColumn++)
{
if (lcActionsTableColumns[CurrentColumn].Name.Equals("Target"))
tableStructure.TargetColumnIndex = CurrentColumn - 1; // Indexes from Excel are starting from 1, and we are using 0 based indexes
if (lcActionsTableColumns[CurrentColumn].Name.Equals("Location"))
tableStructure.LocationColumnIndex = CurrentColumn - 1; // Indexes from Excel are starting from 1, and we are using 0 based indexes
if (lcActionsTableColumns[CurrentColumn].Name.Equals("Path"))
tableStructure.PathColumnIndex = CurrentColumn - 1; // Indexes from Excel are starting from 1, and we are using 0 based indexes
}
tableStructure.setFirstColumnIndex();
if(!tableStructure.isValid())
throw new FormatException(String.Format("Table doesn't contains all necessary columns headers : {0}", tableStructure.ToString()));
return tableStructure;
}
private void fillWithActions(CStep o_step, TableTypes typeOfTable, Excel.ListObject tableRef, object[,] table, int ColumnIndex)
{
logger.Debug(String.Format("Found {0} Excel lines to process.", table.GetLength(0)));
for (int line = 0; line < table.GetLength(0); line++)
{
object CellValue = table[line, ColumnIndex];
if(!(CellValue is ExcelDna.Integration.ExcelEmpty))
{
string Target = "";
if (table[line, tableStructure.TargetColumnIndex] is string) Target = (string)table[line, tableStructure.TargetColumnIndex];
string Path = "";
if (table[line, tableStructure.PathColumnIndex] is string) Path = (string)table[line, tableStructure.PathColumnIndex];
string Location = "";
if (table[line, tableStructure.LocationColumnIndex] is string) Location = (string)table[line, tableStructure.LocationColumnIndex];
logger.Debug(String.Format("Found item [Target={0}, Location={1}, Path={2}, Value={3}].", Target, Location, Path, CellValue));
try
{
logger.Debug(String.Format("Analysing current item."));
CInstruction o_instruction = detectAndBuildInstruction(Target, Location, Path, CellValue, typeOfTable);
if (typeOfTable == TableTypes.TABLE_ACTIONS)
{
logger.Debug("Adding item to list of actions to perform");
o_step.actions.Add(o_instruction);
}
else if (typeOfTable == TableTypes.TABLE_CHECKS)
{
logger.Debug("Adding item to list of checks to perform");
o_step.checks.Add(o_instruction);
}
else
throw new NotImplementedException(String.Format("This type of table ({0}) is not currently implemented", typeOfTable));
}
catch(InvalidCastException ex)
{
logger.Error("Problem when trying to find an equivalence for item.", ex);
report.add(new MessageReport("Invalid value in cell", tableRef.Range[line + 2, ColumnIndex + 1], ex.Message, Criticity.Error));
}
catch (Exception ex)
{
logger.Error("Invalid item processed.", ex);
report.add(new MessageReport("Cell problem", tableRef.Range[line + 2, ColumnIndex + 1], ex.Message, Criticity.Error));
}
}
}
}
private static void AddExcelHeadingText(Excel.Worksheet sheet)
{
//Apply styling to heading text
sheet.Cells[1, 1].Value = "Girl Agency Financial Report";
sheet.Cells[1, 1].Font.Name = "Impact";
sheet.Cells[1, 1].Font.Underline = true;
sheet.Cells[1, 1].Font.Size = 14;
}
private void WorkbookDeactivateEventHandler(Excel.Workbook wb)
{
ribbon.Invalidate();
}
/// <summary>
/// This method was called from the host instance after selection changed.
/// </summary>
/// <param name="selectedRange">active selection</param>
private void Application_SheetSelectionChangeEvent(NetOffice.COMObject sh, Excel.Range selectedRange)
{
try
{
// we check for auto translation and skip selections with more than 256 cells for this simple example
if (checkBoxAutoTranslate.Checked && selectedRange.Count <= 256)
{
ClearError();
textBoxTranslation.Text = string.Empty;
foreach (var item in selectedRange)
{
string requestedText = item.Text as string;
if (!String.IsNullOrWhiteSpace(requestedText))
{
string translatedText = Client.DataService.Translate(
comboBoxSourceLanguage.SelectedItem as string,
comboBoxTargetLanguage.SelectedItem as string,
requestedText);
textBoxTranslation.Text += translatedText + " ";
}
}
}
sh.Dispose();
selectedRange.Dispose();
}
catch (Exception exception)
{
ShowError(string.Format("An errror occured. Details: {0}", exception.Message));
}
}
public void OnConnection(Excel.Application application, NetOffice.OfficeApi._CustomTaskPane parentPane, object[] customArguments)
{
try
{
Application = application;
Application.SheetSelectionChangeEvent += new Excel.Application_SheetSelectionChangeEventHandler(Application_SheetSelectionChangeEvent);
}
catch (Exception exception)
{
ShowError(string.Format("An errror occured. Details: {0}", exception.Message));
}
}
private void addTempoIfExists(CStep o_step, Excel.ListObject loSourceFiles, int ColumnIndex)
{
//'Delay retrieval. We know that data is contained inside Total line property
object delay = loSourceFiles.TotalsRowRange.Cells[1, ColumnIndex + 1].Value; // We get values from excel, and array indexes begin with 1, not 0
if (delay != null)
{
try
{
logger.Debug(String.Format("Trying to retrieve temporisation with value \"{0}\".", delay));
CInstrWait o_tempo = new CInstrWait();
o_tempo.data = Convert.ToInt32(delay);
logger.Debug("Adding temporisation to results");
o_step.actions.Add(o_tempo);
}
catch(Exception ex)
{
logger.Error("Failed to parse temporisation.", ex);
report.add(new MessageReport("Invalid value for temporisation", loSourceFiles.TotalsRowRange.Cells[1, ColumnIndex + 1], String.Format("Invalid value, an integer was expected, but we had {0}", delay ), Criticity.Error));
}
}
}
/// <summary>
/// Returns the valid file format for the instance. Documents with macro's need a bit xtra config since 2007
/// </summary>
/// <param name="application">the instance</param>
/// <returns>the format</returns>
private static XlFileFormat GetFileFormat(Excel.Application application)
{
double Version = Convert.ToDouble(application.Version, CultureInfo.InvariantCulture);
if (Version >= 12.00)
return XlFileFormat.xlOpenXMLWorkbookMacroEnabled;
else
return XlFileFormat.xlExcel7;
}
private static void SetValidationRange(Excel.Worksheet sh, long currow, long columnnr)
{
try
{
//sh.Cells[currow, x].Value = GetPriorityRange(sh, x);
sh.Cells[currow, columnnr].Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertInformation,
null, GetPriorityRange(sh, columnnr));
}
catch (Exception)
{
sh.Cells[currow, columnnr].Validation.Modify(XlDVType.xlValidateList,
XlDVAlertStyle.xlValidAlertInformation,
null, GetPriorityRange(sh, columnnr));
}
}
private static string GetPriorityRange(Excel.Worksheet sh, long columnnr)
{
long r;
var ur = (Excel.Range)sh.UsedRange;
for ( r = 2; r < ur.Rows.Count; r++)
{
Excel.Range rng = sh.Cells[r, columnnr];
//return rng.Value.ToString();
if (rng.Value == null || rng.Value.ToString().StartsWith("&") ) // .ToString().Equals("")
return "=" + sh.Cells[2, columnnr].Address + ":" + sh.Cells[ (r - 1), columnnr].Address;
}
return "";
}
public static bool IsTestdataSheet(Excel.Worksheet sh)
{
Excel.Range rng = sh.UsedRange;
Debug.WriteLine(rng.Rows.Count);
for (int r = 1; r < rng.Rows.Count; r++)
{
var style = (Excel.Style)sh.Cells[r, 1].Style;
var s = style.Name;
if (s.Equals("actionword"))
return true;
}
return false;
}
private void WorkbookOpenEventHandler(Excel.Workbook wb)
{
//ApplyProtection(wb);
}
public ProjectionSummaryTab(Tuple<List<string>, DataTable, List<double>, List<string>, DataTable, List<double>> projectionData,
Excel.Workbook oWB, string prjNme)
{
this.projectionNameOfSummaryTabs = prjNme;
this.shts = (Excel.Sheets)oWB.Sheets;
this.oSheet = (Excel.Worksheet)this.shts.Add();
SetupSheetNameAndLocationInWorkbook();
OutputSummaryResultsAndFormat(projectionData.Item2, projectionData.Item3, 5); //Output for Prior Year
OutputSummaryResultsAndFormat(projectionData.Item5, projectionData.Item6, 8); //Output for Current Year
FinalizeSheetName();
}
private static void AddDataRows(Excel.Worksheet sheet)
{
sheet.Name = "Financial report";
var reportResult = GetData();
var value = new string[reportResult.Length, 5];
for (var row = 0; row < reportResult.Length; row++)
{
Console.WriteLine("Importing:\n---------------");
for (int col = 0; col < reportResult[row].Length; col++)
{
value[row, col] = reportResult[row][col];
Console.WriteLine(reportResult[row][col]);
}
}
var range = sheet.Range(sheet.Cells[4, 1],
sheet.Cells[reportResult.Length + 3, reportResult[1].Length]);
range.Value = value;
Console.WriteLine("Ready!!!");
}
