Formula and language issues in connection with SheetRender (XPS)

Dear Aspose-Support Team,


we have several issues about formulas and language problems in connection with SheetRender as XPS.

we use 7.3.1.

Please use following code to reproduce the issues:

Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(“c:\Tagesumsatzberich.xlsx”, new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Xlsx));
wb.CalculateFormula(true);
foreach (Worksheet ws in wb.Worksheets)
{
Console.WriteLine(ws.ConditionalFormattings.Count);
SheetRender sr = new SheetRender(ws, new ImageOrPrintOptions() { OnePagePerSheet = true, SaveFormat = SaveFormat.XPS });
sr.ToImage(0, “c:\test” + ws.Index + “.xps”);

}

See attached file “Tagesumsatzbericht.xlsx”, this is the test source workbook.

If you open the workbook in an Excel application and take look on sheet “Arbeitstage” ther are many calculated values generated with different formulas. Run the code with this file and open the “test3.xps” file nothing is calculated.

I don´t know if language settings (german) in workbook produce this issue.

Best regards

Bernhard

Hi,

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

I have generated the xps sheet but I did not notice problem because in your source xlsx file, the same sheet has lot of #VALUE!, so Ms-Excel and Aspose.Cells output looks same to me.

Please see the output xps generated by running your code with the latest version:
Aspose.Cells
for .NET v7.3.1.1


Please highlight your issues with red circles in screenshots. This will help us sort out your issue quickly.

Screenshot:

Hi,


It looks like an issue with German language settings for formulas. Aspose.Cells works by default in US English settings. Could you attach a screen shot taken for the "Arbeitstage " sheet when calculating formulas in your MS Excel (with German settings) manually, we will check your issue soon.

Thank you.

Hi,


ok after investigating this…

You have to set System Region and Language Settings:

1. Format - German (Germany)
2. Location - Germany

After opening the excel-file with Excel you can see values on “Arbeitstage” sheet. (see Attachment)

Best Regards

Bernhard

Hi,

Thanks for your feedback.

When I applied German regional settings, I was able to notice the issue. I have also applied German Regional Settings through code but still calculation of formula is wrong.

We have logged this issue in our database. We will look into it and fix the problem and once the issue is fixed or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-41012.

Please see the code below and the screenshot for your reference.

C#


string filePath = @“F:\Shak-Data-RW\Downloads\Tagesumsatzberich.xlsx”;


LoadOptions opts = new LoadOptions(LoadFormat.Xlsx);

opts.Region = CountryCode.Germany;


Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(filePath, new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Xlsx));

wb.CalculateFormula(true);

foreach (Worksheet ws in wb.Worksheets)

{

Console.WriteLine(ws.ConditionalFormattings.Count);

SheetRender sr = new SheetRender(ws, new ImageOrPrintOptions() { OnePagePerSheet = true, SaveFormat = SaveFormat.XPS });

sr.ToImage(0, filePath + ws.Index + “.xps”);


}

Screenshot:

Hi,

We have fixed this issue.

Please download and try this fix: Aspose.Cells for .NET v7.3.1.2 and let us know your feedback.

Hi,


thank you, will test it asap.

Kind regards

Bernhard

Hi,

Thanks for using Aspose.Cells.

Once, you have tested it, please share your feedback. We have closed this ticket for a now because the fix works fine.

If you still face this issue, please feel free to let us know, we will look into it and reopen this issue if needed.

The issues you have found earlier (filed as CELLSNET-41012) have been fixed in this update.


This message was posted using Notification2Forum from Downloads module by aspose.notifier.