Export to Excel without Merging Columns

  • marty.seed

    SSCrazy

    Points: 2407

    We have many reports that our users export to excel and perform additional filters, often times when the report is exported columns that seem to be perfectly lined up in SSRS are gaining a merged blank column in Excel. I can't for the life of me figure out why this happens. I have lined everything up on the grid lines in SSRS.

    Any help or advice is appreciated

  • Jaselnewpar

    SSC Eights!

    Points: 949

    I had this problem a while back and the answer was to remove the header from the report. I was going to link the source I got the information from but it is no longer live so I'll do my best with how to go about removing the headers:

    You'll have to edit your rsreportserver.config. You can find it under c:\progra~1\Microsoft SQL Server\MSRVersioin_Instance\Reporting Service\ReportServer\rsreportserver.config

    Back this file up BEFORE YOU MAKE CHANGES!!!!!!!!!

    Open it with a text editor or even visual studio.

    Look for <Render>

    This will list all the options for exporting your reports.

    Add the following code:

    <Extension Name="EXCEL (No Header)" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering">

    <OverrideNames>

    <Name Language="en-AU">Excel (No Header)</Name>

    </OverrideNames>

    <Configuration>

    <DeviceInfo>

    <SimplePageHeaders>True</SimplePageHeaders>

    </DeviceInfo>

    </Configuration>

    </Extension>

    Save your change. Restarting your browser should enable the change, worst case you'll have to restart the ReportServer service.

    Hope this helps.

  • jshahan

    SSCarpal Tunnel

    Points: 4622

    You could also try a comma delimited export and import the results to excel.

  • Jason Wolfkill

    SSCrazy Eights

    Points: 9772

    marty.seed (9/27/2011)


    We have many reports that our users export to excel and perform additional filters, often times when the report is exported columns that seem to be perfectly lined up in SSRS are gaining a merged blank column in Excel. I can't for the life of me figure out why this happens. I have lined everything up on the grid lines in SSRS.

    Any help or advice is appreciated

    I experienced this problem in SSRS 2005. One of my coworkers discovered that the text box containing the report name on the reports created in BIDS by the wizard caused the problem. If the table was wider than the text box, the Excel output included a merged blank column in the column corresponding to the table column immediately below the right side of the text box. We were having to hit "Unmerge cells" in Excel, then find and delete the column - what a pain. My coworker solved the problem when she discovered that making the text box just a hair wider than the table avoided the problem in the first place.

    Jason Wolfkill

  • msbi.afaik

    SSC Enthusiast

    Points: 109

    Hi,

    There was a similar issue to show the full text box data in the excel, but somehow ssrs excel export doesnt show it properly.

    Only by hitting the F2 function button on the specific cell in excel shows me the full data.

    Is there any solution for the problem?

    Regards,
    KarthikShanth.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply