Excel Uploads & Validation Made Easy in Angular

In the last article we learned about how we can generate csv file from base64 encoded data passed over the network via REST API and make it downloadable on client end.

But today we will be diving into different aspect of it, how to validate excel on client end and after that encode in base64 and send it to backend over network via REST API. Administrator or business professionals may require such feature on a web application to upload data to a database or perform any action on that database, and various other use cases. Let's explore how to achieve this using Angular coupled with backend APIs for data transmission.

Prerequisites:

• Basic familiarity with the Angular framework

• Node.js installed on your local machine

To begin, clone the repository from AngularCodeHub
git clone https://github.com/UlbertAO/AngularCodeHub

Open the project in your preferred code editor (e.g., VSCode) and run:
npm install

Once the installation completes, launch the application using:

ng serve -o

This will open the application in your default browser.

Navigate to the "UploadData" route to follow.

Implementing Backend API

Within the service, create a method to communicate with the backend API and pass excel file data in base64 format.

Why choose base64 format?

• Seamless integration with JSON/XML

• Prevention of binary corruption

• Universal acceptance across different systems due to its standard character set

Consider the following method within your service:

  uploadData(payload: UploadDataReqModal): Observable<UploadDataResModal> {
    return this.http.post<UploadDataResModal>(
      this.appSetting.baseURLs?.UploadData,
      payload
    );
  }

Validate excel data

We have used ‘xlsx’ package to work with excel on client side.

Basic validation of filetype check and size limit check can be done using “name” and “size” attribute of file input.

this.kbFile = pFileList[0];
this.kbFile.name
this.kbFile.size

When we go on validating excel, first we would like to check if all mandatory headers are present and then check data corresponding to it.

The main idea here is to convert sheet data to a list of objects and then validate that list, ‘xlsx’ package will help us to do the same.

Read file as a binary string using FileReader and on load validate for mandatory headers then convert sheet data to list of objects and validate data as per requirement.

     const reader: FileReader = new FileReader();
      reader.readAsBinaryString(this.kbFile);
      reader.onload = (e) => {
        const binarystr: string | ArrayBuffer | null = reader.result;
        const wb: XLSX.WorkBook = XLSX.read(binarystr, { type: 'binary' });

        // selected the first sheet
        const wsname: string = wb.SheetNames[0];
        const ws: XLSX.WorkSheet = wb.Sheets[wsname];

        // extract header from excel
        const headers: string[] = [];
        const columnCount = XLSX.utils.decode_range(ws['!ref'] || '').e.c + 1; // get col count !ref is range A1:G1
        for (let i = 0; i < columnCount; ++i) {
          headers[i] = ws[`${XLSX.utils.encode_col(i)}1`].v; // get values of 1 col
        }
        // match extracted headers with predefined mandatoryHeaders 
        for (const headerVal of headers) {
          if (
            !this.mandatoryHeaders
              .map((x) => x.toLocaleLowerCase())
              .includes(headerVal.toLocaleLowerCase())
          ) {
            this.uploadStatus = {
              status: false,
              msg: 'Mandatory headers are missing OR Unecessary headers are present',
            };
            this.uploaderLoader.style.borderColor = '#d60000';
            return;
          }
        }

        // convert data in excel to json and validate key value
        const data = XLSX.utils.sheet_to_json(ws);
        let blankEntriesRow = '';
        let rowCount = 1;
        for (const row of data) {
          if (!this.mandatoryHeaderKeyCheck(row)) {
            blankEntriesRow += rowCount + ', ';
          }
          rowCount++;
        }
        if (blankEntriesRow) {
          this.uploadStatus = {
            status: false,
            msg:
              'We have found blank details against the entries in row(s) ' +
              blankEntriesRow +
              'please verify and reupload the document.',
          };
          this.uploaderLoader.style.borderColor = '#d60000';
          return;
        }

        // validation completed
        this.uploadKb(); // call method to convert excel in base64 and send it to backend 
      };
  uploadKb() {
    if (this.kbFile) {
      const dReader: FileReader = new FileReader();
      dReader.readAsBinaryString(this.kbFile);
      dReader.onload = (e: any) => {
        const fileData = btoa(e.target.result.toString());// convert excel file to bace64
        const payload = {
          FileBytesData: fileData,
        };
        this.adminService
          .uploadData(payload)
          .pipe(takeUntil(this.ngUnsubscribe))
          .subscribe({
            next: (data) => {
              if (data.isSuccess) {
                this.uploadStatus = {
                  status: true,
                  msg: '',
                };
                this.uploaderLoader.style.borderColor = '#00911c';
              } else {
                this.uploadStatus = {
                  status: false,
                  msg: 'Something Went Wrong',
                };
                this.uploaderLoader.style.borderColor = '#d60000';
              }
            },
            error: (error) => {
              this.uploadStatus = {
                status: false,
                msg: 'Something Went Wrong',
              };
              this.uploaderLoader.style.borderColor = '#d60000';
            },
          });
      };
    }
  }

Hope you find this article helpful.