Chart Data Range

[Supermonster]

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

[harry.support]

Hi Anton,

Thanks for your inquiry.
You can set chart.DataRange = sheet.Range["A1:G5"] (in our demo, A1:G1 is the column header) and then remove the blank or invalid ChartSeries.
A full demo below is for you:

Code: Select all

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



[Supermonster]

Ok. I had such a guess

Thank you!