Adding Chart Shapes and Chart Worksheets with ExpertXls |
A chart can be placed in a worksheet, as a chart shape, or can fill a worksheet of its own, a chart worksheet. The charts of a worksheet are in its Charts collection and the chart worksheets of a workbook are in its ChartWorksheets collection.
The series of a chart are generated from a data source range or added one by one. With a data source range, the first row and the first column of the range hold the category and series names, and the seriesByRows parameter tells if each row or each column of the range is a series. ExcelChartType lists the 2D and 3D chart types: column, bar, line, area, pie, doughnut, radar, scatter, stock, cone, cylinder and pyramid charts.
Gradient, texture and preset fills of the chart area and plot area are Excel 2007 or later features; for Excel 97-2003 workbooks use solid fills.
The data source range B7:G12 has the product names in row 7 (C7:G7), the quarter names in column B (B8:B12) and the values in C8:G12. The chart is placed over the cells of columns 2 to 8, rows 16 to 33.
ExcelChart chart = worksheet.Charts.AddChart(ExcelChartType.ColumnClustered, worksheet["B7:G12"], true, 2, 16, 8, 33); chart.Title.Text = "Product Units Sold per Quarter"; chart.Title.Font.Size = 12; chart.Title.Font.Color = Color.DarkBlue; // the axes chart.ValueAxis.Title.Text = "Units sold"; chart.ValueAxis.ShowVerticalTitleText(); chart.CategoryAxis.Title.Text = "Products"; chart.CategoryAxis.Font.Size = 8; // the chart area and plot area if (workbook.Format == ExcelWorkbookFormat.Xlsx_2007) { chart.ChartArea.Interior.FillType = ExcelShapeFillType.GradientFill; chart.ChartArea.Interior.GradientFillOptions.GradientColorsMode = ExcelGradientColors.Preset; chart.ChartArea.Interior.GradientFillOptions.PresetGradientType = ExcelShapeFillPresetGradientType.Wheat; chart.PlotArea.Interior.FillType = ExcelShapeFillType.TextureFill; chart.PlotArea.Interior.TextureFillOptions.TextureType = ExcelShapeFillTextureType.Stationery; } else { chart.ChartArea.Interior.FillType = ExcelShapeFillType.SolidFill; chart.ChartArea.Interior.SolidFillOptions.BackColor = Color.FromArgb(255, 204, 153); } // the legend and the data labels of one series chart.Legend.Position = ExcelChartLegendPosition.Bottom; chart.Series["Yearly Total"].DataPoints.All.Label.ContainsValue = true;
ExcelChart customChart = worksheet.Charts.AddChart(ExcelChartType.LineMarkers, 2, 36, 8, 54); customChart.Title.Text = "Units Sold of the First Three Products"; ExcelChartSeries firstQuarter = customChart.Series.AddSeries("First Quarter"); firstQuarter.CategoryNamesRange = worksheet["C7:E7"]; firstQuarter.ValuesRange = worksheet["C8:E8"]; ExcelChartSeries fourthQuarter = customChart.Series.AddSeries("Fourth Quarter"); fourthQuarter.CategoryNamesRange = worksheet["C7:E7"]; fourthQuarter.ValuesRange = worksheet["C11:E11"]; fourthQuarter.DataPoints.All.Label.ContainsValue = true; // a data table with the values below the chart customChart.ShowDataTable = true; customChart.DataTable.ShowLegendKey = true;
A chart worksheet is added after the worksheets of the workbook: