I have multiple sheets in an excel file and I want to open each sheet in the separate spreadsheets without exporting each sheet individually

[Vaishali Jain]

Hi,

let say, I have multiple sheets in an excel file and I want to open each sheet in the separate spreadsheets. how can I do that.

Right now I have to export each sheet individually and open that in a spreadsheet but I want to open all the sheets in one go using one excel file but create individual spreadsheets for each of them.

Attaching excel containing two sheets in it. Please create two spreadsheets using one excel file without exporting each sheet individually.

Thanks,

Vaishali

Attachment: twosheetscode_89a17bc3.zip

[Sridhar Alagiri Samy]

Hi Vaishali,

We have validated your query regarding loading each sheet in a different spreadsheet inside our EJ2 Accordion item based on the code and information you provided. For your convenience, we have prepared a sample to meet your requirements, which is shared below.

In the ngOnInit method, we fetch the Excel file from our local server, process it with our Workbook Open API, and convert it into a spreadsheet Workbook JSON format.

ngOnInit() {

        this.openExcel()

    }

    openExcel() {

        // Fetching Excel file from the local server, use your localhost URL here.

        fetch('https://localhost:7016/Home/PreviewExcel', {

            method: 'POST',

            headers: {

                'Content-Type': 'application/json',

            },

            body: JSON.stringify({ FileName: '2 sheets' }),// Specify the file name to open from local server

        }).then((response) => response.json())

            .then((data) => {

                // Return the Excel file as workbook model.

                // console.log(data.Workbook);

                // Generating the multiple spreadsheet model based on the sheet count.

                this.updateSpreadsheetModel(data.Workbook);

            });

  }

The workbook JSON data contains all workbook information, including the sheets collection. We iterate through the sheet collection and generate a different spreadsheet model for each sheet.

updateSpreadsheetModel(workbook): void {

        // Here we are generating multiple spreadsheet model to render multiple spreadsheet with each sheet in the different spreadsheet.

        const spreadsheets: SpreadsheetModel[]  = workbook.sheets.map(sheet => {

          return {

            sheets: [sheet],

            definedNames: workbook.definedNames,

            filterCollection: workbook.filterCollection,

            sortCollection: workbook.sortCollection

          };

        });

        this.spreadsheets = spreadsheets;

}

Once the individual spreadsheet models for each sheet are generated and updated in the spreadsheets collection property, we render the accordion component. Inside the accordion item, we iterate through the spreadsheets collection and render each spreadsheet based on your requirements.

<div *ngIf="!!spreadsheets" class="control">

      <ejs-accordion class="p-2 bg-white mb-6" #accordion>

        <e-accordionitems>

          <e-accordionitem expanded='true'>

            <ng-template #header>

              <div class="font-bold">Cover Page</div>

            </ng-template>

            <ng-template #content>

              <div>

                <ng-container *ngFor="let spreadsheet of spreadsheets; index as i;">

                  <div class="flex justify-end space-x-4 w-full">

                    <div>{{spreadsheet.sheets[0].name}}</div>

                  </div>

                  <div class="mt-[10px]" [id]="'page' + i">

                    <ejs-spreadsheet #sheetRef (created)='createdHandler(i, sheetRef)'

                      openUrl='https://services.syncfusion.com/angular/production/api/spreadsheet/open' allowOpen='true'

                      [showSheetTabs]="false" [showFormulaBar]="false" [showRibbon]="false" [allowScrolling]="true"

                      [scrollSettings]="{ isFinite: true }" [sheets]="spreadsheet.sheets"

                      [definedNames]="spreadsheet.definedNames"></ejs-spreadsheet>

                  </div>

                </ng-container>

              </div>

            </ng-template>

          </e-accordionitem>

        </e-accordionitems>

      </ejs-accordion>

    </div>

Sample: https://stackblitz.com/edit/angular-crfbpf-mvrh1j?file=src%2Fapp.component.ts

ASP.NET Core service: Please check the attachment, Before running the client-side sample, please follow the below steps to run the application and test the sample.

1. Download and unzip the attached application.
2. Build and run the application.
   
3. Update your localhost URL in the ngOnInit fetch call.

Please review the above details and let us know if you need further assistance.

Regards,

Sridhar A.

Attachment: WebApplication1_e2af0f53.zip

[Vaishali Jain]

Hi,

Hi, I have tried the same above code with the attached local xlsx file inside asset folder instead of local server but spreadsheet is not opening, giving error 
Unexpected token 'P', "PK

In the underlined text

openExcel() {

        // Fetching Excel file from the local server, use your localhost URL here.

        fetch('https://localhost:7016/Home/PreviewExcel', {

            method: 'POST',

            headers: {

                'Content-Type': 'application/json',

            },

            body: JSON.stringify({ FileName: '2 sheets' }),// Specify the file name to open from local server

        }).then((response) => response.json())

            .then((data) => {

                // Return the Excel file as workbook model.

                // console.log(data.Workbook);

                // Generating the multiple spreadsheet model based on the sheet count.

                this.updateSpreadsheetModel(data.Workbook);

            });

  }

error - 

ERROR TypeError: Failed to parse URL from assets/fry9c.xlsx

Attachment: workbook_69a2de47.zip

[Vaishali Jain]

Hi,

I have updated my code like this  

But now styles, merging everything got removed, I mean the original excel file is different from the file loaded on the spreadsheet

  spreadsheets: any;

  ngOnInit(): void {

    this.openExcel();

  }

  openExcel() {

    const url = 'assets/fry9c.xlsx';

    fetch(url)

      .then(response => {

        if (!response.ok) {

          throw new Error(`Network response was not ok: ${response.statusText}`);

        }

        return response.arrayBuffer();

      })

      .then(arrayBuffer => {

        const workbook = this.xlsxToWorkbook(arrayBuffer);

        this.updateSpreadsheetModel(workbook);

      })

      .catch(error => {

        console.error('Error fetching or opening the Excel file:', error);

      });

  }

  xlsxToWorkbook(arrayBuffer: ArrayBuffer): any {

    const data = new Uint8Array(arrayBuffer);

    const workbook = XLSX.read(data, { type: 'array' });

    return workbook;

  }

  updateSpreadsheetModel(workbook: any): void {

    const sheets = workbook.SheetNames.map((sheetName: string) => {

      const sheet = workbook.Sheets[sheetName];

      const sheetData = XLSX.utils.sheet_to_json(sheet, { header: 1 });

      return { name: sheetName, ranges: [{ dataSource: sheetData }] };

    });

    this.spreadsheets = sheets.map((sheet: any) => ({

      sheets: [sheet],

      definedNames: [],

      filterCollection: [],

      sortCollection: []

    }));

  }

  <div *ngIf="spreadsheets && spreadsheets.length" class="control">

      <ejs-accordion class="p-2 bg-white mb-6" #accordion>

        <e-accordionitems>

          <e-accordionitem expanded="true">

            <ng-template #header>

              <div class="font-bold">Cover Page</div>

            </ng-template>

            <ng-template #content>

              <div>

                <ng-container *ngFor="let spreadsheet of spreadsheets; index as i;">

                  <div class="flex justify-end space-x-4 w-full">

                    <div>{{spreadsheet.sheets[0].name}}</div>

                  </div>

                  <div class="mt-[10px]" [id]="'page' + i">

                    <ejs-spreadsheet

                      #sheetRef

                      (created)="createdHandler(i, sheetRef)"

                      openUrl="https://services.syncfusion.com/angular/production/api/spreadsheet/open"

                      allowOpen="true"

                      [showSheetTabs]="false"

                      [showFormulaBar]="false"

                      [showRibbon]="false"

                      [allowScrolling]="true"

                      [scrollSettings]="{ isFinite: true }"

                      [sheets]="spreadsheet.sheets"

                      [definedNames]="spreadsheet.definedNames"

                    ></ejs-spreadsheet>

                  </div>

                </ng-container>

              </div>

            </ng-template>

          </e-accordionitem>

        </e-accordionitems>

      </ejs-accordion>

    </div>

Attachment: 20240613_180240_fd8c1d1d.zip

[Babu Periyasamy]

Hi Vaishali Jain,

We have reviewed your reported query based on the provided details and code snippets. Upon validating your shared code snippet, we noticed that you are using a third-party library like ‘xlsx’ to convert the file to a Workbook JSON model, which is not compatible with the workbook model supported by our Spreadsheet.

As mentioned in our previous update, you need to fetch the file from the local server, which will convert the file into the proper Workbook JSON model compatible with our Spreadsheet. Therefore, we suggest launching the previously shared WebApplication and fetching the file from there to obtain the proper Workbook JSON model supported by our Spreadsheet. And then the sheets will render with all used features properly.

For more information regarding the JSON workbook structure of Spreadsheet, please refer the below documentation link,

https://ej2.syncfusion.com/documentation/spreadsheet/how-to/create-a-object-structure

Kindly, check the above information and use the local server to fetch the file in your end and get back to us for further clarifications.

[Vaishali Jain]

thanks. its working for me

[Babu Periyasamy]

Hi Vaishali Jain,

We are happy to hear that your problem has been resolved. Kindly get back to us if you need any further assistance.