Why does SSRS export a corrupted XLS file?

Question

I have a SSRS report in SQL Server 2012 which exports to Word and PDF which open in respective readers. However when i open the XLS file in MS-Excel i get the message asking if it should repair the corrupted file. If i click yes, it shows an empty file with the message as below. I am using SQL Server 2012. This happens for data of one particular day only.

Replaced Part: /xl/worksheets/sheet1.xml part with XML error.  Illegal xml character. Line 14, column 6935.

I guess i have to study this post http://christianspecht.de/2014/01/14/excel-found-unreadable-content-when-exporting-a-reporting-services-report/

Swabber

Are you able to view the report for this particular day in SSRS or in the exported PDF? – Maverick

Correct, you've got characters outside of the expected range and your options are to scrub those values or look at patching your servers. Too lazy to check, but one of the Cummulative Updates (CU) or Service Packs (SP) addressed this. – Reredos

@Maverick Yes able to view in PDF, WOrd, SSRS. I ran the code as in the URL above and it appears to work now. – Swabber

@Reredos This one i guess 1920886 2922935 (support.microsoft.com/kb/2922935 ) FIX: Excel rendering extension produces corrupted files when you use nonprintable codes in SSRS 2012 – Swabber

Answer 1

I had the same issue and in my case it was problem with data precision. Apparently SSRS is not able to process 18 decimal places and my procedure returned this data for some of the fields. That what probably happens in your case. The data for a specific day just end up with a value that has too many decimal places to process by the SSRS.

One option is to change data type in the table to support lower precision. Other solution is to convert the numbers in the stored procedure that populates the report.

SELECT CONVERT(NUMERIC(38,8),MyNumber) AS MyNumber_v2

You can read more about this bug here.

Tusker

Answer 2

I had similar problem with percentage type field. When value 0% appeared in the data output the report export to Excel generated broken file. The auto-repaired Excel file contained 0.00000000000 values instead of 0.00% as was expected. The initial field data type in DB was DECIMAL(24,14). I added CONVERT to DECIMAL(10,7) clause into query and the problem was fixed.

Earache

Answer 3

I had a similar issue while exporting to excel (xlsx format) from SSRS 2012 with Oracle database. But the error message was "We found a problem with some content in XYZ.xlsx, Do you want us to try to recover as much as we can? If you trust the source of this workbook click Yes". The issue was because of non-printable character in one of the fields. The issue was resolved by changing the SQL query to use regular expression as "select column1, REGEXP_REPLACE(column2, '[^[:print:]]', '') as column2 from table". Basically, the regular expression removes any non-printable characters from column2.

Cornela

Sometimes issue could be in number formatting, sometimes in strange characters, and sometimes because add hyperlink on textbox of ssrs report, you need to know where exactly the cell that cause the problem – Council

Answer 4

I was encountered with same situation while exporting in excel format. In my case by closely looking report output i found that there was non-ASCII character in output. I removed non-ASCII character and after that excel export was working fine. I removed non-ASCII character from containing column by using following function.

CREATE FUNCTION fnc_RemoveNonASCII 
(
@nstring nvarchar(255)
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @Result varchar(255)
SET @Result = ''

DECLARE @nchar nvarchar(1)
DECLARE @position int

SET @position = 1
WHILE @position <= LEN(@nstring)
BEGIN
    SET @nchar = SUBSTRING(@nstring, @position, 1)
    --Unicode & ASCII are the same from 1 to 255.
    --Only Unicode goes beyond 255
    --0 to 31 are non-printable characters
    IF UNICODE(@nchar) between 32 and 255
        SET @Result = @Result + @nchar
    SET @position = @position + 1
END
RETURN @Result
END
GO

Tricostate

Answer 5

I also encountered the same issue mine was caused by an embedded image. BTW I know that this response is very late, however it may be some use for others going through similar problems with SSRS reports. Specifically exporting a report where it produces a corrupt Excel .xls or .xlsx file. The cause of my particular problem was just a little .JPEG image back button, but equally it could be something like a project logo image, something that typically appears on many reports. After much hunting down many blind alleys (from my perspective) as there are multiple reasons for the corruptions. My error message mentioned a problem with drawing.xml, which pointed me in the direction of my embedded image. Equally having LF, CR or other non ASCII characters in the data can cause also exports to Excel to break.

If you put this snippet in the "Visibility"- "Hidden" section "=(Globals!RenderFormat.IsInteractive = False)" it will still display the image when you run the report, but it stop the export corruption.

Edgewise

Answer 6

Check out cells(or column header) fonts on your report design (in report builder). Maybe one of them does not exists on your machine!

Quadrille

See here : social.msdn.microsoft.com/Forums/sqlserver/en-US/… – Elliotelliott

Answer 7

I had the same issue and I found the root cause. My subscription was rewriting over the same file each day this cause the issue. Removing the file BEFORE generating it has solved the issue. Hope it helps

Regent

Answer 8

I updated my decimals from 18,0 to 10,0 but that was not the issue. I found there was division by 0 for some fields. Once I resolved those by using an IIf(varx = 0, 0, ), I was able to get rid of this error.

Meara