0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

現実世界を動かす技術をシェアしよう！～身近なものづくりから最先端テクノロジーまで大募集！～
@kensukemachii(ken)

Typescript

0
Posted at

function main(workbook: ExcelScript.Workbook) {
const sheet = workbook.getActiveWorksheet();

// ==============================
// 設定
// ==============================
const HEADER_ROW_INDEX = 0; // 1行目
const FIRST_DATA_ROW_INDEX = 1; // 2行目

const PLANNED_START_COLUMN = 6; // G列
const PLANNED_END_COLUMN = 7; // H列
const ACTUAL_START_COLUMN = 8; // I列
const ACTUAL_END_COLUMN = 9; // J列

const TIMELINE_START_COLUMN = 10; // K列

const PLANNED_COLOR = "#9DC3E6"; // 予定：水色
const ACTUAL_COLOR = "#70AD47"; // 実績：緑
const DELAY_COLOR = "#FF6B6B"; // 遅延：赤
const WEEKEND_COLOR = "#F2F2F2"; // 土日：薄い灰色
const HEADER_COLOR = "#D9EAF7";

// 実績開始日があり、実績終了日が空欄の場合、
// 今日までを実績期間として表示する。
const DRAW_OPEN_ACTUAL_TO_TODAY = true;

// 異常に長い期間が入力されていた場合の安全制限。
const MAX_TIMELINE_DAYS = 1095; // 約3年

// ==============================
// 使用範囲と最終行を取得
// ==============================
const usedRange = sheet.getUsedRange(true);

if (!usedRange) {
throw new Error("ワークシートにデータがありません。");
}

const lastRowExclusive =
usedRange.getRowIndex() + usedRange.getRowCount();

const dataRowCount =
lastRowExclusive - FIRST_DATA_ROW_INDEX;

if (dataRowCount <= 0) {
throw new Error("2行目以降にタスクデータがありません。");
}

// G:Jをまとめて取得
const dateRange = sheet.getRangeByIndexes(
FIRST_DATA_ROW_INDEX,
PLANNED_START_COLUMN,
dataRowCount,
4
);

const dateValues = dateRange.getValues();
const todaySerial = getTodayExcelSerial();

interface TaskDates {
plannedStart: number | null;
plannedEnd: number | null;
actualStart: number | null;
actualEnd: number | null;
}

const tasks: TaskDates[] = [];

let minimumDate = Number.POSITIVE_INFINITY;
let maximumDate = Number.NEGATIVE_INFINITY;

// ==============================
// 日付をExcelシリアル値へ変換
// ==============================
for (let row = 0; row < dateValues.length; row++) {
const plannedStart = normalizeExcelDate(dateValues[row][0]);
const plannedEnd = normalizeExcelDate(dateValues[row][1]);
const actualStart = normalizeExcelDate(dateValues[row][2]);

let actualEnd = normalizeExcelDate(dateValues[row][3]);

if (
  DRAW_OPEN_ACTUAL_TO_TODAY &&
  actualStart !== null &&
  actualEnd === null
) {
  actualEnd = Math.max(actualStart, todaySerial);
}

const task: TaskDates = {
  plannedStart,
  plannedEnd,
  actualStart,
  actualEnd
};

tasks.push(task);

const dates = [
  plannedStart,
  plannedEnd,
  actualStart,
  actualEnd
];

for (const date of dates) {
  if (date !== null) {
    minimumDate = Math.min(minimumDate, date);
    maximumDate = Math.max(maximumDate, date);
  }
}

}

if (
!Number.isFinite(minimumDate) ||
!Number.isFinite(maximumDate)
) {
throw new Error(
"G列からJ列に有効な日付がありません。Excelの日付形式で入力してください。"
);
}

const timelineDays =
maximumDate - minimumDate + 1;

if (timelineDays > MAX_TIMELINE_DAYS) {
throw new Error(
表示期間が${timelineDays}日あります。 +
日付の入力誤りがないか確認してください。
);
}

// ==============================
// ガントチャート範囲を初期化
// ==============================
const timelineArea = sheet.getRangeByIndexes(
HEADER_ROW_INDEX,
TIMELINE_START_COLUMN,
lastRowExclusive - HEADER_ROW_INDEX,
timelineDays
);

// K列以降の今回使用する範囲を消去
timelineArea.clear(ExcelScript.ClearApplyTo.all);

// ==============================
// 日付ヘッダーを作成
// ==============================
const headerValues: string[] = [];

for (let day = 0; day < timelineDays; day++) {
headerValues.push(
excelSerialToLabel(minimumDate + day)
);
}

const headerRange = sheet.getRangeByIndexes(
HEADER_ROW_INDEX,
TIMELINE_START_COLUMN,
1,
timelineDays
);

headerRange.setValues([headerValues]);

headerRange
.getFormat()
.getFill()
.setColor(HEADER_COLOR);

headerRange
.getFormat()
.getFont()
.setBold(true);

headerRange
.getFormat()
.setHorizontalAlignment(
ExcelScript.HorizontalAlignment.center
);

headerRange
.getFormat()
.setVerticalAlignment(
ExcelScript.VerticalAlignment.center
);

// 日付を縦向きに表示
headerRange
.getFormat()
.setTextOrientation(90);

headerRange
.getFormat()
.setRowHeight(60);

// 1日分の列幅
timelineArea
.getFormat()
.setColumnWidth(24);

// ==============================
// 土日を薄い灰色にする
// ==============================
for (let day = 0; day < timelineDays; day++) {
const serial = minimumDate + day;
const dayOfWeek = getDayOfWeek(serial);

// 0 = 日曜日、6 = 土曜日
if (dayOfWeek === 0 || dayOfWeek === 6) {
  const weekendRange = sheet.getRangeByIndexes(
    FIRST_DATA_ROW_INDEX,
    TIMELINE_START_COLUMN + day,
    dataRowCount,
    1
  );

  weekendRange
    .getFormat()
    .getFill()
    .setColor(WEEKEND_COLOR);
}

}

// ==============================
// 各タスクのバーを描画
// ==============================
for (let row = 0; row < tasks.length; row++) {
const sheetRow = FIRST_DATA_ROW_INDEX + row;
const task = tasks[row];

// ------------------------------
// 予定期間：水色
// ------------------------------
if (
  task.plannedStart !== null &&
  task.plannedEnd !== null &&
  task.plannedStart <= task.plannedEnd
) {
  paintDateRange(
    sheet,
    sheetRow,
    TIMELINE_START_COLUMN,
    minimumDate,
    task.plannedStart,
    task.plannedEnd,
    PLANNED_COLOR
  );
}

// ------------------------------
// 実績期間
// ------------------------------
if (
  task.actualStart !== null &&
  task.actualEnd !== null &&
  task.actualStart <= task.actualEnd
) {
  /*
   * 予定終了日を超えていない実績：
   * 緑色
   */
  let normalActualEnd = task.actualEnd;

  if (task.plannedEnd !== null) {
    normalActualEnd = Math.min(
      task.actualEnd,
      task.plannedEnd
    );
  }

  if (task.actualStart <= normalActualEnd) {
    paintDateRange(
      sheet,
      sheetRow,
      TIMELINE_START_COLUMN,
      minimumDate,
      task.actualStart,
      normalActualEnd,
      ACTUAL_COLOR
    );
  }

  /*
   * 予定終了日を超えた実績：
   * 赤色
   */
  if (
    task.plannedEnd !== null &&
    task.actualEnd > task.plannedEnd
  ) {
    const delayStart = Math.max(
      task.actualStart,
      task.plannedEnd + 1
    );

    paintDateRange(
      sheet,
      sheetRow,
      TIMELINE_START_COLUMN,
      minimumDate,
      delayStart,
      task.actualEnd,
      DELAY_COLOR
    );
  }

  /*
   * 予定終了日がない場合は、
   * 実績期間全体を緑色にする。
   */
  if (task.plannedEnd === null) {
    paintDateRange(
      sheet,
      sheetRow,
      TIMELINE_START_COLUMN,
      minimumDate,
      task.actualStart,
      task.actualEnd,
      ACTUAL_COLOR
    );
  }
}

}

console.log(
ガントチャートを作成しました。 +
期間：${excelSerialToLabel(minimumDate)} ～ +
${excelSerialToLabel(maximumDate)}
);
}

/**

  • 指定した日付範囲を塗りつぶす。
    */
    function paintDateRange(
    sheet: ExcelScript.Worksheet,
    sheetRow: number,
    timelineStartColumn: number,
    minimumDate: number,
    startDate: number,
    endDate: number,
    color: string
    ): void {
    if (startDate > endDate) {
    return;
    }

const startOffset = startDate - minimumDate;
const length = endDate - startDate + 1;

const barRange = sheet.getRangeByIndexes(
sheetRow,
timelineStartColumn + startOffset,
1,
length
);

barRange
.getFormat()
.getFill()
.setColor(color);
}

/**

  • Excelセルの値を日付シリアル値へ変換する。
  • Excelの日付セルは通常numberとして取得される。
  • yyyy/mm/dd、yyyy-mm-dd形式の文字列にも対応。
    */
    function normalizeExcelDate(
    value: string | number | boolean
    ): number | null {
    if (
    typeof value === "number" &&
    Number.isFinite(value)
    ) {
    return Math.floor(value);
    }

if (typeof value !== "string") {
return null;
}

const text = value.trim();

if (text === "") {
return null;
}

const match = text.match(
/^(\d{4})/-/-$/
);

if (match) {
const year = Number(match[1]);
const month = Number(match[2]);
const day = Number(match[3]);

const utcMilliseconds = Date.UTC(
  year,
  month - 1,
  day
);

return Math.floor(
  utcMilliseconds / 86400000 + 25569
);

}

const parsedDate = new Date(text);

if (Number.isNaN(parsedDate.getTime())) {
return null;
}

const utcMilliseconds = Date.UTC(
parsedDate.getFullYear(),
parsedDate.getMonth(),
parsedDate.getDate()
);

return Math.floor(
utcMilliseconds / 86400000 + 25569
);
}

/**

  • Excelシリアル値を「月/日」形式へ変換する。
    */
    function excelSerialToLabel(serial: number): string {
    const milliseconds =
    (serial - 25569) * 86400000;

const date = new Date(milliseconds);

const year = date.getUTCFullYear();
const month = date.getUTCMonth() + 1;
const day = date.getUTCDate();

return ${year}/${month}/${day};
}

/**

  • Excelシリアル値から曜日を取得する。
  • 0：日曜日、6：土曜日
    */
    function getDayOfWeek(serial: number): number {
    const milliseconds =
    (serial - 25569) * 86400000;

return new Date(milliseconds).getUTCDay();
}

/**

  • 今日の日付をExcelシリアル値で取得する。
    */
    function getTodayExcelSerial(): number {
    const now = new Date();

const utcMilliseconds = Date.UTC(
now.getUTCFullYear(),
now.getUTCMonth(),
now.getUTCDate()
);

return Math.floor(
utcMilliseconds / 86400000 + 25569
);
}

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?