Formatting Charts with Microsoft.Office.Interop.Excel

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

Select all Open in new window