Noob Front End Engineer Blog

CONTACT

Node.jsでExcelを元にファイル一括編集

2020/01/06

「このExcelのA列のファイルのB列の文字列をC列の文字列にしてクレメンス!ちな、7,000件な!」
さすがに手入力はやってられん。という事で、Node.js で自動化してみました。

※ 完成品のコードはこちら

テスト環境の下準備

Node.jsはインストール済みで最低限のjs知識がある方を前提として
最終的なディレクトリ構造はこんな感じを想定してます。

ディレクトリ構造

プロジェクトの作成

任意のディレクトリを作成してnpm initしましょう。
入力項目は任意で決めて大丈夫です。

mkdir excel-replaceer
cd excel-replaceer
npm init

モジュールのインストール

今回使用するモジュールはfsxlsxの2つです。

fs : https://www.npmjs.com/package/fs
xlsx : https://www.npmjs.com/package/xlsx

npm install fs
npm install xlsx

テスト用のExcelファイルの作成

読み込んでくるtest.xlsxファイルを作成します。
今回はプロジェクト直下に/testというディレクトリを作成し
そこに読み込んでくるExcelファイルと置換するhtmlファイルを設置します。

今回、Excelのデータ内容は下記の様な感じで作成します。

A B C
1 test01.html dummy test01
2 test02.html dummy test02
3 test03.html dummy test03

テスト用のhtmlファイルの作成

今回のテストで置換するhtmlファイルを作成します。
『A列』に記載がある通りの名前でhtmlを3つ作成してください。
ディレクトリはExcelファイルと同様/test配下に保存します。

htmlファイルの中身は任意で決めてもらえばよいですが、
今回は『B列』の「dummy」を置換するのでどこかしらにdummyを記述してください。

test01,2,3.html
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>dummy</title>
</head>
<body>
    
</body>
</html>

これでテストを行う為の下準備は完了です。

モジュール作成

次は実際にExcelから情報を取得して、置換処理を行うまでの解説です。

index.jsの作成

プロジェクト直下にindex.jsを作成してください。
ここに実際の置換処理を書いていきます。

node moduleの読み込み

下準備の段階でインストールを行ったfsxlsxを使用するので
この2つを呼び出して、いつでも使えるように変数に格納します

index.js
const xlsx = require('xlsx');
const utils = xlsx.utils;
const fs = require('fs');

utilsxlsxの機能の一つで、
こちらも毎回呼び出す手間を省く為に変数に格納しています。 これでモジュールを使う準備ができました。

Excelファイルの読み込み

Excelファイルの操作はxlsxモジュールを使用して行います。

index.js
// 上部 略

const testX = xlsx.readFile('./test/test.xlsx');
const sheet = testX.Sheets['Sheet1'];

readFileを使用することによってExcelファイルの読み込みができます。
読み込んだExcelの内容をtextXに格納し、その中のSheet1の情報を
sheet変数に格納しています。

セルの値を取得する為にはsheet['A1']というように
シート情報からセル名を指定して取得してきますが、
今回は入力されている範囲を取得して、ループを回してデータを取得します。

範囲の取得

index.js
// 上部 略

const range = sheet['!ref'];
const rangeN = utils.decode_range(range);

次にシートの記載がある範囲を取得します。
先ほどシートの情報を格納したsheet変数から['!ref]を指定すると
範囲が取得できるので、こちらをrengeの変数に格納します。
今回の場合、rangeには『A1:C2』が入っています。

範囲を取得することはできましたが、今取得してきた範囲は
テキストデータになっている為、A列からC列までループ処理は回せません。
これを解決するためにutilsを使用していきます。

utilsdecode_range()を使用して、
先ほどとってきたテキストデータの範囲を渡してあげる事で、
詳細な範囲データを返してくれます。これをrangeNに格納しています。
中身は下記の様になっています。

{ s: { c: 0, r: 0 }, e: { c: 2, r: 1 } }

それぞれ情報内容はこんな感じ

key 情報
s スタートのセル情報
e エンドのセル情報
c 列 (A, B, C ~~) の情報
r 行 (0, 1, 2 ~~) の情報

この情報を利用すれば、ループ分を回して行数分実行したり
列分実行したり、全レコード分実行したりできます。

今回は『B列』に元データ『C列』に変更後データがある状態を想定して作るので
行数分、置換が実行できれば良いことになります。

ループ・置換処理

今回は『A列』に置換したいファイル名が記載されており、
『B列』に変更前の文字列、『C列』に変更後の文字列が記載されているので
行数分、置換処理を実行すれば良いことになります。

for文で行数分ループするようにしましょう。

index.js
// 上部 略

for (let r = rangeN.s.r; r <= rangeN.e.r; r++) {
    let address = utils.encode_cell({c:0, r:r});
    let cell = sheet[address];
}
for (let r = rangeN.s.r; r <= rangeN.e.r; r++)

rangeN.s.rのスタートの行番号から、
rangeN.e.rのエンドの行番号までループする処理を書きます。

次にセルの情報を取得してきます。
『A列』に置換したいファイル名が記載されているので
列のインデックス番号であるcc:0で固定して編集するファイルを取得しましょう。

let address = utils.encode_cell({c:0, r:r});
let cell = sheet[address];

utilsencode_cell()を使用することでcrのプロパティで
指定されたセルを、テキストベースに変換してくれます。

let address = utils.encode_cell({c:0, r:0});
の場合は、addressに「A1」が入ります。

これで、cellに『A列』のデータ(ファイル名)が入るように設定できました。
指定のファイルを読み込んでみましょう。

// fs.readFile([読み込むファイルのパス], [読み込む文字コード], [コールバック関数]); 
fs.readFile('./test/' + cell.v, 'utf-8', (err, data) => {
    // エラー処理
    if (err) {
        console.log(`${cell.v} 】ファイル読み込みエラー`);
        throw err;
    }
});

ファイルの読み書きにはfsモジュールを使用します。
ファイルの読み込みではreadFile()を使用します。
上記のように、簡単なエラー処理も書いておきましょう。
このコールバック関数内で、置換の処理を行っていきます。
コールバック関数で渡しているdataには取得してきた内容が入っています。

index.js
// 変更前の内容を取得
let B_address = utils.encode_cell({c:1, r:r});
let B_cell = sheet[B_address];

// 変更後の内容を取得
let A_address = utils.encode_cell({c:2, r:r}); 
let A_cell = sheet[A_address];

// 置換
const beforeTxt = data;
const afterTxt = beforeTxt.replace(new RegExp(B_cell.v,"g"), A_cell.v);

今回は『B列』に変更前の文字列、『C列』に変更後の文字列が
入っていることがわかっているので、それぞれ『A列』の情報を
とってきた時と同様に、cプロパティを固定して情報を取ってきます。

あとはおなじみのreplace()を使用して置換をおこない
afterTxtに格納しておきます。

index.js
// fs.writeFile([書き込むファイルパス], [書き込む内容], コールバック関数)
fs.writeFile('./test/' + cell.v, afterTxt, (err) => {
    if (err) {
        console.log(`${cell.v} 】ファイル置換エラー`);
        throw err;
    }

    console.log(`${cell.v} 】success !`);
});

最後にファイルの上書きを行っていきます。
ファイルの書き込みにはwriteFile()を使用します。
こちらにも簡単なエラー処理を書いてあげましょう。

index.jsの完成形はこんな感じ

index.js
// モジュールのインストール
const xlsx = require('xlsx');
const utils = xlsx.utils;

const fs = require('fs');

// エクセルファイルの読み込み
const testX = xlsx.readFile('./test/test.xlsx');

// シートの読み込み
const sheet = testX.Sheets['Sheet1'];

// セルの範囲の取得
const range = sheet['!ref'];
// console.log(range);

// セルの範囲を数値化
const rangeN = utils.decode_range(range);

// ループ処理
for (let r = rangeN.s.r; r <= rangeN.e.r; r++) {
    // ファイル名取得
    let address = utils.encode_cell({c:0, r:r});
    let cell = sheet[address];

    // htmlの読み込み
    fs.readFile('./test/' + cell.v, 'utf-8', (err, data) => {
        // エラー処理
        if (err) {
            console.log(`${cell.v} 】ファイル読み込みエラー`);
            throw err;
        }

        // 置換処理
        // 変更前の内容を取得
        let B_address = utils.encode_cell({c:1, r:r});
        let B_cell = sheet[B_address];

        // 変更後の内容を取得
        let A_address = utils.encode_cell({c:2, r:r}); 
        let A_cell = sheet[A_address];

        // 置換
        const beforeTxt = data;
        const afterTxt = beforeTxt.replace(new RegExp(B_cell.v,"g"), A_cell.v);
        
        // ファイルの上書き
        fs.writeFile('./test/' + cell.v, afterTxt, (err) => {
            if (err) {
                console.log(`${cell.v} 】ファイル置換エラー`);
                throw err;
            }

            console.log(`${cell.v} 】success !`);
        });
    });
}

これで処理は完成しました!

処理の実行

最後に下記で実行して置換が行われるか試してみてください!

node index.js

Written by daichi iwamoto