February 27, 2009 at 7:51 am
Ok, so I know this is a common issue and I have found tons of articles online. I thought I would ask SSC since I cannot cure it. I have several reports that have columns that can contain a lot of data, or a little bit of data. The cangrow property is set on those fields accordingly to allow them to expand. When you view the reports in RS they look fine. Export to PDF, they look fine. Export to excel, they do not expand. You would have to manually expand each row which is not an option. I have several merged cells on the report, I have text boxes below the report in the footer, and some of the reports have 3 tables, and some have 1. I resolved the issue on the reports which only have 1 table. I removed as many merged cells as possible, removed all whitespace from above/below/beside the table and moved the footer up so that it is as close to the table as possible. This resolved the issue. The reports that have multiple tables however, I cannot seem to correct this issue. The SQL itself is just a simple select, and I am using Visual Studio to develop the reports. If anyone else has found a universal cure, it would be much appreciated!
February 27, 2009 at 9:02 am
Mike
Not sure if this is what you are looking for, but try this in your next Excel report.
Manually
Select adjacent columns by clicking on the column headers(I selected 4)
On the main menu click
Format
Auto format
Options
Uncheck all but Width/height
If this works to your satisfaction then convert those actions to code in your Visual Studio application.
which will give you:
Columns("A : D").Select
Selection.AutoFormat Format:=xlRangeAutoFormatSimple,
Number:=False, Font:=False, Alignment:=False, Border:=False,
Pattern:=False, Width:=True.
For just a specific number of rows change the above select to the range desired, for example:
Range("A1 : D7").Select
Hope this helps.
February 27, 2009 at 9:08 am
I followed the steps you outlined, but when I uncheck all but width/height it actually makes the rows I selected smaller.
February 27, 2009 at 9:12 am
Just to recap, when I export my report from RS to Excel, it loses some formatting. The cells that are supposed to grow when they have too much data do not grow at all and data is cut off. It is only in excel this happens, and it is only on the reports that have multiple tables.
February 27, 2009 at 10:30 am
Mike AAAUUUUUUUGGH for my first recomendatin, just one more wild possibility - set the cells so that text wraps. Note text with text containing a tab - this does not work.
Convert to code in your Visual Studio app.
Range("D3:D9").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Or manully
Format
Cells
Wrap text
Other than these suggestions I am at a loss.
February 27, 2009 at 10:59 am
After the export, by default, the cells are already set to wrap. The text is in fact wrapping, but the rows are not expanding their height.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply