Setting Box Whisker Chart Style in Excel

deergha · January 28, 2019, 5:55am

Using Aspose Cells for .NET version 19.1.0.0

Hi,

I want to change chart style for box whisker such that it hides the mean line of the series data and no color in the box when exported to excel sheet.

Sample Code:
int index = sheet.Charts.Add(ChartType.BoxWhisker, 3 3, 20, 10);
var chart = sheet.Charts[index];
var chartArea = “B5:C15”;
var categoryData = “A5:A15”;
chart.SetChartDataRange(chartArea, true);
chart.NSeries.CategoryData = categoryData;

int i = 0;
foreach (var series in chartData.Series)
{
chart.NSeries[i].Name = series.name;
i++;
}

chart.SeriesAxis.AxisBetweenCategories = false;
chart.SecondValueAxis.AxisBetweenCategories = false;

Chart.png (11.9 KB)

amjad.sahi · January 28, 2019, 8:39am

@deergha,

Thanks for the sample code segment and screenshot.

Could you provide a sample Excel file (containing your desired chart in it, you may create/format the chart manually in MS Excel), we will check it soon. Also kindly provide your complete code (runnable) and output Excel file by Aspose.Cells APIs. This will help us evaluate your issue precisely and to consequently figure it out soon.

deergha · January 28, 2019, 9:39am

Hi @Amjad_Sahi,
thanks for the response. Here is the link for the excel sheet:
Chart BoxWhisker.zip (10.7 KB)

Sample Code::
The below function is called for crearting box whisker in Excel.

private void CreateBoxChart(LayoutContainer chartInfo, BoxPlotChartData chartData,
ChartConfig chartConfig, Worksheet sheet, char columnName, int numOfCategory)
{

        int index = sheet.Charts.Add(ChartType.BoxWhisker, ColumnHeadersRow, 3, ColumnHeadersRow + 20, 10);

        var chart = sheet.Charts[index];
        var chartArea = "B" + (ChartDataStartRow + 1 ) + ":" + columnName + numOfCategory;
    
        var categoryData = "A" + (ChartDataStartRow + 1 ) + ":A" + numOfCategory;
        chart.SetChartDataRange(chartArea, true);
        chart.NSeries.CategoryData = categoryData;
       
        int i = 0;
        foreach (var series in chartData.Series)
        {
            chart.NSeries[i].Name = series.name;
            chart.NSeries[i].LayoutProperties.ShowMeanLine = false;
            chart.NSeries[i].Area.FillFormat.FillType = FillType.None;
            chart.NSeries[i].Border.FormattingType = ChartLineFormattingType.Solid;
            chart.NSeries[i].Border.Style = LineType.Solid;
            i++;
        }

        chart.SeriesAxis.AxisBetweenCategories = false;
        chart.SecondValueAxis.AxisBetweenCategories = false;
        chart.CategoryAxis.AxisBetweenCategories = false;
        
        if (chartConfig.XAxisConfigs != null)
        {
            chart.CategoryAxis.Title.Text = chartConfig.XAxisConfigs.Title ?? "";
        }
        chart.CategoryAxis.Title.RotationAngle = 0;
        chart.CategoryAxis.AxisLine.IsVisible = false;


        if (chartConfig.YAxisConfigs != null)
        {
            chart.ValueAxis.Title.Text = chartConfig.YAxisConfigs.Title ?? "";
        }
        chart.ValueAxis.Title.RotationAngle = 0;
        chart.ValueAxis.AxisLine.IsVisible = false;

        
    }

On hovering the chart, I am unable to set the series name through the code. But the same works if the chart is created on selecting the data from ms-excel. Please help me to set the series name as well.

ahsaniqbalsidiqui · January 28, 2019, 3:46pm

@deergha,

Thank you for sample code and Excel sheet. We are afraid to share that this code cannot be used to reproduce the scenario as there are many missing references like LayoutContainer, BoxPlotChartData and ChartConfig. You may please share a complete sample solution which can be executed standalone. Share the output file created by the program and the expected output file created by Excel with differentiation. Also provide some images etc. for exact issue identification.

deergha · January 29, 2019, 6:27am

Hi @ahsaniqbalsidiqui
Here is the running code for Box Whisker:

using System.Collections.Generic;
using Aspose.Cells;
using Aspose.Cells.Charts;
using Aspose.Cells.Drawing;

namespace Test_BoX_whisker
{
class Program
{
static void Main(string[] args)
{
var index = 0;
var sheetName = “BoxwhiskerChart”;
const string excelTemplateRelativePath = @“C:\Test\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(“”);
for (int i = 2; i <= 6; i++)
{
datacell = sheet.Cells[“A” + i];
datacell.PutValue(“1-Jan”);
}
for (int i = 7; i <= 11; i++)
{
datacell = sheet.Cells[“A” + i];
datacell.PutValue(“2-Jan”);
}
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[“C2”].PutValue(40);
sheet.Cells[“C3”].PutValue(50);
sheet.Cells[“C4”].PutValue(60);
sheet.Cells[“C5”].PutValue(150);
sheet.Cells[“C6”].PutValue(30);
sheet.Cells[“C7”].PutValue(50);
sheet.Cells[“C8”].PutValue(90);
sheet.Cells[“C9”].PutValue(110);
sheet.Cells[“C10”].PutValue(140);
sheet.Cells[“C11”].PutValue(150);
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 {“Team 1”, “Team 2”};
int series_index = 0;
foreach (var series in seriesName)
{
chart.NSeries[series_index].Name = series;
chart.NSeries[series_index].LayoutProperties.ShowMeanLine = false;
chart.NSeries[series_index].Area.FillFormat.FillType = FillType.None;
chart.NSeries[series_index].Border.FormattingType = ChartLineFormattingType.Solid;
chart.NSeries[series_index].Border.Style = LineType.Solid;
series_index++;
}
excel.Save(templatePath+“output.xlsx”);
}
}
}

This code creates Box whisker Chart at the specified location. I have attached the desired excel sheet file. The file contains both the charts - generated by the system as well as expected output.

Boxwhisker.zip (16.9 KB)

ahsaniqbalsidiqui · January 29, 2019, 3:29pm

@deergha,

Thank you for the sample code. You are also requested to send us the template file “ReportExcelTemplate.xlsx” file as my own sample files are not creating the desired output through your sample code.

deergha · January 30, 2019, 5:29am

Hi @ahsaniqbalsidiqui,

Here is the executable code having ReportExcelTemplate.xlsx attached in Excel folder.
BoXChartCode.zip (17.4 KB)

The attachment - BoxWhisker.zip has the expected outcome sheet. Boxwhisker.zip (16.9 KB)

ahsaniqbalsidiqui · January 30, 2019, 3:55pm

@deergha,

Thanks for providing all the necessary information to reproduce the problem here. We were able to observe the issue but we need to look into it more. We have logged the requirement in our database for investigation and for a solution. Once, we will have some news for you, we will update you in this topic.

This issue has been logged as

CELLSNET-46568 - Setting Box Whisker Chart Style

deergha · February 1, 2019, 5:02am

@ahsaniqbalsidiqui,

Please let me know if there is any update.

ahsaniqbalsidiqui · February 1, 2019, 8:23am

@deergha,

We have fixed your issue (logged earlier as “CELLSNET-46568”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

deergha · February 1, 2019, 8:26am

@ahsaniqbalsidiqui
Is there any planned release date for this issue? Also, I would like to know if we can give custom color for series in line, area, scatter, bar and column chart.

amjad.sahi · February 1, 2019, 8:35am

@deergha,

You may expect the fix in the next few days (3-5) in the fix was not delivered today.

Yes, sure, you may do that for other chart types. If you find any issue, give us complete details, sample code (runnable) and template file(s) to show the issue, we will check it soon.

amjad.sahi · February 1, 2019, 10:15am

@deergha,

Please try our latest version/fix: Aspose.Cells for .NET v19.1.5 (attached)

Your issue should be fixed in it.

Please try your scenario with the following sample code:
e.g
Sample code:

  var index = 0;
            var sheetName = "BoxwhiskerChart";
            //string excelTemplateRelativePath = Constants.sourcePath + @"CELLSNET46568.xlsx";
            //var templatePath = excelTemplateRelativePath;
            const string excelTemplateRelativePath = "e:\\test2\\Boxwhisker.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("");
            for (int i = 2; i <= 6; i++)
            {
                datacell = sheet.Cells["A" + i];
                datacell.PutValue("19-Jan");
            }
            for (int i = 7; i <= 11; i++)
            {
                datacell = sheet.Cells["A" + i];
                datacell.PutValue("1-Feb");
            }

            sheet.Cells["B1"].PutValue("Team A");
            sheet.Cells["B2"].PutValue(50);
            sheet.Cells["B3"].PutValue(150);
            sheet.Cells["B4"].PutValue(300);
            sheet.Cells["B5"].PutValue(400);
            sheet.Cells["B6"].PutValue(500);
            sheet.Cells["B7"].PutValue(50);
            sheet.Cells["B8"].PutValue(150);
            sheet.Cells["B9"].PutValue(300);
            sheet.Cells["B10"].PutValue(400);
            sheet.Cells["B11"].PutValue(500);

            sheet.Cells["C1"].PutValue("Team B");
            sheet.Cells["C2"].PutValue(50);
            sheet.Cells["C3"].PutValue(150);
            sheet.Cells["C4"].PutValue(300);
            sheet.Cells["C5"].PutValue(400);
            sheet.Cells["C6"].PutValue(500);
            sheet.Cells["C7"].PutValue(50);
            sheet.Cells["C8"].PutValue(150);
            sheet.Cells["C9"].PutValue(300);
            sheet.Cells["C10"].PutValue(400);
            sheet.Cells["C11"].PutValue(500);
            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" };
            var seriesTheme = new List<ThemeColorType> { ThemeColorType.Accent1, ThemeColorType.Accent2 };
            int series_index = 0;
            foreach (var series in seriesName)
            {
                chart.NSeries[series_index].Name = series;
                chart.NSeries[series_index].LayoutProperties.ShowMeanLine = false;
                chart.NSeries[series_index].LayoutProperties.ShowInnerPoints = false;
                chart.NSeries[series_index].Area.FillFormat.FillType = FillType.None;
                chart.NSeries[series_index].Border.FormattingType = ChartLineFormattingType.Solid;
                chart.NSeries[series_index].Border.Style = LineType.Solid;
                chart.NSeries[series_index].Border.ThemeColor = new ThemeColor(seriesTheme[series_index], 0);
                series_index++;
            }

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


    }

Let us know your feedback.
Aspose.Cells19.1.5 For .Net2_AuthenticodeSigned.Zip (4.7 MB)
Aspose.Cells19.1.5 For .Net4.0.Zip (4.8 MB)

deergha · February 1, 2019, 11:54am

Hi @Amjad_Sahi,

Thanks for the response. It is working fine except that I am unable to get the legend. Can you please look into the issue.

Also, I have attached sample code for line chart where I am not able to set customized series color . The same issue can be replicated for area, scatter, bar and column chart. I have attached Excel Sheet mentioning the expected outcome. Line Chart.zip (19.1 KB)

amjad.sahi · February 1, 2019, 12:18pm

@deergha,

Thanks for the sample code and template files.

Please use the line of code (in bold) to set customized series color for your needs:
e.g
Sample code:

var index = 0;
List colorList = new List { Color.Red, Color.Black };
const string excelTemplateRelativePath = “E:\test2\setting box whisker\new1\Line Chart\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("Feb");
        sheet.Cells["A4"].PutValue("Mar");
        sheet.Cells["A5"].PutValue("Apr");
        sheet.Cells["A6"].PutValue("May");
        sheet.Cells["A7"].PutValue("June");
        sheet.Cells["A8"].PutValue("July");
        sheet.Cells["A9"].PutValue("Aug");
        sheet.Cells["A10"].PutValue("Sep");
        sheet.Cells["A11"].PutValue("Oct");
        sheet.Cells["A12"].PutValue("Nov");
        sheet.Cells["A13"].PutValue("Dec");

        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["B12"].PutValue(90);
        sheet.Cells["B13"].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);
        sheet.Cells["C12"].PutValue(130);
        sheet.Cells["C13"].PutValue(150);

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

        var categoryData = "A" + 2 + ":A" + 11;
        int index1 = sheet.Charts.Add(ChartType.Line, 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;
        foreach (var series in seriesName)
        {
            chart.NSeries[series_index].Name = series;
            chart.NSeries[series_index].Area.FillFormat.FillType = FillType.None;
            chart.NSeries[series_index].Border.FormattingType = ChartLineFormattingType.Solid;
            chart.NSeries[series_index].Border.Style = LineType.Solid;
            chart.NSeries[series_index].Area.FillFormat.FillType = FillType.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 + "out1.xlsx");

deergha · February 1, 2019, 12:56pm

@Amjad_Sahi,

Thanks for the solution. It is working fine. Can you please check if legend can be available for BoxPlot chart. I am able to get the series name but it is not showing up in legend.

amjad.sahi · February 1, 2019, 6:02pm

@deergha,

Good to know the suggested code figures out your issue.

Could you elaborate on it and provide screenshot comparing the output chart (by Aspose.Cells) Vs your desired chart (manually created in MS Excel), we will check it soon.

deergha · February 4, 2019, 10:35am

Hi @Amjad_Sahi,

Here is the excel sheet file. I have mentioned the expected output. LegendforBoxPlot.zip (14.6 KB)

ahsaniqbalsidiqui · February 4, 2019, 4:19pm

@deergha,

Thanks for providing sample file. You may please add following code at the end of sample code provided here. It shall create required legend in the chart.

//Set Legend's width and height
Legend legend = chart.Legend;

//Legend is at right side of chart by default.
//If the legend is at left or right side of the chart, setting Legend.X property will not take effect.
//If the legend is at top or bottom side of the chart, setting Legend.Y property will not take effect.
legend.Y = 1500;
legend.Width = 50;
legend.Height = 50;
//Set legend's position
legend.Position = LegendPositionType.Top;
excel.Save(@"out1.xlsx");

aspose.notifier · February 18, 2019, 12:16pm

The issues you have found earlier (filed as CELLSNET-46568) have been fixed in Aspose.Cells for .NET v19.2. This message was posted using BugNotificationTool from Downloads module by Amjad_Sahi