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

Aspose.Cells Product Family
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);
…

21

Hi,

Thanks for your reply. Please see the screenshot. Here I generated an excel file using two custom formats -

1. [$-1010409]"$"#'##0;("$"#'##0)

2. [$-1010409]"$"#,##0;("$"#,##0)

These two formats are not giving same output. However my initial problem was something different.

Problem description -

When I am trying to add custom format [$-1010409]"$"#'##0;("$"#'##0) through Aspose.Cells, after rendering excel, the format is converting to [$-1010409]"$"#\'##0;("$"#\'##0). You will notice a front slash just before the single quote.

My question is why this extra front slash is rendering?

Thanks,

Dipankar

22

Hi,

As we have said, Style.Custom uses standard separators ‘,’ as group separator. When assign custom formatting to Style.Custom, user should use ‘,’ to represent the group separator. When you input the custom format as “#’##0”, the character “’” will be taken as its literal value, not a group separator. For excel’s custom string, this character should be escaped to represent itself, so ‘’ will be insert it before it in the custom string.If you use ‘,’ as the group separator, just like the code in our previous post, you will get the cell formatted with localized group separator “’” as you expected in ms excel in your default locale. If you cannot generate the excel file as you expected with aspose.cells, please provided you code and we will check it soon.

Thank you.