Excel file with multiple sheets from SSRS Reports

Question

Hi,

Let's say i have 3 byte arrays, each representin a .xls file. How can i combine them into a single xls file with 3 sheets. The SSRS reports are very rich and include charts sp oledb is not an option.

Performance is not important so i could save them to disk if needed, as a last resort i could even use an excel macro (if i knew how to do that). I tried to use microsodt.office.interop.excel but i could only manage to add a new sheet to a file, i couldn't add an existing sheet.

Any help would be appreciated.

Answer 1

It seems to me that you just need a way to programatically write Byte arrays to a WorkBook.
Here is a method that will write a byte[] as a sheet to a specific WorkBook:

public static void WriteToSheet(string targetBookPath, byte[] fileBytes)
{
    try {
        
        object x = Type.Missing;
        
        //Create a temp file to encapsulate Byte array
        string tmpPath = IO.Path.ChangeExtension(IO.Path.GetTempFileName(), ".xls");
        My.Computer.FileSystem.WriteAllBytes(tmpPath, fileBytes, false);
        
        //Start Excel Application (COM)
        Excel.Application xlApp = new Excel.Application();
        
        //Open target book
        Excel.Workbook targetBook = xlApp.Workbooks.Open(targetBookPath, x, x, x, x, x, x, x, x, x, 
        x, x, x, x, x);
        
        //Open temp file with Excel Interop
        Excel.Workbook sourceBook = xlApp.Workbooks.Open(tmpPath, x, x, x, x, x, x, x, x, x, 
        x, x, x, x, x);
        
        //Get a reference to the desired sheet 
        Excel.Worksheet sourceSheet = (Excel.Worksheet)sourceBook.Worksheets(1);
        
        //Copy the temp sheet into WorkBook specified as "Before" parameter
        Excel.Worksheet targetBefore = (Excel.Worksheet)targetBook.Worksheets(1);
        try {
            sourceSheet.Copy(targetBefore, x);
            
            //Save and Close
            sourceBook.Close(false, x, x);
            targetBook.Close(true, x, x);
            xlApp.Workbooks.Close();
                
            xlApp.Quit();
        }
        catch (Exception ex) {
            Debug.Fail(ex.ToString);
        }
        finally {
            
            //Release COM objects
            //   Source
            DESTROY(sourceSheet);
            DESTROY(sourceBook);
            
            //   Target
            DESTROY(targetBefore);
            DESTROY(targetBook);
            
            //   App
                
            DESTROY(xlApp);
        }
        
        //Kill the temp file
            
        My.Computer.FileSystem.DeleteFile(tmpPath);
    }
    catch (Exception ex) {
        Debug.Fail(ex.ToString);
    }
}

The DESTROY method releases the COM stuff, which is pretty important:

public static void DESTROY(object o)
{
    try {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(o);
    }
    catch (Exception ex) {
        Debug.Fail(ex.ToString);
        ErrorLog.Write(ex.ToString);
    }
    finally {
        o = null;
    }
}

If I understand correctly, all you would need to do is:

1. Create a new WorkBook
2. Loop through Byte arrays
3. Call WriteToSheet for each Byte array

Answer 2

SpreadsheetGear for .NET can do it:

• Load source workbooks from the byte arrays with SpreadsheetGear.Factory.GetWorkbookSet().Workbooks.OpenFromMemory(byte[]). Depending on the source of the byte arrays, you might prefer to load directly from a stream with GetWorkbookSet().Workbooks.OpenFromStream(System.IO.Stream stream).
• Create a new destination workbook with Factory.GetWorkbook() or open a template destination workbook with Factory.GetWorkbook(string filename).
• Copy worksheets from each of the source workbooks with SpreadsheetGear.ISheet.CopyAfter or ISheet.CopyBefore.
• Save the destination workbook with IWorkbook.SaveAs(string filename, FileFormat fileFormat), IWorkbook.SaveToMemory(FileFormat fileFormat) or IWorkbook.SaveToStream(System.IO.Stream stream, FileFormat fileFormat).

You can see a number of live ASP.NET samples here and download the free trial here.

There is a "Worksheet with Chart to Multiple Worksheets with Charts" sample which might be somewhat useful on our Excel Reporting Samples page here.

Disclaimer: I own SpreadsheetGear LLC

Answer 3

Do the sheets have to be generated as separate reports and then combined? SoftArtisans OfficeWriter can programmatically combine multiple spreadsheets from byte arrays, but we also have SSRS integration that would allow you to create multi-sheet reports directly. Using the OfficeWriter Designer Excel Add-In you could design a report with 3 worksheets right in Excel and publish it to SSRS.

Disclaimer: I work for SoftArtisans