Export to excel and textwrap

[anishravindran]

Hello,

For the text wrapping option while exporting to excel (XLSX) the height of the cell is not getting adjusted to view the full text content.

Tried applying the following options (TextWrap, CanGrow)for both text component and DataSouce band. Please refer the screenshot for the same or the highlighted row.

Also I have applied CanShrink= true since I want the other rows to have the minimum height where no text wrap happens. Please can anyone advice?

Thank You

[HighAley]

Hello.

This is a well-known issue of Excel.
The Excel is not a fixed layer format and if you change zoom level you will see how this text jumps from one line to another.
Sorry it's impossible to fix this from our side.

Thank you.

[anishravindran]

Hello,

Thanks for the reply. But being the text content in a single cell I think this will not happen even if you zoom the level.

But do you have some suggestions that any other properties will help in this scenario, since our old system reporting tool was rendering this correctly and we are a doing a like to like migration with your tool. Please refer the screenshot of the old report for the same data.

Thanks again.

[Ivan]

Hello,

Usually, there are 2 issues might happen with the report exporting to Excel in Stimulsoft Reports
1. The Line spacing for the multi-line text is bigger than the spacing in the preview, so the last line of text might be truncated a little bit.
2. The text does not fit into the cell by its width, but in the preview it fits, so the last word is moved to the next line.

The reasons of these issues are as follows:
1. The rendering of the text in Excel is done there with its own methods which do not match with any of the following methods:
Winforms (GDI+ (Standard, Typographic), GDI (WYSIWYG)), nor with the Wpf and nor with the Flex neither.
Usually, the line spacing in Excel is about 5-15% more than in the preview, especially for the small fonts.
Unfortunately, Excel does not support any means of controlling the line spacing.

2. Because of our own methods of rendering the characters' width and the words' width is different, so the text could easily not fit into the cell by the width and the last word in the cell's line will be moved to the next line, unfortunately.
And there is no way to control the text width in Excel as well.

3. Also, the WYSIWYG function in MS-Office applications often works incorrectly. The width of the words can vary on a different scale. Especially for the small fonts.
As a result of the one scale the text will fit on one line, but for another scale it will not, so the last word will be moved to the next line.
The best look the document will have in the print preview.
So it is not an unusual situation when the Excel report in the edit mode and in the print preview does not match. Even more differences could be spotted if in the edit mode someone changes the page scale from 50% to 200%. During this exercise, the longest text in the page might be cut to the different lines depending on the scale size.

So because of the above-mentioned problems, it is almost impossible to reflect the report's view in Excel.
But hopefully, there is a light at the end of the tunnel! But the report template should be worked on to accommodate the solution for the problem. The report template should be just slightly modified as per the following suggestions:

Line width
1. Try to select different fonts. For each font try to use different TextQuality parameter. And the best result will be achieved for the line in the preview that looks the longest. So the combinations of TextQuality and the Font for that line should be used in the report.
2. Set the right margin for the textbox. Excel does not have margins for its cells, so there will be some reserve of an empty space for the text.

Line height:
3. Append an empty string to the end of the text to compensate the error/discrepancy of the line height. This is the simplest but is the worst way at the same time.
4. Is not the simplest way. Set the AllowHtmlTags property to true and add the following tag to the beginning of the text;
<line-height="1.1"> and then replace if required all the carriage returns with <br> tags. After that, the preview will have the line-height bigger than a normal string. And after that, the adjusting coefficient should be determined and set correctly. Let's start from 1.1 for the adjusting coefficient value, as it might vary for the different fonts.

Thank you.

[anishravindran]

Hello Ivan,

Thanks a lot for the detailed reply and putting the limitations which is not within your boundaries. Great to see the wonderful support.
Let me check the suggestions you have put to me and see that can solve my problem. Thanks again.

Thank You.

[HighAley]

Hello, Anish.

We are always glad to help you.
Let us know if you need our help.

Thank you.

[anishravindran]

Hello Ivan,

Happy to share you that your suggestion for setting a margin fixed my issue. Just setting bottom margin for the text control did the job.

Please refer screenshot.

Thanks a lot. Much appreciate.

[Lech Kulikowski]

Hello,

Thank you for the provided information.