Data format issue after Workbook.save()

Aspose.Cells Product Family
1

Hi,

I have data format issue AFTER Workbook.save(), steps to reproduce,
1.Save xls file by Aspose.
Workbook wb = new Workbook(“CF10173.xls”); wb.save(“CF10173_SavedByAspose.xls”);
2.Open original file CF10173.xls in Excel, in Cell M34, manually input value 1.23456, and M34 will show 1 which is expected format.
3.Open Aspose saved file CF10173_SavedByAspose.xls in Excel, in Cell M34, manually input value 1.23456 and M34 will show 1.23456 instead of 1.

Note: this excel file contains conditional format. My concern is the saved file should have the same format as Excel.CF10173_updated.zip (236.1 KB)

2

@huichen,
I have tried this scenario using latest version Aspose.Cells for Java 19.4.6 but could not observe this issue. The resaved file when opened in Excel, behaves similar to the one sent by you i.e. when I enter 1.23456 in M34, it shows 1. Could you please try this scenario using the latest version and provide the feedback? Also, ensure that you have sent us the right file for testing as its name suggests that it is saved file and not the source template file.

3

Hi,

I just downloaded latest Aspose.Cells for Java 19.4 and confirmed the issue is still there.

My local env is win7(64) + Excel 2010. Do you have the same env to verify?

Also, could you please test the other file CF10173_SavedByAspose.xls and let me know the result (just open and check)?

Thanks,
Hui

4

@huichen,
Please note that you have shared only 1 file, CF10173_SavedByAspose.xls and I am unable to observe the issue with it. Could you share the source template file CF10173.xls for our analysis. We will check again and provide our feedback.

5

Hi,

Thanks for the update. I have re-uploaded the attachment with 3 files.

If there isn’t any issue with CF10173_SavedByAspose.xls on your machine, could you please try some other machine and Excel version?

Regards,
Hui

6

@huichen,
I have tested this scenario in macOS v10.14.4 and Excel for MAC also. No issue is observed and 1 is displayed when 1.23456 is entered in M34. You may also please test this scenario on different system using latest version and share the feedback.

7

Hi,

I have tested the CF10173_SavedByAspose_v19.4.xls on both Excel 2010 and Excel 2016 and the issue persists.

Could you please check the file CF10173_SavedByAspose_v19.4.xls on Excel 2010 and Excel 2016?

Thanks,
Hui

8

@huichen,
I have checked it again with different Excel versions and get always 1 when sample value 1.23456 is entered in cell M34. Please note that this cell is formatted with the custom string “#,##0_ ;[Red]-#,##0”, therefore displaying decimal values is not possible with this setting and only integer part of the value will be displayed.

I am afraid that without reproducing this issue here, it is not possible for us to provide assistance. You may please share your environment details, OS and system locale settings. We will try to investigate it further if possible.

9

Hi,

FYI, I have uploaded the original file that saved by Aspose. CF10173_1604422.zip (489.8 KB)

In this file CF10173_1604422.xls, the problem cells are in worksheet “Sector Companies”, J15/K15/K71/L71/M71/etc which are formatted with the custom string “#,##0_ ;[Red]-#,##0”, but still displaying decimal values.

Please check this file on your machine and please let me know if there is still no issues.

Thanks,
Hui

10

@huichen,
Here is the image showing behavior in Excel 2016 (Tested in Excel 2013 and 2010 also). No issue is observed and values are shown as per the custom string.

Excel365View.png (19.7 KB)
Excel2010View.png (55.7 KB)

11

Hi,

You don’t need to input/change anything for this file.

When open the file, does J15 show 878 or 878.45641? It shows 878.45641 on my machine which is NOT correct.

Thanks,
Hui

12

@huichen,
It shows 878 as shown in the attached images. Tested in Excel 2016 for Windows 7 and macOS Version 10.14.4 both.
Screenshot 2019-05-24 at 4.12.48 AM.jpg (350.3 KB)

13

Hi,

I have 878.45641 shown in Excel 2010 when opening CF10173_1604422.xls. Screenshot CF10173_1604422_Excel2010.png (45.0 KB)

Could you please share your screenshot for CF10173_1604422.xls in Excel 2010 / Windows7?

Thanks,
Hui

14

@huichen,
We were able to observe this issue on one of our test machines. We want to investigate this issue more and need the original file which was saved and shared by you here. Please send us the original file because we have to produce the output file here for our testing.

15

Hi,

The original file is here: CF10173_1604422.zip (489.8 KB)

Please let me know if you need more information.

Thanks,
Hui

16

@huichen,
We have investigated the issue in detail by comparing the original file and saved file in different Excel versions and operating systems. One behavior which is always observed is that the original file and file saved by Aspose.Cells show similar values. It shows that its not an issue with the Aspose.Cells but behavior of Excel in different environments. You may please test it on some other machines and share the feedback.
ComparisonsExcel2010Windows7.png (119.2 KB)
ComparisonsExcel2016Windows7.png (74.6 KB)
ComparisonsExcel2010Windows8.png (116.2 KB)
ComparisonsExcel2016macOS.jpg (445.9 KB)

17

Hi,

I would like to re-write my issue with attachments. CF10173_1604422_20190527.zip (963.8 KB)

I have uploaded two files, one is saved by Excel, the other is saved by Aspose.

The issue is when open CF10173_1604422_savedByAspose.xls, it shows decimals while the other file will show correctly without any decimals.

18

@huichen,
I would like to clarify my findings again. Your sample file is loaded and saved again using Aspose.Cells latest version. Now there are two files one is the original file and the second one is the saved file (by Aspose.Cells). As per your statement, your original file shows integer value only whereas the saved file shows complete float value i.e. integer and decimals both.

I opened these two files side by side in the following environments:

  1. Excel 2010, Windows 7 - BOTH the files show decimal values

  2. Excel 2010, Windows 8 - BOTH the files show decimal values

  3. Excel 2016, Windows 7 - BOTH the files show values without decimal

  4. Excel 2016, macOS - BOTH the files show values without decimal

These results at our end show that before and after saving the file, there is no difference if both the files are tested in the same environment. Hence it does not seem to be an issue with Aspose.Cells. On the other hand, if we see the above results, Excel 2010 shows decimal values in both the files but Excel 2016 does not show decimal values in both the files. Hence it seems to be the difference in the behavior of Excel while handling this custom formatting string.

Now coming back to your statement, you mentioned that in the same environment (Excel 2010/Windows 7) original file does not show decimal whereas saved file shows decimal values. This behavior is not reproduced by any means here. You may please share an image where both the files are opened side by side. We will try to investigate it more (if possible).

Hope this clarifies the situation.

19

Hi,

Thanks for the update. Could you please just follow below steps and share the results in different environments?

  1. open the file CF10173_1604422_savedByAspose.xls in Excel, check ‘Sector Companies’!J15
  2. open the file CF10173_1604422_savedByExcel.xls in Excel, check ‘Sector Companies’!J15

Below is my test result under Excel 2010/Win7, CF10173_1604422_Excel2010Win7.png (28.9 KB)

Thanks,
Hui

20

@huichen,
Although we are still not able to reproduce this issue here, we have decided to investigate it thoroughly. We have logged the issue in our database for investigation and for a fix(if applicable). Once, we will have some news for you, we will update you in this topic.

This issue has been logged as
CELLSJAVA-42916 - Data format issue after Workbook.save()