I've been having some problems with a report on export to Excel. I have four tables, contained within a rectangle at the top of the report, followed by a second page of tables dynamically generated by the data being fed into the report.
On export to Excel, the top sections of tables appear to export with a fixed row height that does not show all of the string contained within the cell.
I've had similar issues before with "title" textboxes, but these are usually easily solved by expanding the height of the box within the report. In this case, this hasn't worked!
Any idea?
Regards,
Jon
Hello Jon,
How are the Tables positioned? Are they positioned vertically, or are the tables located beside each other? The only way I was able to reproduce the behavior you describe is to create a cell which contains enough text to cause Excel to reach its row height limit. Excel has a limit of 409.5 for row heights. Are your reports hitting this limit, or do the rows have room to grow, but aren't?
Ian|||Hi Ian,
The tables are positioned both horizontally and vertically as there's four tables in a square formation (imagine one table at each point of the square).
The text certainly doesn't reach the row height within excel. In fact, it only needs around 3 times the amount required by a single row at a font size of 10 pt.
The size of the row within the report designer should be enough on it's own to accomodate the amount of text. And within report manager, the row re-sizes to the correct height required.
Thanks for getting back to me,
Jon
|||
Can you post a screenshot of the problem? I'm having trouble visualizing what the issue is.
Thanks, Geoff
|||If you want to render the exact same format in Excel than in Preview, it can be challenging.
Excel export is usually that bad that I simply format all reports to be as flat as possible.
I stay away from csv export because it just is not practicable.
I am also used to systematically remove all the "Merge Cells and Wrap Text" options in Excel or format the report to avoid this behavior as much as I can since this is to me a major anoyance and a increase of file size I do not need.
if you need a report formated like this you do not need an Excel output, you need an HTML archive or a PDF.
I always use WEB format for stuff with complex layout I want to preserve. people can still copy paste this from a web page or an email to an Excel if they want.
If you need Excel it is most likely to be for further analytical processing like adding foormulas and building pivot tables, in which case, the merge and wrap stuff is just a waste of time.
Microsoft team, what I am dreaming about is a PLAIN Excel export option without all these bernacles that makes the file bloated, hard to download and open when big and require much work to remove the unwanted formating.
CSV export as it is today is close to be impossible to use, it is too disconnected from the original report.
Philippe
|||Unfortunately I don't have an easy way of linking an image, and no ftp site to upload to (unless there's an easier way that I'm missing?).However, try and imagine this: You have a cell, populated by a small paragraph of text spanning three lines within report manager. On export to Excel, the cell height in Excel is only large enough to show the first of the three lines of text. It does not seem to grow dynamically to show all the text, without the Excel user expanding the height of the cell manually.
Does that make any more sense?|||
Have you set CanGrow on the text box? If it isn't set, or there are other objects rendered to the same row that do not have CanGrow set, it may not adjust the text box properly when rendering to Excel. If you already have the textbox set to the appropriate height in the RDL, then I'm not sure what is causing the issue and we'll need to investigate further.
Hope that helps!
-Geoff
|||Hi Philippe, thanks for the feedback.
Wrap text in Excel will not increase the size of the Excel file, as it is only a single boolean flag. Merge cells, however, will increase the size of the file.
The difficulty of using the Excel output for data is something we're very aware of, and we are considering an Excel data export for a future release, as well as improving the CSV output. If your needs can be met by simple data output to Excel, you may want to check out SoftArtisans OfficeWriter, as it will let you define a template in Excel and then drop data into it:
http://officewriter.softartisans.com/officewriter-250.aspx
- Geoff
|||Hi Geoff,Thanks for getting back to me. Yes, the CanGrow property is set to true. In fact, I tried it with the other option for decreasing contents too, just out of interest. Neither of them seem to affect the export to Excel in this case (although it has on others).
Could it be to do with the fact I have 4 tables within a rectangle?
Jon
|||
If you have other tables at the same horizontal level, or other report items at all (i.e., other items rendered in the same row) they could very well be affecting the autogrow property in Excel.
I would remove all but one table and see if the behavior is still incorrect. Also, try removing the rectangle.
- Geoff
|||I've removed the rectangle, and all surrounding tables and it's still behaving in the same way.Very odd. Like I've said before, I've had this problem with textboxes, but normally increasing the height in the report designer helps this issue.
By the way. Geoff - any chance you can have a look at this for me? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=497582&SiteID=1
I think it may be a bug, and but i'd appreciate your thoughts. It's causing an issue for myself and one of my colleagues.
Thanks,
Jon
|||Jon, if you could put this into Connect (https://connect.microsoft.com/SQLServer ) then we can start taking a look at it via our regular issue pipeline.
Thanks, Geoff
|||I've now logged this with connect.Thanks for your help Geoff.
Jon
|||Hi,
I have the same problem. I have one table and the Header Row is set to 0,59062in in height and shows four lines of text 7pt bold.
If I export to PDF everything looks fine but in excel export the row is just one text line high.
Does anoyone know how to solve this?
Thanks,
Mats|||Hello Mats,
In the Excel renderer, if a textbox has CanGrow set to true, along with other criteria, a flag will be set on the textbox's row in the Biff 8 output to indicate that the row's height should be automatically adjusted to fit the text. However, this does not always work for some scenerios, e.g., if the textbox uses a non-default font or the textbox spans more than one cell.
Therefore, if you set CanGrow to false on the textboxes within the row, the Excel renderer will not set this flag, and will use the size specified in the RDL instead.
Ian
No comments:
Post a Comment