npm-scripts でExcelファイルからJSONファイルを生成する

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

https://pugjs.org/api/getting-started.html

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

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

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

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

というわけで、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: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スクリプトを登録。

```json: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 の作成

```js: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ファイルが生成されます:ok_hand:

```
npm run json
```

## 複数シートをJSON化

### 仕様

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

### ファイル構造

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

```
root
├── data/
│   ├── hoge.xls
│   │   ├── hoge-sheet1
│   │   └── hoge-sheet2
│   └── fuga.xls
│       ├── fuga-sheet1
│       └── fuga-sheet2
├── json/
│   ├── hoge-sheet1.json
│   ├── hoge-sheet2.json
│   ├── fuga-sheet1.json
│   └── fuga-sheet2.json
├── npm-scripts/
│   └── excel-sheets-to-json.js
└── package.json
```

### 準備

#### package.json の作成

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

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

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

#### node_module のインストール

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

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

```
npm i ansi-colors fs glob xlsx -D
```

#### JavaScript の作成

```js:excel-sheets-to-json.js
const c = require('ansi-colors')
const fs = require('fs')
const glob = require('glob')
const XLSX = require('xlsx')

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

  init() {
    glob(this.pattern, (err, excels) => {
      if (err) {
        console.error(err)
        return
      }
      const excelFiles = []
      const existsSheetsNames = []

      // Excelファイルをループ
      for (const excel of excels) {
        const fileName = excel.match(/.+\/(.+?)?$/)[1]
        const workbook = XLSX.readFile(excel)
        const sheetCount = workbook.SheetNames.length

        // Excelファイルのシートをループ
        for (let i = 0; i < sheetCount; i++) {
          const sheet = workbook.Sheets[workbook.SheetNames[i]]
          const sheetName = workbook.SheetNames[i]
          const sheetNameRegex = /^[0-9a-zA-Z_-]*$/
          const jsonFilePath = `${this.dest}${sheetName}.json`
          let isExistsSheetName = false
          let data = XLSX.utils.sheet_to_json(sheet, {
            defval: ''
          })

          // シート名の重複を判定
          for (const excelFile of excelFiles) {
            if (excelFile.sheets.includes(sheetName)) {
              isExistsSheetName = true
              if (!existsSheetsNames.includes(sheetName)) {
                existsSheetsNames.push(sheetName)
              }
            }
          }

          // Excelファイル名とシート名を含む配列を作成
          if (!excelFiles.some((file) => file.name === fileName)) {
            excelFiles.push({ name: fileName, sheets: [sheetName] })
          } else {
            const index = excelFiles.findIndex(({ name }) => name === fileName)
            excelFiles[index].sheets.push(sheetName)
          }

          // シート名が日本語 or すでに存在している場合は次のループへ
          if (!sheetNameRegex.test(sheetName) || isExistsSheetName) continue

          this.writeJsonFile(data, jsonFilePath)
        }
      }
      this.outputExistsSheetNames(excelFiles, existsSheetsNames)
    })
  }

  // JSONファイルの書き込み
  writeJsonFile(data, path) {
    const jsonStr = JSON.stringify(data)
    // JSONファイルの格納ディレクトリがなければ作成
    if (!fs.existsSync(this.dest)) {
      fs.mkdirSync(this.dest, { recursive: true })
    }
    // JSONファイル作成
    fs.writeFileSync(path, jsonStr)
    console.log(`Created '` + c.green(path) + `'`)
  }

  // 重複しているシート名をログに出力
  outputExistsSheetNames(excelFiles, existsSheetsNames) {
    for (const existsSheet of existsSheetsNames) {
      console.error(
        c.red('ERR! ') +
          `sheet name already exists, '` +
          c.magenta(existsSheet) +
          `'`
      )
      for (const excel of excelFiles) {
        if (excel.sheets.includes(existsSheet)) {
          console.error(
            `'` +
              c.magenta(existsSheet) +
              `' in '` +
              c.magenta(`${this.src}${excel.name}`) +
              `'`
          )
        }
      }
    }
  }
}
const excelSheetsToJson = new ExcelSheetsToJson()
excelSheetsToJson.init()
```

### npm-scripts の実行

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

```
npm run json
```