Bug in handling String with a leading ' char

Aspose.Cells Product Family
1

Hi team,


We are currently using Aspose Cells 8.5.2 and we noticed that whenever we use cell.putValue(String stringValue) any String that begins with the char ', has this first character removed before the String is used.

We know from Excel that for cells with General format you will need this character to avoid having texts looking like numbers or formulas actually being converted into numbers or formulas - for instance you would need to write a phone number like '+26 8549 9348 to avoid it being seen as the positive number 2685499348.
And when we look at the worksheet, Excel does not display these leading ’ chars, BUT, if you select the cell you can see in the formula bar that the character is in fact still there.

I do not know the reason you may have for actually taking this character out, but I would suggest that you do not, because this means that you are changing the original data we have. Not showing this leading character is a display feature and nothing more.

Our customers uses Excel files in many inventive ways, and we do know that whatever they have been putting in their data needs to come out the exact same way when they download it from us and we export it from a database into an Aspose workbbok.

Please let me know your thoughts on this.

Best regards
Claes
2

Hi Claes,

Thanks for your posting and using Aspose.Cells.

We have tested this issue with the following code using v8.5.2 and found that ’ appears before the cell value of A1. I have attached the output xls file for your reference. So it works fine.

If your issue issue still occurs, then please provide us your sample code replicating this issue so that we could investigate this issue further.

Java


Workbook wb = new Workbook();


Worksheet ws = wb.getWorksheets().get(0);

Cell cell = ws.getCells().get(“A1”);


cell.putValue("'1234567");


wb.save(“outb.xls”);


3

Hi,


Sorry for taking so long to reply.
There must be some additional functionality invoked by us that is giving this result…
I am having a hard time figuring out exactly how this happens.

In simple tests like yours, the ’ char remains as it should.
Confusingly enough, when debugging I can note that asking the cell for the content (unformatted String or as pure object) I get the text without the leading ', but in the output file it is indeed still there.
When we create files through our platform (where a whole lot of additional coding is wrapped around it all) the leading ’ chars are always removed from the resulting file.

So to conclude, there is nothing more I can ask of you until I figure out what the difference in the processes might be, unless you know of any setting/function that might affect this behaviour?

I will get back to you if I find a sample of pure Aspose code that can reproduce this.

Best regards
Claes
4

Hi again,


I managed to find the correct circumstances just now.
Try this code snippet:

Workbook wb = new Workbook();
Worksheet ws = wb.getWorksheets().get(0);
Style style = wb.createStyle();//use wb to get a new style object
style.setNumber(49);//display type text

Cell cell = ws.getCells().get(“A1”);
cell.putValue("'12345");
cell.setStyle(style);//use the new style

wb.save(“outb.xls”); //in output file, the leading ’ is gone

Best regards
Claes
5

Hi Cleas,


Thank you for writing back.

We have evaluated the case while using the latest version of Aspose.Cells for Java 8.5.2.4 & manually using the Excel application. Please note, Excel produces the same results as of Aspose.Cells APIs if you repeat the following steps.

  • Right click on any cell and select the Format Cells menu.
  • On the Format Cells dialog, select the Custom from the Number tab and @ from right hand side window.
  • Click OK to close the Format Cells dialog.
  • Enter any number value starting with ’ char in the pre-formatted cell.

You will notice that Excel will remove the leading ’ char and also warns that the value is probably number stored as text. Based on these observations, we think that Aspose.Cells APIs behave correctly as they follow Excel’s guidelines and specifications in its implementation.
6

Hi,


Yes Excel does not display the leading ’ char in the cell, but if you look in the formula bar in your screenshot you can see that it still reads '123.

However if you follow my sample code, the formula bar will show only 123, without the leading ', i.e the data has changed rather than simply being displayed in a certain way, as per normal Excel behaviour.

Best regards
Claes
7

Hi Claes,


You are right, Aspose.Cells generated XLS does not show the leading ’ char in the formula bar whereas Excel generated XLS shows it. I have logged an investigative ticket with Id CELLSJAVA-41481 to look further into this matter. Please spare us little time to properly analyze the situation and get back to you with updates in this regard.
8

Hi,

Thanks for using Aspose.Cells.

Please set the style before setting the value. Please see the following code:

C#
Workbook wb = new Workbook();
Worksheet ws = wb.getWorksheets().get(0);

Style style = wb.createStyle();//use wb to get a new style object
style.setNumber(49);//display type text

Cell cell = ws.getCells().get(“A1”);
cell.setStyle(style);//use the new style
cell.putValue("'12345");

wb.save(“D:\Filetemp\dest.xls”);

If input “'12345” into the cell in MS Excel, MS Excel will set the value of the cell as “12345” and update the property of the style that prefix characters are present in the cell.

Aspose.Cells works as MS Excel.

This issue is caused by that the property is lost if you reset the style after inputting value.

9

Hi again,


We are having some issues again due to the leading single quote being lost if style is set after the actual value.

The reason that we set the styles later in the process is that we often work with large files and apply varying formats across different areas of cells, and for this reason we have implemented what we can call styleManagers that makes sure to apply similar styles to as large regions as possible automatically, so that when new reports are implemented no one needs to think about how and when the styles are applied - this is optimized anyway (applying styles per region is much more time efficient).

So, therefor, we would really like to have the same data kept regardless of when we set the style.
I know that Excel has a similar behaviour with intepreting the input data based on the style used in the cell, but since Aspose receives the data as the actual Object it is, my argument is that this Object should not change with the style. How it is displayed should of course change, but not what it actually represents.

Pls get back to me with your thoughts on this topic. And also, let me know if there are other types of data where I might loose information by applying the style after setting the value.

Best regards
Claes
10

Hi,

Thanks for your posting and using Aspose.Cells.

We have logged your comment in our database against this issue. We will look into it and provide you advice or any workaround or solution if possible. Once there is some news for you, we will let you know asap.

11

Hi,


We made sure to apply styles and values in the order suggested by you and the output files are fine.
However, it now turns out that it does not matter, because if a cell holds a text starting with a single quote, we are unable to get that first quote included when checking the aspose cell object.

Simple example (testing version 8.7.2 now):
Workbook wb = new Workbook();
Worksheet ws = wb.getWorksheets().get(0);
Cell cell = ws.getCells().get(0,0);
cell.putValue("‘test’");
// cell.getValue() will return "test’"

To me it is fine that cell.getDisplayStringValue returns “test’” without the leading single quote, just like Excel shows it, but that it is impossible to retrieve this quote even through the getValue method seems odd to me.

Best regards
Claes

12

Hi,

Thanks for your posting and using Aspose.Cells.

Your code looks fine and your argument looks valid. However we will look into it and see if anything can be done to resolve your issue. For that matter, we have logged your comment in our database against this issue for product team investigation. Once there is some news for you, we will let you know asap.

13

Hi,


Please check whether string handling with ’ by Style.getQuotePrefix(), see the following sample code for your needs:
e.g
Sample code:

Workbook wb = new Workbook();
Worksheet ws = wb.getWorksheets().get(0);
Cell cell = ws.getCells().get(0,0);
cell.putValue("‘test’");
Style style = cell.getStyle();
String str = cell.getDisplayStringValue();
if(style.getQuotePrefix())
{
str = “’” + str;
}
System.out.print(str);

Hope, this helps a bit.

Thank you.
14

Thanks, this does provide us with a solution for our problems.

Pls be so kind to let me know if the behaviour would be changed in the future to enable us to get the correct string directly.

Best regards
Claes
15

Hi,


Good to know that your issue is sorted out by the suggested solution.
Also, I have discussed regarding your concerns with the product team. Well, we have no plans to change the behavior of Cell.getDisplayStringValue() for this situation at the moment, so you may safely use this workaround for your requirements.

Thank you.