I have the following requirement: datarange for my chart is not a solid range of cells. For example, values can be in cells A2:D5 and G2:G5. How can I add all the series to chart?
Thanks, Anton
using Spire.Xls;
using Spire.Xls.Charts;
namespace MySupport
{
class Program
{
static void Main(string[] args)
{
Workbook workbook = new Workbook();
//Initailize worksheet
workbook.CreateEmptySheets(1);
Worksheet sheet = workbook.Worksheets[0];
sheet.Name = "Chart data";
sheet.GridLinesVisible = false;
//Writes chart data
CreateChartData(sheet);
//Add a new chart worsheet to workbook
Chart chart = sheet.Charts.Add();
//Set region of chart data
chart.DataRange = sheet.Range["A1:G5"];
chart.SeriesDataFromRange = false;
//Set position of chart
chart.LeftColumn = 1;
chart.TopRow = 6;
chart.RightColumn = 11;
chart.BottomRow = 29;
chart.ChartType = ExcelChartType.Area;
//Chart title
chart.ChartTitle = "Sales market by country";
chart.ChartTitleArea.IsBold = true;
chart.ChartTitleArea.Size = 12;
chart.PrimaryCategoryAxis.Title = "Country";
chart.PrimaryCategoryAxis.Font.IsBold = true;
chart.PrimaryCategoryAxis.TitleArea.IsBold = true;
chart.PrimaryValueAxis.Title = "Sales(in Dollars)";
chart.PrimaryValueAxis.HasMajorGridLines = false;
chart.PrimaryValueAxis.TitleArea.TextRotationAngle = 90;
chart.PrimaryValueAxis.MinValue = 1000;
chart.PrimaryValueAxis.TitleArea.IsBold = true;
// remove series refering to E2:F5
for (int i = chart.Series.Count - 1; i >= 0; i--)
{
if (chart.Series[i].Values.RangeAddress.EndsWith("E2:E5")
|| chart.Series[i].Values.RangeAddress.EndsWith("F2:F5"))
{
chart.Series.RemoveAt(i);
}
}
foreach (ChartSerie cs in chart.Series)
{
cs.Format.Options.IsVaryColor = true;
cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true;
}
chart.Legend.Position = LegendPositionType.Top;
workbook.SaveToFile("Sample.xls");
System.Diagnostics.Process.Start("Sample.xls");
}
private static void CreateChartData(Worksheet sheet)
{
//Country
sheet.Range["A1"].Value = "Country";
sheet.Range["A2"].Value = "Cuba";
sheet.Range["A3"].Value = "Mexico";
sheet.Range["A4"].Value = "France";
sheet.Range["A5"].Value = "German";
//Jun
sheet.Range["B1"].Value = "Jun";
sheet.Range["B2"].NumberValue = 6000;
sheet.Range["B3"].NumberValue = 8000;
sheet.Range["B4"].NumberValue = 9000;
sheet.Range["B5"].NumberValue = 8500;
//Aug
sheet.Range["C1"].Value = "Aug";
sheet.Range["C2"].NumberValue = 3000;
sheet.Range["C3"].NumberValue = 2000;
sheet.Range["C4"].NumberValue = 2300;
sheet.Range["C5"].NumberValue = 4200;
//Sep
sheet.Range["D1"].Value = "Sep";
sheet.Range["D2"].NumberValue = 3000;
sheet.Range["D3"].NumberValue = 2000;
sheet.Range["D4"].NumberValue = 2300;
sheet.Range["D5"].NumberValue = 4200;
//Oct
sheet.Range["G1"].Value = "Oct";
sheet.Range["G2"].NumberValue = 3000;
sheet.Range["G3"].NumberValue = 2000;
sheet.Range["G4"].NumberValue = 2300;
sheet.Range["G5"].NumberValue = 4200;
//Style
sheet.Range["A1:D1"].Style.Font.IsBold = true;
sheet.Range["G1"].Style.Font.IsBold = true;
sheet.Range["B2:D5"].Style.NumberFormat = "\"$\"#,##0";
sheet.Range["G2:G5"].Style.NumberFormat = "\"$\"#,##0";
}
}
}