ExcelFile

ExcelFile

Load, process and parse an Excel File containing a list of PH municipalities. The Excel File should contain a column with string pattern: "municipalityName (ProvinceName)"

Constructor

new ExcelFile(params)

Initialize an ExcelFile object

Source:
Parameters:
Name Type Description
params Object

Constructor parameter Object

Name Type Attributes Description
url string <optional>

(Optional) Remote download URL of an excel file
pathToFile string
  • Full local file path of an existing Excel file, required if params.url is not provided
    • Full local file path to an existing or non-existent Excel file on which to download/save the remote Excel file from params.url, if the params.url parameter is provided
settings Object <optional>

(Optional) Region settings configuration object following the format of the /app/config/regions.json file. Defaults to the mentioned file if not provided.
fastload boolean <optional>

(Optional) Start loading and parsing the local excel file on class initialization if the "url" param is not provided.

  • If false or not provided, call the .init() method later on a more convenient time.

Members

DATA_ROW_START_MARKER :string

A marker text found in the Excel file and the sheetjs-parsed this.#data[] array that indicates the start of actual municipality data in the next array element (i.e., the next Excel row).

Source:
Type:
  • string

EVENTS :Object.<string, string>

List of EventEmitter events.

Source:
Type:
  • Object.<string, string>

(private) data :Array.<Object>|null

Objects[] Array corresponding to excel rows extracted from the excel sheet by sheetjs.

Source:
Type:
  • Array.<Object> | null

(private) datalist :Array.<Object>|null

Object[] Array of processed string corresponding to the column in the excel file that contains the list of municipalities following the pattern: "municipalityName (provinceName)" Content: [{ municipality, province }, ... ]

Source:
Type:
  • Array.<Object> | null

events :function

Node event emitter for listening to custom events.

Source:
Type:
  • function

(private) invalidRows :Array.<string>

Invalid data rows that do not follow the expected "municipalityName (provinceName)" uniform string pattern e.g., also having a province that's not included in the PAGASA Rainfall Analysis Table in "City of Isabela (City of Isabela (Not a Province))"

Source:
Type:
  • Array.<string>

malformedText :Array.<String>

string[] array of malformed (garbled) text characters to watch for in the Excel file. Its value is set in the process.env.SPECIAL_CHARACTERS env variable.

Source:
Type:
  • Array.<String>

malformedTextCorrections :Object

Object key-value pairs where keys are items in the ExcelFile.malformedText[] and values are their corrected character conversions.

Source:
Type:
  • Object

(private) metadata :Object.<string, (string|null)>

10-day Excel file information

Source:
Type:
  • Object.<string, (string|null)>

(private) options :Object.<string, (string|number)>

Other class settings and configurations

Source:
Type:
  • Object.<string, (string|number)>

(private) pathToFile :string|null

Full file path to excel file on local storage

Source:
Type:
  • string | null

(private) settings :Object|null

Region information from the /app/config/regions.json or other JSON config file.

Source:
Type:
  • Object | null

(private) sheets :Array.<string>|null

Excel sheet names parsed by sheetjs.

Source:
Type:
  • Array.<string> | null

(private) url :string|null

Remote download URL of an excel file

Source:
Type:
  • string | null

(private) workbook :Array.<Object>|null

Excel workbook object parsed by sheetjs

Source:
Type:
  • Array.<Object> | null

Methods

download() → {Promise.<void>}

Downloads a remote excel file to this.#pathToFile and loads sheetjs parsed-content

Source:
Throws:

If the download or loading fails.

Type
Error
Returns:
Type:
Promise.<void>

Resolves when the Excel file is downloaded and loaded succesfully.

followsStringPattern(str) → {boolean}

Checks if a string follows the pattern: "municipalityName (provinceName)"

Source:
Parameters:
Name Type Description
str string

String to check
Returns:
Type:
boolean

true | false

getMunicipalityName(str) → {string}

Extracts the municipality name from a string following the pattern: "municipalityName (provinceName)"

Source:
Parameters:
Name Type Description
str string
Returns:
Type:
string

municipality name

getProvinceName(str) → {string|null}

Extracts the province name from a string following the pattern: "municipalityName (provinceName)"

Source:
Parameters:
Name Type Description
str string
Returns:
  • Type:
    string

    province name

  • Type:
    null

    Returns null if "provinceName" is not found

(async) init()

Loads an existing excel file contents to a JSON object. Downloads a remote excel file if a remote this.#url is provided on the constructor. Initializes the malformed characters and their normalized conversions.

Source:

listAllProvinces(fromExcel) → {Array.<String>}

Lists the province names of a region defined in the settings (PAGASA seasonal config) file or from the parsed Excel file

Source:
Parameters:
Name Type Default Description
fromExcel boolean false

Flag to return the province names from the parsed 10-day Excel file. Defaults to false.

  • Note: Province names from a "remote" Excel file may change without notice.
  • It may differ from the contents of the "default" settings (PAGASA seasonal config) file.
  • If the province names from the "remote" Excel file and "default" settings (PAGASA seasonal config) file vary, consider initializing an ExcelFile or ExcelFactory class with a custom settings config file following the format of the default settings file in /app/config/regions.json
Returns:
Type:
Array.<String>

List of all provinces from a 10-day Excel file.

listMunicipalities(provinces) → {Object}

List the municipalities of given province(s)

Source:
Parameters:
Name Type Description
provinces Array.<String>

Array of case-sensitive province names. Starts with an upper case.
Returns:
Type:
Object

Returns an object with the format: [ { province1: ['municipality1', 'municipality2', .... ] }, { province2: ['municipality1', 'municipality2', .... ] }, ... ]

listProvinces(regionName) → {Array.<String>}

Lists the province names of a region defined in the settings file

Source:
Parameters:
Name Type Description
regionName string

Region name that matches with the /app/config/regions.json file's data[N].name
Returns:
Type:
Array.<String>

List provinces under the regionName.

listRegions(key) → {Array.<String>}

Lists the region names defined in the settings file

Source:
Parameters:
Name Type Default Description
key Object null

Key name of the region data definition key.

  • Valid values are: name, abbrev, region_num, and region_name
  • See the /app/config/regions.json file -> data[] item keys for more information.
Returns:
Type:
Array.<String>

A list of province information by key

load()

Loads an Excel file from a local directory using sheetjs. Stores Excel file data as JSON in this.#data

Source:
Throws:

Excel parsing and reading errors.

Type
Error

setOptions(options) → {boolean}

Sets the local this.#options settings

Source:
Parameters:
Name Type Description
options Object

Miscellaneous app settings defined in this.#options
Returns:
Type:
boolean

shapeJsonData(provinces) → {Object}

Get the requested data with other misc data

Source:
Parameters:
Name Type Description
provinces Array.<String>

List of provinces
Returns:
Type:
Object

Formatted raw data with misc. metadata

writeMunicipalities(provinces, fielName, prettify) → {Object}

Writes queried municipalities data to a JSON file. Lists municipalities by by provinces.

Source:
Parameters:
Name Type Description
provinces Array.<String>

Array of case-sensitive province names. Starts with an upper case.
fielName string

Full file path to a JSON file
prettify boolean

Write the JSON content with proper spacings and newlines
Returns:
Type:
Object

Formatted raw data with misc. metadata

(static) hasMalformedText(str) → {boolean}

Checks if a string contains malformed characters defined in the ExcelFile.malformedText[] list.

Source:
Parameters:
Name Type Description
str string

String to check for garbled or malformed characters
Returns:
Type:
boolean

Flag indicating if the input str contains malformed characters

(static) hasSpecialChars(str) → {boolean}

Checks if a string contains special characters

Source:
Parameters:
Name Type Description
str string

String to check
Returns:
Type:
boolean

(static) initMalformedTextList()

Initializes the ExcelFile.malformedText[] string array with malformed text characters from the process.env.SPECIAL_CHARACTERS env variable.

  • It also builds the ExcelFile.malformedTextCorrections Object containing corrections of the malformed texts.
Source:

(static) removeGarbledText(str) → {string}

Cleans/removes default-known special characters and garbled text defined in config from string.

Source:
Parameters:
Name Type Description
str string

String to clean
Returns:
Type:
string
  • Clean string