Merged Columns on Export to Excel

[Ctrax]

The attached screenshots show a Telerik report (Q3 2011) containing a table with several columns.  When I run the report and export it to Excel, the Category column ends up in merged columns L and M in Excel.  As you can see in the screenshots, the report contains no objects above or below the table in the area where the merged cells are showing up.  The report is set to Landscape orientation, and the merged cells are showing up between the 8-9 inch mark on the page.  I have also confirmed that there are no objects hiding behind the table that could be causing the issue.  I have replicated this same issue on other reports containing tables as well, but also have similar reports with table where the issue doesn't occur at all.  So I have been unable to pinpoint a pattern in any way.  I have also tried resizing the columns in various ways, but always end up with merged cells in that same area, sometime occurring for the MakeModel field instead of Category.

Any idea what might be causing these merged cells to show up when exporting this report to Excel?

[Stef]

Hi Keith,

The Excel rendering provides a logical page layout as close as possible to the original report's layout. Reports are exported in Excel based on the Design Considerations for Excel Rendering article. Please go through the article and check the Merging Cells paragraph and test the suggested settings.


I hope the above information is helpful.

Regards,
Stef
Telerik

 

 

[Ctrax]

Hi Stef,

Thanks you for your response.  I had already gone through this article and did check the details mentioned in the Merging Cells paragraph, which then led me to posting to this forum for additional advice.  Since these are table columns, there is no alignment causing the issue.  As the article advised, I played around with the column widths in the table, which in Visual Studio gets set in inches.  If I manually set the column width to 70pt in the properties window, it automatically translates it to 0.97in, as shown in this attached screenshot.  This screenshot also shows the resulting Excel file from this example, which ends up with merged cells K and L for the Category column.

Do you have any other advice as to what may be causing this merge?

Thanks,
Keith

[Stef]

Hi Keith,

The taken approach is the correct one to avoid the merged columns in Excel. To adjust columns by using pixels (more media oriented unit measure), you can change the report's UnitOfMeasure property.
Already existing(added) items will keep their originally set values for Size and you will have to manually change them.

Regards,
Stef
Telerik

 

 

[ANDREI]

  Hello. I have the same issue with cell's merge. This issue happens just in case when I have an image in the cell. I went through the "Merging Cells" paragraph of the "Design Considerations for Excel Rendering" article  and tested the suggested settings.   I've noticed that if I have an image in the report ​the separate column is created for this image after rendering. An​d the "Allignment"  setting for this column is set to "Merge & Center".(see Merged Cells.png attach) This setting doesn't allow to sort the excel document. If I click "unmerge cells" in the all columns with images( see Unmerged Cells.png attach)  - sorting becomes possible.  If there are no any images in the report - the "Allignment"  setting for all columns isn't  set to "Merge & Center" and the sorting is possible.  

       The left and right edges of picturebox and other report items line up with one another. I also set the report's UnitOfMeasure property to px and changed size of picturebox and its parent items to px. But it didn't help. Is it possible to render unmergred cells with images?

[Stef]

Hi Andrei,

The reported problem is not reproducible with the Product Catalog.trdx file. Note that overlapping items will be moved based on internal logic of the Excel rendering mechanism (in general, overlapping items must be avoided and report designers warn for such issues during designing the report).

Please test if visualizing the content via Table item, where  the PictureBox item is in a separate cell resolves the issue.

Regards,
Stef
Telerik

[ANDREI]

Hi Stef,

I added Table item and put all my items in it. It didn't help. If a table cell contains a PictureBox item - all column in the rendered excel document will be set to "Merge & Center". And it's impossible to sort all document untill I unmerge the column with images manually. I use telerik reporting Q1 2014. Where can I look at the Product Catalog.trdx file? Could you please show me any example?

[Stef]

Hello Andrei,

After placing the items in a Table item, please test resizing them. When you align items, align also items in other sections.

Demo reports are installed by default under C:\Program Files (x86)\Telerik\Reporting <VERSION>\Examples\CSharp|VB, and TRDX files can be found under C:\Program Files (x86)\Telerik\Reporting <VERSION>\Report Designer\Examples.

Regards,
Stef
Telerik

[ANDREI]

Hello Stef,

I made the simple telerik report "ProductPhoto.trdx" in the Telerik Report Designer for example. This  report retrieves 10 rows from the "[AdventureWorks].[Production].[ProductPhoto]" table with three columns: "Product Photo ID", "Modified Date", "Large Photo"

[ANDREI]

When I export this report to excel worksheet I can't sort it by "Modified Date" or by "Product Photo ID" untill I unmerge  the "Large Photo" column using  the  "Home" tab, "Alignment" section. Could you please tell me how can I correct this report in order to make the "Large Photo" column unmerged after export? I added the .gif exstention in order to be able to send you this report.

[Stef]

Hi Andrei,

If I understand you correctly, the issue is that the column with images is merged, which prevents the sorting in this column, unless the cells are unmerged. Still the rest content is rendered correctly without producing merged columns or rows. PictureBox items are rendered as Excel Picture Objects, thus my recommendation is to sort by other column containing value.


The Excel rendering produces XLS and XLSX files:

• Microsoft Excel 2007 and above (OpenXML 2.0)
• Microsoft Excel 97-2003 (BIFF, v.8)

Depending on the format sorting of images is different and requires additional settings. Feel free to log a feature request for having images rendered in Excel in such manner they can be sorted based on the Excel version.

On report level, I can suggest you to allow users to sort data before exporting it:

1. Sort data before loading it;
2. Sort data via expressions e.g. How to add interactive sorting to a Table item?

I hope this information helps you.

Regards,
Stef
Telerik

[ANDREI]

Hi Stef
Thanks for your explanation.
I just want to add that when column with images is merged, this prevents the sorting not only in this column, but in other columns too.
I can sort just one column independently in the rendered Excel document, but I can't sort all rows by some column, unless the cells are unmerged. .
As I understand correctly it's  impossible now to render PictureBoxes with unmerged columns.

[Stef]

Hi Andrei,

The column with the PictureBox items (Picture objects in Excel) is rendered merged by the Excel rendering mechanism. The issue is logged in our system for investigation and possible improvements.

My recommendation is still to log a feature request, in order to raise the priority of the task by allowing other community members to vote for it.


Regards,
Stef
Telerik

[Himesh]

I have the same cell merge issue after export to the excel in Telerik Q3 2015.I tryed with table template. but its not working. how i can over come this issue?

[Stef]

Hello Himesh,

In case the report does not contain a column with PictureBox items, please check Design Considerations for Excel Rendering (Merging Cells).

If the issue is as the described by Andrei, it is logged in our system for improvement and you will need to manually unmerge the cells until there is a fix.


Feel free to open a support ticket and send us a demo report to check its settings.

Regards,
Stef
Telerik

[Amanda]

Stef - we're experiencing this issue on two columns in a table. We're tried resizing and even re-creating the columns. They still come out as merged in excel. Is this still an open issue?

[Todor]

Hello Amanda,

The issue described in this thread has already been fixed for version Telerik Reporting Q1 2016 (version 10.0.16.113) and above.
Check the Merging Cells article section for suggestions on avoiding cell merging in Excel export.

Regards,
Todor
Progress Telerik