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

JScriptでExcelファイルを編集する

More than 1 year has passed since last update.

概要

JScriptでExcelファイルを編集する機会があったのでメモ。
値の変更などは基本的にはExcel VBAと同じだが、定数的なのが使えないので数字で直接指定することになるのでそこで詰まらないようにまとめておく。
(家に実行環境が無いので未検証)

Excelファイル読み込み、シートを開く、編集準備

var ExcelApp = new ActiveXObject("Excel.Application");
ExcelApp.Visible = true;
ExcelApp.DisplayAlerts = false;
var WB = ExcelApp.Workbooks.Open("[Excelファイルのパス]");
var WS = WB.Worksheets(1);
WS = WB.Worksheets("シート名");

JavaScript でエクセル触ってみたけど | nmm実験室

値の書き換え

// Cells(行, 列)
WS.Cells(1,1).Value = 1;
// 日付は/区切りで
WS.Cells(1,1).Value = "2018/11/26";

Valueは省略してもいい。

色の扱い

24bitの整数値で指定する。16進数では"BBGGRR"。parseInt()を使うと楽。
RGBの順番で指定したほうがわかりやすいので関数を作って対応。

// BGRで指定
var red = parseInt("0000FF", 16);

// RGBから変換
function Convert_RGB(r, g, b){
  var color;
  color += r;
  color += g << 8;
  color += b << 16;
  return color;
}

背景色

// 背景色を黄色に
WS.Cells(1,1).Interior.Color = ConvertRGB(255, 255, 0);

Interior.Color property (Excel) | Microsoft Docs

罫線

// 四方に普通の線を引く
WS.Cells(1,1).Borders.LineStyle = 1;
// 下にドットを引く
WS.Cells(1,1).Borders(9).LineStyle = -4118;

罫線
Borders object (Excel) | Microsoft Docs
位置
xlBordersIndex enumeration (Excel) | Microsoft Docs
スタイル
xlLineStyle enumeration (Excel) | Microsoft Docs

応用

forで回して配列に設定した背景色を適用する

var r = Convert_RGB(255,   0,   0);
var g = Convert_RGB(  0, 255,   0);
var b = Convert_RGB(  0,   0, 255);
var colors = [r,g,b];
for (var i=0; i < 15; i++) {
  WS.Cells(1,i+1).Interior.Color = colors[i % 3];
}
Why do not you register as a user and use Qiita more conveniently?
  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
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