//*************************************************************************
// 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();
}
}