SSRS Report Execution Service: export to xlsx without error

Question

Hello thanks for looking.

I have some .net code that uses the SSRS Report Execution Service to download a report for the user as an xlsx file.

It works & everything in the report is present & accounted for.

One annoying thing though. When opened in Excel 365 it gives a popup saying:

We found a problem with some content in "theReport.xlsx" Do you want us to try to recover as much as we can? If you trust the source of this workbook click yes

When I click yes then it indicates that the workbook was repaired and the report looks normal.

It does not give any indication what it repaired in the log file, just that it was repaired.

Please take a look at my code. Perhaps there is something small I can change to get rid of the error when the Excel sheet is opened.

 private void DownloadQuoteExport()
        {
            

            string reportName = "reportName";
            string fileName = "filename";

            

            //create web services instance
            ReportExecutionService rs = getService();

            //render report 1st parameter
            ParameterValue param1 = new ParameterValue();
            param1.Name = "QuoteID";
            param1.Value = quoteId.ToString();

            try
            {
                
                executeReport(reportName, new ParameterValue[] { param1 }, "EXCELOPENXML", fileName, rs);

            }
            catch (Exception ex)
            {
                Response.Write(ex.Message + "<br>" + ex.StackTrace.ToString());
            }
        }

 private void executeReport(String reportName, ParameterValue[] rptParams, String rptFormat, string strRptFileName, ReportExecutionService service)
        {
            string encoding;
            string mimeType;
            string extension;
            Warning[] warnings = null;
            string[] streamIDs = null;
            string historyID = null;

            ExecutionInfo execInfo = new ExecutionInfo();
            ExecutionHeader execHeader = new ExecutionHeader();

            service.ExecutionHeaderValue = execHeader;
            execInfo = service.LoadReport(reportName, historyID);

            service.SetExecutionParameters(rptParams, "en-us");
            String SessionId = service.ExecutionHeaderValue.ExecutionID;
            byte[] result = service.Render(rptFormat, null, out extension, out encoding, out mimeType, out warnings, out streamIDs);

            Response.ClearContent();

          
                if (rptFormat == "EXCELOPENXML")
                {
               
                Response.AppendHeader("content-disposition", "attachment; filename=" + strRptFileName + ".xlsx");
                
                Response.ContentType = "application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            }

            Response.BinaryWrite(result);
            Response.Flush();

        }

Answer 1

This is finally what worked.

private void executeReport(String reportName, ParameterValue[] rptParams, String rptFormat, string strRptFileName, ReportExecutionService service)
        {
            string encoding;
            string mimeType;
            string extension;
            Warning[] warnings = null;
            string[] streamIDs = null;
            string historyID = null;

            ExecutionInfo execInfo = new ExecutionInfo();
            ExecutionHeader execHeader = new ExecutionHeader();

            service.ExecutionHeaderValue = execHeader;
            execInfo = service.LoadReport(reportName, historyID);

            service.SetExecutionParameters(rptParams, "en-us");
            String SessionId = service.ExecutionHeaderValue.ExecutionID;
           
            byte[] result = service.Render(rptFormat, null, out extension, out encoding, out mimeType, out warnings, out streamIDs);

            Response.ClearContent();
            Response.Clear();
            
             
                if (rptFormat == "EXCELOPENXML")
                {
                 Response.AppendHeader("content-disposition", "attachment; filename=" + strRptFileName + ".xlsx");

                 Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
               
            }

            Response.BinaryWrite(result);
            Response.Flush();
            Response.SuppressContent = true;
            try
            { 
                HttpContext.Current.ApplicationInstance.CompleteRequest();
            }
            catch (ThreadAbortException ex)
            {
                //ignore
            }

        }