【Excel】Excelでテスト用のCSVデータを作成する

概要

開発中のアプリケーションでテストを行うときに、テストで使用するテーブルデータが必要になりました。
アプリでは CSV によるデータのインポートが可能なので、テストにマッチするそれっぽい CSV を作成したいというニーズがありました。

VSCode では Python の Jupyter Notebook を簡単に動かせるため、
取り急ぎ Python で CSV 作成を行うための DevContainer を作成して配布していましたが、
もう少しカジュアルに使用できるものも欲しいので Excel でも同じようなものを作成してみました。

ポイント

記事タイトルにもあるように、これは CSVファイル を作成するものではなく、CSVデータ をシート上に作成するものです。

Office Scripts ではファイルの保存はできないため、ファイル作成まで行う場合は Power Automate 経由でこのスクリプトを実行するなどの工夫が必要になります。
(main の戻り値にブックを指定して外側とやりとりするイメージでしょうか…あまり詳しくないです)

Excelのブックを開き、このスクリプトを実行することでシートにデータを流し込むスクリプトになります。

外部のCSVライブラリが使えれば高精度でテキストファイルとして直接作成することができますが、
Office Scripts では使用できないので、Excel の「CSV形式で保存」に頼るのが安全だと思われます。
シートに展開する以上、Excel の最大行数という制約があります。

スクリプト

① Excel 制御
データ定義した DataUnit 分のデータシートを作成します。
(CSV形式での保存を手動で行うため、複数シートを同時に作る意味は少し薄れました…)

② ジェネレート関数群
ランダムな値を作成するための関数群です。ここは Github Copilot に丸投げしました。

③ データ定義
名前、レコード数、カラム構成、行ごとの値の定義をしています。
この例では、user_id でリレーション可能な user_data と transaction_data を作成しています。

interface DataUnit {
  name: string;
  rowsCount: number;
  columnNames: string[];
  rowParams: (index: number) => string[];
}

// -------------------- ① Excel 制御 (main) --------------------

function main(workbook: ExcelScript.Workbook): void {

  const dataUnits: DataUnit[] = [userData, transactionData];

  for (const dataUnit of dataUnits) {
    // シートの作成
    const name = dataUnit.name;
    const csvSheet = workbook.getWorksheet(name) || workbook.addWorksheet(name);
    csvSheet.getRange().clear();

    // ヘッダーの作成
    const headerRange = csvSheet
      .getRange('A1')
      .getResizedRange(0, dataUnit.columnNames.length - 1);
    headerRange.setValues([dataUnit.columnNames]);

    // ボディの作成
    const values: string[][] = Array.from({ length: dataUnit.rowsCount }).map((_ , index) => {
      return dataUnit.rowParams(index + 1)
    })

    const bodyRange = csvSheet
      .getRange('A2')
      .getResizedRange(values.length - 1, values[0].length - 1);
    bodyRange.setValues(values);
  }
}

// -------------------- ② ジェネレート関数群 --------------------

function generateRandomString(length: number): string {
  const characters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
  let result = '';
  for (let i = 0; i < length; i++) {
    const randomIndex = Math.floor(Math.random() * characters.length);
    result += characters[randomIndex];
  }
  return result;
}

function getRandomNumberBetween(min: number, max: number): string {
  return (Math.floor(Math.random() * (max - min + 1)) + min).toString();
}

function getRandomElement(arr: string[]): string {
  const randomIndex = Math.floor(Math.random() * arr.length);
  return arr[randomIndex];
}

function getRandomDateBetween(min: string, max: string): string {
  const minDate = new Date(min);
  const maxDate = new Date(max);
  const randomTime = minDate.getTime() + Math.random() * (maxDate.getTime() - minDate.getTime());
  const randomDate = new Date(randomTime);

  const year = randomDate.getFullYear();
  const month = String(randomDate.getMonth() + 1).padStart(2, '0');
  const day = String(randomDate.getDate()).padStart(2, '0');

  return `${year}-${month}-${day}`;
}

// -------------------- ③ データ定義 --------------------

// 取引データユニット
const transactionData: DataUnit = {
  name: 'transaction_data',
  rowsCount: 100000,
  columnNames: ['id', 'date', 'user_id', 'type', 'item_name', 'price', 'quantity' ],
  rowParams: (index: number) => [
    index.toString(),
    getRandomDateBetween('2020-01-01', '2025-01-01'),
    getRandomNumberBetween(1, 100000),
    getRandomElement(['buy', 'sell']),
    generateRandomString(10),
    getRandomNumberBetween(1000, 1000000),
    getRandomNumberBetween(1, 10),
  ]
}

// ユーザーデータユニット
const userData: DataUnit = {
  name: 'user_data',
  rowsCount: 100000,
  columnNames: ['id', 'user_id', 'last_name', 'first_name', 'gender', 'age', 'status'],
  rowParams: (index: number) => [
      index.toString(),
      index.toString(),
      generateRandomString(10),
      generateRandomString(10),
      getRandomElement(['male', 'female', 'other', 'none']),
      getRandomNumberBetween(20, 60),
      getRandomElement(['a', 'b', 'c', 'd', 'e', 'f', 'g']),
    ]
};

実行結果

まとめ

楽しかったです。