Help us understand the problem. What is going on with this article?

Node.jsでExcelファイルのread/write

More than 3 years have passed since last update.

Node.jsでExcelファイルのread/write

by Kazunori-Kimura
1 / 10

概要

  • Node.js で Excelファイルが扱える xlsx を紹介します
  • Excelファイルの読み書きができます
  • PCにExcel入れて無くても大丈夫

なんで Node.js?

  • VBAだとMacで動かない
  • VBAのエディタ使いづらい
  • コードのバージョン管理しづらい

xlsxのよさげなポイント


サンプル


workbook, worksheetの読み込み

const XLSX = require("xlsx");
const Utils = XLSX.utils; // XLSX.utilsのalias
// Workbookの読み込み
const book = XLSX.readFile("test.xlsx");
// Sheet1読み込み
const sheet1 = book.Sheets["Sheet1"];

セル範囲の取得

// セルの範囲
const range = sheet1["!ref"]; //B2:B4
// セル範囲を数値表現に変換
const decodeRange = Utils.decode_range(range);
/* => {
        s: { c: 1, r: 1 },
        e: { c: 1, r: 3 }
      } */

セル範囲をくるくる回して値を取得

// Sheet1に記載されている数値を合計する
let value = 0;
for (let rowIndex = decodeRange.s.r; rowIndex <= decodeRange.e.r; rowIndex++) {
  for (let colIndex = decodeRange.s.c; colIndex <= decodeRange.e.c; colIndex++) {
    // 数値表現をセルアドレス ("A1"など) に変換
    const address = Utils.encode_cell({ r: rowIndex, c:colIndex });
    const cell = sheet1[address];
    if (typeof cell !== "undefined" && typeof cell.v !== "undefined") {
      value += cell.v; //cell: { t: 'n', v: 100, w: '100' }
    }
  }
}
console.log(`合計= ${value}`);

書き込み

// Sheet2読み込み
const sheet2 = book.Sheets["Sheet2"];
// セル更新
sheet2["C2"] = { t: "s", v: "hoge", w: "hoge" };
// 範囲の更新を忘れずに (30分ハマった)
sheet2["!ref"] = "B2:C2";
book.Sheets["Sheet2"] = sheet2;
// ファイルを書き込み
XLSX.writeFile(book, "test.xlsx");

サンプルレポジトリ

https://github.com/Kazunori-Kimura/node-xlsx-sample

Kazunori-Kimura
フリーランスのプログラマーです。JavaScript好き。 C#, Javaもやれます。 お仕事ください。
https://kazunori-kimura.github.io/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした