Excel not opening when exporting from reports manager

Question

Excel file not opening when exporting from reports manager. I know what is the issue. but to fix the issue I need help. Please can anyone suggest me how to fix the error. Below are the details. when trying to open excel message showing is

Excel found unreadable content in 'CustomerDetails.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

I have renamed the excel to zip and read the contents in notepad++

there is percentage symbol in the line which is sowing in below with space before the symbol which is causing to open excel.

confirmation of the Address % based

any suggestions please how to sort out this?

Thanks.

Answer 1

I am going to make an assumption that the data being returned from the database is text based (text, varchar or nvarchar), and has some unusual characters in it, which can cause this.

In this case, you can use an UDF, something like what is below, to clean up the text at report run time. (tweak this to do whatever you need; remove the replace for char 10 and 13 if you want to keep carriage returns and line feeds in the output)

CREATE FUNCTION [dbo].[udf_CleanText]
(
    @dx text
)

RETURNS varchar(max)

AS

BEGIN
    DECLARE @cdx varchar(max) = @dx

    SELECT @cdx = REPLACE(@cdx, char(20), '')
    SELECT @cdx = REPLACE(@cdx, char(21), '')
    SELECT @cdx = REPLACE(@cdx, char(13), '')
    SELECT @cdx = REPLACE(@cdx, char(10), '')
    SELECT @cdx = REPLACE(@cdx, char(18), '')
    SELECT @cdx = REPLACE(@cdx, char(17), '')
    SELECT @cdx = REPLACE(@cdx, char(22), '')

    RETURN @cdx
END

To use this, add it to the query for the report.

SELECT dbo.udf_CleanText(TextField) AS TextFieldClean
FROM tblTable

If this is happening due to some calculation in the report, like an expression that returns NaN, or Infinity, then you need to change your expressions to handle that.

Adding an IIF into the mix to check for unexpected values can help here. Some examples below:

SSRS Formula or expression to change NaN to 0

NaN and Infinity values in SSRS