Number formatting problem when value contains semicolon

Aspose.Cells Product Family
1

Hello,

We are using Aspose Cells version 8.3.1.0, and are having problems formatting numbers.
The code we are using to populate the spreadsheet with the data from our database is:

sheet.Cells.ImportDataReader(reader, true, 0, 0, false, "mmm-yyyy", true);

I understood from the documentation that the last value (‘ConvertStringToNumber’), set to true in our code, is what formats the text as numbers. Setting as false shows the green triangles in Excel, with the error ‘Number Stored as Text’.

However we seem to have a problem with one column. The column shows aspect ratios (e.g. 19:10). With the ‘ConvertStringToNumber’ bool set to true, this seems to be converting the text to a decimal number. Turning the value to false instead shows the value correctly, however then all the other numbers are then stored as text:

Capture2.PNG (1.2 KB)

We have tried a number of different combinations of styles and style flags, however have been unable to find a method to display both the aspect ratios correctly, but have the other numbers stored as numbers.

Please can you advise?
Thank you

2

Hi,

Thanks for the screenshot and details.

Well, DateTime values are actually stored in numeric notations. That’s why when you set convertStringToNumber parameter to “true”, the so called aspect/ratios (Date/Time values) are converted to numeric values. There is no automatic way to cope with it. The simple way to accomplish your task is you will keep using your existing approach to import data from the data reader (with convertStringToNumber Boolean attribute set as true). After your data is imported, you will then convert your numeric (decimal) values to aspect ratios/DateTime values by applying relevant style/formattings to the underlying column, see the following code segment for your reference:
e.g
Sample code:

…
Worksheet sheet = workbook.Worksheets[0];

        Style style1 = workbook.CreateStyle();
        StyleFlag flag = new StyleFlag();
        style1.Custom = "h:mm";
        flag.NumberFormat = true;
        //Apply the style to the first column(A). 
        sheet.Cells.ApplyColumnStyle(0, style1, flag);

…

Hope, this helps a bit.

Thank you.

3

Thank you, this seems to have resolved our issue

4

Hi,

Good to know that your issue is sorted out by the suggested code. Feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.