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