SheetRender XPS and custom numberformat

Aspose.Cells Product Family
1

Hi,


we use Aspose.Cells to generate XPS files from existing excel files. But problem is custom numberformat in other languages. If we generate an excel file with german format e.g. numberformat and convert it to xps with sheetrender, date format will not be the same as in excel (see attachments).

The format string is for example

Is this a known issue or can you repoduce it? Is there a workaround for this?

Best regards

Bernhard
2

Hi,

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

Please download and use the latest version:
Aspose.Cells
for .NET v7.2.0.6
and let us know your feedback.

If the problem still occurs, then we will need need your source (xls/xlsx) files as you have shown in a screenshot and the code to replicate this problem.

We will look into it and help you asap.

3

Hi,


I tried it with 7.2.1.0, but with no success. Please see attachments. An excel file with custom formats and the xps result as png screenshot.

The excel file created with german language.

best regards


4

Hi,

Thanks for your file.

I was able to replicate this issue using the following code with the latest version:
Aspose.Cells
for .NET v7.2.1.1

Please see the screenshot for your reference.

We have logged this issue in our database. Devlopment team will look into this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as CELLSNET-40694.

C#


string path = @“F:\Shak-Data-RW\Downloads\ExcelWorkbook-28d6cfaf-3030-43c2-8cca-e3b30c28effb.xlsx”;

Workbook workbook = new Workbook(path);


Worksheet sheet = workbook.Worksheets[0];


//Apply different Image / Print options.

Aspose.Cells.Rendering.ImageOrPrintOptions options = new Aspose.Cells.Rendering.ImageOrPrintOptions();

options.OnePagePerSheet = true;

options.SaveFormat = SaveFormat.SVG;


SheetRender sr = new SheetRender(sheet, options);

for (int i = 0; i < sr.PageCount; i++)

{

sr.ToImage(i, path + i + “.out.svg”);

}



Screenshot:
5

Hi,


sorry, ok, it is a svg file, but we need xps!


6

Hi,

You are right.

I have attached the xps file generated with the latest version

The issue is same with xps file too. Please see the screenshot below.

C#


string path = @“F:\Shak-Data-RW\Downloads\ExcelWorkbook-28d6cfaf-3030-43c2-8cca-e3b30c28effb.xlsx”;


Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(path);

//Get the first worksheet

Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];


//Apply different Image and Print options

Aspose.Cells.Rendering.ImageOrPrintOptions options = new Aspose.Cells.Rendering.ImageOrPrintOptions();

//Set the Format

options.SaveFormat = SaveFormat.XPS;

//Render the sheet with respect to specified printing options

Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender(sheet, options);

sr.ToImage(0, path + “out_printingxps.xps”);


Screenshot:

7

Hi,


do you have any news for me on this issue?

I found some conditional formats which will not be rendered on SheetRender, please see attachments (an excel file with conditional formats and a xps result file).

Best Regards

Bernhard
8

Hi,

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

I have tested your old issue with id: CELLSNET-40694 with the latest version:
Aspose.Cells
for .NET v7.2.1.9
and found the issue still exists.

I have logged your comments in our database against the issue id: CELLSNET-40694

9
bernhard76:
Hi,

do you have any news for me on this issue?

I found some conditional formats which will not be rendered on SheetRender, please see attachments (an excel file with conditional formats and a xps result file).

Best Regards

Bernhard

Hi,

For your newly reported issue, please download and try the latest version: Aspose.Cells for .NET v7.2.1.9

I have tested your source xlsx file with it and it is working fine. I have attached the output xps file generated by the following code.

C#
string path = @"F:\Shak-Data-RW\Downloads\test.xlsx";

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(path);
//Get the first worksheet
Aspose.Cells.Worksheet sheet = workbook.Worksheets[0];

//Apply different Image and Print options
Aspose.Cells.Rendering.ImageOrPrintOptions options = new Aspose.Cells.Rendering.ImageOrPrintOptions();
//Set the Format
options.SaveFormat = SaveFormat.XPS;
//Render the sheet with respect to specified printing options
Aspose.Cells.Rendering.SheetRender sr = new Aspose.Cells.Rendering.SheetRender(sheet, options);
sr.ToImage(0, path + "out_printingxps.xps");

10

Hi,

We have fixed this issue.

Please download and try the latest version: Aspose.Cells for .NET v7.2.2.1

11
Ok, first issue was fixed, but following seems not to be fixed. Is that still on your focus or should I open a new issue?

bernhard76:

I found some conditional formats which will not be rendered on SheetRender, please see attachments (an excel file with conditional formats and a xps result file).

12

Hi,

It looks fine to me. So it is not a bug. Could you please recheck.

I have attached the screenshot for your reference.

Attachment: test.xlsxout_printingxps.xps

Please download and use the latest version:
Aspose.Cells
for .NET v7.2.2.1


Screenshot:

13

Ok, after many tests I have found the reason for incorrect format on sheet rendering.


It seems sheet renderer produces this in cooperation with Workbook.CalculateFormula()

Following Code:


Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(“c:\test.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”);

}


Result and Source in Attachment

Can you reproduce this issue?

Best regards

Bernhard
14

Hi,


Thank you for your feedback and using Aspose.Cells.

When I open the source file “test.xlsx” as you provided in your post, and Enable Editing in MS-Excel, Excel is not able to calculate the value in Cell A6, as you can see in the attached snapshot.

In my opinion, Enable Editing allows to calculate the formulae in the workbook and it then generates this “#Value” error. It may be due to the same reason that Aspose.Cells is giving the output as you have mentioned in your provided Xps file earlier to this post. Can you please check and verify your source file for the issue as I have mentioned and let us know your feedback? We will try to help you as soon as possible.

Also, we recommend you to download and use this latest version of Aspose.Cells: Aspose.Cells for .NET v7.2.2.5 in your applications.

15

You have an english version of excel, the given format in the formula is the german date format “TT” for the day, “JJJJ” for the year.


If you test it with adding german in editing language in excel not #Value" error occurs, is this right?

If we use the german date format in cell formats rendering was good! But not in formula formats.



16

Hi,


Please add two lines to your code, e.g


Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook(“c:\test.xlsx”, new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Xlsx));

wb.Settings.Region = CountryCode.Germany;
wb.Settings.LanguageCode = CountryCode.Germany;

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”);

}


I have tested using your code (after adding the two lines in bold) in German regional settings it works fine. The output image is fine. It will work fine for your German Excel / environment (region and local language settings).

Thank you.
17

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

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

Amjad Sahi:
Hi,

Please add two lines to your code, e.g


Aspose.Cells.Workbook wb = new Aspose.Cells.Workbook("c:\\test.xlsx", new Aspose.Cells.LoadOptions(Aspose.Cells.LoadFormat.Xlsx));

wb.Settings.Region = CountryCode.Germany;
wb.Settings.LanguageCode = CountryCode.Germany;

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");

}


I have tested using your code (after adding the two lines in bold) in German regional settings it works fine. The output image is fine. It will work fine for your German Excel / environment (region and local language settings).

Thank you.

with this code the datetime format delivers the text like "25.07.2012 Wed", expected text in german should be "25.07.2012 Mi". The "#Value" failure in formulas shoud be "#N/A" in german.

I attached an example file which shows you this issue.

Best Regards

Bernhard
19

Hi Bernhard,


Thanks for your feedback and sample file.

I have reopened your issue CELLSNET-40694 and asked the developer to fix your issue in accordance with German settings. Once we have any update, we will let you know.

Thank you.
20

Hi,

Thanks for using Aspose.Cells for .NET

Please download and try this fix: Aspose.Cells for .NET v7.3.0.3

We have fixed the issue of “Mi” for “Wed” in Germany Locale.

Let us know if you face any other issue, we will be glad to help you.