Create Box and Whisker chart in Excel file using Aspose.Cells for .NET in C#

Aspose.Cells Product Family
1

Is it possible that I can use this product to create a “Box and Whisker chart”?

2

Hi,

Thanks for your inquiry.

Please try the following Sample code with the attached template file for your requirement.

Sample code:

Workbook workbook = new Workbook();
workbook.Open(@"F:\test\BoxWhisker\Book1.xls");
Charts charts = workbook.Worksheets[0].Charts;
charts.Add(ChartType.Line, 5, 5, 25, 14);
charts[0].NSeries.Add("=B2:D6", false);

for (int i = 0; i < 5; i++)
{
ASeries series = charts[0].NSeries[i];
series.Line.IsVisible = false;

switch (i)
{
case 1:
case 3:
series.MarkerStyle = ChartMarkerType.Dash;
series.MarkerForegroundColorSetType = FormattingType.Custom;
series.MarkerForegroundColor = System.Drawing.Color.Black;
series.MarkerBackgroundColorSetType = FormattingType.None;
series.HasHiLoLines = true;
break;
case 2:
series.MarkerStyle = ChartMarkerType.Diamond;
series.MarkerForegroundColor = System.Drawing.Color.Black;
series.MarkerBackgroundColorSetType = FormattingType.None;
series.HasUpDownBars = true;
break;
}

}
workbook.Save(@"F:\test\BoxWhisker\dest.xls");

Thank you.

3

Thank you Amjad,

I have tried your sample code, it works. But when I try to generate the similar chart on the attached file by using data on the attached file, I can not figure out how to do it. Could you please give me some hints on this?

4

Hi,

Could you create your desired chart (based on the data) manually in MS Excel and post the excel file here, we will check and guide you to create it using Aspose.Cells API.

Thank you.

5

Hi,

I have attached two excel files and vb6 code.

In Book2.xls, I created the chart.

Next steps to do:

1. Delete the series 1 from Legend.

2. Assign text to x axis. (e.g 1 to All etc.)

3. Assign text to y axis. (e.g 10 to 10K)

Thank you very much for your help.

Dongbo

6

Hi Dongbo,

I have created the code for your next steps. The code is in VB.NET so you may change it to Vb6 accordingly.

Sample code:

Dim workbook As New Workbook()

workbook.Open("f:\test\BoxWhisk\book2.xls")

Dim chart As Chart = WorkBook.Worksheets(0).Charts(0)

'Remove the first series

chart.NSeries.RemoveAt(0)

'Format the value axis tick labels

chart.ValueAxis.TickLabels.NumberFormat = "#,##0k"

'Saving the Excel file

WorkBook.Save("f:\test\BoxWhisk\book3.xls")

The output file is also attached for your reference.

Thank you.

7

Hi,

By the way, there are not enough series for your Box and Whisker chart in your template file. So, we cannot think which chart type your are requiring for. We appreciate if you could manually create your desired Box and Whisker chart you looking for. It will help us to understand your need and we will guide you through to accomplish your task.

Thank you.