Bug with Chart.SecondValueAxis?

Aspose.Cells Product Family
1

Hi,

I am using Aspose.Cells to draw Excel line charts. Basically I want to draw a chart for several data series in both value axis. However, the second axis cannot be shown correctly. If I open the generated chart in Excel 2003, it does not show the second axis. If I open the chart in Excel 2007, the second axis shows, but in a completely wrong position (next to the first value axis).

Is it a bug in Aspose.Cells?

My code snippet looks like this:

// init some data

...

Chart _chart = workbook.Worksheets[0].Charts[0];

// some settings

_chart.NSeries[0].Line.Style = LineType.Solid;

_chart.NSeries[0].Line.Weight = WeightType.MediumLine;

_chart.NSeries[0].MarkerStyle = ChartMarkerType.None;

_chart.NSeries[0].Name = "Line 1- very very long";

_chart.NSeries[0].PlotOnSecondAxis = true;

_chart.NSeries[1].Line.Color = Color.Green;

_chart.NSeries[1].Name = "Line 2- even longggggggggggggger";

_chart.NSeries[2].Line.Color = Color.Red;

_chart.NSeries[2].PlotOnSecondAxis = true;

_chart.NSeries[2].Name = "Line 3- also very very long";

Axis axis2 = _chart.SecondValueAxis;

axis2.IsVisible = true;

axis2.TickLabels.NumberFormat = "0";

axis2.TickLabels.Font.Size = 15;

axis2.Title.Text = "Test";

// save chart

_chart.Calculate();

workbook.Save(fullPath, FileFormatType.Default);

Many thanks for any help!

2

Hi,

Well, I tested your case (using some of your code) a bit, it works fine with the attached version.

Could you try the attached version, if you still find the issue, kindly create a sample console application with input+output files here, we will check your issue soon.

Thank you.

3

Hi,

Thanks a lot for for feedback. Apparently I found where the bug is: I used an existing chart (created in Excel as an empty chart template) for drawing instead of using Charts.Add() for createing a new one.

// init

...

Worksheet dataSheet = workbook.Worksheets[1];

Worksheet chartSheet = workbook.Worksheets[0]

Chart _chart = chartSheet.Charts[0];

...

Is there any way to work around this? I still want to use chart template created by Excel because of styles, predefined text boxes,...

Many thanks.

Nguyen

4

Hi,

Could you post your sample template excel file (containing your existing chart) and the generated file(containing updated chart) after setting your code, we will look into it soon.

Thank you.

5

Hi,

Please find attached template file (chart1) and generated file (chart1_output).

Btw, another problem for me is the chart's legend. I want to put the legend at the bottom of the chart without overlapping the Plot area. How can I do this in Aspose.Cells ?

Before this code works well in Excel2003 MS's API:

_chart.PlotArea.Height -= legend.Height;

But it does not work with Aspose. I tried to do something like

_chart.PlotArea.Height -= legend.Height * legend.TextFont.Size;

It seems to me that Legend.Height in Aspose does not return the correct value as in Excel. Is there a better way to do this with Aspose?

Thanks in advance for your help.

Nguyen

6

Hi,

Thanks for providing us the template files.

We will look into it and get back to you soon.

Thank you.

7

Hi,

Please check the following sample code, the second value axis is visible and legend is in bottom.

Workbook book = new Workbook();
book.Open(@“F:\FileTemp\chart1.xls”);


_chart.NSeries.Add(“data!B2:E50”,true);

// some settings

_chart.NSeries[0].Line.Style = LineType.Solid;

_chart.NSeries[0].Line.Weight = WeightType.MediumLine;

_chart.NSeries[0].MarkerStyle = ChartMarkerType.None;

_chart.NSeries[0].Name = “Line 1- very very long”;

_chart.NSeries[0].PlotOnSecondAxis = true;

_chart.NSeries[1].Line.Color = System.Drawing.Color.Green;

_chart.NSeries[1].Name = “Line 2- even longggggggggggggger”;

_chart.NSeries[2].Line.Color = System.Drawing.Color.Red;

_chart.NSeries[2].PlotOnSecondAxis = true;

_chart.NSeries[2].Name = “Line 3- also very very long”;




Axis axis2 = _chart.SecondValueAxis;
axis2.TickLabels.NumberFormat = “0”;

axis2.TickLabels.Font.Size = 15;

axis2.Title.Text = “Test”;
_chart.CategoryAxis.CategoryType = CategoryType.TimeScale;
_chart.CategoryAxis.MajorUnitScale = TimeUnit.Years;
_chart.CategoryAxis.MajorUnit = 1;
_chart.SecondCategoryAxis.Crosses = CrossType.Maximum;
_chart.Legend.Position = LegendPositionType.Bottom;
// save chart
_chart.Calculate();
ChartFrame plotArea = _chart.PlotArea;
if(plotArea.Y + plotArea.Height > _chart.Legend.Y)
{
plotArea.Height = plotArea.Height - (plotArea.Y + plotArea.Height - _chart.Legend.Y) - 10;//10 is an estimated offset.
}

If you do not call “_chart.SecondCategoryAxis.Crosses = CrossType.Maximum”, the second chart will be covered by the primary axis.


If you want to change the position of plot area, please call Chart.Calculate() method first.

If you still have any problem, please generate a template chart file in MS Excel and post it here. We will check it soon.



Thank you.

8

Hi Amjad,

It solved my problem!

Thanks a lot for your quick support.

Nguyen