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