Vertical alignment and column widths not always preserved when converting from �XML Spreadsheet 2003� format to XLSX format

Aspose.Cells Product Family
1

Dear Aspose support,

I have found some cases where when converting from “XML Spreadsheet 2003” format to XLSX format using Aspose, the formatting is different after and before conversion (when I open the files in excel 2010).

This is related to the issue described in the thread at url :

Free Support Forum - aspose.com

The previous issue has been focused on issues related to font sizes, and I have been advised to open new requests about other styling issues.

In this new sample xml file, after conversion with Aspose for Java 8.6.2, there are differences in vertical alignment and column width. Most cells containing numbers have different alignment - the numbers are vertically in the bottom of the cells instead of in the middle. Other cells have similar alignment issue as well, like cells I4 and I5. In the second sheet, several columns have different widths after conversion : columns K to P are a bit narrower after conversion (and that causes the date in column N to be unreadable for me in excel 2010 after conversion, while it is OK before conversion).

Would you be able to reproduce those differences by opening my sample in excel, check how it looks, then convert it to xlsx using Aspose.Cells for Java, and check whether the formatting is different for you as well ?

Note : I converted the file with code like :
InputStream is = <get input stream of file in “XML Spreadsheet 2003” format>;
Workbook wb = new Workbook(is);
wb.save(outputFile, SaveFormat.XLSX);

Thank you very much for your support,

Aspose.Cells User.

2

Hi,

Thanks for your posting and using Aspose.Cells.

Please check the attached xlsx file generated with the following sample code using the latest version: Aspose.Cells
for Java v8.6.2.4 and let us know if you still find any issues inside it. Please provide us some screenshots highlighting these issues so that we could look into it and log the tickets accordingly in our database for a fix. Thanks for your cooperation.

Java
String filePath = “F:\Aspose_sample_47_utf-8.xml”;
Workbook wb = new Workbook(filePath);
wb.save(filePath + “.out.xlsx”, SaveFormat.XLSX);

3

Thank you very much for your quick reply and for providing a sample from the latest version. For the record, the version that I use currently reports “8.6.2.0” so it is not the latest indeed.

I have checked the generated file in Excel 2010 and I can observe the same differences that I saw with my own generated file : in the first sheet different vertical alignment, and in the second sheet different column sizes.

I will attach annotated screenshots that show the differences I see.

Thanks and regards,

Aspose.Cells User.

4

Hi,

Thanks for your nice screenshots highlighting the issues and using Aspose.Cells.

We were able to observe these issues and logged it in our database for investigation. We will look into them and fix these issues. Once the issues are resolved or we have some other update for you, we will let you know asap.

These issues have been logged as

  • CELLSJAVA-41638 - Vertical alignment is not preserved when converting from “XML Spreadsheet 2003” format to XLSX format
  • CELLSJAVA-41639 - Column widths are not preserved when converting from “XML Spreadsheet 2003” format to XLSX format

5

Hi again,


This is to update you that we have fixed the problems logged earlier as CELLSJAVA-41638 & CELLSJAVA-41639. We will shortly provide the fix here after ensuring the quality and incorporating other enhancements.
6


Wow, this is great to hear, that was fast, thank you so much !

If you are able to provide a beta version or something similar before general release, I could probably help test it as well, my company has many xml excel files to convert (>150), and I have a tool to convert all of them in batch and check for differences automatically, so I could be able to provide interesting feedback if that can be useful. If it’s not possible no problem, I will then test it and provide feedback when the general update gets distributed.

Best regards,

Aspose.Cells User.

7

Hi,


I think the fix will be shared with you sometime today or max by tomorrow so I hope you can wait till then. Thank you for your understanding.
8

This is perfect, I can wait even a few days no problem. Thank you so much for your great support !

Thanks and regards,

Aspose.Cells User.

9

The issues you have found earlier (filed as CELLSJAVA-41639;CELLSJAVA-41638) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
10

Dear Aspose Support,

I tested converting with Aspose 8.6.3, and I am happy to confirm that the vertical alignment issue seems to be solved by this update (I did detect some vertical alignment issues in 3 of my files, but more than 75 files that had such issues are now entirely fixed, so this looks good to me - the remaining issues might be because of issues in my files).

However, the column width issue does not seem to be fixed. I tested with many of my files, then I confirmed again with the sample I provided as part of this post.

Could you try to convert the Aspose_sample_47 sample I provided with Aspose 8.6.3, and check the width of the columns ? I observe that the columns 10, 11, 12, 13 and 14 in the second sheet still have different width between the original xml and the converted xlsx, can you see this difference as well ?

Also, I observed that with Aspose.Cells 8.6.3, the conversion process can be stuck with some files - I had to kill the java process in those case. I will investigate that further and open a new forum thread about this issue. I did not have that issue with the previous Aspose version.

Best regards,

Aspose.Cells User.

11

Hi,

Thanks for your feedback and using Aspose.Cells.

We were able to see this issue but there is a very minute difference. Could you please highlight the issues with some screenshots so that we could fix them precisely. I have attached the output excel file generated with the latest version 8.6.3 for your reference.

12


Thank you very much for investigating this issue. I confirm that the differences are very subtle. I will attach a screenshot of a file before and after conversion, where it can be seen that the column limits do not align.

Doing those comparisons made me discover another issue with the handling of dates where the day and month are reversed if the day is less than 13 - I will open a separate thread for that. It seems to be a regression in 8.6.3, as it was OK with 8.6.2.

13

Hi,

Thanks for your nice screenshot highlighting the issue thoroughly and using Aspose.Cells.

We have reopened this issue and logged your comment and screenshot for further investigation and a fix.

  • CELLSJAVA-41639 - Column widths are not preserved when converting from “XML Spreadsheet 2003” format to XLSX format

It is a very good idea to create separate thread for separate issues because threads are linked to database tickets and help product team to deal issues one at a time and it also remove confusion or ambiguity for them.

14

Hi,

Thanks for using Aspose.Cells.

It’s very strange that MS Excel adds one pixel for those column.

a) CELLSJAVA-41639:
We found if the column does not contain ss:AutoFitWidth setting, we have to auto fit column width for the cells whose values are date time or number. And only when the fitted width is greater than the setting in the file, the fitted width works.

b)CELLSJAVA-41639:
We could not understand why MS Excel expands one pixel from column “J” to “M”.

For example:
The setting of column “L”:
The width of column “L” in the saved xlsx file is 77 pixels, it’s same as 57.75*72/96 (the stored width is in unit of point), but the displayed width of xml in MS Excel is 78.

Now we have no solution to deal with this problem.

15

@Aspose.CellsUser,
This is to inform you that we have fixed your issue (logged earlier as “CELLSJAVA-41639”) now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

16

The issues you have found earlier (filed as CELLSJAVA-41639) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi