gulp でExcelファイルからJSONファイルを生成する

前回 npm-scripts でExcelファイルからJSONファイルを生成する方法を紹介しました。

https://qiita.com/taqumo/items/51f8034801494d2b3588

しかし、JSONファイル生成後、Pug のコンパイルが必要となりました。

Pug のコンパイルは gulp で実行していたため、JSONファイルの生成も npm-scripts ではなく gulp で行うことにしました。

というわけで、gulp で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
├── gulpfile.js
└── package.json
```

### 準備

#### package.json の作成

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

```json:package.json
{
  "name": "gulp-excel-sheet-to-json",
  "version": "1.0.0",
  "private": true
}
```

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

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

| モジュール名 | 役割                     |
|-------------|--------------------------|
| ansi-colors | ログメッセージに色をつける |
| fancy-log   | 時刻付きのログを表示 |
| gulp        | gulp |
| xlsx        | Excelファイルを操作する |
| vinyl       | Vinylオブジェクトを返す |

```
npm i ansi-colors fancy-log gulp xlsx vinyl -D
```

#### gulpfile.js の作成

```js:gulpfile.js
const c = require('ansi-colors')
const log = require('fancy-log')
const gulp = require('gulp')
const through2 = require('through2')
const XLSX = require('xlsx')
const Vinyl = require('vinyl')

// gulpタスクを定義
const excelSheetToJson = () => {
  const src = 'data/'
  const dest = 'json/'
  const pattern = `${src}/!(~$)*.+(xls|xlsx|xlsm)`
  return gulp
    .src(pattern)
    .pipe(
      through2.obj((file, enc, cb) => {
        const excelFilePath = file.path
        const excelFileName = excelFilePath.match(/([^\\]*)\./)[1]
        const workbook = XLSX.readFile(excelFilePath)
        const sheet1 = workbook.Sheets[workbook.SheetNames[0]]
        const obj = {
          path: excelFileName + '.json'
        }
        obj.contents = Buffer.from(
          JSON.stringify(XLSX.utils.sheet_to_json(sheet1), null, 2)
        )
        log(
          `Created '` +
            c.green(`${dest}${excelFileName}.json`) +
            `'`
        )
        cb(null, new Vinyl(obj))
      })
    )
    .pipe(gulp.dest(dest))

// gulpタスクを登録
exports.excelSheetToJson = excelSheetToJson;
```

### gulpタスクを実行

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

```
npx gulp excelSheetToJson
```

## 複数シートを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
├── gulpfile.js
└── package.json
```

### 準備

#### package.json の作成

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

```json:package.json
{
  "name": "gulp-excel-sheets-to-json",
  "version": "1.0.0",
  "private": true
}
```

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

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

| モジュール名 | 役割                     |
|-------------|--------------------------|
| ansi-colors | ログメッセージに色をつける |
| fancy-log   | 時刻付きのログを表示 |
| fs          | ファイルを操作する |
| glob        | 特定のルールに従ったファイルを取得 |
| gulp        | gulp |
| xlsx        | Excelファイルを操作する |

```
npm i ansi-colors fancy-log fs glob gulp xlsx -D
```

#### gulpfile.js の作成

```js:gulpfile.js
const c = require('ansi-colors')
const fs = require('fs')
const glob = require('glob')
const log = require('fancy-log')
const XLSX = require('xlsx')

const excelSheetsToJson = (cb) => {
  const src = 'data/'
  const dest = 'json/'
  const pattern = `${src}/!(~$)*.+(xls|xlsx|xlsm)`
  
  // JSONファイルの書き込み
  const writeJsonFile = (data, path) => {
    const jsonStr = JSON.stringify(data)
    // JSONファイルの格納ディレクトリ作成
    if (!fs.existsSync(dest)) {
      fs.mkdirSync(dest, { recursive: true })
    }
    // JSONファイル作成
    fs.writeFileSync(path, jsonStr)
    log(`Created '` + c.green(path) + `'`)
  }

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

  glob(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 = `${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

        writeJsonFile(data, jsonFilePath)
      }
    }
    outputExistsSheetNames(excelFiles, existsSheetsNames)
    cb()
  })
}

// gulp のタスクを登録
exports.excelSheetsToJson = excelSheetsToJson;
```

### gulpタスクを実行

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

```
npx gulp excelSheetsToJson
```