Export to Excel using the built-in ReportViewer function

Question

I would like to know whether it is possible to set the output of Excel as "locked", in the sense that when I try to change the value of a cell, a warning appears that we cannot change it if we remove the sheet protection.

I know that we can develop our own Excel automation code and set a password to protect the sheet immediately before saving it. But is there an easy way to do this with the built-in ReportViewer function?

Answer 1

After some research, I managed to find a solution :) The idea is to intercept the ReportViewer "Export Report" function and then start your own process. This process will get the result that the Excel file generates, and then read it and apply all the necessary changes and save it before it is sent as a Download to User. However, it should be noted that the interception method will differ depending on what type of reporting we use. In my case, my ReportViewer uses WebForm instead of WinForm, and most of the explanations explain the ReportExport event, available only in WinForm.

For those using WinForm, you can override the ReportExport event as follows:

void reportViewer_ReportExport(object sender, Microsoft.Reporting.WinForms.ReportExportEventArgs e) { e.Cancel = true; // insert your own code to export excel } 

There is no ReportExport event handler in WebForm. The options I can think of create a custom button in .aspx that will execute our custom code or directly display Excel without having to preview the report. I decided to transfer the excel file directly. I will use the data set and get the data from the stored procedure. Then I assign the dataset to the RDLC and call the Render method to get the result. The output format is in byte [], and I use FileStream to write it. After that I open the Excel file with Interop and apply protection. Here is the code:

 // Setup DataSet (Adapter and Table) YourTableAdapters.ATableAdapter ds = new YourTableAdapters.ATableAdapter(); YourDataSet.ADataTable dt = new YourDataSet.ADataTable (); ds.Fill(dt, outlet, period); // Create Report DataSource ReportDataSource rds = new ReportDataSource("DataSet1", (System.Data.DataTable)dt); // Variables needed for ReportViewer Render method Warning[] warnings; string[] streamIds; string mimeType = string.Empty; string encoding = string.Empty; string extension = string.Empty; // Setup the report viewer object and get the array of bytes ReportViewer viewer = new ReportViewer(); viewer.ProcessingMode = ProcessingMode.Local; viewer.LocalReport.ReportPath = "YourReport.rdlc"; viewer.LocalReport.DataSources.Add(rds); // Add datasource here byte[] bytes = viewer.LocalReport.Render("Excel", null, out mimeType, out encoding, out extension, out streamIds, out warnings); // Prepare filename and save_path, and then write the Excel using FileStream. String temp_path = Path.Combine(Server.MapPath(Config.ReportPath), "FileName.xls"); FileStream fs = new FileStream(temp_path, FileMode.Create); fs.Write(bytes, 0, bytes.Length); fs.Close(); // Open the Excel file created, and add password protection. PIDExcel pidexcel = new PIDExcel(); pidexcel.CollectExcelPID(); Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); Microsoft.Office.Interop.Excel.Range lock_range = null; int excelid = pidexcel.GetNewExcelID(); Microsoft.Office.Interop.Excel.Workbook xlWorkBook = null; try { //xlApp.Visible = true; xlWorkBook = (Microsoft.Office.Interop.Excel.Workbook)xlApp.Workbooks.Open(temp_path, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing); foreach(Microsoft.Office.Interop.Excel.Worksheet displayWorksheet in xlApp.ActiveWorkbook.Worksheets) { lock_range = xlApp.Cells; lock_range.Select(); lock_range.EntireColumn.Locked = true; displayWorksheet.Protect("<your password here>"); } } catch (Exception ex) { throw new Exception(ex.Message.Replace("'", "")); ; } finally { // Set First Sheet Active xlWorkBook.Sheets[1].Select(); xlApp.DisplayAlerts = false; xlWorkBook.Save(); xlWorkBook.Close(Type.Missing, Type.Missing, Type.Missing); xlApp.Quit(); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlWorkBook); System.Runtime.InteropServices.Marshal.FinalReleaseComObject(xlApp); GC.WaitForPendingFinalizers(); GC.Collect(); pidexcel.KillExcel(excelid); } 

Using this concept, I can easily generate report output, since I use RDLC as a template to populate the data provided by the SP, and then visualize it. Imagine the problem if we manually copy the report using Excel (set boundaries, change cells, groupings).