public static void ProcessSheetWithProcessor(Worksheet sheet, ILogProcessor processor) { Sheets sheets = ThisAddIn.app.ActiveWorkbook.Sheets; // Check if the data chosen was already processed for (int n = 1; n <= sheets.Count; n++) { if (sheets[n].Name == Util.GetProcessedName(sheet.Name)) { return; } } // Disable UI updates ThisAddIn.app.ScreenUpdating = false; bool displayStatusBar = ThisAddIn.app.DisplayStatusBar; ThisAddIn.app.DisplayStatusBar = false; XlCalculation calculation = ThisAddIn.app.Calculation; ThisAddIn.app.Calculation = XlCalculation.xlCalculationManual; bool enableEvents = ThisAddIn.app.EnableEvents; ThisAddIn.app.EnableEvents = false; Worksheet outputSheet = AddSheetForOutput(sheet); System.Diagnostics.Trace.WriteLine("About to construct FastSheetReader at " + new DateTimeOffset(DateTime.UtcNow).ToUnixTimeSeconds()); FastSheetReader reader = new FastSheetReader(sheet, processor.GetSeparator()); System.Diagnostics.Trace.WriteLine("Finished constructing FastSheetReader at " + new DateTimeOffset(DateTime.UtcNow).ToUnixTimeSeconds()); FastSheetWriter writer = new FastSheetWriter(outputSheet); System.Diagnostics.Trace.WriteLine("Finished constructing FastSheetWriter at " + new DateTimeOffset(DateTime.UtcNow).ToUnixTimeSeconds()); Util.RunPipeline(reader, processor, writer, true); System.Diagnostics.Trace.WriteLine("Finished running pipeline at " + new DateTimeOffset(DateTime.UtcNow).ToUnixTimeSeconds()); writer.Flush(); System.Diagnostics.Trace.WriteLine("Flushed writer at " + new DateTimeOffset(DateTime.UtcNow).ToUnixTimeSeconds()); // Tag the sheet with a machine type mark so we don't have to dig into the cell // data to identify machine type when trying to generate summary statistics outputSheet.CustomProperties.Add(Util.MACHINE_TYPE_MARK_NAME, processor.GetUniqueTag()); // Re-enable UI updates ThisAddIn.app.ScreenUpdating = true; ThisAddIn.app.DisplayStatusBar = displayStatusBar; ThisAddIn.app.Calculation = calculation; ThisAddIn.app.EnableEvents = enableEvents; writer.FormatPretty(); System.Diagnostics.Trace.WriteLine("Finished format pretty at " + new DateTimeOffset(DateTime.UtcNow).ToUnixTimeSeconds()); // Don't AutoFit, as it can be really slow. outputSheet.Columns.AutoFit(); System.Diagnostics.Trace.WriteLine("Returning at " + new DateTimeOffset(DateTime.UtcNow).ToUnixTimeSeconds()); }
public static bool CopyPasteText(string txt, Range target) { bool ret = false; // save current application settings XlCalculation saveCalc = default(XlCalculation); bool saveDisplayAlerts = false; bool saveScreenUpdating = false; bool saveEnableEvents = false; saveCalc = target.Application.Calculation; saveDisplayAlerts = target.Application.DisplayAlerts; saveScreenUpdating = target.Application.ScreenUpdating; saveEnableEvents = target.Application.EnableEvents; // disable application auto settings to improve performance target.Application.DisplayAlerts = false; target.Application.Calculation = XlCalculation.xlCalculationManual; target.Application.ScreenUpdating = false; target.Application.EnableEvents = false; //Dim lineCnt = Split(txt, vbLf).Length // resize the table to fit rows and columns target.Application.EnableAutoComplete = false; //Dim rng As Range = target.ListObject.Range.Resize(lineCnt + 1, target.ListObject.ListColumns.Count) //target.ListObject.Resize(rng) try { Clipboard.SetText(txt); target.Worksheet.Paste(target); Clipboard.Clear(); ret = true; } catch { Clipboard.Clear(); ret = false; } target.Application.EnableAutoComplete = true; target.Application.DisplayAlerts = saveDisplayAlerts; target.Application.Calculation = saveCalc; target.Application.ScreenUpdating = saveScreenUpdating; target.Application.EnableEvents = saveEnableEvents; return(ret); }
//Public Sub ApplicationInfo() // Try // Debug.Print("Application.CommonAppDataPath:" & Application.CommonAppDataPath.ToString) // Debug.Print("Application.CommonAppDataRegistry:" & Application.CommonAppDataRegistry.ToString) // Debug.Print("Application.CompanyName:" & Application.CompanyName.ToString) // Debug.Print("Application.CurrentCulture:" & Application.CurrentCulture.ToString) // Debug.Print("Application.CurrentInputLanguage:" & Application.CurrentInputLanguage.ToString) // Debug.Print("Application.ExecutablePath:" & Application.ExecutablePath.ToString) // Debug.Print("Application.LocalUserAppDataPath:" & Application.LocalUserAppDataPath.ToString) // Debug.Print("Application.ProductName:" & Application.ProductName.ToString) // Debug.Print("Application.ProductVersion:" & Application.ProductVersion.ToString) // Debug.Print("Application.SafeTopLevelCaptionFormat:" & Application.SafeTopLevelCaptionFormat.ToString) // Debug.Print("Application.StartupPath:" & Application.StartupPath.ToString) // Debug.Print("Application.UserAppDataPath:" & Application.UserAppDataPath.ToString) // Debug.Print("Application.UserAppDataRegistry:" & Application.UserAppDataRegistry.ToString) // Debug.Print("ThisAddin.Application.StartupPath:" & Globals.ThisAddIn.Application.StartupPath.ToString) // Debug.Print("ThisAddin.Application.ActiveWorkbook.Name:" & Globals.ThisAddIn.Application.ActiveWorkbook.Name.ToString) // Debug.Print("ThisAddin.Application.ActiveWorkbook.Path:" & Globals.ThisAddIn.Application.ActiveWorkbook.Path.ToString) // Debug.Print("ThisAddin.Application.ActiveWorkbook.FullName:" & Globals.ThisAddIn.Application.ActiveWorkbook.FullName.ToString) // Debug.Print("ThisAddin.Application.ActiveWorkbook.FullNameURLEncoded:" & Globals.ThisAddIn.Application.ActiveWorkbook.FullNameURLEncoded.ToString) // Debug.Print("ThisAddin.Application.DefaultFilePath:" & Globals.ThisAddIn.Application.DefaultFilePath.ToString) // Debug.Print("ThisAddin.Application.Name:" & Globals.ThisAddIn.Application.Name.ToString) // Debug.Print("ThisAddin.Application.NetworkTemplatesPath:" & Globals.ThisAddIn.Application.NetworkTemplatesPath.ToString) // Debug.Print("ThisAddin.Application.Path:" & Globals.ThisAddIn.Application.Path.ToString) // Catch ex As Exception // MessageBox.Show("ApplicationInfo():" & ex.ToString) // End Try //End Sub public static void CalculationsOff() { // Don't bother trying to save current if no open workbooks. if (ExcelApplication.Workbooks.Count > 0) { PriorCalculationState = ExcelApplication.Calculation; ExcelApplication.Calculation = XlCalculation.xlCalculationManual; } else { // Assume the intent is to run with calculation and screen updates on. // Hopefully we never get called with no workbooks open. PriorCalculationState = XlCalculation.xlCalculationAutomatic; } }
public void OnProfileActiveWorkbook(IRibbonControl control) { Application app = (Application)ExcelDnaUtil.Application; Profiler profiler = new Profiler(app.ActiveWorkbook); // save calc settings XlCalculation calcSave = app.Calculation; bool iterSave = app.Iteration; if (app.Calculation != XlCalculation.xlCalculationManual) { app.Calculation = XlCalculation.xlCalculationManual; } WorkbookTimerResult result = profiler.GetWorkbookTimerResults(); Workbook wb = app.Workbooks.Add(XlWBATemplate.xlWBATWorksheet); Worksheet sheet = (Worksheet)wb.Sheets[1]; sheet.Name = "Summary"; sheet.Cells[1, 1].Value2 = "Calculation Time Summary (in ms)"; sheet.Cells[2, 1].Value2 = profiler.WorkbookName; sheet.Cells[4, 2].Value2 = "FullCalc"; sheet.Cells[4, 3].Value2 = "Recalc"; sheet.Cells[4, 4].Value2 = "Volatility"; sheet.Cells[5, 1].Value2 = "Workbook"; sheet.Cells[5, 2].Value2 = result.FullCalcTimerResult; sheet.Cells[5, 3].Value2 = result.RecalcTimerResult; sheet.Cells[5, 4].Value2 = result.RecalcTimerResult / result.FullCalcTimerResult; sheet.Cells[7, 1].Value2 = "SheetName"; sheet.Cells[7, 2].Value2 = "SheetCalc"; sheet.Cells[7, 3].Value2 = "UsedRange"; sheet.Cells[7, 4].Value2 = "Overhead"; int row = 8; foreach (SheetTimerResult sheetResult in result.SheetTimerResults) { sheet.Cells[row, 1].Value2 = sheetResult.SheetName; sheet.Cells[row, 2].Value2 = sheetResult.SheetCalcTime; sheet.Cells[row, 3].Value2 = sheetResult.UsedRangeCalcTime; sheet.Cells[row, 4].Value2 = Math.Max(sheetResult.SheetCalcTime - sheetResult.UsedRangeCalcTime, 0.0); row++; } //format results ((Range)sheet.Columns[1]).EntireColumn.AutoFit(); ((Range)sheet.Columns[2]).EntireColumn.AutoFit(); ((Range)sheet.Columns[3]).EntireColumn.AutoFit(); ((Range)sheet.Columns[4]).EntireColumn.AutoFit(); ListObject table = sheet.ListObjects.AddEx(XlListObjectSourceType.xlSrcRange, sheet.Range["$A$7:$D$" + (row - 1).ToString()], null, XlYesNoGuess.xlYes); table.Name = "SheetCalcResults"; table.ShowTableStyleRowStripes = false; //sort sheet calc results table.Sort.SortFields.Clear(); table.Sort.SortFields.Add(sheet.Range["B8"], XlSortOn.xlSortOnValues, XlSortOrder.xlDescending, XlSortDataOption.xlSortNormal); table.Sort.Header = XlYesNoGuess.xlYes; table.Sort.MatchCase = false; table.Sort.SortMethod = XlSortMethod.xlPinYin; table.Sort.Apply(); sheet.Range["A1"].Select(); //restore calc settings if (app.Calculation != calcSave) { app.Calculation = calcSave; } if (app.Iteration != iterSave) { app.Iteration = iterSave; } }