private void btnDrawIt_Click(object sender, EventArgs e)
{
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet oSheet;
Excel.Range oRng;
Excel._Chart oChart;
//Start Excel and get Application object.
oXL = new Excel.Application();
oXL.Visible = true;
//Get a new workbook.
oWB = (Excel._Workbook)(oXL.Workbooks.Add(Missing.Value));
oSheet = (Excel._Worksheet)oWB.ActiveSheet;
Random rand = new Random(DateTime.Now.Millisecond);
//Add table headers.
oSheet.Cells[1, 1] = "Key Indicators";
oSheet.Cells[2, 1] = "Total Employees";
oSheet.Cells[3, 1] = "Total Service Hours";
oSheet.Cells[4, 1] = "Performance Factor";
oSheet.Cells[5, 1] = "Serv Hrs per Employee";
oSheet.Cells[6, 1] = "Total Widgets";
oSheet.Cells[1, 2] = "Monthly Average";
oSheet.Cells[1, 3] = "'Jan 11";
oSheet.Cells[1, 4] = "'Feb 11";
oSheet.Cells[1, 5] = "'Mar 11";
oSheet.Cells[1, 6] = "'Apr 11";
oSheet.Cells[1, 7] = "'May 11";
oSheet.Cells[1, 8] = "'Jun 11";
oSheet.Cells[1, 9] = "'Jul 11";
oSheet.Cells[1, 10] = "'Aug 11";
oSheet.Cells[1, 11] = "'Sep 11";
oSheet.Cells[1, 12] = "'Oct 11";
oSheet.Cells[1, 13] = "'Nov 11";
oSheet.Cells[1, 14] = "'Dec 11";
// Format the title cells
oSheet.get_Range("A1", "N1").Font.Bold = true;
oSheet.get_Range("A1", "N1").VerticalAlignment =
Excel.XlVAlign.xlVAlignCenter;
oSheet.get_Range("A2", "A2").WrapText = true;
// Fill rows with random data, populated for Jan, Feb, and Mar only.
// The "Total Employees" and "Total Widgets" are integer. The others are float.
for (int rowCounter = 2; rowCounter < 7; rowCounter++)
{
for (int columnCounter = 2; columnCounter < 6; columnCounter++)
{
if ((rowCounter == 2) || (rowCounter == 6))
{
int theThing = rand.Next(11);
oSheet.Cells[rowCounter, columnCounter] = theThing;
}
else
{
double theThing = rand.NextDouble() * 10.0;
oSheet.Cells[rowCounter, columnCounter] = theThing;
}
}
}
//Format the floating point cells
oSheet.get_Range("B3", "M3").NumberFormat = "#.00";
oSheet.get_Range("B4", "M4").NumberFormat = "#.00";
oSheet.get_Range("B5", "M5").NumberFormat = "#.00";
// Autofit the cells
oRng = oSheet.get_Range("A1", "N1");
oRng.EntireColumn.AutoFit();
//Add a Chart for a comparison of Total Service Hours vs. Total Widgets only.
// Create the initial chart
oChart = (Excel._Chart)oWB.Charts.Add(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
oChart.Name = "TheChartName";
// Define the range for the data to be charted. Note that this initally includes some rows that we don't want.
Excel.Range chartRange = oSheet.get_Range("A1", "N6");
// Use the chartwizard to customize the chart
oChart.ChartWizard(chartRange, // use the range that we just defined
Excel.XlChartType.xlLine, // this is just a line chart
Missing.Value,
Excel.XlRowCol.xlRows, // the series data is in the rows, not the columns
1, // the row and column contain labels
1,
true, // include a legend
"This is my chart title", // this is the chart title
Missing.Value,
Missing.Value,
Missing.Value);
// Get the collection of data rows that are included in the chart
Excel.SeriesCollection myCollection = (Excel.SeriesCollection)oChart.SeriesCollection(Missing.Value);
// Remove the lines that we do not want
myCollection.Item(4).Delete();
myCollection.Item(3).Delete();
myCollection.Item(1).Delete();
// Format the chart title
oChart.ChartTitle.Font.Name = "Arial";
oChart.ChartTitle.Font.Size = 20;
oChart.ChartTitle.Font.Color = ColorTranslator.ToOle(System.Drawing.Color.Red);
// Format the chart border
oChart.ChartArea.Border.Color = ColorTranslator.ToOle(System.Drawing.Color.Blue);
oChart.ChartArea.Border.Weight = Excel.XlBorderWeight.xlMedium;
Excel.Axis theCategoryAxis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlCategory, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
Excel.Axis theValueAxis = (Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue, Microsoft.Office.Interop.Excel.XlAxisGroup.xlPrimary);
// ????? How do I set the font name, the font size, the bold setting, and the color
// ????? for each axis. I'm not talking about an AxisTitle. I'm talking about the
// ????? labels on the tick marks on each axis, i.e. the "Jan 11", "Feb 11", etc.
// ????? Second question - How do I set the background color for the chart?
// Add the chart to the original worksheet rather than have it as a stand-alone chart
oChart.Location(Excel.XlChartLocation.xlLocationAsObject, oSheet.Name);
int myIndex = oSheet.Shapes.Count;
// Stretch the chart to span the range A9 through N20
oRng = (Excel.Range)oSheet.get_Range("A9", "N20");
oSheet.Shapes.Item(myIndex).Top = (float)(double)oRng.Top;
oSheet.Shapes.Item(myIndex).Left = (float)(double)oRng.Left;
oSheet.Shapes.Item(myIndex).Width = (float)(double)oRng.Width;
oSheet.Shapes.Item(myIndex).Height = (float)(double)oRng.Height;
//Make sure Excel is visible and give the user control of Microsoft Excel's lifetime.
oXL.Visible = true;
oXL.UserControl = true;
}
Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.
TRUSTED BY
ASKER