Disable Text Wrapping in Excel Export of Reporting Services

Well, i guess that's a pretty elaborate title, but it's what this post is about. How you text-wrapping when exporting to Excel via SQL Server Reporting services 2005 on the web be disabled.

It is unfortunate that there is no configuration option in when authoring a report to control whether the text can wrap or not.

Why disable text wrapping?

image_thumb6  

In the above screenshot we can see that when a given cell is constrained in size (i.e fixed height).
With text wrap on: the cell will not show the complete text unless it is wide enough.
With text wrap off: the complete text will display by overflowing onto the adjacent empty cells. So long as the next cells are empty.

But why would I want to do that when I could simply merge cells B + C + D together? It all comes down to usability and user expectations. If the end user expects to be able to sort the data in the columns then merged cells is just a feature that will be an obstacle in his path. In the screenshot below I try to sort by column B, but 1 of the rows contains merged cells so Excel naturally prompts the user.

image_thumb10


If you do find yourself with a reporting services report that you have spent hours creating in your report designer and you are handed the above niche requirement, there are the following options for you friend:

If a third party product is not an option for you, you may find the following helpful.

Disable Text Wrap using Microsoft.Office.Interop.Excel

Office.Interop requires that Excel is installed on the machine that will render the report. Here is the function required:

        private static void DisableTextWrap(string fileName)
        {

            Application excel = new Application();
            try
            {
                Workbook workbook = excel.Workbooks.Open(
                        fileName,
                        Type.Missing, false, Type.Missing, Type.Missing,
                        Type.Missing, true, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing);
                try
                {
                    Worksheet wsheet = workbook.Sheets[1] as Worksheet;
                    try
                    {
                        Range excelRange = wsheet.UsedRange;
                        try
                        {
                            excelRange.WrapText = false;
                        }
                        finally
                        {
                            Marshal.ReleaseComObject(excelRange);
                        }
                    }
                    finally
                    {
                        Marshal.ReleaseComObject(wsheet);
                    }
                    workbook.Save();

                }
                finally
                {
                    workbook.Close(false, fileName, null);
                    Marshal.ReleaseComObject(workbook);
                }
            }
            finally
            {
                excel.Quit();
                Marshal.ReleaseComObject(excel);
            }
        }

The above function will open an excel file and disable text-wrap in the used area of only the first spreadsheet

Enable a Web Server to run Excel through COM automation

In order to enable a web server to run Excel through COM automation you can follow this very helpful and detailed advice:

http://blog.crowe.co.nz/archive/2006/03/02/589.aspx

One point to note after following the above instructions
After running DCOMCNFG
In the Security Tab
Under Activation  
Click customise. I would recommend that the excel is launched (activated) by a user that has already used excel once on the server you are trying to perform this. So, first create a user, then go to Excel right click and use 'Run As..' option and select the newly created user. The reason for doing this is that when a user runs excel for the first time, excel creates a profile for them and does some custom installation and also pops up a dialog box which is something we would like to avoid during the COM automation. Doing this will ensure that the installation of the user profile is not trying to take place under the COM automation.  

Hope this helps someone!

Comments (2) -

7/4/2008 4:16:32 AM #

My opinion is that you should
www.gemboxsoftware.com/GBSpreadsheet.htm">avoid COM automation, especially on server.
Our GemBox.Spreadsheet Free Excel component can be used free of charge for that task, as long as XLS/CSV/XLSX files
don't have more than 150 rows and 5 worksheets.

Zeljko

7/5/2008 9:46:09 AM #

Anastasiosyal

I agree with you that excel automation should be used carefully. I would not use it on a loaded web site, but it's good enough for generating the monthly or weekly reports in the scenario it is used in.

Anastasiosyal

Comments are closed