使用ReportViewer内置功能导出到Excel [英] Export to Excel using ReportViewer built-in feature

问题描述

我想知道是否可以将Excel输出设置为锁定，因为当我们尝试更改单元格的值时，会出现一个警告，表示我们无法更改它，除非我们删除了Sheet的保护。

I'd like to know if it is possible to set the Excel output as "Locked", in the sense that when we try to change a Cell's value, then there will be a warning indicating that we can not change it unless we remove the Sheet's protection.

我知道我们可以开发一个自定义的Excel自动化代码，并在保存之前设置一个密码来保护工作表。但是，使用ReportViewer的内置功能有没有办法完成这个任务？

I know that we can develop a custom Excel automation code, and set a Password to protect the sheet just before we save it. But, is there any easy way to accomplish this using ReportViewer's built-in feature?

推荐答案

在做了一些研究之后，设法找到解决方案:)
想法是拦截ReportViewer的Export Report功能，然后运行我们自己的进程。此过程将获得正在生成的Excel文件的输出，然后读取它并应用任何必要的更改，并在作为下载到用户发送之前再次保存。
然而，应该注意的是，截取方法将根据我们使用的报告类型而有所不同。在我的情况下，我的ReportViewer正在使用WebForm而不是WinForm，大多数解释都说明了仅在WinForm中可用的ReportExport事件。

After doing some research, I've managed to find the solution :) The idea is to intercept the Export Report function of ReportViewer, and then run our own process. This process will get output which is the Excel file being generated, and then read it and apply whatever changes necessary, and save it again before it is sent as a Download to User. It should be noted however, that the interception method will be different based on what type of Reporting that we use. In my case, my ReportViewer is using WebForm instead of WinForm, and most explanation out there is explaining about ReportExport event which is only available in WinForm.

对于使用WinForm的用户，你可以这样覆盖ReportExport事件：

For those using WinForm, you can override the ReportExport event like this :

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

在WebForm中，没有ReportExport的事件处理程序。我可以想到的选项是在.aspx中创建一个自定义按钮，它将执行我们的自定义代码，或者直接呈现excel，而不需要预览报表。
我决定直接呈现excel文件。我将使用数据集并从存储过程获取数据。然后，我将数据集分配到RDLC中，并调用Render方法来获取输出。输出格式是byte []，我用FileStream来写。完成后，我使用Interop打开Excel文件并应用保护。这是代码：

In WebForm, there is no event handler of ReportExport. The options that I can think of are creating a custom button in .aspx that will execute our custom code, or directly render the excel without needing to preview the report. I decided to render the excel file directly. I will use a dataset and get the data from Stored Procedure. Then, I assign the dataset into RDLC and call the Render method to get the output. The output format is in byte[], and I use FileStream to write it. After it is done, I open the Excel file using 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);

}

通过使用这个概念，我可以轻松设计报表输出因为我使用RDLC作为模板来填充SP提供的数据，然后渲染它。如果我们使用Excel（设置边框，合并单元格，分组）手动编写报表，想像一下麻烦。

By using this concept, I can easily design the report output since I'm using RDLC as a template to populate the data supplied by SP, and then render it. Imagine the hassle if we manually code the report using Excel (setting borders, merge cells, groupings).

这篇关于使用ReportViewer内置功能导出到Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！