Display chart on left Y-Axis and Right Y-axis

how to display chart on on left Y Axis and Right Y-axis (secondary axis) using the data from a datatable. We are able to display the chart on Left Y-axis and not on Right-Y axis.

Hi,

Well, I think after you import data to the woksheet, you can use ASeries.PlotOnSecondAxis to to true value.

Please check the following sample example for your refernece. In the example, I use some dummy data for the two series.

Workbook wb = new Workbook();
wb.ChangePalette(Color.DarkRed, 55);
Worksheet worksheet = wb.Worksheets[0];
int chartIndex = worksheet.Charts.Add(ChartType.Line, 5, 0, 20, 8);
Chart chart = worksheet.Charts[chartIndex];

chart.ValueAxis.AxisLine.Color = Color.Maroon;
chart.ValueAxis.AxisLine.Style = LineType.Dot;
chart.ValueAxis.AxisLine.Weight = WeightType.WideLine;
chart.ValueAxis.MinValue = 0;
chart.ValueAxis.MaxValue = 80;
chart.ValueAxis.MajorUnit = 10;

chart.SecondValueAxis.IsVisible = true;
chart.SecondValueAxis.AxisLine.Color = Color.DarkRed;
chart.SecondValueAxis.AxisLine.Style = LineType.Dot;
chart.SecondValueAxis.AxisLine.Weight = WeightType.MediumLine;
chart.SecondValueAxis.MinValue = 0;
chart.SecondValueAxis.MaxValue = 1.4;
chart.SecondValueAxis.MajorUnit = 0.2;

chart.NSeries.Add("{50, 70, 20}", true);
chart.NSeries.Add("{0.6, 0.8, 1}", true);

//Plot the second series on second axis line.

chart.NSeries[1].PlotOnSecondAxis = true;

wb.Save("f:\\test\\test_secondaxis2.xls");

Thank you.

We already tried this but the second right Y axis is ploating on the top X-axis. How can we control the position of the secondary Y axis? Please see attached excel file.

lobj_Chart.SecondValueAxis.IsVisible = True

If lb_hasLeftAxis Then

lobj_Chart.NSeries.AddR1C1(ls_LeftAxisCells, True)

End If

If lb_hasRightAxis Then

lobj_Chart.NSeries.AddR1C1(ls_RightAxisCells, True)

lobj_Chart.NSeries(lobj_Chart.NSeries.Count - 1).PlotOnSecondAxis = True

End If

Hi,

Thanks for considering Aspose.

The chart you attached is the one you want to create and are you creating bar chart?

I tried my testing code and it works fine, attached is the output file. The secondvalueaxis is plotted on second y-axis. I created a column chart with two value axis plotted on both y-axis respectively.

Sample code:

Workbook wb = new Workbook();
wb.ChangePalette(Color.DarkRed, 55);
Worksheet worksheet = wb.Worksheets[0];
int chartIndex = worksheet.Charts.Add(ChartType.Column, 5, 0, 20, 8);
Chart chart = worksheet.Charts[chartIndex];

chart.ValueAxis.AxisLine.Color = Color.Maroon;
chart.ValueAxis.AxisLine.Style = LineType.Dot;
chart.ValueAxis.AxisLine.Weight = WeightType.WideLine;
chart.ValueAxis.MinValue = 0;
chart.ValueAxis.MaxValue = 80;
chart.ValueAxis.MajorUnit = 10;

chart.SecondValueAxis.IsVisible = true;
chart.SecondValueAxis.AxisLine.Color = Color.DarkRed;
chart.SecondValueAxis.AxisLine.Style = LineType.Dot;
chart.SecondValueAxis.AxisLine.Weight = WeightType.MediumLine;
chart.SecondValueAxis.MinValue = 0;
chart.SecondValueAxis.MaxValue = 1.6;
chart.SecondValueAxis.MajorUnit = 0.2;

chart.NSeries.Add("{70, 50, 20}", true);
chart.NSeries.Add("{0.6, 0.8, 1}", true);


//Plot the second series on second axis line.

chart.NSeries[1].PlotOnSecondAxis = true;

wb.Save("f:\\test\\testcol_secondaxis2.xls");

If you still could not resolve it, could you create a your desired chart in MS Excel and post us here, we will check it soon.

Thank you.

PlotOnSecondAxis in the sample given by you also is coming in the upper X-axis. Our requirement needs to be shown on the right side of lower X- axis (besides the first series data). Please check the attachment, we need the chart into excel in that format.

Hi,

Which version of Aspose.Cells you are using? Did you try my code in the previous post and was the result different with mine(I did attach the excel output file)? We appreciate if you could create your desired chart in MS Excel, then we may guide you how to accomplish the task using the code.

Thank you.

Version is 4.5. If you above, you sent the sample code with an attachment of excel document too (testcol_secondaxis2.xls).We tried your code and it is different from what we are looking for. Please see the attached chart in excel. We don’t want the “blue” bar plotting on top x-axis, instead it should be on lower x-axis on the right side. See the attached .ppt file how it should look like when we get it into chart.

Hi,

Thanks for providing us the template (ppt) file containing the image of the chart.

We will check it and get back to you soon.

Thank you.

Hi,

Please try the following codes:

Workbook wb = new Workbook();
wb.ChangePalette(System.Drawing.Color.DarkRed, 55);
Worksheet worksheet = wb.Worksheets[0];
int chartIndex = worksheet.Charts.Add(ChartType.Column, 5, 0, 20, 8);
Chart chart = worksheet.Charts[chartIndex];

chart.ValueAxis.AxisLine.Color = System.Drawing.Color.Maroon;
chart.ValueAxis.AxisLine.Style = LineType.Dot;
chart.ValueAxis.AxisLine.Weight = WeightType.WideLine;
chart.ValueAxis.MinValue = 0;
chart.ValueAxis.MaxValue = 80;
chart.ValueAxis.MajorUnit = 10;

chart.SecondValueAxis.IsVisible = true;
chart.SecondValueAxis.AxisLine.Color = System.Drawing.Color.DarkRed;
chart.SecondValueAxis.AxisLine.Style = LineType.Dot;
chart.SecondValueAxis.AxisLine.Weight = WeightType.MediumLine;
chart.SecondValueAxis.MinValue = 0;
chart.SecondValueAxis.MaxValue = 1.6;
chart.SecondValueAxis.MajorUnit = 0.2;

chart.NSeries.Add("{70, 50, 20}", true);
chart.NSeries.Add("{70, 50, 20}", true);
chart.NSeries.Add("{0.6, 0.8, 1}", true);


chart.NSeries[0].Overlap = -100;
//Plot the second series on second axis line.
chart.NSeries[2].PlotOnSecondAxis = true;
chart.SecondValueAxis.CrossAt = 0;//plot second axis on the bottom axis.
chart.NSeries[2].GapWidth = 300;//chart.NSeries[0].GapWidth * 2

If you still have the problem of creating chart, please create the chart in MS Excel and post the file here. We will check the chart option soon and know how to create the chart with Aspose.Cells API.

Hi, I tried your sample code and it works to some extent for our requirement. Please see the attached excel and let me know if it is possible to move the series (Forest Products and Fish Products) to Right Y axis. Currently it is showing at Left Y axis.

lobj_Chart.SecondValueAxis.IsVisible = True

If lb_hasLeftAxis Then

lobj_Chart.NSeries.AddR1C1(ls_LeftAxisCells, True)

End If

If lb_hasRightAxis Then

lobj_Chart.NSeries.AddR1C1(ls_RightAxisCells, True)

' lobj_Chart.NSeries(lobj_Chart.NSeries.Count - 1).Overlap = -100

lobj_Chart.NSeries(lobj_Chart.NSeries.Count - 1).PlotOnSecondAxis = True

lobj_Chart.SecondValueAxis.CrossAt = 0

' lobj_Chart.NSeries(lobj_Chart.NSeries.Count - 1).GapWidth = 300

Hi,

I think you should fill some external zero data to the series.See attached template file and created file.

And check the following codes:

Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\template.xls");
Charts charts = workbook.Worksheets[1].Charts;
charts.Add(ChartType.Column, 5, 1, 24, 24);
charts[0].NSeries.Add("=GATS Data!C11:J14", false);
charts[0].SecondValueAxis.IsVisible = true;
charts[0].SecondValueAxis.CrossAt = 0;
charts[0].NSeries[2].PlotOnSecondAxis = true;
charts[0].NSeries[2].Name = "=GATS Data!B11";
charts[0].NSeries[3].PlotOnSecondAxis = true;
charts[0].NSeries[3].Name = "=GATS Data!B12";
charts[0].NSeries.CategoryData = "=GATS Data!C6:I6";
workbook.Save(@"F:\FileTemp\created.xls");

how to add zero data to the series so that it shows in the end

Hi, we are looking to plot the excel chart in the attachment provided. could you provide code for it as we tried different ways but could not succeed in getting the Right Y-axis correctly. Attached excel sheet contains 2 sheets: 1. Gats Data which is our requirement. 2. Gats Chart which we come-up using aspose code. We need code to get the chart look like 1. Gats Data chart.

lobj_Chart.SecondValueAxis.IsVisible = True

If lb_hasLeftAxis Then

lobj_Chart.NSeries.AddR1C1(ls_LeftAxisCells, True)

lobj_Chart.NSeries(lobj_Chart.NSeries.Count - 1).Name = iobj_Workbook.Worksheets("Gats Data").Cells(li_Row + 5, li_LowestPrecedenceColumnNumber).Value.ToString

' ls_RightAxisCells = "{0,0,0,0,0,0,0}"

'lobj_Chart.NSeries.Add(ls_RightAxisCells, True)

'lobj_Chart.NSeries(lobj_Chart.NSeries.Count - 1).Overlap = -100

'lobj_Chart.NSeries(lobj_Chart.NSeries.Count - 1).PlotOnSecondAxis = True

End If

If lb_hasRightAxis Then

'ls_RightAxisCells = "{0,0,0,0,0,0" & ls_RightAxisCells & "}"

'lobj_Chart.NSeries.Add(ls_RightAxisCells, True)

lobj_Chart.NSeries.AddR1C1(ls_RightAxisCells, True)

lobj_Chart.NSeries(lobj_Chart.NSeries.Count - 1).Overlap = -100

lobj_Chart.NSeries(lobj_Chart.NSeries.Count - 1).PlotOnSecondAxis = True

lobj_Chart.SecondValueAxis.CrossAt = 0

lobj_Chart.NSeries(lobj_Chart.NSeries.Count - 1).GapWidth = 300

lobj_Chart.NSeries(lobj_Chart.NSeries.Count - 1).Name = iobj_Workbook.Worksheets("Gats Data").Cells(li_Row + 5, li_LowestPrecedenceColumnNumber).Value.ToString

End If

lobj_Chart.NSeries.CategoryData = "{" & ls_CategoryDataCells & "}"

lobj_Chart.NSeries.SecondCatergoryData = "{" & ls_CategoryDataCells & "}"

Hi,

Please try the following codes:

Workbook workbook = new Workbook();
workbook.Open(@"F:\FileTemp\chart.xls");
Charts charts = workbook.Worksheets[1].Charts;
charts.Add(ChartType.Column, 5, 1, 24, 24);
charts[0].NSeries.Add("='GATS Data'!C7:I8", false);
charts[0].NSeries.Add("='GATS Data'!J7:J12", true);

charts[0].NSeries.CategoryData = "='GATS Data'!C6:I6";
charts[0].SecondValueAxis.IsVisible = true;
charts[0].NSeries[2].PlotOnSecondAxis = true;
charts[0].NSeries[2].Type = ChartType.LineWithDataMarkers;


workbook.Save(@"F:\FileTemp\dest.xls");

In the previous demo, if we do not set the Overlap and GapWidth,the series plotted on the primary axis will be overlaped by the series plotted on the second value axis.

If you do not how to create the chart with API, please create a chart with MS Excel, then set the option with API as you did in MS Excel.