public static Excel.Range GetSheetRange(Worksheet sheet, string rangeString, Primer.RefType refType) { var parser = new Primer.RefParser(rangeString, ThisAddIn.MyApp, refType); int rowNumber = parser.firstRowNumber; int columnNumber = parser.firstColumnNumber; int rowNumber2 = parser.secondRowNumber; int columnNumber2 = parser.secondColumnNumber; return(sheet.Range[sheet.Cells[rowNumber, columnNumber], sheet.Cells[rowNumber2, columnNumber2]]); }
public void FormatRange() { /* * Loop the rows to build the target range and clear range with Union * Paste formats in the target range */ ThisAddIn.MyApp.DisplayAlerts = false; ThisAddIn.MyApp.ScreenUpdating = false; Excel.Range newTargetRange = targetCell.Offset[0, 1]; Excel.Range newMergeRange = targetCell; Excel.Range newHeaderRange = targetCell; Primer.RefParser targetParser = new Primer.RefParser(targetRange.Address, ThisAddIn.MyApp); int rowNum_start = targetParser.firstRowNumber; int colNum_start = targetParser.firstColumnNumber; int rowNum_end = targetParser.secondRowNumber; int colNum_end = targetRange.End[Excel.XlDirection.xlToRight].Column; int shift = colNum_start; Excel.Range formatRange; ThisAddIn.MyApp.ScreenUpdating = false; int rangeCount = 0; for (int i = rowNum_start; i <= rowNum_end; i++) { Excel.Range row = targetSheet.Range[$"A{i}"].EntireRow; if (shift + 1 <= colNum_end) { formatRange = targetSheet.Range[row.Cells[1, shift + 1], row.Cells[1, colNum_end]]; } else { formatRange = targetCell.Offset[0, 1]; } Excel.Range mergeRange = targetSheet.Range[row.Cells[1, colNum_start], row.Cells[1, shift]]; newTargetRange = ThisAddIn.MyApp.Union(newTargetRange, formatRange); newMergeRange = ThisAddIn.MyApp.Union(newMergeRange, mergeRange); Excel.Range headerRange = targetSheet.Range[row.Cells[1, shift], row.Cells[1, shift]]; newHeaderRange = ThisAddIn.MyApp.Union(newHeaderRange, headerRange); mergeRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; rangeCount += 3; if (rangeCount > 240) { ExecuteFormat(newTargetRange, newMergeRange, newHeaderRange, templateRange); rangeCount = 0; newTargetRange = targetCell.Offset[0, 1]; //resets the union ranges back to their defaults newMergeRange = targetCell; newHeaderRange = targetCell; } shift++; } ExecuteFormat(newTargetRange, newMergeRange, newHeaderRange, templateRange); //execute the final batch ThisAddIn.MyApp.DisplayAlerts = true; ThisAddIn.MyApp.ScreenUpdating = true; }
public static void FollowLink() { //XlCall.Excel(XlCall.xlcAlert, "Follow Link Code Here!"); Excel.Application MyApp = (Excel.Application)ExcelDnaUtil.Application; string link_reference = (string)MyApp.ActiveCell.Formula; //cell reference to go to Primer.RefParser parser = new Primer.RefParser(link_reference, MyApp, Primer.RefType.A1); if (parser.malformed == false) { parser.firstCell.Worksheet.Activate(); parser.firstCell.Select(); } }
public static void SetArrayFormulas(string cellRange, string formula, Primer.RefType refType = Primer.RefType.A1) { var sheet = GetActiveSheet(); var parser = new Primer.RefParser(cellRange, ThisAddIn.MyApp, refType); if (refType == Primer.RefType.R1C1) { //Need to convert back to A1 cellRange = parser.ConvertRangeA1_R1C1(cellRange); } Excel.Range setRange = GetSheetRange(sheet, cellRange, refType); setRange.FormulaArray = formula; sheet.Cells[1, 1].Calculate(); //this doesn't seem to do much.. }
public MenuBuilder() { Selection = ObjModel.GetSelection(); Parser = new Primer.RefParser(Selection.Address, ThisAddIn.MyApp); CellRegion = GetRegion(); }