HTML のコーディングに Pug を利用するようになってから、JSONデータを HTML に反映できてとっても便利になりました。

Excelファイルにデータを入力
マクロでJSONファイル生成

という流れでJSONファイルを生成していましたが、VBA（Visual Basic for Applications）がよくわかりません…

そこで、JavaScript でどうにかならんものかと試行錯誤してみたら…

Node.js でできちゃいました

というわけで、ExcelファイルからJSONファイルを生成する方法を紹介します。

Excelファイルと生成されるJSONファイルの仕様

Excelファイルの1行目の値が各アイテムの key となる
Excelファイルの2行目以降が各アイテムとなり、セルが value となる
Excelファイルの拡張子は xls xlsx xlsm が対象
セルが空の場合は空文字を出力
数字だけの文字列は数値に変換して出力

Excelファイルのサンプル

A	B	C
	B1	C1
A2	B2	C2
A3	B3	3

生成されるJSONファイル

JSON

[
  {
    "A": "",
    "B": "B1",
    "C": "C1"
  },
  {
    "A": "A2",
    "B": "B2",
    "C": "C2"
  },
  {
    "A": "A3",
    "B": "B3",
    "C": 3
  }
]

単一シートをJSON化

仕様

特定のディレクトリ内のすべてのExcelファイルが対象
Excelファイルの1シート目のデータがJSONファイルとして生成される
生成されるJSONファイル名はExcelファイル名と同じ

ファイル構造

dataフォルダ内のExcelファイルがjsonフォルダに格納されます。

root
├── data/
│   ├── hoge.xls
│   ├── fuga.xls
│   └── piyo.xls
├── json/
│   ├── hoge.json
│   ├── fuga.json
│   └── piyo.json
├── npm-scripts/
│   └── excel-sheet-to-json.js
└── package.json

準備

package.json の作成

package.json をルート直下に作成します。

scripts フィールドに json という名前で excel-sheet-to-json.js を実行するnpmスクリプトを登録。

package.json

{
  "name": "excel-sheet-to-json",
  "version": "1.0.0",
  "private": true,
  "scripts": {
    "json": "node ./npm-scripts/excel-sheet-to-json.js"
  }
}

node_module のインストール

必要な node_module は以下となります。

モジュール名	役割
ansi-colors	ログメッセージに色をつける
fs	ファイルを操作する
glob	パターンにマッチしたパスを見つける
xlsx	Excelファイルを操作する

npm i ansi-colors fs glob xlsx -D

JavaScript の作成

excel-sheet-to-json.js

const c = require('ansi-colors')
const fs = require('fs')
const glob = require('glob')
const XLSX = require('xlsx')

class ExcelSheetToJson {
  constructor() {
    this.src = 'data/'
    this.dest = 'json/'
    this.pattern = `${this.src}/!(~$)*.+(xls|xlsx|xlsm)`
  }

  init() {
    glob(this.pattern, (err, files) => {
      if (err) {
        console.error(err)
        return
      }
      for (const file of files) {
        const fileName = file.match(/([^/]*)\./)[1]
        const data = this.getExcelData(file, fileName)
        this.writeJsonFile(data, fileName)
      }
    })
  }

  // エクセルファイルの1シート目のデータを取得
  getExcelData(excelFilePath, excelFileName) {
    const workbook = XLSX.readFile(excelFilePath)
    const Sheet1 = workbook.Sheets[workbook.SheetNames[0]]
    // meta 管理用のエクセルの場合
    if (excelFileName === 'meta') {
      const data = XLSX.utils.sheet_to_json(Sheet1, {
        defval: ''
      })
      const metaData = {}
      for (const obj of data) {
        const pageData = {}
        for (const [key, value] of Object.entries(obj)) {
          if (key === 'pageUrl') continue
          pageData[key] = value
        }
        metaData[obj.pageUrl] = pageData
      }
      return metaData
    }
    // meta 管理用のエクセル以外
    return XLSX.utils.sheet_to_json(Sheet1)
  }

  // JSONファイルの書き込み
  writeJsonFile(data, excelFileName) {
    const jsonStr = JSON.stringify(data)
    // JSONファイルの格納ディレクトリがなければ作成
    if (!fs.existsSync(this.dest)) {
      fs.mkdirSync(this.dest, { recursive: true })
    }
    // JSONファイル作成
    fs.writeFile(`${this.dest}${excelFileName}.json`, jsonStr, (err) => {
      if (err) {
        console.error(
          c.red('ERR! ') +
            `Failed to create '${this.dest}${excelFileName}.json'：${err}`
        )
        return
      }
      console.log(
        `Created '` + c.green(`${this.dest}${excelFileName}.json`) + `'`
      )
    })
  }
}
const excelSheetToJson = new ExcelSheetToJson()
excelSheetToJson.init()

npm-scripts の実行

以下コマンドを入力するとJSONファイルが生成されます

npm run json

複数シートをJSON化

仕様

特定のディレクトリ内のすべてのExcelファイルが対象
Excelファイルの各シートがJSONファイルの対象
- シート名が英数字・ハイフン（-）・アンダーバー（_）の場合はJSONファイルを生成する
- シート名が日本語（英数字 -_ 以外）の場合はJSONファイルを生成しない
- 異なるExcelファイルで同一シート名が使われている場合はJSONファイルを生成しない
生成されるJSONファイル名はシート名（英数字 -_）と同じ