Tuesday, July 01, 2008 8:44 PM,
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?
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.
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!