使用C＃ReportViewer的FileStream创建新的Excel工作表

Question

我有此代码渲染reportViewer：

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文件中以报告名称为工作表名称。看起来其余的都在那里（文件大小增加），但只是未正确格式。

如何使每个报告进入其他工作表？

注意：

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

我试图避免使用额外的库来完成此操作，这是一个非常轻巧的程序的一部分。

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

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:nnu2022 The file name or path does not exist.nu2022 The file is being used by another program.");
}
finally
{
    if (fs != null)
    {
        fs.Close();
    }
    if (app != null)
    {
        app.Workbooks.Close();
    }
}

欢迎任何输入/反馈。