使用C# ReportViewer从文件流创建新的Excel工作表

Question

我有这段代码来渲染报表查看器：

Warning[] warnings;
string[] streamids;
string mimeType;
string encoding;
string extension;

FileStream fs = new FileStream(@"c:\output.xls", FileMode.Create);

foreach (ReportViewer report in this.reports)
{    
    byte[] bytes = report.LocalReport.Render("Excel", null, out mimeType, out encoding, out extension, out streamids, out warnings);

    fs.Write(bytes, 0, bytes.Length);
}

fs.Close();

我的问题是，只有第一个报告以报告名称作为工作表名称的形式可在Excel文件中查看。其他报告似乎也存在（文件大小增加），但格式不正确。

如何使每个报告进入不同的工作表？

备注:

我尝试将设备信息字符串添加到startpage = 0，但这也没有帮助。

我正在尝试避免使用额外的库来完成此操作，因为它是一个非常轻量级的程序的一部分。

类似的未回答的问题： https://stackoverflow.com/questions/20763129/how-to-export-data-to-excel-via-reportviewer-with-multiple-named-worksheets

- RyanS

Answer 1

所以我想出了一个解决方法..

• 创建一个空工作簿的Excel对象
• 将每个报告渲染到输出文件中
• 渲染完成后，将文件加载到Excel对象中作为另一个工作簿
• 对于每个报告重复步骤2和3（覆盖Excel文件）
• 将每个工作簿复制到第一个工作簿中
• 将工作簿保存到所需文件上

有点疯狂，但它能够正常运行，并且只使用由savefiledialog指定的文件，因此非常安全可靠：

FileStream fs = null;
Microsoft.Office.Interop.Excel.Application app = null;
try
{
    app = new Microsoft.Office.Interop.Excel.Application();
    app.Workbooks.Add("");

    Warning[] warnings;
    string[] streamids;
    string mimeType;
    string encoding;
    string extension;

    // Uses a string of comma separated ints to determine which reports to print
    foreach (string indexChecked in Properties.Settings.Default.PrintAllReports.Split(','))
    {
        // Create temp file
        fs = new FileStream(saveFileDialog1.FileName, FileMode.Create);
        byte[] bytes = this.reports[Convert.ToInt32(indexChecked)].LocalReport.Render("EXCELOPENXML", null, out mimeType, out encoding, out extension, out streamids, out warnings);
        fs.Write(bytes, 0, bytes.Length);
        fs.Close();
        fs = null;
        // Add copy of workbook
        app.Workbooks.Add(saveFileDialog1.FileName);
    }

    // Process each workbook and combine
    // http://stackoverflow.com/questions/7376964/how-to-merge-two-excel-workbook-into-one-workbook-in-c
    for (int i = app.Workbooks.Count; i >= 2; i--)
    {
        int count = app.Workbooks[i].Worksheets.Count;
        app.Workbooks[i].Activate();

        for (int j = 1; j <= count; j++)
        {
            Microsoft.Office.Interop.Excel._Worksheet ws = (Microsoft.Office.Interop.Excel._Worksheet)app.Workbooks[i].Worksheets[j];
            ws.Select(Type.Missing);
            ws.Cells.Select();

            Microsoft.Office.Interop.Excel.Range sel = (Microsoft.Office.Interop.Excel.Range)app.Selection;
            sel.Copy(Type.Missing);

            Microsoft.Office.Interop.Excel._Worksheet sheet = (Microsoft.Office.Interop.Excel._Worksheet)app.Workbooks[1].Worksheets.Add(
                Type.Missing, Type.Missing, Type.Missing, Type.Missing
            );

            sheet.Paste(Type.Missing, Type.Missing);
            sheet.Name = ws.Name;
            Clipboard.Clear();
            app.Workbooks[i].Close();
        }
    }

    // Remove it if it exists, we already asked once
    if (System.IO.File.Exists(saveFileDialog1.FileName))
    {
        try
        {
            System.IO.File.Delete(saveFileDialog1.FileName);
        }
        catch (System.IO.IOException)
        {
            // It will ask and then overwrite in the next step if the delete failed
        }
    }
    app.Workbooks[1].SaveAs(saveFileDialog1.FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges);
    app.Workbooks[1].Close();
    app = null;
}
catch (IOException)
{
    // uses a wrapper method
    this.showErrorDialog("Report Generator cannot access the file '" + saveFileDialog1.FileName + "'. There are several possible reasons:\n\n\u2022 The file name or path does not exist.\n\u2022 The file is being used by another program.");
}
finally
{
    if (fs != null)
    {
        fs.Close();
    }
    if (app != null)
    {
        app.Workbooks.Close();
    }
}

欢迎提供任何意见或反馈。

- RyanS