wrap text, text not wrapping

[Giancarlo Aguilera]

Hi!

First and foremost and I wanna say thanks for all your help, whoever the folks are that answer my posts (melba) yet sure enough I never say thanks for the answers. Also, so far XlsIO kicks butt, loving it

Anyway, I'm having a problem. I have attached an excel file that illustrates the problem I am having. Please open the sheet called "Page 2".

1) Please take a look at cell C49, notice that it displays the value "Total (Equiv.)", yet the actual value of this cell is "Total (Equiv.) Units". I have set the wraptext property of this cell to true yet it has no effect, the row height remains such that you cannot see the contents of the cell. The result is the same whether I do or do not invoke autofitrow for this row. Also, please note the cell immediately below this one (C50), this cell does not manifest this problem rather the row's height adjusts such that the entire value of the cell is visible. Why is this?

2) Please take a look at cell M62, which has the same problem as above. However, this cell contains a number, not text, not sure if this matters. I can definitely live with this problem for the above cell (C49) since I have the workaround of simply not wrapping its text and letting the extra characters flow to the next cells, since these next cells have no content. However, for cell M62 this is a huge problem for me. Furthermore, I cannot simply autofit the entire column because it will result in a much bigger size that what I need, rather I need the column width to stay fixed and the actual autowrap functionality to work.

What do you think the problem could be? For me this is a huge problem simply because I am using XlsIO as a full blown reporting tool (and so far things have gone great except for this), and any reporting tool MUST be able to consistently wrap text, since I believe this to be very basic functionality of any reporting tool. Should I not be using XlsIO as a full blown reporting tool? Your prompt attention to this matter is greatly appreciated

Thanks

GA

Sample94.zip

[Melba Winshia]

Hi GA,

1) Regarding the issue with cell C49:
------------------------------------

Could you please set the wrap text property as false? This would display the contents of the cell [C49] clearly. Here is the sample for your reference:

Wrap.zip

2) Regarding the issue with cell M62:
-------------------------------------

I was able to reproduce the problem. But I tested for this behavior by setting wrap text property in MS Excel for the cell with number [M62]. But it does not seem to work. Could you please provide more details on how this can be done in MS Excel? This would help us in investigating further on this issue. Please let me know if I am missing some thing. However could you please set the column width by using the ColumnWidth property so that you can see the contents of the cell clearly? Here is the sample for your reference:

Wrap.zip

Here is the screenshot of the output file generated:

wrap.png

Please take a look and let me know if you have any other questions.

Best Regards,
Melba

[Administrator]

Cell C49 is very interesting. MS Excel (at least that one that is installed on my PC) shows or not full content of the cell depending on the zoom rate. When zoom is 100% or 85 it shows it. When 70% it doesn't show correctly. So it looks like MS Excel has bug here. For example, maybe they deside whether string fits cell or not (when WrapText = true) in zoom = 100%, but when they draw it using different zoom factor they doing something wrong and replace value with ###. And it looks like C50 is wrapped correctly only because it is one character longer.

As you have already said M62 contains number so it can't be wrapped (property is called Wrap TEXT). So it looks like the only solution here is to set correct column width, or set ShrinkToFit property to true.

[Giancarlo Aguilera]

First and foremost, thank you both for the replies! Much appreciated

Regarding cell C49, this is not an issue for me, since it's a zoom issue like anonymous pointed out, and even though the issue exists on screen, when printed out, C49 prints out in its entirety. And even if it were not a zoom issue, I would simply not wrap the text in this cell, like you suggested Melba.

But my problem remains with cell M62, the one that has the number. Indeed not even Excel adjusts the height of the row, so your argument Melba is how can OR why should XlsIO do so. Indeed it seems Excel simply ignores wraptext when the cell in question contains a number, which is kinda shocking on the one hand but then again Excel adjusts the width of cell's column if the number is too big. However, for me increasing the size of these columns in not an option, simply because this report contains many many columns and MUST be print landscape on 8.5 X 11. So again, I need some other workaround. One workaround is to:

range.Text = number.ToString(numberFormat)
range.WrapText = true;

instead of:

range.Number = number;
range.NumberFormat = numberFormat;
range.WrapText = true;

However, I would prefer not to go this route because the excel file will end up with many cells with green triangles in one corner that signal that a number is stored as text :), but much more importantly than this, I want the user to be able to see the "raw" number value in addition to the formatted value. For example, if you take a look at cell M54, it displays the formatted value of 8,341 but if you notice the actual raw value is 8341.182, and this is something I will lose if I do range.Text instead of range.Number, you follow me? Again, ideally AutofitRow would take care of this. I understand that Excel does not do this, so my assumption is that since XlsIO is "Excel Faithful", it ignores any number cells when determining what the height of the row should be, just like Excel. But I need more :). What about an AutofitRow overload that will do what I need, and that is, NOT ignore numeric cells when determing the height of the row. Is this something that can be done (soon)? I understand your point about but please understand mine, and that is, I am using XlsIO as a full blown reporting tool, as a replacement of the current reporting tool we use, which like any other reporting tool (crystal, active reports, etc...), have reliable support for wrapping text based on the DISPLAYED value.

Thanks

GA

[Administrator]

The problem with that cell is not because of bad height. Simply MS Excel can't wrap numbers (or doesn't want to for some reason). So even if row height is big, MS Excel will show that number as #####. That means that AutofitRow won't help you here. Possible solutions are:
1) enlarge column width (maybe some other column or columns can be made smaller so the whole report width will stay the same)
2) set different font (font size or remove bold attribute)
3) set ShrinkToFit to true (ms excel will automatically make font smaller to fit value into cell)
4) change number format

[Melba Winshia]

Hi GA,

Thanks for your detailed explanation.

But these are the possible ways to do this work.

1) Autofit rows.
2) By adjusting the columnwidth
3) range.Text = number.ToString(numberFormat)
range.WrapText = true;

These are the only possible ways to see the contents of the cell clearly. Sorry for the inconvenience.
Please let me know if you have any other questions.

Best Regards,
Melba

[Leigh]

Hi, i'm not sure if this helps but I have resolved your problems by doing the following. It seems complicated but it really is simple. In ref to your issue in in cell C49/50 with the text not fitting the cell although wrap text is selected. If you move your curser to the right hand side of the cell headed 'C' (ie the row header) a cross hair should appear. Double click and the cell should expand to fit all the text. This should now display all text required in cell 49. You can also use the same process to shorten the height of cell C50. Move curser to the bottom of the row headed 50, double click once the cross hair appears. The row should shorten in height.

I think the problem in cell M62 & M65 is due to the cell width being to short. If you expand the the whole column (m) by double clicking the header of the row marked M. Using the same double click on the cross hair as mentioned before. You will notice that the whole cloumn expands to fit to the biggest cell. You will notice that the it has expanded too much for the cells in question. This is because if you look to where you have inputed the people working in conjunction with this spreadhsheet i'e Account Executive: you will notice that it has expanded to fit this title. As this is the largest cell due to the length of it's characters it will expand to fit this in. It you would like to reduce this to only fit the cell in then i suggest you merge the cells L & M 5. Merge cells by highlighten both right click on the highlighteds cell, selcet format and the check merge cells. This now moves the Account Executive title into one cell and removes it from Cell M5. If you now double click the column M you will find it will now auto adjust to the next shortest cell length.

I have often found that problems can occure in cells which seem to have the right format only to find that it is the format of other cells next to or arond your the cell which are the problem

New Compressed (zipped) Folder.zip