Maybe I'm missing something, I'm doing a boxplot compatible with excel 2007 (by hand) and viewing that in Excel 2010.
I'm using a Combo chart like this.
chart = chartWorkSheet.Shapes.AddChart(ChartType.Combo, cellChartBegin, new Point(0, 0), cellChartEnd, new Point(100, 100)); chart.SeriesOverlap = 100; //I need overlaped columns chart.GapWidth = 20; ChartType[] chartTypes = new ChartType[seriesCount]; for (int i = 0; i < seriesCount; ++i) { chartTypes[i] = ChartType.ColumnStacked; } chart.SetComboChartSourceData(vRange.ToString(), chartTypes); foreach(Series serie in chart.SeriesCollection) { serie.Fill = new ChartSolidFill(new WorkbookColorInfo(document.BoxColor)); serie.Border = new ChartBorder() { Fill = new ChartSolidFill(new WorkbookColorInfo(document.BorderColor)) }; } chart.ChartTitle = new ChartTitle() { Text = new FormattedString(document.ChartTitle) }; chart.ChartArea.Border = new ChartBorder() { LineStyle = BorderLineStyle.Solid, WidthInPoints = 1 }; AxisCollection axis = chart.AxisCollection; Axis xAxis = axis[AxisType.Category, AxisGroup.Primary]; xAxis.MajorTickMark = TickMark.None; xAxis.MinorTickMark = TickMark.None; xAxis.TickLabelPosition = TickLabelPosition.Low; xAxis.AxisTitle = new ChartTitle() { Text = new FormattedString(Resource.MainResources.BoxPlotChart_CategoryAxisTitle) }; xAxis.AxisBetweenCategories = true; xAxis.Position = AxisPosition.Bottom; Axis yAxis = axis[AxisType.Value, AxisGroup.Primary]; SetAxisProperties(document.YAxis, yAxis); chart.SeriesCollection[0].Fill = new ChartEmptyFill(); chart.SeriesCollection[0].Border = new ChartBorder() { Fill = new ChartEmptyFill()}; chart.SeriesCollection[1].ErrorBars = new ErrorBars() { Direction = ErrorBarDirection.Minus, ErrorValueType = ErrorValueType.Percentage, Value = 100, EndStyle = EndStyleCap.Cap, WidthInPoints = 2 }; chart.SeriesCollection[1].Fill = new ChartEmptyFill(); chart.SeriesCollection[1].Border = new ChartBorder() { Fill = new ChartEmptyFill() }; chart.SeriesCollection[4].Fill = new ChartEmptyFill(); chart.SeriesCollection[4].Border = new ChartBorder() { Fill = new ChartEmptyFill() }; chart.SeriesCollection[4].ErrorBars = new ErrorBars() { Direction = ErrorBarDirection.Minus, ErrorValueType = ErrorValueType.Percentage, Value = 100, EndStyle = EndStyleCap.Cap, WidthInPoints = 2 }; //Add mean serie Series newSerie = chart.SeriesCollection.Add(); newSerie.Name = new SeriesName(Resource.MainResources.BoxPlotChart_LegendItem_Mean); WorksheetRegion xRange = GetWorksheetRegion(dataWorkSheet, startRow, startRow + GetTotalRequiredRows() - 1, 0, 0); WorksheetRegion yRange = GetWorksheetRegion(dataWorkSheet, startRow, startRow + GetTotalRequiredRows() - 1, 10, 10); newSerie.XValues = new XValues(dataWorkSheet, xRange.ToString()); newSerie.Values = new SeriesValues(dataWorkSheet, yRange.ToString()); newSerie.ChartType = ChartType.LineMarkers; newSerie.MarkerStyle = MarkerStyle.Square; newSerie.MarkerSize = 5; newSerie.MarkerFill = new ChartSolidFill(new WorkbookColorInfo(document.MeanMarkerColor)); newSerie.MarkerBorder = new ChartBorder() { Fill = new ChartSolidFill(new WorkbookColorInfo(document.MeanMarkerColor)) }; newSerie.Line = new ChartLine() { Fill = new ChartEmptyFill(), LineStyle = LineStyle.None, WidthInPoints = 0 };
Resulting wrong chart
Expected chart
Data
Hello Hugo,
Thank you for contacting. In order to reproduce the issue I tried your code and setup a sample.
But looks like it has lots of dependent code and i am not able to run your sample.
I attached my sample here , I would recommend you to update the code and send me back a running sample ,which I can use to debug the sample and find the cause of the issue.
Also I would recommend you to isolate it little bit like I did ,remove the code which couldn’t be relevant to the issue.
Looking forward to hear you back.
Regards,
Sample212236.zip
Hi
Thanks for your answer, the main idea is to construct a Box Plot "by hand", not using the specific Box Plot chart type that is not supported from excel 2010.
I was not able to upload a file, this is the isolated code.
Workbook workbook1 = new Workbook(WorkbookFormat.Excel2007); Worksheet chartWorkSheet = workbook1.Worksheets.Add("Sheet 1"); //Add data chartWorkSheet.Rows[0].Cells[1].Value = "2.5% quantile"; chartWorkSheet.Rows[0].Cells[2].Value = "Lower quartile"; chartWorkSheet.Rows[0].Cells[3].Value = "Median"; chartWorkSheet.Rows[0].Cells[4].Value = "Upper quartile"; chartWorkSheet.Rows[0].Cells[5].Value = "97.5% quantile"; chartWorkSheet.Rows[0].Cells[6].Value = "Mean"; chartWorkSheet.Rows[1].Cells[0].Value = "CAT_A"; chartWorkSheet.Rows[1].Cells[1].Value = 0.0432848; chartWorkSheet.Rows[1].Cells[2].Value = 0.1784012; chartWorkSheet.Rows[1].Cells[3].Value = 0.305099; chartWorkSheet.Rows[1].Cells[4].Value = 0.647705; chartWorkSheet.Rows[1].Cells[5].Value = 3.79983; chartWorkSheet.Rows[1].Cells[6].Value = 1.018771398; chartWorkSheet.Rows[2].Cells[0].Value = "CAT_B"; chartWorkSheet.Rows[2].Cells[1].Value = 0.02454; chartWorkSheet.Rows[2].Cells[2].Value = 0.13676; chartWorkSheet.Rows[2].Cells[3].Value = 0.1761; chartWorkSheet.Rows[2].Cells[4].Value = 0.3993; chartWorkSheet.Rows[2].Cells[5].Value = 2.95474; chartWorkSheet.Rows[2].Cells[6].Value = 0.705804688; /// /// The Idea is construct a Box Plot by hand, not using the specific bot plot chart /// that is not supported from excel 2010 /// WorksheetCell cellChartBegin = chartWorkSheet.GetCell("A5"); WorksheetCell cellChartEnd = chartWorkSheet.GetCell("K25"); WorksheetChart chart = chartWorkSheet.Shapes.AddChart(ChartType.Combo, cellChartBegin, new Point(0, 0), cellChartEnd, new Point(100, 100));//cellChartEnd? //We need overlapped series for the same category chart.SeriesOverlap = 100; chart.GapWidth = 20; /////////////////////////////////////////////////// ChartType[] chartTypes = new ChartType[6]; for (int i = 0; i < 5; ++i) { chartTypes[i] = ChartType.ColumnStacked; } chartTypes[5] = ChartType.LineMarkers;//Line markers for the Mean serie chart.SetComboChartSourceData("A1:G3", chartTypes); AxisCollection axis = chart.AxisCollection; Axis xAxis = axis[AxisType.Category, AxisGroup.Primary]; xAxis.MajorTickMark = TickMark.None; xAxis.MinorTickMark = TickMark.None; xAxis.TickLabelPosition = TickLabelPosition.Low; xAxis.AxisTitle = new ChartTitle() { Text = new FormattedString("Category") }; xAxis.AxisBetweenCategories = true; xAxis.Position = AxisPosition.Bottom; Axis yAxis = axis[AxisType.Value, AxisGroup.Primary]; yAxis.ScaleType = ScaleType.Logarithmic; chart.SeriesCollection[0].Fill = new ChartEmptyFill(); chart.SeriesCollection[0].Border = new ChartBorder() { Fill = new ChartEmptyFill() }; chart.SeriesCollection[1].ErrorBars = new ErrorBars() { Direction = ErrorBarDirection.Minus, ErrorValueType = ErrorValueType.Percentage, Value = 100, EndStyle = EndStyleCap.Cap, WidthInPoints = 2 }; chart.SeriesCollection[1].Fill = new ChartEmptyFill(); chart.SeriesCollection[1].Border = new ChartBorder() { Fill = new ChartEmptyFill() }; chart.SeriesCollection[4].Fill = new ChartEmptyFill(); chart.SeriesCollection[4].Border = new ChartBorder() { Fill = new ChartEmptyFill() }; chart.SeriesCollection[4].ErrorBars = new ErrorBars() { Direction = ErrorBarDirection.Minus, ErrorValueType = ErrorValueType.Percentage, Value = 100, EndStyle = EndStyleCap.Cap, WidthInPoints = 2 }; chart.SeriesCollection[5].Line = new ChartLine() { Fill = new ChartEmptyFill(), LineStyle = LineStyle.None, WidthInPoints = 0 }; chart.SeriesCollection[5].MarkerStyle = MarkerStyle.Square; chart.SeriesCollection[5].MarkerSize = 5; workbook1.Save("d:/test.xlsx"); Process.Start("d:/test.xlsx");
Hello,
Thank you for sharing the code,I am able to setup the sample and noticed that combo chart is not stacked as expected. And for that I have logged a bug 272904 into our internal bug tracking system for further discussion with the developer.
Yes you are right BoxAndWhisker chart would be perfect for the requirement although its supported with Office2016 or later version ,and its documented here.
www.infragistics.com/.../infragisticswpf.documents.excel~infragistics.documents.excel.charts.charttype
Another option could be to use stackedcolumn chart instead of combo chart ,although in that case line series will not work.
I noticed that you have another private case for the same issue CAS-212255-V5G8N9, I am going to link the development issue with that case. This way you will be notified automatically when the dev issue is updated.