We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

MS Excel hard returns are being changed to spaces in MS Word tables

Aspose.Cells Product Family
#1

We’re trying to transform MS Excel content to MS Word table. In the process, we’re seeing hard returns characters in Excel are changed into empty spaces while using Aspose.Cells - 17.6.0.0 and Aspose.Words - 17.6.0.0 . We recently upgraded to this version from Aspose.Cells - 5.3.0.0 and Aspose.Words - 9.7.0.0 .Since the upgrade, we’re facing this issue.

Detailed information is given below.

Source MS Excel

image.png (51.5 KB)

Before upgrade (Aspose.Cells - 5.3.0.0, Aspose.Words - 9.7.0.0)
In MS Word:
image.jpg (3.0 KB)

Cell Values:
image.jpg (64.8 KB)

After upgrade (Aspose.Cells - 17.6.0.0, Aspose.Words - 17.6.0.0)
In MS Word:
image.png (1.4 KB)

Cell Values:
image.jpg (64.9 KB)

#2

@sanooj316,

Thanks for the screenshots and details.

How do you transform MS Excel contents to Word document? I guess you might be using some older custom utility code (created and shared by Aspose.Words team in the older forums) to convert/copy Excel spreadsheet formatted data to Word document via Aspose.Cells APIs and Aspose.Words APIs. The custom utility might not be reliable enough. We recommend you to kindly first check your custom utility to sort out your issue as some APIs might be changed and enhanced since the older versions. Moreover, both Aspose.Cells and Aspose.Words are different and diverse APIs having different architectures, so you cannot always parse objects’ data accurately b/w the APIs. Also, you need to evaluate which product is behaving differently. For example, you may save MS Excel spreadsheet manually with formatted data (without using Aspose.Cells APIs) and then try to use your own custom .NET code and Aspose.Words APIs (latest version) to extract/copy the formatted data into MS Word document to see if you still see the same results. If you found it is an issue with Aspose.Cells APIs, kindly do provide a simple console application (runnable) with Aspose.Cells for .NET v17.9 (latest version), zip it and post us (you may upload to some file sharing service, e.g dropbox, Google drive, etc. and share the Download link here) with all the files, we will check it soon.

Moreover, your JPG images (with low quality) are hard to see, so you need to provide the images in PNG as well.

#3

@Amjad_Sahi,

We have an application built on Microsoft VSTO and custom code that transforms the contents. It has been working correctly for a long time and we’re facing this issue only after upgrading to the newer Aspose Cells and Words versions. We’ve checked the custom code thoroughly from our side. It is the Aspose.Cells.Cell object’s
StringValue property which is having this difference for the same input file (Snapshot attached with Cell object’s values from watch at runtime). Kindly note the StringValue property.

For a hard return, the StringValue is \r\n for older version but \n in newer.
You can ignore the Word aspects here, but I’m wondering about this change in Excel cell’s output. If anything has been changed in one of your previous releases, please let us know how to get the desired output as given by the previous version - Aspose.Cells - 5.3.0.0.

Attaching the snapshots as pdf:
Hard returns are being changed to spaces in tables.pdf (409.9 KB)

#4

Hi,

Thanks for providing us further details and PDF file containing the screenshots.

To test your scenario/case, I simply created a template Excel file (attached). In A1 cell of the first worksheet, I enter the value, i.e., “Center aligned cells” in such a way that I clicked “Alt + Enter” after inserting “Center” word, this would create two lines (with wrapped text option on) and the rest of the text, i.e., “aligned cells” would be placed in the second line. Now I used different versions, e.g v5.3.x, 8.3.x and latest version, i.e., Aspose.Cells for .NET v17.9. I noticed and confirmed your mentioned behavior (i.e., for a hard return, the Cell.StringValue has \r\n for older versions but \n in newer) after loading the file into Aspose.Cells object model using the following code and checking the properties of the Cell object in debug mode in VS.NET. I am not sure if this is an issue with our newer version(s) or expected/correct behavior regarding hard returns. This change was introduced later on in newer versions though. Either this was a bug in older versions and was corrected later on or there might be something else which incorporated this behavior.
e.g
Sample code:

   var workbook = new Workbook("e:\\test2\\Bk_msexcelhardreturn1.xlsx");
        var worksheet = workbook.Worksheets[0];
        var cell = worksheet.Cells["A1"];

I need to discuss with product team and we will get back to you soon with more details.
file1.zip (9.7 KB)

#5

Thanks Amjad . Just FYI . MS Word renders the text as same only if we are getting back text as ‘\r\n’

#6

@sanooj316

Please check the attached Excel file.

Sample Excel File.zip (6.0 KB)

The length of the text in the cell A3 is 11. So hard returns should be “\n” not “\r\n”.

Please replace “\n” as “\r\n” by yourself while you convert Excel to Word.

#7

Thanks Shakeel . You can use the file earlier shared by amjad. We upgraded to 17 for performance. and we are seeing multiple difference like this. We have already handled this by replacing \n by \r\n. Just want to understand is there any other possibility where \n is returned from cells?

#9

@sanooj316

We have looked into this issue. Microsoft Excel cells will always return “\n”. Whether you add data in cells manually or you copy/paste data from notepad into cells. It will always return “\n” and it will never return “\r\n”.

#10

Are you saying Old version of aspose is returning wrong data? . Because we are able to get \r\n even now if we use Aspose.Cells - 5.3.0.0

#11

@sanooj316

Yes, it is a bug and reported by some user (if I am not mistaken) and it was then fixed.