今回はTypeScriptを用いたExcel 操作を自動化する例をご紹介します。このスクリプトは、ワークブックからデータを抽出し、それを処理してシートに書き出すというものです。ここでは、スクリプトの詳細な説明とその動作について説明します。TypeScriptを使用してExcel操作を自動化する方法を習得してみましょう！それでは、始めましょう！
目次
はじめに
Excelはデータ分析と操作のための強力なツールですが、反復作業は退屈で時間がかかることがあります。ここで、スクリプトによる自動化の出番です。TypeScriptとExcel Script APIを使用することで、これらの作業を自動化し、時間を節約し、エラーを減らすことができます。
前提条件
このチュートリアルでは以下の前提知識が必要となります
- TypeScriptの基本知識
- Excelの基本的な機能に関する知識
スクリプト概要
以下は、今回説明するTypeScriptのコードです
function main(workbook: ExcelScript.Workbook) {
// アクティブなワークシートを取得
let selectedSheet = workbook.getActiveWorksheet();
// 最終行数取得
let columnARange = selectedSheet.getRange("A:A");
let lastRow = selectedSheet.getRange('A:A').getUsedRange().getLastCell().getRowIndex();
console.log("A列の文字が入っている行数: " + lastRow);
// 最終列数取得
let lastColumn = selectedSheet.getRange('1:1').getUsedRange().getLastCell().getColumnIndex() + 1;
console.log("1行目の文字が入っている列数: " + lastColumn);
// A列の商品名を取得
let rangeA = selectedSheet.getRangeByIndexes(1, 0, lastRow, 1);
let itemValues: (string | number | boolean)[][] = rangeA.getValues();
let itemName: string[] = [];
for (let i = 0; i < itemValues.length; i++) {
for (let j = 0; j < itemValues[i].length; j++) {
itemName.push(String(itemValues[i][j])); // 配列の要素を文字列に変換して追加
}
}
// 行の値を格納
let range = selectedSheet.getRangeByIndexes(1, 0, lastRow, lastColumn);
let vals = range.getValues();
console.log(vals);
// 特定文字の複製数を予めセッティング
const listOfItem: { [key: string]: number } = {
A: 1,
E: 2,
I: 3
};
let numOfListOfItemPast = 1;
for (let i = 0; i < lastRow; ++i) {
//該当文字があればその数、なければ1行
let numOfListOfItem = 1;
for (const key in listOfItem) {
const result = itemName[i].includes(key);
if (result) {
numOfListOfItem = listOfItem[key];
break;
}
}
for (let j = 0; j < numOfListOfItem; ++j) {
for(let k = 0; k < lastColumn; ++k){
// numOfListOfItem_old + j:開始行
let selectedCell = selectedSheet.getCell(numOfListOfItemPast + j, k);
selectedCell.setValue(vals[i][k]);
}
}
numOfListOfItemPast += numOfListOfItem;
}
console.log(1);
}
詳細な説明
アクティブなワークシートの取得
スクリプトは、ワークブック内のアクティブなワークシートを取得することから始まります
let selectedSheet = workbook.getActiveWorksheet();この行は、スクリプトが現在アクティブなシート上で動作することを意味しています
最終行と最終列の取得
次に、シート内のデータが存在する最終行と最終列を取得します
let columnARange = selectedSheet.getRange("A:A");
let lastRow = selectedSheet.getRange('A:A').getUsedRange().getLastCell().getRowIndex();
console.log("A列の文字が入っている行数: " + lastRow);
let lastColumn = selectedSheet.getRange('1:1').getUsedRange().getLastCell().getColumnIndex() + 1;
console.log("1行目の文字が入っている列数: " + lastColumn);これらの行は、getUsedRangeメソッドを使用して、A列と1行目のデータの範囲を特定します。これは、後続のデータ処理ステップに不可欠です。
A列からのデータ取得
次に、A列からすべての値を取得します
let rangeA = selectedSheet.getRangeByIndexes(1, 0, lastRow, 1);
let itemValues: (string | number | boolean)[][] = rangeA.getValues();
let itemName: string[] = [];
for (let i = 0; i < itemValues.length; i++) {
for (let j = 0; j < itemValues[i].length; j++) {
itemName.push(String(itemValues[i][j])); // 配列の要素を文字列に変換して追加
}
}このセグメントは、値を文字列配列itemNameに変換して、操作しやすくしています
特定の条件に基づく行の処理
次に、行を処理するための条件を定義します
const listOfItem: { [key: string]: number } = {
A: 1,
E: 2,
I: 3
};listOfItemオブジェクトは、特定の文字がアイテム名に含まれている場合に、その行を複製する回数を定義しています
ワークシートへのデータ書き込み
最後に、データをワークシートに書き出し、必要に応じて行を複製します
let numOfListOfItemPast = 1;
for (let i = 0; i < lastRow; ++i) {
let numOfListOfItem = 1;
for (const key in listOfItem) {
const result = itemName[i].includes(key);
if (result) {
numOfListOfItem = listOfItem[key];
break;
}
}
for (let j = 0; j < numOfListOfItem; ++j) {
for (let k = 0; k < lastColumn; ++k) {
let selectedCell = selectedSheet.getCell(numOfListOfItemPast + j, k);
selectedCell.setValue(vals[i][k]);
}
}
numOfListOfItemPast += numOfListOfItem;
}このループは、事前定義された条件に基づいて行を複製し、シートに書き出しています
まとめ
今回ご紹介したスクリプトは、特定行を指定数文複製するという複雑なタスクを自動化することを実現しています。このような自動化ツールを活用することで、Excelでの作業効率を大幅に向上させることができます。
付録
TypeScriptを使用してExcelを自動化することで、効率的なデータ管理と操作の世界が広がります。ぜひスクリプト作成を楽しんでください！