Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
30
Series overlap and gap width for Combo chart Excel exporting doesn't work as expected (excel engine)
posted

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