Merge multiple byte arrays to excel with multiple sheets

Anjali K

Hi All

I have a collection of byte arrays - each byte array has been returned from the MS reporting services web service Render() method (rendered in Excel format)

I want to merge these byte arrays into a single byte array which when downloaded has multiple sheets. (each byte array corresponds to a sheet in the result excel)

I want to achieve this using openxml. Could anyone please help.

Thank you.

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Leon D5y ago
You can use Free Spire.XLS library to import arrays to specified location of specific excel worksheet.

First install Free Spire.XLS Via NuGet Package Manager.

Second refer the following code to import array to excel worksheet.
using Spire.Xls;
namespace ImportArrayToExcel
{
    class Program
    {
        private void SetArrayValueExample()
        {
            //Create an Excel file
            Workbook workbook = new Workbook();
            workbook.CreateEmptySheets(1);
            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Create an array
            int maxRow = 10000;
            int maxCol = 200;
            object[,] myarray = new object[maxRow + 1, maxCol + 1];
            bool[,] isred = new bool[maxRow + 1, maxCol + 1];
            for (int i = 0; i <= maxRow; i++)
                for (int j = 0; j <= maxCol; j++)
                {
                    myarray[i, j] = i + j;
                    if ((int)myarray[i, j] > 8)
                        isred[i, j] = true;
                }

            //Insert the array to the worksheet, starting from the cell[1,1]
            sheet.InsertArray(myarray, 1, 1);

            //Save the result file
            workbook.SaveToFile("test.xls",ExcelVersion.Version97to2003);
        }
    }
}
For more information, visit this documentation:
https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Set-an-Array-of-Values-into-a-Range-in-Excel-Worksheet.html
+1
Nitin Sontakke5y ago
Humm..... sounds complicated.

To begin with I will be hugely surprized if a byte array returned by a render method would just be a single work-sheet. That isn't much likely. Have you saved it to the disk and confirmed? I believe that it would be a full MS-Excel file with one work-sheet in it.

And if such is the case...

You are better of writing these each byte arrays in an independent temp Excel files. Then programatically create an empty final / merged Excel file and then iterate through each of temp file and copy their single work-sheets in a final Excel file.

Later delete all temp Excel files and return byte array of final Excel file.

Simple, isn't it?
+1