stu43005 · March 27, 2025 01:17 · Mar 27, 2025
diff --git a/nginx_logs.ts b/nginx_logs.ts
@@ -0,0 +1,62 @@
+function main(workbook: ExcelScript.Workbook) {
+    // 取得使用中的儲存格和工作表。
+    let originalSheet = workbook.getActiveWorksheet();
+
+    // 取得原始資料範圍
+    let dataRange = originalSheet.getRange("A1:D" + originalSheet.getUsedRange().getRowCount());
+    let data = dataRange.getValues();
+
+    let requiredDates = ["20250327", "20250326", "20250325", "20250324", "20250323",
+        "20250322", "20250321", "20250320", "20250319",
+        "20250318", "20250317"];
+
+    let worksheetName = "Converted Data";
+    let oldSheet = workbook.getWorksheet(worksheetName);
+    if (oldSheet) oldSheet.delete();
+
+    let newSheet = workbook.addWorksheet(worksheetName);
+
+    // 寫入新表格的表頭
+    newSheet.getRange("A1").setValue("domain");
+    for (let i = 0; i < requiredDates.length; i++) {
+        newSheet.getCell(0, i + 1).setValue(requiredDates[i]);
+    }
+
+    // 逐行處理原始資料
+    let outputData: (string | number | boolean)[][] = [];
+    data: for (let i = 1; i < data.length; i++) {
+        let domain = data[i][2]; // 取得 domain
+        let size = +data[i][0]; // 取得 size
+        let date = data[i][3];
+        let dateIndex = requiredDates.indexOf(`${date}`);
+
+        if (!domain) continue;
+
+        for (let j = 0; j < outputData.length; j++) {
+            if (outputData[j][0] === domain) {
+                if (dateIndex > -1) {
+                    outputData[j][dateIndex + 1] = size;
+                }
+                continue data;
+            }
+        }
+
+        let rowData: (string | number | boolean)[] = [domain]; // 預備輸出行的初始值
+        for (let j = 0; j < requiredDates.length; j++) {
+            rowData[j + 1] = "";
+        }
+        if (dateIndex > -1) {
+            rowData[dateIndex + 1] = size;
+        }
+        outputData.push(rowData);
+    }
+
+
+    // 將轉換後的資料寫入新表格
+    if (outputData.length > 0) {
+        console.log("rows", outputData.length);
+        console.log("columns", outputData[0].length);
+        console.log(outputData);
+        newSheet.getRangeByIndexes(1, 0, outputData.length, outputData[0].length).setValues(outputData);
+    }
+}