Applying custom format rendering for single quote using Aspose.Cells in .NET

Aspose.Cells Product Family
1
Right now I am using Aspose.Cells version 7.0.2.0. Having one problem in this version.
When trying to add custom format "[$-1010409]"$"#'##0;("$"#'##0)" to a cell, during excel render it is simply adding a \ before single quote and the output does not match with the expected result.
I have tested it with the latest version 7.5.1.0. In this version also, this problem exists.
My question is - Why Aspose.Cells is rendering \' instead of '
Thanks,
Dipankar
2

Hi,


I have evaluated your case and use the following code and it works fine with your desired custom formatting. For your information, Aspose.Cells follows MS Excel’s standards. See the sample code below for your reference:

Sample code:
var workbook = new Workbook();

Worksheet worksheet = workbook.Worksheets[0];

Cells cells = worksheet.Cells;

Aspose.Cells.Cell cell = cells[“A1”];

cell.PutValue(2);

Style style = cell.GetStyle();

style.Custom = “[$-1010409]”$"#’##0;("$"#’##0)";

cell.SetStyle(style);

workbook.Save(“e:\test2\outputcustomformatting1.xlsx”);
I have attached the output Excel file for your reference, there is no "\'", rather it has "'".

If you still have any confusion, please specify your desired custom formatting in MS Excel manually for some cell value and save the file and attach it here, we will check it soon.

Thank you.
3

Hi,

Thanks for your quick response. But unfortunately you have missed my point. The excel that you have uploaded; I can still see the backslash before single quote in the format cells option.

Please try with some bigger value like (1234567) and with the value 0. This will tell you the difference. For example please look into my uploaded file.

My requirement is, the values must be displayed as (For the negative values the first brackets are there) –

$276’258

$14’175’536

($5’882’471)

($176’497’086)

($64’707’176)

($14’175’536)

$5’882’471

$176’474’116

$64’707’176

Thanks,

Dipankar

4

Hi,


Even I tried your value e.g (1234567) but still could not spot the backslash before the “’”, see the attached screen shot for your reference.

Thank you.
5

Hi,

Here is a catch. I can see only difference here that is I am using Excel 2003 and you are using Excel 2007 (probably).

Thanks,

Dipankar

6

Hi,

Another point the value 1234567 should be displayed as $1'234'567

Thanks,

Dipankar

7
Hi,

Please create your expected file with your desired custom formatting for the value that should be seen in MS Excel manually in MS Excel and provide it here. Also attach some screen shots taken in MS Excel, we will check it soon.

Thank you.
8

Hi,

I have attached an excel file with the custom format that I want. Please consider that the file extention is XLS and I am using Excel 2003.

It is not possible to change the excel version at the organization level.

Thanks,

Dipankar

9

Hi,

Please find the attached image for your referrence.

Thanks,

Dipankar

10

Hi,

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

We have opened your source file into MS-Excel 2003 and checked the cell format. We found there is no single quote in the cell custom format. Please see the attached image for your reference.


11

Hi,

Sorry. You have not got my point at all. The source file that I uploaded is just for your referrence which I want as final output. Just tell me how I can add that custom format in Aspose?

The actual issue is when I am trying to add the same format in Aspose and trying to export in excel, there I can see a "\" character before the single quote.

Thanks,

Dipankar

12

Hi,

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

We loaded your source file i.e Report Custom Format.xls using the latest version: Aspose.Cells
for .NET v7.5.1.2 and observed the custom format value in debugging and wrote the following code. It works fine.

Please see the following screenshot for a reference.

C#


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


Cell cell = worksheet.Cells[“B1”];


Style style = cell.GetStyle();

style.Custom = “[$-1010409]"$"#,##0;\("$"#,##0\)”;

cell.SetStyle(style);


workbook.Save(“Output.xls”, SaveFormat.Excel97To2003);


Screenshot:
13

Hi,

But this is not what I want. The format will be same as you shown in the example. But I want single quote instead of comma.

Thanks,

Dipankar

14

Hi,

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

Please provide us your desired custom format with single quote in MS-Excel file which you can create manually and post it here. We will look into your issue and help you asap.

15

Hi,

Sorry for the late reply. Please find the attached excel which is created on 2003. This is the exact output which I want.

Thanks & regards,

Dipankar Haldar

16

Hi,

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

We have looked into your issue and opened your expected output sample file in MS-Excel 2003, we found, it is using comma instead of single quote.

We have created a screenshot for your reference.

Screenshot:

17

Hi,

I think you are not using the right attachment.

Please find the attachment here.

Thanks,

Dipankar

18

Hi,

Thanks for your posting and using Aspose.Cells.

We have again opened your attachment in MS-Excel 2003 and it still shows the same result. The custom formatting is using comma instead of single quote.

Could you provide screenshot of the file at your end?

19

Hi,

Please find the attached screenshot for your referrence.

Thanks,

Dipankar

20

Hi,

Thanks for your screenshot and using Aspose.Cells.

By your screenshot we finally found the difference was caused by the different locale. In your default locale the group separator should be single quote instead of ‘,’. When applying custom format to cell’s style, the Style.Custom uses standard separators such as ‘.’ for decimal separator and ‘,’ for group separator. When formatting the cell values, the standard separator will be replaced with the separator of specified locale used by the workbook. So, excel generated by code like Style.Custom=”#,##0” will format the cell value as “1’234’567” too as you expected in your default locale. For your sample.xls, to generate such an excel file by aspose.cells, such as cell “A3”, you code should be like following:

Cell cell = cells[“A3”];
Style style = cell.GetStyle();
style.Custom = “”$"#,##0;("$"#,##0)";
cell.SetStyle(style);
…