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;
}
}
