AddHistogramImageToPictureBox ( String sWorksheetName, DynamicFilterParameters oDynamicFilterParameters, PictureBoxPlus oPictureBox ) { Debug.Assert( !String.IsNullOrEmpty(sWorksheetName) ); Debug.Assert(oDynamicFilterParameters != null); Debug.Assert(oPictureBox != null); AssertValid(); // This method uses the following technique to get Excel to generate // the histogram image. // // There is a hidden chart on the Miscellaneous worksheet that is // used for the histogram. It gets its data from two columns in a // hidden table on the Overall Metrics worksheet that use Excel // formulas to calculate the frequency distribution of the values in an // Excel column, called the "source column." The formulas use Excel's // INDIRECT() function to get the address of the source column from a // cell named NamedRange.DynamicFilterSourceColumnRange. // // In a new workbook, the frequency distribution columns initially // contain all #REF! errors, because the named range is empty. This // method sets the named range to something like // "Vertices[ColumnName]", then forces Excel to recalculate the // frequency distribution columns. This causes a histogram to appear // in the chart. An image of the chart is then copied to the clipboard // and pasted into the PictureBox. Microsoft.Office.Interop.Excel.Worksheet oOverallMetricsWorksheet, oMiscellaneousWorksheet; Microsoft.Office.Interop.Excel.Range oDynamicFilterSourceColumnRange, oDynamicFilterForceCalculationRange; Microsoft.Office.Interop.Excel.Chart oDynamicFilterHistogram; if ( ExcelUtil.TryGetWorksheet(m_oWorkbook, WorksheetNames.OverallMetrics, out oOverallMetricsWorksheet) && ExcelUtil.TryGetRange(oOverallMetricsWorksheet, NamedRangeNames.DynamicFilterSourceColumnRange, out oDynamicFilterSourceColumnRange) && ExcelUtil.TryGetRange(oOverallMetricsWorksheet, NamedRangeNames.DynamicFilterForceCalculationRange, out oDynamicFilterForceCalculationRange) && ExcelUtil.TryGetWorksheet(m_oWorkbook, WorksheetNames.Miscellaneous, out oMiscellaneousWorksheet) && ExcelUtil.TryGetChart(oMiscellaneousWorksheet, ChartNames.DynamicFilterHistogram, out oDynamicFilterHistogram) ) { // Set the named range to the address of the source column. // Sample: "Vertices[Degree]". oDynamicFilterSourceColumnRange.set_Value(Missing.Value, String.Format( "{0}[{1}]", sWorksheetName, oDynamicFilterParameters.ColumnName ) ); // Excel's automatic calculation may be turned off, either by the // user or by code elsewhere in this dialog. Make sure the // frequency distribution columns get calculated. oDynamicFilterForceCalculationRange.Calculate(); // Make sure the chart is drawn immediately. oDynamicFilterHistogram.Refresh(); // Tell Excel to copy the chart image to the clipboard. (Although // the second argument to CopyPicture is xlBitmap, no bitmap gets // copied. Instead, Excel uses an enhanced metafile.) oDynamicFilterHistogram.CopyPicture( Microsoft.Office.Interop.Excel.XlPictureAppearance.xlScreen, Microsoft.Office.Interop.Excel.XlCopyPictureFormat.xlBitmap, Microsoft.Office.Interop.Excel.XlPictureAppearance.xlScreen); oPictureBox.TryPasteEnhancedMetafile(); } }
AddHistogramToGroupBox ( String sWorksheetName, DynamicFilterParameters oDynamicFilterParameters, GroupBox oGroupBox, Int32 iX, ref Int32 iY ) { Debug.Assert( !String.IsNullOrEmpty(sWorksheetName) ); Debug.Assert(oDynamicFilterParameters != null); Debug.Assert(oGroupBox != null); AssertValid(); // The histogram is a PictureBox containing an image created by Excel. PictureBoxPlus oPictureBox = new PictureBoxPlus(); oPictureBox.Location = new Point(iX, iY); oPictureBox.Margin = Padding.Empty; oPictureBox.Height = HistogramHeight; oPictureBox.SizeMode = PictureBoxSizeMode.StretchImage; oPictureBox.Anchor = AnchorStyles.Left | AnchorStyles.Top | AnchorStyles.Right; oGroupBox.Controls.Add(oPictureBox); AddHistogramImageToPictureBox(sWorksheetName, oDynamicFilterParameters, oPictureBox); iY += oPictureBox.Height + HistogramBottomMargin; return (oPictureBox); }