node-excel-module

Expose excel functions in a XLSX file as a JavaScript module.

master

dev

• node-excel-module
  • Introduction
  • Installation
  • Usage
  • API
    • excelModule.from()
    • Workbook
      • .compile()
      • CellSpec
      • APIFactory
  • Example
  • License

Introduction

• XLSX files are read and parsed by Exceljs.
• Formula evaluation is powered by hot-formula-parser.
• Expose specified cell values and functions via an object.
• The exported object is serializable; that is, the exported object can be serialized to strings through libraries like serialize-javascript.
• Merged cells and shared formulas are supported.
• The minimum raw data is included into the compiled context. It works like a charm even if a formula requires the result from another formula.
• Cross-sheet reference supported.

Installation

npm install excel-module

Usage

const excelModule = require('excel-module')

API

excelModule.from()

• Parameters
  • src string | Readable
• Return
  • Promise<Workbook>

Workbook

Workbook is a descendant class inherited from Exceljs.Workbook.

.compile()

• Parameters
  • spec Record<string, CellSpec> The keys of exported object will be the same as the spec object, which are names of exported APIs.
• Return
  • APIFactory the exported object

CellSpec

The type should be one of the following constructors, Number, Boolean, String and Function.

interface CellSpec {
  cell: string
  type: CellType
  args?: string[]
}

type CellType = |
  FunctionConstructor |
  NumberConstructor |
  StringConstructor |
  BooleanConstructor

APIFactory

type APIFactory = () => Record<string, any>

When specifying cells, use excel syntax like A1, $B$2. Note that both are all treated as absolute coordinates.

Example

See integration tests for more details.

• sum.xlsx

row\col	A	B	C
1	1	2	=SUM(A1:B1)
2	3	4	=SUM(A2:B2)
3	5	6	=SUM(A3:B3)
4	=SUM(A1:B1)

• index.js

const SUM_XLSX = 'path/to/sum.xlsx'

async function main () {
  const workbook = await excelModule.from(SUM_XLSX)
  const apiFactory = await workbook.compile({
    data1: {
      type: Number,
      cell: 'A1'
    },
    data2: {
      type: String,
      cell: 'B1'
    },
    sum: {
      type: Function,
      cell: 'C1',
      args: [ 'A1', 'B1' ]
    },
    sumAll: {
      type: Function,
      cell: 'A4',
      args: [
        'A1', 'B1', 'A2', 'B2', 'A3', 'B3'
      ]
    }
  })

  const api = apiFactory()

  assert(api.data1 === 1)
  assert(api.data2 === '2')
  assert(api.sum() === 3)                      // 1 + 2 = 3
  assert(api.sum(3, 4) === 7)                  // 3 + 4 = 7
  assert(api.sumAll() === 21)                  // 1 + 2 + 3 + 4 + 5 + 6 = 21
  assert(api.sumAll(5, 6, 7, 8, 9, 10) === 45) // 5 + 6 + 7 + 8 + 9 + 10 = 45
}

main()

Each compiled function contains a context of raw data. The context of the example above is shown as follow.

{
  "1!$A$1": 1,
  "1!$B$1": 2,
  "1!$C$1": "=SUM(1!$A$1:$B$1)",
  "1!$A$4": "=SUM(1!$C$1:$C$3)",
  "1!$C$2": "=SUM(1!$A$2:$B$2)",
  "1!$A$2": 3,
  "1!$B$2": 4,
  "1!$C$3": "=SUM(1!$A$3:$B$3)",
  "1!$A$3": 5,
  "1!$B$3": 6
}

License

GLWTPL base on https://github.com/me-shaon/GLWTPL.