How to stop ceiling on the values read from Excel file

Aspose.Cells Product Family
1

Hi,

We are using aspose.cells for reading excel file and we can across one unique issue not getting any solution for this.

while exporting the excel using cells.exportdatatableasstring() we it is rounding off one of numberic value.

in excel it is showing cell value "809081364101727" while we saw from debugging aspose.cell it reading as "809081364101727.5" and output value is shown as "809081364101728". and for the values those decimal point is less than .5 it is giving correct value.

Please let us know from where these decimal values are coming and what is the solution for getting values as shown on excel file.

regards

Anshul

2

Hi,

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

Please provide us your sample code and the source files replicating this issue with the latest version: Aspose.Cells
for .NET v7.4.3.4. We will look into your issue and if we find any issue, we will log it in our database for a fix.

3

Hi Thanks for your quick reply.

Below is the code we are using.

FileStream stream = new FileStream(“Path of File”, FileMode.Open);

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

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

System.Data.DataTable uploadedFeedDataTable = new System.Data.DataTable();

uploadedFeedDataTable = uploadedFeedSheet.Cells.ExportDataTableAsString(0, 0, uploadedFeedSheet.Cells.MaxDataRow + 1, uploadedFeedSheet.Cells.MaxDataColumn + 1, true);

Regards,

Anshul.

4

PFA the test file row numbers 74,114,120,125,and 212 are having issue, there values are auto incrementing.

5

Hi,

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

We were able to observe this issue. We have highlighted the issue in a screenshot. We have logged this issue in our database. We will look into it and fix it or advise you asap.

This issue has been logged as CELLSNET-41735.

Screenshot:

6

Hi,

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

Please unzip the xlsx file and check sheet1.xml file, you will find
809081364101727.5 in it.

How did you create such a
file?

7

Hi,

We didnt understand how unzip the excel file, and couldnt find any attachment in it.

This is the sample business data we received and facing this issue, and we will be receiving the same kind of data every time.

regards

Anshul

8

Hi,

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

Please change the file extension from xlsx to zip and unzip the file. I have also attached the zip file of your sample xlsx file for your reference. Please see the sheet1.xml you will find the values are in decimal point.

9

Thats correct we could see the values in XML and it is .5 and for values greater than .5 xlxs is showing +1 ,

Please let us know how to read and populate the values those are diplayed in excel irrespective of formating or other constraints.

Regards

Anshul

10

Hi,


Any update on this. Actually in excel the values with like 1.5 are shown as 1 but while reading with aspose they are read as 2. So , Is there any setting or work around to read the value as excel does??

Expecting a quick reply.


Regards,
Anshul
11

Hi,

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

We have investigated this issue and found, MS-Excel shows 1.5 as 2 which is same as Aspose.Cells also shows 1.5 as 2. We have attached the screenshot for your reference.

So this is not a bug of Aspose.Cells.

Screenshot:

12

Hi,

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

a) Cells. Exportdatatableasstring()

If you call this method, we will export the string values of the cell as displayed in MS Excel.
If the cell’s values is 1.5 and the number format is 0, “2” will be exported.

b) About 809081364101727.5

In MS Excel, double value only contains 15 significant decimal digits, so 0.5 is removed when the file is loaded, but Aspose.Cells does not check it and just parse it to double.

So the string value of the cell is 809081364101728.

13

This problem was raised because we used number and General format, after using the text format problem was sorted out.

14

Hi,

Thanks for your posting and using Aspose.Cells.

It’s good to know your problem is sorted out. Please feel free to contact if you encounter any other issue. We will be glad to help you further.