[Angular] Read Spreadsheets with SheetJS

Intro

I wanted to send spreadsheets(.xlsx) to sever.
But before sending, I wanted to validate the files.
So I tried to read them with SheetJS.

GitHub - SheetJS/sheetjs: SheetJS Community Edition -- Spreadsheet Data Toolkit
xlsx · docs

Spreadsheet sample

Install & read data

Install

npm install --save xlsx

Open workbook and worksheet

For opening workbook, I used FileReader by Reading XLSX from FileReader.readAsArrayBuffer() · SheetJS/sheetjs Wiki · GitHub.

sheet-loader.service.ts

import { Injectable } from '@angular/core';
import * as xlsx from 'xlsx';
@Injectable({
  providedIn: 'root'
})
export class SheetLoaderService {
  constructor() { }
  public load(file: File) {
    const reader: FileReader = new FileReader();
    reader.onload = (e: any) => {
      /* read workbook */
      const loadResult: string = e.target.result;
      const workbook: xlsx.WorkBook = xlsx.read(loadResult, {type: 'binary'});
      /* set first sheet as target sheet */
      const sheetName: string = workbook.SheetNames[0];
      /* read worksheet */
      const sheet: xlsx.WorkSheet = workbook.Sheets[sheetName];
    };
    reader.readAsBinaryString(file);
  }
}

Find named cell

I wanted to get named cell's position to know where start reading cells.
Could I get them from Workbook or Worksheet?

The answer was from Workbook.

sheet-loader.service.ts

...
export class SheetLoaderService {
  constructor() { }
  public load(file: File) {
    const reader: FileReader = new FileReader();
    reader.onload = (e: any) => {
      /* read workbook */
      const loadResult: string = e.target.result;
      const workbook: xlsx.WorkBook = xlsx.read(loadResult, {type: 'binary'});
      console.log(workbook.Workbook.Names);
...

The result liked below.

(2) [{…}, {…}]
    0:
        Name: "greeting"
        Ref: "Sample!$A$6"
        __proto__: Object
    1:
        Name: "Title"
        Ref: "Sample!$C$14"
        __proto__: Object
    length: 2
    __proto__: Array(0)

Export Named Range to CSV · Issue #708 · SheetJS/sheetjs · GitHub

Get named cell's value

Thus I could get Worksheet name and cell address from "Ref".
Because I couldn't find get cell value directory, I did like this.

sheet-loader.service.ts

...
export class SheetLoaderService {
  constructor() { }
  public load(file: File) {
    const reader: FileReader = new FileReader();
    reader.onload = (e: any) => {
      /* read workbook */
      const loadResult: string = e.target.result;
      const workbook: xlsx.WorkBook = xlsx.read(loadResult, {type: 'binary'});
      const targetCell = this.getTargetCell(workbook);
    }
...
  private getTargetCell(workbook: xlsx.WorkBook):
      { sheet: xlsx.WorkSheet, cell: xlsx.CellObject }|null {
    const targetCell = workbook.Workbook.Names
      .find(n => n.Name === 'Title');
    console.log(targetCell.Ref);
    if (targetCell == null) {
      return null;
    }
    // targetCell.Ref's value was {WorksheetName}!${Column}${Row}
    const splitCellNameRefs = targetCell.Ref.split('!');

    const sheet: xlsx.WorkSheet = workbook.Sheets[splitCellNameRefs[0]];
    // convert from $C$14 to { c: 2, r: 13 }
    const cellAddress = xlsx.utils.decode_cell(splitCellNameRefs[1]);
    // convert from { c: 2, r: 13 } to C14
    const encodedCellAddress = xlsx.utils.encode_cell(cellAddress);
    // sheet['C14'] -> get value of the cell where was at Col:C, Row;14.
    const cellValue: xlsx.CellObject = sheet[encodedCellAddress];
    return {
      sheet,
      cell: cellValue
    };
  }

The result liked below.

{t: "s", v: "Hello world", r: "<t xml:space="preserve">Hello world</t>", h: "Hello world", w: "Hello world"}
  t: "s"
  v: "Hello world"
  r: "<t xml:space="preserve">Hello world</t>"
  h: "Hello world"
  w: "Hello world"
  __proto__: Object

I thought this code had been a little verbose.
There might be a more concise way to write.

Get cell values

sheet-loader.service.ts

...
export class SheetLoaderService {
  constructor() { }
  public load(file: File) {
    const reader: FileReader = new FileReader();
    reader.onload = (e: any) => {
      /* read workbook */
      const loadResult: string = e.target.result;
      const workbook: xlsx.WorkBook = xlsx.read(loadResult, {type: 'binary'});
      const targetCell = this.getTargetCell(workbook);
      if (targetCell == null) {
        return null;
      }
      const endColumnNumber = this.getEndColumnNumber(targetCell.sheet, targetCell.cellAddress);
      console.log(endColumnNumber);
      const cellValues = this.getCellValues(targetCell.sheet, targetCell.cellAddress, endColumnNumber);
      console.log(cellValues);
    }
...
  private getEndColumnNumber(sheet: xlsx.WorkSheet, startCell: xlsx.CellAddress): number {
    let currentColumn = startCell.c;
    while (true) {
      const nextCellValue = sheet[xlsx.utils.encode_cell({c: currentColumn, r: startCell.r})];
      // if the cell value was empty, stop and return column number
      if (nextCellValue == null ||
          nextCellValue.v == null ||
          nextCellValue.v === '') {
        return currentColumn;
      }
      currentColumn += 1;
    }
  }
  private getCellValues(sheet: xlsx.WorkSheet, startCell: xlsx.CellAddress, endColumnNumber: number): CellValue[] {
    const cellValues = new Array<CellValue>();
    for (let i = startCell.c; i <= endColumnNumber; i++) {
      let currentRow = startCell.r;
      while (true) {
        const nextCellValue = sheet[xlsx.utils.encode_cell({c: i, r: currentRow})];
        if (nextCellValue == null ||
            nextCellValue.w == null ||
            nextCellValue.w === '') {
          break;;
        }
        // use formatted cell value.
        cellValues.push({
          column: i,
          row: currentRow,
          value: nextCellValue.w
        });
        currentRow += 1;
      }
    }
    return cellValues;
  }

The result liked below.

(8) [{…}, {…}, {…}, {…}, {…}, {…}, {…}, {…}]
  0: {column: 2, row: 13, value: "Hello world"}
  1: {column: 2, row: 14, value: "12月31日"}
  2: {column: 2, row: 15, value: "TRUE"}
  3: {column: 2, row: 16, value: "1"}
  4: {column: 3, row: 13, value: "It’s sunny today"}
  5: {column: 3, row: 14, value: "1月3日"}
  6: {column: 3, row: 15, value: "FALSE"}
  7: {column: 3, row: 16, value: "2"}
  length: 8
  __proto__: Array(0)