Excel formatting

[IT]

I'm loving the new excel export option, but I have a question.
To what degree can I control the format of the excel document (row height, column width, etc)? If that is possible, how do I do it? Thanks!

[Rossen Hristov]

Hi Josh,

Thank you for contacting Telerik Support.

Telerik Reporting is designed to allow the developer to specify the report's design and data-binding, and leave the actual exporting work to one of the available rendering extensions. When you create a report and export it to Excel, the Excel Rendering Extension will try to render it by preserving its layout and look-and-feel as much as possible. Therefore, you can control the creation of the Excel document to virtually any degree -- it all depends on how you create the report in design-time. For a simple example, if you create a TextBox that is 80 pixels wide then you will get an Excel column that is 80 pixels wide. So, it all depends on the way you will design your reports.

Please, do not hesitate to contact us if you have any other questions.

All the best,
Ross
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Sarju]

Hi,
  Can I control the page setup when the excel document needs to be printed, i.e. by default it should be in landscape mode? 

Thanks
-Sarju

[Rossen Hristov]

Hello Sarju,

The Report has a PageSettings property. You can adjust the page orientation from there by setting Landscape to true.

Kind regards,
Ross
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Sarju]

Hi,
  I am sorry; I did'nt realize that this thread was related to Telerik Reporting.  I wanted to know the formatting options in relation to the export to excel function available with the Rad Grid.

Thanks
-Sarju

[Sebastian]

Hi Sarju,

Basically, the grid will be exported with the relevant style setting if you choose ExportSettings -> ExportOnlyData = true. Furthermore, with the Prometheus version of our web grid you can choose native export to Excel which allows you to fine-tune the grid appearance prior to the table rendering in the resulting excel file:

http://www.telerik.com/DEMOS/ASPNET/Prometheus/Grid/Examples/GeneralFeatures/Exporting/DefaultCS.aspx

http://www.telerik.com/help/radcontrols/prometheus/?grdExport.html

Best regards,
Stephen
the Telerik team

Instantly find answers to your questions at the new Telerik Support Center

[Clinton Smyth]

Hi

I'm experiencing formatting issues when exporting to Excel from Telerik Reports (version 2009 Q2).  The data appears to be fine, however, when data in a cell wraps onto the next line, Excel displays only part (i.e. top half) of the wrapped data.  This formatting issue results in the user having to resize each row so they can see all the data. 

Additionally, on each row, we have both long data (which is wrapped) and short data that fits on one line.  The long data appears in merged cells (which are partially obscured) and the short data appears in the top cell (with subsequent blank unmerged cells below).  This results in each line of data being stored in Excel in a mixture of merged and unmerged cells.  It would be better to have the whole row with uniform vertical merging of cells.

I have the CanGrow property set to true.

My questions are:

1. How can I export the data to Excel so that all the data is visible?
2. How can I have each line of data on 1 row in Excel (i.e. uniform vertical merging)?

Thanks

[Peter]

Hello Clinton Smyth,

As my colleague explained in the following forum post some of the applications use different line spacing while drawing text lines so we have no way of knowing what line spacing would a viewer software (Acrobat, Excel etc.) use. In other words, we've made it look correct for the image rendering (PDF) and excel in particular is drawing the text lines with different line spacing.

About your second question if you are not using table item this is expected behavior. With the current implementation in order all of the cells in excel to be merged or unmerged you have to nail up the textboxes in the detail section or panel with dock set to "left" or "right" and make sure that the detail section or the panel height is as the height of the textboxes.

All of this is necessary because Excel export is being created to look as in the report it is exported from. It would be rather unexpected to export a report and it would look totally different from what you've expected.

Thank you for the understanding on the matter.

Greetings,
Peter
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Clinton Smyth]

Hi Peter

Thanks for your reply.  I'll implement the changes you've suggested regarding merging the cells.

Just to confirm, are you saying that there is no way of getting Excel to render the rows correctly?  This problem surfaced during a demo to an important client, whose comment was 'Well that's no good'.

Surely there's something we can do. Is it not possible to manually set the row height (we could estimate the height based on the length of the wrapping field)?

[Steve]

Hi Clinton,

As you probably know, report items can only grow vertically, so the height is not a constant, unless you set CanGrow property of the textbox to false. In this line of thoughts, you can make the height of the textbox to match the height of the biggest field you've got to show and set CanShrink=true to shrink if applicable. But otherwise no, you cannot estimate the height manually during runtime - this is all controlled by the respective rendering extension.

Kind regards,
Steve
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[Clinton Smyth]

Hi Steve

I used the method suggested by Peter to merge the cells which has worked fine - each row of data is now on one Excel row.

However, for some reason the CanShrink property does not work.  I resized the detail row in the report to be large - as suggested - but it does not shrink, even with CanShrink set to true!  In contrast, I can see the effects of turning CanGrow on and off (albeit with the formatting issue we're dealing with).

Another work around I had in mind was to add a carriage return onto the end of the data in the long field.  This produces a gap after each row in all exported formats (we're specifically interested in PDF and Excel).  We can live with this gap in Excel but it's not acceptable in the PDF report. 

So .. is it possible to capture the click of the 'Export' link in the report toolbar?  I couldn't find an "OnExporting" event.  If we can do this, and can check if we're exporting to Excel, I could add the carriage return on the fly to the long field in each row in the datasource.

[Steve]

Hi Clinton,

Currently the web report viewer does not expose such an event because the 'export' functionality is handled by an http handler which is separate object and there is no way to attach an event to it. The only way to go this way, would be to prepare your own UI for the toolbar and handle the control's events as you need them. This way you can bind the report to two different datasources depending on the export format.
As for the CanShrink property, it would not work with docked items as it contradicts with the purpose of the dock. That is why as my colleague suggested, the best way to go would be to use the native Table item.

Kind regards,
Steve
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.

[anwar]

Hi.

i am using Teleric.Reporting.Report to generate excel report. By using this, excel is generating fine. But the excel grid lines are not visible. Is there any option to enable the grinlines.

Thanks in Advance,
Anwar

[Steve]

Hi Anwar,

Whether the excel grid lines are shown or not is controlled via the ShowGridlines device setting. The default value is false, for more info see the following articles:

• Telerik Reporting Configuration Section
• Excel Device Information Settings

Regards,
Steve
the Telerik team

[Thomas]

Hi,
I have some questions about the export to excel. I dont work with the reportviewer and create a excel worksheet on the fly and want to  format some settings:

1. How can I format the print settings (print in special order [from left to right to buttom an not from left to buttom to right]; set the selection of printed page)

2. Problem: I got a table with 200 rows, but when i want to print the table ( and i set printheader on evey page) excel show that i got only 1 page , but there are 20 pages?! And the header is not shown on every page.

Thanks,

Thomas

[Steve]

Hi Thomas,

Thanks you for your interested in Telerik Reporting and up to your questions:

• We do not have control over your printer settings as the report is not aware of your printer. It seems you talk about "across then down" layout, while the default one for a report is "down then across". "Across then down" layout means that you have fixed amount of columns and you want items to be filled horizontally first. You can handle such scenario even now with some manual preparation of the data and our agile CrossTab item. The idea is to do the CrossTab grouping by a criteria that will always return equal number of columns. This is accomplished by using "Id % X" and "Id / X" where X = number of columns and Id is consequential numbering (1, 2, 3, ....)
• Excel is not a page oriented format, meaning it can be considered unlimited by width and height when opened in a program for viewing excel files. Should you print it however, it would be "paged" by the program you use. You can see how many pages will be generated when you go in MS Office PrintPreview. Page Headers and Footers in Excel are only shown when printing due to the same reason.
  
  

All the best,
Steve
the Telerik team

[Thomas]

Hi Steve,

thanks for your support! I will look what i can do with your help :)

Ragards,
Thomas

[Jeff]

I have several tabular reports built with your Q1 2013 SP1 release.  I am having an issue when exporting to the Excel 97-2003 format.  While print preview of the report before exporting displays as one page (desired), after exporting and a print preview there are now 2 pages (undesirable).  The last page is always blank.   I have reduced margins to zero and have at one point allowed the design of the report to only be 5 inches wide, but still 2 pages! I have gone into the exported report and when I shrink the last column after the last row of data and then do a print preview, it fits into one page! 

I tried attaching the export output, but it wasn't allowed.  

[Jeff]

Well, an hour later I came up with a fix that I can share.  Each report's background color had been set to white.  When I cleared that setting the exported report now fits onto one page in print preview, whew.