//************************************************************************* // Method: AddHistogramToGroupBox() // /// <summary> /// Adds a dynamic filter control histogram to a GroupBox. /// </summary> /// /// <param name="sWorksheetName"> /// Name of the worksheet containing the column the histogram is for. /// </param> /// /// <param name="oDynamicFilterParameters"> /// Parameters for the dynamic filter control. /// </param> /// /// <param name="oGroupBox"> /// GroupBox to which the histogram should be added. /// </param> /// /// <param name="iX"> /// x-coordinate of the histogram. /// </param> /// /// <param name="iY"> /// y-coordinate of the histogram. Gets increased by the height of the /// histogram. /// </param> /// /// <returns> /// The new PictureBox that displays the histogram. /// </returns> //************************************************************************* protected PictureBox 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); }
//************************************************************************* // Method: AddHistogramImageToPictureBox() // /// <summary> /// Adds a dynamic filter control histogram image to a PictureBox. /// </summary> /// /// <param name="sWorksheetName"> /// Name of the worksheet containing the column the histogram is for. /// </param> /// /// <param name="oDynamicFilterParameters"> /// Parameters for the dynamic filter control. /// </param> /// /// <param name="oPictureBox"> /// The PictureBox to which the histogram image should be added. /// </param> //************************************************************************* protected void 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.TryGetNamedRange(oOverallMetricsWorksheet, NamedRangeNames.DynamicFilterSourceColumnRange, out oDynamicFilterSourceColumnRange) && ExcelUtil.TryGetNamedRange(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(); } }