Switch Row/Column (switch axes) in xlsio chart (syncfusion)

821 views Asked by At

I am looking for a way to switch between axes of the chart being generated with Syncfusion XlsIO library. I was not able to find any solution due to the library being commercial and less used, and the Knowledge Base being very limited.

Basically, I am using an excel template to create new sheets and populate some data to them accordingly. I am able to do everything, but somehow the axes don't come-up the way I want. I want a way to simply do what a enter image description here (switch Row/Column button in Excel.exe) would do to the generated chart i.e. switch axes of the chart. Or may be control the axis programatically, and switch between X axis and Y axis with code.

Though, I have a solution that we can generate the data for the chart as Transpose of what I am currently generating, but it seems to be a very big deal as the application being maintained is live and it is almost impossible to change the orientation (transposed) of the data now.

Here is my code:

IChartShapes cs = ws.Charts; // ws is the worksheet being generated
foreach (IChart cs1 in cs)
{
    string strCName = cs1.Name;
    IRange rngs = ws.Range[cs1.Name]; //chart name and named range are the same
    cs1.PrimaryValueAxis.Font.Size = 4;
    cs1.PrimaryCategoryAxis.Font.Size = 4;
    cs1.DisplayBlanksAs = ExcelChartPlotEmpty.NotPlotted;
    cs1.DataRange = rngs;
    IChartCategoryAxis csa = cs1.PrimaryCategoryAxis;
    csa.CategoryLabels.WrapText = true;
}

Any help will be much appreciated.

1

There are 1 answers

0
Cyberpks On

Got the answer!!! It was way too simple. Though no one seems to be interested in the question, but still if someone happens get stuck with such a problem, this may help him/her.

Basically, the answer was just setting a property of chart IsSeriesInRows that is True by default, to False, and call this after adding the range to the chart.

So here is the corrected code.

IChartShapes cs = ws.Charts; // ws is the worksheet being generated
foreach (IChart cs1 in cs)
{
    string strCName = cs1.Name;
    IRange rngs = ws.Range[cs1.Name]; //chart name and named range are the same
    cs1.PrimaryValueAxis.Font.Size = 4;
    cs1.PrimaryCategoryAxis.Font.Size = 4;
    cs1.DisplayBlanksAs = ExcelChartPlotEmpty.NotPlotted;
    cs1.DataRange = rngs;

    //// Answer added here ////
    cs1.IsSeriesInRows = false; //This tells the chart that the series are to be picked from the row itself.

    //// Answer ends here ////
    IChartCategoryAxis csa = cs1.PrimaryCategoryAxis;
    csa.CategoryLabels.WrapText = true;
}