Column chart with two Y axes

Aspose.Cells Product Family
1

Hi,

i’m using aspose for a long time and i can assure you that i’m very glad with all the helps that you gave us.
in our company we need to create a column chart with two Y axes (see attached picture) .
is that possible with aspose ?
can you give us a simple code to do this ?

thanks.
2

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please see the following code, it creates a column chart with two Y-axis. We have also attached the output file generated by this code for your reference.

C#


//Instantiating a Workbook object

Workbook workbook = new Workbook();


//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = workbook.Worksheets[0];


//Adding a sample value to “A1” cell

worksheet.Cells[“A1”].PutValue(50);


//Adding a sample value to “A2” cell

worksheet.Cells[“A2”].PutValue(100);


//Adding a sample value to “A3” cell

worksheet.Cells[“A3”].PutValue(150);


//Adding a sample value to “B1” cell

worksheet.Cells[“B1”].PutValue(4);


//Adding a sample value to “B2” cell

worksheet.Cells[“B2”].PutValue(20);


//Adding a sample value to “B3” cell

worksheet.Cells[“B3”].PutValue(50);


//Adding a chart to the worksheet

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 5, 0, 15, 5);


//Accessing the instance of the newly added chart

Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];


//Adding SeriesCollection (chart data source) to the chart ranging from “A1” cell to “B3”

chart.NSeries.Add(“A1:B3”, true);


chart.NSeries.CategoryData = “A1:A3”;

chart.NSeries.SecondCatergoryData = “B1:B3”;


chart.NSeries[1].PlotOnSecondAxis = true;

chart.SecondValueAxis.IsVisible = true;


//Saving the Excel file

workbook.Save(“book2.xlsx”);

3

Hi,

i already tried this code and i had the same result that you have.
as you see in the attached picture the reslut is not what we want. We need the columns to be displayed next each other in two Y axes like in the picture that i give you in the previous mail.

4

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please provide us your expected output file which you can create manually using MS-Excel 2010 and post it here. We will look into your issue and help you asap.

5

Hi ,

it’s seems that excel is one having problems with the column chart with two Y axes.
you will find in the attached file an Excel file with the the result that we want but iin this example we used a workaround to get the correct result.
6

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We were able to create chart with two y-axis using the above code, but unable to get the expected results as your expected output file. We have logged this issue in our database. We will look into it and provide you a sample code if possible.

This issue has been logged as CELLSNET-41896.

7

Hi,

Please try the following code segment. For creating your desired chart, you must ensure the count of series on primary axis to be same as on secondary axis, the primary category axis and the secondary category axis are on the same side (bottom).

Sample code:

string path = @"D:\Aspose\User\0633";

//Instantiating a Workbook object

Workbook workbook = new Workbook(FileFormatType.Xlsx);

//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = workbook.Worksheets[0];

//Adding a chart to the worksheet

int chartIndex = worksheet.Charts.Add(Aspose.Cells.Charts.ChartType.Column, 5, 0, 15, 5);

//Accessing the instance of the newly added chart

Aspose.Cells.Charts.Chart chart = worksheet.Charts[chartIndex];

string emptySeries = "{0,0,0}";

//primary series. the first and second series is data, the third is empty.

chart.NSeries.Add("{1,2,3}", false);

chart.NSeries.Add("{4,6,8}", false);

chart.NSeries.Add(emptySeries, false);

//second series. make the first and second series be empty, the third be data.

chart.NSeries.Add(emptySeries, false);

chart.NSeries[3].PlotOnSecondAxis = true;

chart.NSeries.Add(emptySeries, false);

chart.NSeries[4].PlotOnSecondAxis = true;

chart.NSeries.Add("{1,3,9}", false);

chart.NSeries[5].PlotOnSecondAxis = true;

chart.SecondValueAxis.IsVisible = true;

//Make the 2 category axis use same source data

string category = "{a,b,c}";

chart.NSeries.CategoryData = category;

chart.NSeries.SecondCatergoryData = category;

//Make the 2 category axis at same side

chart.ValueAxis.CrossType = CrossType.Automatic;

chart.SecondValueAxis.CrossType = CrossType.Automatic;

chart.SecondCategoryAxis.IsVisible = false;

//Saving the Excel file

workbook.Save(Path.Combine(path, "Test.xlsx"));

Thank you.