Merge multiple byte arrays to excel with multiple sheets

Question

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

Leon D · Answer

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 for ( int j = 0; 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

Nitin Sontakke · Answer

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?