PuppeteerでExcelファイルを読み取り入力させる

一般の業務では、非常にExcelファイルを扱うシーンが多いです。管理帳票として使っているケースが多いですが、そこから更に次の業務が待ってるのが常です。しかし、せっかく管理帳票でデータを入れているのに、ウェブアプリで再度入力は無駄な作業以外の何物でもありません。

Excelシートからデータを読み取り、まとめてウェブアプリに繰り返し登録作業を行えたら非常に便利ですね。そこで今回は手元のExcelシートからとあるサービスに連続で登録するPuppeteerを作ってみようと思います。

今回使用するモジュール等

• puppeteer-core – npm
• xlsxモジュール – xlsxファイルを読み書きするためのモジュール
• prompts – npm

今回は、以前もElectronで利用したxlsxモジュールを用いて読み書きを行います。デスクトップに用意したtellist.xlsxファイルに基づいてサイトに情報を連続登録を試みます。

また、今回は利用にあたって問題点と解消方法があるのでその点を踏まえてコーディングをする必要があります。

今回の入力上の問題点

今回はこれまでのようなダウンロードやアップロードではなく、サイトに情報を登録するタイプのPuppeteerです。その際の要件は以下の通りです。

1. Excelはきちんと整形されたデータである必要がある（セルの結合やらわけのわからないレイアウトであってはNG）
2. Excel側には完了した場合用のフラグ列を用意しておく
3. Puppeteer側は、Excelファイルの読み書きが必要である。
4. Puppeteer側は2.のフラグが無いデータを登録対象として処理を行う。
5. すでに過去に登録済み（社員IDとメアドで判定されてる）で、削除をした場合には新規登録で同じデータはエラーになるので注意が必要なサービスです。

図：入力自体はそれほど難しくなさそうなUI

入力用のExcelファイルについて

今回使用するサービス用のExcelシートは、登録リストと部署コードの2つのシートで構成されています。入力欄はそれぞれウェブサービスの入力欄に対応しています。また、最後の列は登録済みかどうかのフラグになっています。このフラグが空のレコードだけが今回登録する対象になるデータになります。

また、今回のサービスは、部署選択のセレクトボックスが用意されているのですが、各selectboxのoptionにはvalue値がランダムに設定されており、自分の所属部署名（例：Z_111）に対応するselectboxのvalue値をChrome Developer Toolで調べて於いて、部署コードシートに入れてあります。所属部署コードがこのselectboxのvalue値になります。

所属部署は入力規則のドロップダウンになっており、選択するとvlookup関数で対応するselectboxの値が呼ばれるようになっています。この値がPuppeteerにてselectboxを選択する時に、await page.select('対象のelementのID', 選択するvalue)にて、選択させる事が可能になります（部署名のテキストだと具合が悪い為今回はこういった仕組みを使っています）。

図：入力用のExcelシート

図：xlsxモジュールで読み取り配列にした様子

ソースコード

冒頭部分

//使用するモジュール
const puppeteer = require('puppeteer-core');
const prompts = require("prompts");
var fs = require('fs');
const path = require("path");
var shell = require('child_process').exec;
var spawnSync = require('child_process').spawnSync;
const xlsx = require('xlsx');
const Utils = xlsx.utils;

//デスクトップのパスを取得
var dir_home = process.env[process.platform == "win32" ? "USERPROFILE" : "HOME"];
var deskpath = require("path").join(dir_home, "Desktop");

//オープンするURL
var url = "対象のサービスのログインURL";
var useradd = "対象のサービスのユーザ追加ページのURL";

//Excelファイルをロードする
var xlsxfile = deskpath + "//tellist.xlsx";
let workbook = xlsx.readFile(xlsxfile);

//Chromeのパスを取得（ユーザ権限インストール時）
const userHome = process.env[process.platform == "win32" ? "USERPROFILE" : "HOME"];
var kiteipath = "C:\\Program Files (x86)\\Google\\Chrome\\Application\\chrome.exe";
var temppath = path.join(userHome, "AppData\\Local\\Google\\Chrome\\Application\\chrome.exe");

//chrome場所判定
if(fs.existsSync(kiteipath)){
  var chromepath = kiteipath
  console.log("プログラムフォルダにChromeみつかったよ");
}else{
  if(fs.existsSync(temppath)){
    var chromepath = temppath;
    console.log("ユーザディレクトリにChrome見つかったよ");
  }else{
    console.log("chromeのインストールが必要です。");
    //IEを起動してChromeのインストールを促す　　　
    shell('start "" "iexplore" "https://www.google.co.jp/chrome/"')
    return;
  }
}

//プロンプト表示
getprompt();

• 今回はログインページでログイン後にユーザ追加ページへ直接移動してユーザ追加を行いますので、2つのURLを変数に入れてあります。
• xlsxファイルの読み書き用モジュールを追加しています。
• グローバル変数でデスクトップのパスを取得しておきます。
• つづけて、getprompt()を実行してユーザの入力を受付待ちします。
• chromeはいつもの「C:\\Program Files (x86)\\Google\\Chrome\\Application\\chrome.exe」ではなく、「C:\\Users\\ユーザー名\\AppData\\Local\\Google\\Chrome\\Application\\chrome.exe」となるため、ユーザ毎のパスを取得して、chromepathに格納する

プロンプト入力受付部分

//プロンプトを表示
async function getprompt(){
    // 入力を待ち受ける内容
    let question = [
      {
        type: "text",
        name: "id",
        message: "ログインIDを入力してください"
      },
      {
        type: 'password',
        name: "pass",
        message: "パスワードを入力してください"
      }
    ];

    // promptsの起動
    let response =  await prompts(question);

    //回答を取得
    var userid = String(response.id);
    var pw = String(response.pass);

    //main関数を呼び出し
    main(userid,pw);
}

• ユーザIDはメールアドレス、パスワードは文字列型で引き受けます。
• 受付後にmain関数に引き渡して、puppeteerを実行

Puppeteer部分

//ブラウザ操作メイン関数
async function main(userid,pw) {
    const browser = await puppeteer.launch({
      headless: false,
      executablePath: chromepath,
      ignoreDefaultArgs: ["--guest",'--disable-extensions','--start-fullscreen','--incognito',],
      slowMo:100,
    });

  　//pageを定義
    const page = await browser.newPage()
    const navigationPromise = page.waitForNavigation()

    //alertが出たら確認ボタンをクリックする
    page.on('dialog', async dialog => {
      await dialog.accept();
    });

    //ログインページを開く
    await page.goto(url)
    await page.setViewport({ width: 1200, height: 900 })
    await navigationPromise

    //IDとpassでログインする
    await page.type('.ui-page #user_account', userid)
    await page.type('.ui-page #user_password', pw)
    await page.waitForSelector('.ui-page > .content > #login_form > .ui-btn > input')
    await page.click('.ui-page > .content > #login_form > .ui-btn > input')

    //Excelファイルを読み込みフラグの無いデータを連続登録
    //シートを読み込みする
    var array = [];
    var ss = workbook.Sheets["登録リスト"];

    //セルの有効レンジを取得する(ただし空白は存在すると看做される)
    var range = ss["!ref"];

    //範囲情報を数値情報へ変換する
    var dRange = Utils.decode_range(range);

    //セル情報を配列にpushする
    for (let rowIndex = dRange.s.r; rowIndex <= dRange.e.r; rowIndex++) {
      //1行目はスルーする（タイトル行の為）
      if(rowIndex == 0){
        console.log("タイトル行スルー");
        continue;
      }

      //一時配列を用意
      var tempArray = [];

      for (let colIndex = dRange.s.c; colIndex <= dRange.e.c; colIndex++) {
        //アドレス名を取得する
        var address = Utils.encode_cell({ r: rowIndex, c:colIndex });
        var cell = ss[address];

        //空のセル
        if (typeof cell !== "undefined" && typeof cell.v !== "undefined") {
          tempArray.push(cell.v);
        }else{
          tempArray.push("");
        }
      }

      //書き込み用配列にpushする
      array.push(tempArray);
    }

    //取得した配列データを元に登録作業を開始
    var alength = array.length;
    var clength = alength + 1;
    for(var i = 0;i < alength; i++){
      //ユーザ追加画面へ移動
      await Promise.all([
        page.goto(useradd, {waitUntil: "domcontentloaded"}),
        page.waitForNavigation(),
      ]);

      //登録フラグがあったらスルー
      if(array[i][10] == "✔"){
        continue;
      }

      //新規追加ボタンをクリック
      await page.waitForSelector('.content > #form_search #btn_addnew')
      await page.click('.content > #form_search #btn_addnew')
      await navigationPromise

      //入力と選択
      await page.type('#form_user #user_kana_last', array[i][3])  //かな（姓）
      await page.type('#form_user #user_kana_first', array[i][4]) //かな（名）
      await page.type('#form_user #user_name_last', array[i][1])   //氏名（姓）
      await page.type('#form_user #user_name_first', array[i][2])  //氏名（名）
      await page.type('#form_user #employee_code', array[i][0])  //社員番号
      await page.select('.ui-grid-a #org_unit_id', String(array[i][6]))    //所属コード
      await page.type('#form_user #ext_phone_number', String(array[i][7]))  //内線番号
      await page.type('#form_user #email_address', array[i][8])  //メールアドレス
      await page.type('#form_user #user_account', array[i][8])   //ユーザアカウント
      await page.type('#form_user #user_password', array[i][9])  //パスワード
      await page.type('#form_user #user_password2', array[i][9]) //パスワード確認入力

      //追加ボタンをクリック
      await page.waitForSelector('.ui-panel-wrapper #btn_addnew')
      await page.click('.ui-panel-wrapper #btn_addnew')

      //ページ遷移をウェイト
      await navigationPromise
      //2秒一応ウェイトさせる
      await sleep(2000)

      //シートに✔マークを入れる作業(2行目以降からスタート)
      var count = i + 2;
      ss["K" + count] = { t: "s", v: "✔", w: "✔" };
      ss["!ref"] = "A1" + ":K" + clength;
      workbook.Sheets["登録リスト"] = ss
      xlsx.writeFile(workbook, xlsxfile);
    }

    //page.onイベントを削除
    await page.removeAllListeners('dialog');

    //終了メッセージを表示
    const script = `window.alert('処理が完了しました')`;
    await page.addScriptTag({ content: script });

    //ブラウザを閉じる
    await browser.close()
}

• page.onのイベント追加によって、登録時に出てくるalertのOKボタンをdialog.acceptで自動で押しています。
• xlsxモジュールで登録リストシートにあるデータをロードし、まずは二次元配列に変換します。
• 二次元配列化したxlsxのデータを元にループで順番に登録作業を行います。
• 登録後、都度登録リストのK列に✔を加えたデータを上書きでxlsx.writeFileしています。
• フォームに入力時にテキスト型に数値を入れるとエラーが出る箇所が二箇所あるので、Stringで型変換をしておきます。
• 最後に完了メッセージを出す前に、page.onイベントで追加したalertのOKボタンを押すイベントをremoveAllListenersにてイベントを削除しないとメッセージが出ずに閉じてしまいます。

問題点

今回のスクリプトを使うにあたって、現在2つの問題点があります。

• puppeteer 3.2.0は、page.onで追加したイベントリスナーを削除する為にpage.removeListnerをしてもイベント削除ができずまた、page.removeAllListenersをしてもis not a functionとエラーが出てしまう。これは、Puppeteer3.2.0のバグで現在対応中のようですので、puppeteer-coreは3.1.0を利用しましょう（npm i puppeteer-core@3.1.0でインストールする）
• xlsxモジュールは読み書きが出来るのですが、書き込み時にセルの書式等が全てクリアされた状態で書き込まれてしまいます。フォーマットを維持した状態で書き込みたい場合にはxlsx-populateというモジュールを使うと、レイアウトを崩さずに書き込みが可能です。

xlsx-populate-wrapperを使ってみる

xlsxモジュールは、書式を無視して書き込みをしてしまうのと、少々扱いが面倒臭いという事もあって、xlsx-populateおよびそれを更に楽にしたxlsx-populate-wrapperモジュールを使って読み書きをする事で既存のレイアウトや書式を壊さずに書き込みができるのと、座標の読み書きが楽になるので、こちらを使っての方法を試してみました。

今回は読み書きに特化して一番楽だと思われるxlsx-populate-wrapperを使ってのコードに置き換えてみようと思います。使う場合には、npm i xlsx-populate-wrapperでインストールが必要です。

//冒頭部分
const xlsxpop = require("xlsx-populate-wrapper");

//Excelファイルをロードする
var xlsxfile = deskpath + "//tellist.xlsx";
const workbook = new xlsxpop(xlsxfile);

・・・・中略・・・・

//ブラウザ操作メイン関数
async function main(userid,pw) {
    const browser = await puppeteer.launch({
      headless: false,
      executablePath: chromepath,
      ignoreDefaultArgs: ["--guest",'--disable-extensions','--start-fullscreen','--incognito',],
      slowMo:100,
    });

  　//pageを定義
    const page = await browser.newPage()
    const navigationPromise = page.waitForNavigation()

    //alertが出たらの確認ボタンをクリックする
    page.on('dialog', async dialog => {
      await dialog.accept();
    });

    //ログインページを開く
    await page.goto(url)
    await page.setViewport({ width: 1200, height: 900 })
    await navigationPromise

    //IDとpassでログインする
    await page.type('.ui-page #user_account', userid)
    await page.type('.ui-page #user_password', pw)
    await page.waitForSelector('.ui-page > .content > #login_form > .ui-btn > input')
    await page.click('.ui-page > .content > #login_form > .ui-btn > input')

    //Excelファイルを読み込みフラグの無いデータを連続登録
    var jsondata;
    var dlength;

    //ワークブック読み込み
    await workbook.init()
      .then(wb => {
        //ワークシートを読み込み
        jsondata = workbook.getData("登録リスト");
        dlength = jsondata.length;
        return;
      })

    //ループでJSONデータを入力していく
    for(var i = 0;i<dlength;i++){
      //ユーザ追加画面へ移動
      await page.goto(useradd)
      await navigationPromise

      //登録フラグがあったらスルー
      var flgman = jsondata[i]["登録フラグ"];

      if(flgman == undefined){
        //空なので処理続行
      }else{
        //フラグがあるのでスルーする
        continue;
      }

      //新規追加ボタンをクリック
      await page.waitForSelector('.content > #form_search #btn_addnew')
      await page.click('.content > #form_search #btn_addnew')
      await navigationPromise

      //入力と選択
      await page.type('#form_user #user_kana_last', jsondata[i]["かな（姓）"])  //かな（姓）
      await page.type('#form_user #user_kana_first', jsondata[i]["かな（名）"]) //かな（名）
      await page.type('#form_user #user_name_last', jsondata[i]["氏名（姓）"])   //氏名（姓）
      await page.type('#form_user #user_name_first', jsondata[i]["氏名（名）"])  //氏名（名）
      await page.type('#form_user #employee_code', jsondata[i]["従業員番号"])  //社員番号
      await page.select('.ui-grid-a #org_unit_id', String(jsondata[i]["所属部署コード"]))    //所属コード
      await page.type('#form_user #ext_phone_number', String(jsondata[i]["内線番号"]))  //内線番号
      await page.type('#form_user #email_address', jsondata[i]["メールアドレス"])  //メールアドレス
      await page.type('#form_user #user_account', jsondata[i]["メールアドレス"])   //ユーザアカウント
      await page.type('#form_user #user_password', jsondata[i]["パスワード"])  //パスワード
      await page.type('#form_user #user_password2', jsondata[i]["パスワード"]) //パスワード確認入力

      //追加ボタンをクリック
      await page.waitForSelector('.ui-panel-wrapper #btn_addnew')
      await page.click('.ui-panel-wrapper #btn_addnew')

      //ページ遷移をウェイト
      await navigationPromise
      //2秒一応ウェイトさせる
      await sleep(2000)

      //登録フラグに✔を入れる
      jsondata[i]["登録フラグ"] = "✔";
    }

    //ワークブック読み込み
    await workbook.init()
      .then(wb => {
        //ワークシートに書き込み
        workbook.update("登録リスト",jsondata);
        return workbook.commit();
      })
      .catch(error => {
        throw error
      })

    //page.onイベントを削除
    await page.removeAllListeners('dialog');

    //終了メッセージを表示
    const script = `window.alert('処理が完了しました')`;
    await page.addScriptTag({ content: script });

    //ブラウザを閉じる
    await browser.close()
}

• デフォルトでUsedRangeでシートデータを取得してくれるので余計な作業が必要ありません。
• データはJSON形式で取得してくれる。
• JSONのキー（タイトル行）を元にデータを取り出して、順次フォームへ登録していきます。配列と違って１行目データの処理等などは不要です。
• workbook.init()でデータの読み込みとデータの書き出しのそれぞれをやらせます。workbook.initの中ではpromiseで処理されているが故に、その中でpuppeteerのコードを動かそうとするとエラーになるため、切り離してあります。
• jsonデータを一気に書き込みしますが、Range指定なども必要ありません。
• 書き込み時だけはreturnにてworkbook.commit()しないと確定しませんので注意が必要です。

図：こんな感じのJSONで取得してくれるのでとても楽

図：無事にレイアウト崩さずに✔を入れられました。

関連リンク

• 【Node】エクセルを読み込む（ループで回して読み込み）
• Node.js でエクセルファイルを更新する
• ドロップダウン選択からオプションを選択する方法
• puppeteerでダイヤログを扱う方法
• EventEmitterでイベントリスナーを削除する
• TypeError: this.global.page.addListener is not a function with puppeteer@3.2.0 #353
• TypeError: this.global.page.addListener is not a function #5944
• Node.jsでイベントを使用する
• SheetJS/sheetjs
• Node.jsでExcelファイルのread/write
• xlsx-populatenode.jsで簡易データベースとしてExcelファイルを使う
• xlsx-populateで取得した値がややこしいことになってた時に文字列だけを取り出す方法