Download Multiple Excel Files via Web API .NET and Angular

Introduction

To export multiple Excel files, we can use multiple approaches. Here's how you can achieve that.

1. Zip file download. Explain in the article
2. multiple worksheets in a single Excel file. article
3. Multiple times API calls to get multiple files.
4. Using base64string, download multiple files.

Approach 3. Multiple times API calls to get multiple files.

In modern web applications, it’s common to gather data from multiple API endpoints and combine it into a single downloadable file. This guide explains how to accomplish this using ASP.NET Core and Angular, focusing on creating an Excel file from multiple API hits and providing it for download.

Controller

[HttpGet("export-excel")]
public IActionResult ExportExcelFile()
{
    // Call the service method to generate the Excel file
     var excelFile = _fileExportService.ExportExcelFile();

    // Define the name for the downloaded file
    var fileName = "ExportFiles.xlsx";

    // Return the file to the client with the correct MIME type for Excel files
    return File(excelFile, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", fileName);
}

• excel file is the content of the file (byte array).
• "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" is the MIME type indicating that the file is an Excel spreadsheet.
• fileName is the name of the file as it will appear when downloaded.

Service. cs

public byte[] ExportExcelFile() 
{
    // Calls the GenerateExcelFile method with a list of 'infos' and returns the byte array of the generated Excel file
    return GenerateExcelFile(infos);
}

private byte[] GenerateExcelFile(List<Info> infos)
{
    // Initialize the starting row index for data entry, skipping the header row
    var currentRowIndex = 2; 

    // Set the license context for the EPPlus library to NonCommercial (required for EPPlus usage)
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

    // Create a new ExcelPackage instance which represents an Excel file
    using var package = new OfficeOpenXml.ExcelPackage();

    // Add a new worksheet to the Excel package named "sheet 1"
    var worksheet = package.Workbook.Worksheets.Add("sheet 1");

    // Set the header values in the worksheet
    worksheet.Cells["A1"].Value = "Name";          // Set cell A1 to "Name"
    worksheet.Cells["B1"].Value = "Description";   // Set cell B1 to "Description" (corrected from B2 to B1)
    worksheet.Cells["C1"].Value = "Address";       // Set cell C1 to "Address"

    // Populate the worksheet with data from the 'infos' list
    infos.ForEach(info =>
    {
        // Write data to the worksheet starting from row 2
        worksheet.Cells[currentRowIndex, 1].Value = info.Name;          // Set column A to Name
        worksheet.Cells[currentRowIndex, 2].Value = info.Description;   // Set column B to Description
        worksheet.Cells[currentRowIndex, 3].Value = info.Address;       // Set column C to Address

        // Move to the next row for the next entry
        currentRowIndex += 1;
    });

    // Convert the ExcelPackage to a byte array and return it
    return package.GetAsByteArray();
}

Angular Front-End Changes

Site.component.ts: Injects the EncryptionService into the component or service where this constructor is used. This allows you to use the methods of EncryptionService within this class.

Calls the downloadExcelSheet method of the EncryptionService twice in succession.

 
constructor(private aesService: EncryptionService) {} // Service injection
// calling multiple time to download excel sheet
downloadExcelSheet()
{
  this.aesService.downloadExcelSheet();
  this.aesService.downloadExcelSheet();
}

Encryption.service.ts

import { HttpClient } from '@angular/common/http';
import { Injectable } from '@angular/core';
import { saveAs } from 'file-saver';

@Injectable({
  providedIn: 'root'
})
export class EncryptionService {

  constructor(private http: HttpClient) {}

  downloadExcelSheet(): void {
    this.http.get('http://localhost:5003/api/export-excel', { responseType: 'blob' })
      .subscribe((blob: Blob) => {
        const fileName = 'ExportFiles.xlsx';
        saveAs(blob, fileName);
      }, error => {
        console.error('Download failed', error);
      });
  }
}

Sample Output ScreenShot

Approach 4. Using base64string, download multiple files.

not recommended. It will increase file size by 33% and is not suitable for large files.

API Controller

[HttpGet("export-multiple-excel-singletime")]
public MultipleFileDto ExportExcelFilesingletime()
{
    // Call the service to generate the Excel files and return them as Base64 strings
    var excelFiles = _fileExportService.GenerateExcelBase64();

    return excelFiles;  // Return the DTO containing file names and Base64 strings
}

Service File

public MultipleFileDto GenerateExcelBase64()
{
    // Create an instance of the DTO to store the files and their names
    MultipleFileDto multipleFileDto = new MultipleFileDto();
    
    // Generate Base64 strings for two sets of Excel files
    var File1 = GenerateExcelFileBase64(infos);
    var File2 = GenerateExcelFileBase64(files);
    
    // Populate the DTO with the Base64 files and their respective filenames
    multipleFileDto = new MultipleFileDto
    {
        File1 = File1,  // Base64 string for the first Excel file
        FileName1 = "ExportFile1.xlsx",  // Name of the first Excel file
        File2 = File2,  // Base64 string for the second Excel file
        FileName2 = "ExportFile2.xlsx"  // Name of the second Excel file
    };
    
    // Return the DTO containing the Base64-encoded files and their filenames
    return multipleFileDto;
}

GenerateExcelBase64(): This method generates two Excel files, converts them to Base64, and returns them in a MultipleFileDto. It calls the GenerateExcelFileBase64() method to generate each file.

GenerateExcelFileBase64() method

private string GenerateExcelFileBase64(List<Info> infos)
{
    // Start the row index for data
    var currentRowIndex = 2;

    // Set EPPlus license context to NonCommercial
    ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

    // Create a new Excel package (workbook)
    using var package = new OfficeOpenXml.ExcelPackage();

    // Add a worksheet to the workbook
    var worksheet = package.Workbook.Worksheets.Add("sheet 1");

    // Define headers for the Excel file
    worksheet.Cells["A1"].Value = "Name";
    worksheet.Cells["B2"].Value = "Description";
    worksheet.Cells["C1"].Value = "Address";

    // Fill the worksheet with data from the `infos` list
    infos.ForEach(info =>
    {
        worksheet.Cells[currentRowIndex, 1].Value = info.Name;
        worksheet.Cells[currentRowIndex, 2].Value = info.Description;
        worksheet.Cells[currentRowIndex, 3].Value = info.Address;
        currentRowIndex += 1;
    });

    // Get the Excel file as a byte array
    var fileBytes = package.GetAsByteArray();

    // Convert the byte array to a Base64 string
    var base64String = Convert.ToBase64String(fileBytes);

    // Return the Base64-encoded string of the Excel file
    return base64String;
}

GenerateExcelFileBase64(): This method generates an Excel file using the EPPlus library, populates it with data from the infos list, and then converts it into a Base64 string. The string is returned so it can be sent to the front end as part of the DTO.

DTO's

 public class MultipleFileDto 
 {
 public string File1 { get; set; }
     public string File2 { get; set; }
     public string FileName1 { get;set; }
     public string FileName2 { get; set; }
 }

Angular Front End Changes: In this Angular implementation, the process involves retrieving multiple Base64-encoded Excel files from the backend and then converting them into binary files for download.

Angular Front End Code Breakdown: downloadMultipleExcelSheet().

downloadMultipleExcelSheet(): void {
    this.http.get('http://localhost:5003/api/FileExport/export-multiple-excel-singletime')
    .subscribe((response: any) => {
       if (response != null && response != undefined) {
           // Check if File1 is available, then download it
           if (response.file1 != undefined && response.file1 != null && response.file1 != '') {
               this.downloadBase64File(response.file1, response.fileName1);
           }
           // Check if File2 is available, then download it
           if (response.file2 != undefined && response.file2 != null && response.file2 != '') {
               this.downloadBase64File(response.file2, response.fileName2);
           }
       }
    });
}

• API Call: The downloadMultipleExcelSheet method makes an HTTP GET request to the backend (/export-multiple-excel-singletime). It retrieves the JSON response containing Base64-encoded Excel files (file1 and file2) and their filenames (fileName1 and fileName2).
• Conditionals: Checks ensure that the Base64 strings for the files are not null or empty. If valid, it calls downloadBase64File() to handle the download.

downloadBase64File()

downloadBase64File(base64Data: string, filename: string) {
    // Convert Base64 string to a binary string
    const binaryString = window.atob(base64Data);
    const bytes = new Uint8Array(binaryString.length);
    
    // Convert binary string to bytes
    for (let i = 0; i < binaryString.length; i++) {
        bytes[i] = binaryString.charCodeAt(i);
    }
  
    // Create a Blob object to represent the binary data
    const blob = new Blob([bytes], { type: 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet' });

    // Create a URL for the Blob and initiate the download
    const url = window.URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = filename;  // Set the file name for download
    document.body.appendChild(a);
    a.click();  // Trigger the download

    // Clean up by revoking the object URL
    window.URL.revokeObjectURL(url);
}

• Base64 to Binary: The method decodes the Base64 string into a binary format using window.atob(). A Uint8Array is created to hold the byte data, and each character in the binary string is converted to its byte representation.
• Blob Creation: A Blob object is created to represent the Excel file's binary data. The MIME type is set to application/vnd.openxmlformats-officedocument.spreadsheetml.sheet for Excel files.
• Download: The Blob is turned into a URL using createObjectURL(), and an <a> element is dynamically created to trigger the download. After the download is triggered, the URL is revoked to free up resources.

Sample Output

This process allows you to handle multiple file downloads from a single API response, which can be useful for scenarios where you need to download multiple Excel files in one go.