我要回家想一想~

首先要讀取 xlsx 需要 js library 支援

找到了 sheetjs 可以使用

然後使用 eval 方式把它讀入到 postman (ref3)

再來就是 Tests 的時候使用 sheetjs 讀取 response

注意這邊要使用 pm.response.stream 的方式 (ref4)

如果是使用 pm.response.test (postman 轉成字串會因為 encoding 方式導致資料錯誤 ref5) 會遇到 "Cannot find file [Content_Types].xml in zip"

// load js
eval(postman.getGlobalVariable("module:sheetjs"));
var workbook = XLSX.read(pm.response.stream, {
    type: 'buffer'
});
workbook.SheetNames.forEach(function(sheetName) {
    var sheet = workbook.Sheets[sheetName];
    /* loop through every cell manually */
    var range = XLSX.utils.decode_range(sheet['!ref']); // get the range
    for(var R = range.s.r; R <= range.e.r; ++R) {
        for(var C = range.s.c; C <= range.e.c; ++C) {
            /* find the cell object */
            console.log('Row : ' + R);
            console.log('Column : ' + C);
            var cellref = XLSX.utils.encode_cell({c:C, r:R}); // construct A1 reference for cell
            if(!sheet[cellref]) continue; // if cell doesn't exist, move on
            var cell = sheet[cellref];
            console.log(cell.v);
        }
    }
    // Here is your object
    var XL_row_object = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[sheetName]);
    var json_object = JSON.stringify(XL_row_object);
    console.log(sheetName);
});

讀取成功後就可以驗證啦~

完整 sample: https://drive.google.com/file/d/1XMWSgyGqQ758TdX9AqdryP8A-w0lJLpm/view?usp=sharing

ref1: https://docs.sheetjs.com/

ref2: https://learning.postman.com/docs/writing-scripts/script-references/test-examples/

ref3: https://blog.postman.com/api-testing-tips-from-a-postman-professional/

ref4: https://github.com/postmanlabs/postman-app-support/issues/2024

ref5: https://github.com/SheetJS/sheetjs/issues/1206