Axis Range - different behaviour in XLS and XLSX?

mrcook · July 4, 2017, 11:04pm

I generate the attached Excel documents with the same code, however the result for XLS (Excel97To2003) is different to XLSX.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

In the code I set the range of the X axis to be defined as min 5 and max 150, i.e.

chart.CategoryAxis.IsAutomaticMinValue = true;

chart.CategoryAxis.MinValue = 5;

etc.

However, in the XLS output this is ignored and Excel uses its own range. In the XLS the range of the X axis is not set.

In the XLSX this range is applied.

Is this difference a “feature” of Excel or a problem with Aspose?

Thanks,

Amjad_Sahi · January 2, 2021, 6:50pm

Hi,

It might be due different behaviours of XLS and XLSX file formats of MS Excel. Anyways, could you try our latest fix/version e.g v7.3.3.3: Aspose.Cells for .NET v7.3.3.3 if it works fine.

If you still find the issue with latest fix/version, kinldy paste your complete sample runnable code or attach sample console application (you may zip it prior attaching) here, we will check your issue soon.

Thank you.

mrcook · July 4, 2017, 11:05pm

Hi,

Thank you for the quick reply.

I am using .NET 3.5 Client profile - is that link Client Profile?

Thanks

shakeel.faiz · July 4, 2017, 11:04pm

mrcook:

I generate the attached Excel documents with the same code, however the result for XLS (Excel97To2003) is different to XLSX.

In the code I set the range of the X axis to be defined as min 5 and max 150, i.e.

chart.CategoryAxis.IsAutomaticMinValue = true;

chart.CategoryAxis.MinValue = 5;

etc.

However, in the XLS output this is ignored and Excel uses its own range. In the XLS the range of the X axis is not set.

In the XLSX this range is applied.

Is this difference a “feature” of Excel or a problem with Aspose?

Thanks,

Hi,

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

Please add the following code and try setting the following properties and generate your xls file, it should fix your issue.

C#

//Also add following code in your existing code
chart.CategoryAxis.IsAutomaticMaxValue = false;
chart.CategoryAxis.IsAutomaticMinValue = false;

If you still face this issue, please provide us your complete runnable code replicating this issue. We will look into it and help you asap

shakeel.faiz · July 4, 2017, 11:05pm

mrcook:

Hi,

Thank you for the quick reply.

I am using .NET 3.5 Client profile - is that link Client Profile?

Thanks

Hi,

No, it was not a ClientProfile Link. To get the latest version for ClientProfile, please use this link: Aspose.Cells for .NET 7.3.3

mrcook · July 4, 2017, 11:05pm

I tried:

chart.CategoryAxis.IsAutomaticMaxValue = false;
chart.CategoryAxis.IsAutomaticMinValue = false;

but the problem still occurs.

I cannot send source as it is as we are using other 3rd party components. We could write a new standalone client to test this for you but it will take us some time. I wanted to ask if you are aware of this issue in Excel or Aspose already before we investigate this further?

shakeel.faiz · July 4, 2017, 11:05pm

Hi,

Thanks for your input.

Well, mostly such issue occurs because you need to set some extra properties to make them work. Means, such issues are mostly related with sample code.

However, it could also be a bug or some broken feature and we will investigate it once we will get your standalone application.

Also, we will look into it at our end and help you with some advice asap.

mrcook · July 4, 2017, 11:05pm

I reproduced the problem by modifying one of your demos,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

In your demo project:

Aspose\Aspose.Cells for .NET\Demos\C Sharp\Web\Aspose.Cells\Aspose.Cells.Demos.2010.sln

I updated

scatter-connected-by-lines.aspx

in:

private void CreateStaticReport(Workbook workbook)

After:

//Set properties of chart

add

chart.CategoryAxis.IsLogarithmic = true;

chart.CategoryAxis.IsAutomaticMaxValue = false;

chart.CategoryAxis.IsAutomaticMinValue = false;

chart.CategoryAxis.MinValue = 5;

chart.CategoryAxis.MaxValue = 150;

Now when I run this page and select to generate XLS or XLSX the results are different.

I hope you can now reproduce this.

Amjad_Sahi · July 4, 2017, 11:05pm

Hi,

Thanks for pointing out the issue.

After an initial test with v7.3.3, I can notice the issue for XLS file format (XLSX works fine though) by just running the demo code after adding your suggested lines i.e.
After:
//Set properties of chart
Add:
chart.CategoryAxis.IsLogarithmic = true;
chart.CategoryAxis.IsAutomaticMaxValue = false;
chart.CategoryAxis.IsAutomaticMinValue = false;
chart.CategoryAxis.MinValue = 5;
chart.CategoryAxis.MaxValue = 150;

in the "scatter-connected-by-lines.aspx/cs" featured demo. The category axis for XLS chart still shows max value as 100 instead of 150.

I have logged a ticket with an id: CELLSNET-41186 for your issue. We will look into your issue soon.

Thank you.

mrcook · July 4, 2017, 11:05pm

Hi,

Any news/schedule for this fix?

Thanks,

Amjad_Sahi · July 4, 2017, 11:05pm

Hi,

I am afraid, your issue is not sorted out yet. However, I have asked the concerned developer to update on your issue. Once we receive any update on it, we will let you know here.

Sorry for any inconvenience caused!

Amjad_Sahi · July 4, 2017, 11:05pm

Hi,

We have investigated the
logarithmic issues for Excel 2003. We found that for XLS file format, the maximum value
or the minimum value must be the values in the pattern/range such as, 1, 10, 100, … and so on, so, they seem
to be an integer that should be multiple of 10. <o:p></o:p>

Therefore, the values, 5 and 150, are not accepted for Excel 2003 file format.

Thank you.

mrcook · July 4, 2017, 11:05pm

Thank you for the helpful answer - this does explain the problem. We will write a workaround in our client.

All the best,

shakeel.faiz · July 4, 2017, 11:05pm

Hi,

Thanks for your posting and considering Aspose.Cells.

It’s good to know that you now understand the limitation of Excel 2003 format and you can now devise a workaround for it.

We have closed this thread now. Let us know if you face any other issue, we will be glad to help you asap.

Also, for other other chart types and general Aspose.Cells API(s), we recommend you to download and try our latest offline demos, which you can find from the given links.