How to split multiple sheets containing Spreadsheet into individual Excel files with each sheet in Angular Spreadsheet?

This knowledge base article explains you how to split multiple sheets containing Spreadsheet into individual Excel files with each sheet in Angular Spreadsheet’s.

We can convert the entire workbook to JSON data by using the saveAsJson method. Then, using the fetch method, send the converted JSON data to the server. On the server side, we can convert the received JSON data into a file stream and divide the file stream into distinct excel files using the AddCopy method of the XLSIO library.

[app.component.html]

<div class="control-section">
   <button id="customBtn" class="e-btn" (click)=" splitSheets()">
     Split Excel
   </button>
   <ejs-spreadsheet #default (created)="created()">
       <e-sheets>
           <e-sheet name="Car Sales Report">
               <e-ranges>
                   <e-range [dataSource]="data"></e-range>
               </e-ranges>
               <e-columns>
                   <e-column [width]="180"></e-column>
                   <e-column [width]="130"></e-column>
                   <e-column [width]="130"></e-column>
                   <e-column [width]="180"></e-column>
                   <e-column [width]="130"></e-column>
                   <e-column [width]="120"></e-column>
               </e-columns>
           </e-sheet>
           <e-sheet name="Car Sales">
               <e-ranges>
                   <e-range [dataSource]="grossPay"></e-range>
               </e-ranges>
               <e-columns>
                   <e-column [width]="120"></e-column>
                   <e-column [width]="130"></e-column>
                   <e-column [width]="130"></e-column>
                   <e-column [width]="120"></e-column>
                   <e-column [width]="130"></e-column>
                   <e-column [width]="120"></e-column>
               </e-columns>
           </e-sheet>
       </e-sheets>
   </ejs-spreadsheet>
</div>

[app.component.ts]

import { SpreadsheetAllModule } from '@syncfusion/ej2-angular-spreadsheet';
import { Component, ViewEncapsulation, ViewChild } from '@angular/core';
import { getDefaultData, grossPay } from './data';
import { SpreadsheetComponent } from '@syncfusion/ej2-angular-spreadsheet';

@Component({
 standalone: true,
 imports: [SpreadsheetAllModule],
 selector: 'app-root',
 templateUrl: 'app.component.html',
 styleUrls: ['app.component.css'],
 encapsulation: ViewEncapsulation.None
})

export class AppComponent {
 @ViewChild('default')
 public spreadsheetObj: SpreadsheetComponent;
 public data: Object[] = getDefaultData();
 public grossPay: Object[] = grossPay();
 created() {
   this.spreadsheetObj.cellFormat({ fontWeight: 'bold', textAlign: 'center', verticalAlign: 'middle' }, 'A1:F1');
 }
 
 // Button click functionality to split the sheets into individual excel.
 splitSheets() {
   this.spreadsheetObj.saveAsJson().then((Json) =>
   // You should add the launched local service path.
     fetch('https://localhost:44354/api/spreadsheet/SaveExcel', {
       method: 'POST', // or 'PUT'
       headers: {
         'Content-Type': 'application/json',
       },
       body: JSON.stringify({
         FileName: 'SavedSampleFile',
         JSONData: JSON.stringify((Json as any).jsonObject.Workbook),
         PDFLayoutSettings: JSON.stringify({ FitSheetOnOnePage: false }),
       }),
     }).then((basePath) => {
       alert("File has been saved successfully.");
     })
   );
 }
}

[Controller.cs]

using System;
using System.IO;
using Microsoft.AspNetCore.Mvc;
using Syncfusion.EJ2.Spreadsheet;
using Syncfusion.XlsIO;

namespace WebAPI.Controllers
{
   [Route("api/[controller]")]
   [ApiController]
   public class SpreadsheetController : ControllerBase
   {

       [HttpPost]
       [Route("SaveExcel")]
       [RequestSizeLimit(int.MaxValue)]
       public string Save([FromBody] SaveSettings saveSettings)
       {
           ExcelEngine excelEngine = new ExcelEngine();
           IApplication application = excelEngine.Excel;
           try
           {
               // Convert Spreadsheet data as Stream 
               string basePath = Path.GetFullPath("Files//" + saveSettings.FileName + ".xlsx");
               saveSettings.FileContentType = ContentType.Xlsx;
               saveSettings.VersionType = VersionType.Xlsx;
               Stream fileStream = Workbook.Save<Stream>(saveSettings);
               IWorkbook splitWorkbook = application.Workbooks.Open(fileStream);
               // Iterate between the workbook to save the sheets individually as excel into a folder.
               foreach (IWorksheet worksheet in splitWorkbook.Worksheets)
               {
                   IWorkbook newWorkbook = application.Workbooks.Create(0);
                   newWorkbook.Worksheets.AddCopy(worksheet);
                   FileStream newFileStream = new FileStream("Files//" + worksheet.Name + ".xlsx", FileMode.Create);
                   newWorkbook.SaveAs(newFileStream);
                   newFileStream.Close();
                   newWorkbook.Close();
               }
               return ("File Saved");
           }
           catch (Exception ex)
           {
               return ("File not saved - Error");
           }
       }
   }

   public class FileNames
   {
       public string FileName { get; set; }
   }
}

Sample Link:

https://stackblitz.com/edit/angular-ovvze7-h69veq?file=src%2Fapp.component.ts

Service Link:

https://www.syncfusion.com/downloads/support/directtrac/general/ze/Split_Excel_Controller1462620279

Note: Need to launch the service before running the sample.

Output:

For further information about this, see the documentation that follows.

https://ej2.syncfusion.com/angular/documentation/spreadsheet/open-save

https://help.syncfusion.com/file-formats/xlsio/working-with-excel-worksheet#move-or-copy-a-worksheet

https://support.syncfusion.com/kb/article/11650/how-to-remove-trial-version-tab-created-in-spreadsheet
Conclusion
I hope you enjoyed learning about how to split multiple sheets containing Spreadsheet into individual Excel files with each sheet in Angular Spreadsheet.

You can refer to our Angular Spreadsheet’s feature tour page to know about its other groundbreaking feature representations and documentations. You can also explore our Angular Spreadsheet example to understand how to present and manipulate data.

For current customers, you can check out our Angular components from the License and Downloads page. If you are new to Syncfusion, you can try our 30-day free trial to check out our Angular Spreadsheet and other Angular components.

If you have any queries or require clarifications, please let us know in comments below. You can also contact us through our support forums, Direct-Trac, or feedback portal. We are always happy to assist you!