注: 以下の翻訳の正確性は検証されていません。AIPを利用して英語版の原文から機械的に翻訳されたものです。
TypescriptでExcelファイルを解析して処理するにはどうすればよいですか？
このコードは、ExcelJSライブラリを使用してTypescriptでExcelファイルを解析し、処理します。Excelファイルを読み込み、ワークブックを処理し、ヘッダーを検証し、データをアンピボットして新しいデータオブジェクトを作成します。
Copied!1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227import { OntologyEditFunction, Timestamp } from "@foundry/functions-api"; import { Uuid } from "@foundry/functions-utils"; import { Objects, Upload, SampleData } from "@foundry/ontology-api"; import { Workbook } from "exceljs"; interface DataRow { target: string; unit: string; property: string; value: number; uploaded_at: Timestamp; upload_key: string; } export class ParseExcel { @OntologyEditFunction() public async addData(upload: Upload): Promise<void> { try { const arrayBuffer = await this.getArrayBufferFromAttachment(upload); // Create a workbook from the arrayBuffer // arrayBufferからWorkbookを作成 const workbook = new Workbook(); await workbook.xlsx.load(arrayBuffer); const meltedData = this.processWorkbook(workbook, upload.uploadedAt!, upload.uploadKey!); const version = this.generateVersion(upload.title!, upload.uploadedAt!); meltedData.forEach(row => { this.createNewData(row, version); }); upload.status = 'Success' // Update Upload Version // アップロードバージョンの更新 upload.version = version; } catch (error: any) { upload.status = 'Failed'; upload.errorMessage = error.message; } } /** * Validate header to make sure it has all necessary headers * ヘッダーが必要な全てのヘッダーを持っているか確認 */ private validateHeader(header: string[]): string | null { const target = 'target'; const yIntercept = 'Y_intercept'; if (header[1] !== target || header[2] !== yIntercept) { // console.log(header) return 'The header must start with target and Y_intercept in the first two columns.'; } const regex = /^[a-zA-Z0-9_]+$/; for (const columnName of header) { if (!regex.test(columnName)) { return `The header contains invalid characters. Only English letters and underscores are allowed. Invalid column name: ${columnName}`; } } return null; } private createNewData(row: DataRow, version: string) { const newData = Objects.create()._sampleData(Uuid.random()); newData.title = `${row.target} ${row.unit} ${row.property} Data`; newData.value = row.value; newData.target = row.target; newData.unit = row.unit; newData.version = version; newData.property_ = row.property; newData.uploadKey = row.upload_key; newData.propertyKey = this.generatePropertyKey(row.unit, row.property); return newData; } private generatePropertyKey(unit: string, property: string){ return `Sample_Property_Key_${unit}_${property}` } private generateVersion(unit: string, uploaded_at: Timestamp): string { // Format the timestamp as YYYYMMDD_HHMMSS // タイムスタンプをYYYYMMDD_HHMMSS形式にフォーマット const formattedDate = uploaded_at.toISOString().slice(0, 10).replace(/-/g, ""); const formattedTime = uploaded_at.toISOString().slice(11, 19).replace(/:/g, ""); // Create the version string using the unit and the formatted timestamp // unitとフォーマットされたタイムスタンプを使ってバージョン文字列を作成 const version = `${unit}_${formattedDate}_${formattedTime}`; return version; } /** * Reads ArrayBuffer from the attachment in the given Upload object. * @param upload The Upload object containing the attachment. * @returns ArrayBuffer * アップロードオブジェクト内の添付ファイルからArrayBufferを読み取る */ private async getArrayBufferFromAttachment(upload: Upload): Promise<ArrayBuffer> { const attachmentBlob = await upload.attachment!.readAsync(); return attachmentBlob.arrayBuffer(); } /** * Processes the workbook and returns a 2D array in DataFrame format. * @param workbook The Workbook object to process. * @param uploadedAt タイムスタンプ * @param uploadKey アップロードキー * @returns DataRow[] * ワークブックを処理し、DataFrame形式の2次元配列を返す */ private processWorkbook(workbook: Workbook, uploadedAt: Timestamp, uploadKey: string): DataRow[] { const allMeltedData: DataRow[] = []; workbook.eachSheet((sheet) => { const sheetUnit = sheet.name; // Process header // ヘッダーを処理 let headerProcessed = false; let sheetHeader: string[] = []; const sheetData: any[][] = []; // Process rows of each sheet // 各シートの行を処理 sheet.eachRow((row, rowIndex) => { if (row.values) { const rowValues = row.values as (string | number)[]; // If rowIndex is 1, handle it as a header // rowIndexが1の場合、ヘッダーとして処理 if (rowIndex === 1) { sheetHeader = rowValues.filter(value => value !== null && typeof value === 'string') as string[]; sheetHeader.unshift('unit'); const validationResult = this.validateHeader(sheetHeader); if (validationResult) { throw new Error(validationResult); } headerProcessed = true; } else { const newRowValues = this.processRow(rowValues, sheetHeader); newRowValues.unshift(sheetUnit); sheetData.push(newRowValues); } } }); // If the header was not processed in the rows, add it manually // 行内でヘッダーが処理されていない場合、手動で追加 if (!headerProcessed) { sheetHeader.unshift('unit'); const validationResult = this.validateHeader(sheetHeader); if (validationResult) { throw new Error(validationResult); } } const meltedData = this.unpivotData(sheetData, sheetUnit, uploadedAt, uploadKey, sheetHeader); allMeltedData.push(...meltedData); }); return allMeltedData; } /** * Processes a row from the workbook and returns the new row data. * @param rowValues The values of the row to process. * @param header The header array. * @returns any[] * ワークブックの行を処理し、新しい行データを返す */ private processRow(rowValues: (string | number)[], header: (string | number)[]): any[] { const newRowValues = Array(header.length).fill(0); // Fill with 0 rowValues.forEach((value, index) => { if (index > 0 && header[index - 1] !== undefined) { newRowValues[index - 1] = value; } }); return newRowValues.map((value, index) => { if (header[index] !== 'target' && header[index] !== 'unit') { return typeof value === 'string' ? parseFloat(value) : value; } return value; }); } /** * Unpivots the data and returns an array of MeltedDataRow objects. * @param data The 2D array in DataFrame format to unpivot. * @param unit The unit of the sheet. * @param uploadedAt タイムスタンプ * @param uploadKey アップロードキー * @param header ヘッダー * @returns DataRow[] * データをアンピボットし、MeltedDataRowオブジェクトの配列を返す */ private unpivotData(data: any[][], unit: string, uploadedAt: Timestamp, uploadKey: string, header: string[]): DataRow[] { const idVars = ["unit", "target"]; const meltedData: DataRow[] = []; data.forEach(row => { const idValues = row.slice(0, idVars.length); row.slice(idVars.length).forEach((value, index) => { if (value !== 0) { const newRow: DataRow = { target: idValues[1], unit: unit, property: header[idVars.length + index] as string, // Use header to get the property name value: value, uploaded_at: uploadedAt, upload_key: uploadKey, }; meltedData.push(newRow); } }); }); return meltedData; } }
excel, typescript, functions on objects, file upload, dataframe