Outliers are not connected with the box for the boxplot chart

Aspose.Cells Product Family
1

I am unable to get the connecting lines for the outliers point and the box if the range between outlier point and upper quartile exceeds some value. I have attached the expected outcome excel sheet.
Here is the code :

var index = 0;
List colorList = new List { Color.Red, Color.Black, Color.AliceBlue};
const string excelTemplateRelativePath =
@“C:\Users\Documents\Visual Studio 2015\Projects\BoxPlot\ExcelFolder\ReportExcelTemplate.xlsx”;
var templatePath = excelTemplateRelativePath;
var excel = new Workbook(templatePath);
excel.Worksheets.AddCopy(index);
var sheet = excel.Worksheets[index];
var datacell = sheet.Cells[“A” + 1];

datacell.PutValue("");

        sheet.Cells["A2"].PutValue("Jan");
        sheet.Cells["A3"].PutValue("Jan");
        sheet.Cells["A4"].PutValue("Jan");
        sheet.Cells["A5"].PutValue("Jan");
        sheet.Cells["A6"].PutValue("Jan");
        sheet.Cells["A7"].PutValue("June");
        sheet.Cells["A8"].PutValue("June");
        sheet.Cells["A9"].PutValue("June");
        sheet.Cells["A10"].PutValue("June");
        sheet.Cells["A11"].PutValue("June");
     
        sheet.Cells["B1"].PutValue("Team A");
        sheet.Cells["B2"].PutValue(60);
        sheet.Cells["B3"].PutValue(70);
        sheet.Cells["B4"].PutValue(90);
        sheet.Cells["B5"].PutValue(150);
        sheet.Cells["B6"].PutValue(70);
        sheet.Cells["B7"].PutValue(80);
        sheet.Cells["B8"].PutValue(90);
        sheet.Cells["B9"].PutValue(110);
        sheet.Cells["B10"].PutValue(130);
        sheet.Cells["B11"].PutValue(150);
       
        sheet.Cells["C1"].PutValue("Team B");
        sheet.Cells["C2"].PutValue(40);
        sheet.Cells["C3"].PutValue(50);
        sheet.Cells["C4"].PutValue(60);
        sheet.Cells["C5"].PutValue(160);
        sheet.Cells["C6"].PutValue(30);
        sheet.Cells["C7"].PutValue(50);
        sheet.Cells["C8"].PutValue(20);
        sheet.Cells["C9"].PutValue(120);
        sheet.Cells["C10"].PutValue(140);
        sheet.Cells["C11"].PutValue(160);
       

        var chartArea = "B" + 2 + ":" + "C" + 11;

        var categoryData = "A" + 2 + ":A" + 11;
        int index1 = sheet.Charts.Add(ChartType.BoxWhisker, 0, 3, 0 + 20, 10);

        var chart = sheet.Charts[index1];
        chart.SetChartDataRange(chartArea, true);
        chart.NSeries.CategoryData = categoryData;
        var seriesName = new List<string> { "Team 1","Team 2"};
        int series_index = 0;
        int colorindex = 0;
       
        foreach (var series in seriesName)
        {
            chart.NSeries[series_index].Name = series;
            chart.NSeries[series_index].Area.FillFormat.FillType = FillType.None;
            
            chart.NSeries[series_index].LayoutProperties.ShowMeanLine = false;
            chart.NSeries[series_index].Border.WeightPt = 1;
            chart.NSeries[series_index].LayoutProperties.QuartileCalculation = QuartileCalculationType.Inclusive;
            chart.NSeries[series_index].LayoutProperties.ShowInnerPoints = false;
            chart.NSeries[series_index].Border.FormattingType = ChartLineFormattingType.Solid;
            chart.NSeries[series_index].Border.Style = LineType.Solid;
            chart.NSeries[series_index].Border.Color = colorList[series_index];
           
            series_index++;
        }
        
        chart.ValueAxis.Title.RotationAngle = 0;
        chart.ValueAxis.AxisLine.IsVisible = false;

        chart.PlotArea.Border.IsVisible = false;
        chart.PlotArea.Area.ForegroundColor = Color.White;
        chart.ChartArea.Border.IsVisible = false;

        excel.Save(templatePath + "_output.xlsx");

DemoChart.zip (14.9 KB)

2

@deergha,
We were able to observe the issue but we need to look into it more. We have logged the issue in our database for investigation and for a fix. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46599 - Outliers are not connected with the box for BoxWhisker chart
3

@deergha,

Could you try to set Quartile Calculation to Exclusive if it resolves the issue:
e.g
Sample code:

......
chart.NSeries[series_index].LayoutProperties.QuartileCalculation = QuartileCalculationType.Exclusive;
4

@Amjad_Sahi
sorry we need to use Inclusive quartileCalculationtype. If there is any other solution for this, please let us know.

5

@deergha,

Alright, we will consider your requirements. Let the next fix comes out (in the next few days), hopefully your issue will be fixed in it.

6

@Amjad_Sahi,
would it be possible for you to give me the expected date?

7

@deergha,

We are looking into your issue. I am afraid, there is no ETA at the moment. You may check back in the next week.

Once we have any new information, we will share it with you.

8

Hi @Amjad_Sahi,
Any update on this?

9

@deergha,

We evaluated your issue further. I’m afraid the issue cannot be fixed. MS Excel itself cannot do it too when Quartile Calculation is Inclusive in QuartileCalculationType. Could you please provide an expected Excel file (containing your desired chart) to us (you may create the chart in MS Excel manually), we will check it soon.

10

Hi @Amjad_Sahi,
Yes, even I noticed that when QuartileCalculationType is Inclusive, issue stills persists. I cannot create the desired output file in Excel.

11

@deergha,

Aspose.Cells follows Ms Excel standards and specifications when rendering charts. I am afraid if something is not achieved in MS Excel manually, Aspose.Cells cannot do that. Feel free to contact us any time if you have further comments or questions, we will be happy to assist you soon.