现时需要开发一个Excel下载功能
后台有一个API，负责接收传入的JSON文件，生成带图片的Excel文件在临时目录（生成Excel使用npm exceljs库），并将文件通过Router返回
前台Client调用后台API，读取文件流生成Excel文件下载
API生成Excel文件代码
const getLinePlanExcelJson = (data) => { // let workBook; //workbook properties var workBook = new exceljs.Workbook(); workBook.creator = 'Esquel LPD Project'; workBook.lastModifiedBy = 'Esquel LPD Project'; workBook.created = new Date(); workBook.modified = new Date(); return new Promise((resolve, reject) => { if (typeof (data.customerCode) === 'undefined' || typeof (data.linePlanProducts) === 'undefined') { reject(new Error('{"Error Message":"incorrect format."}')); } //base var let mc_productStyles = data.productStyles; let mc_productFabrics = data.productFabrics; let mc_productTrims = data.productTrims; //--------------------------- let workSheet_name = "LinePlan"; // create worksheet let workSheet = workBook.addWorksheet(workSheet_name, { // properties: { // tabColor: { argb: 'FFC0000' } // }, // pageSetup: { // paperSize: 50, // orientation: 'landscape' // } }); //setting header title let headerTitleArray = config.LinePlan.columns; let headerColumns = []; for (let headerIndex = 0; headerIndex < headerTitleArray.length; headerIndex++) { headerColumns.push({ header: headerTitleArray[headerIndex].title, key: 'col' + headerIndex, headerTitleArray[headerIndex].width }); } //setting header to worksheet workSheet.columns = headerColumns; //setting auto filter workSheet.autoFilter = { from: 'A1', to: 'AA1' } //---------------------------------------------------- //col index let colIndex = 1; let rowIndex = 1; data.linePlanProducts.map((linePlanProduct) => { //get style data let find_StyleData = _.where(mc_productStyles, { styleID: linePlanProduct.productID }); linePlanProduct.productMaterialConfigs.map((colorway) => { //reset col index colIndex = 1; let newrow = []; //style id--------------------------------------- newrow[colIndex] = linePlanProduct.productID; //style image--------------------------------------- colIndex++; newrow[colIndex] = find_StyleData[0].imageURL ? find_StyleData[0].imageURL : ''; // cell = { v: find_StyleData[0].imageURL, t: 's', l: { Target: find_StyleData[0].imageURL, Tooltip: linePlanProduct.productID } }; // //colorway body fabirc image--------------------------------------- colIndex++; newrow[colIndex] = colorway.PrimaryFabricImageUrl ? colorway.PrimaryFabricImageUrl : ''; // cell = { v: colorway.PrimaryFabricImageUrl, t: 's', l: { Target: colorway.PrimaryFabricImageUrl, Tooltip: colorway.primaryFabricID } }; //matching--------------------------------------- colIndex++; //colorway body fabirc item code--------------------------------------- colIndex++; newrow[colIndex] = colorway.primaryFabricID; //colorway body fabirc info--------------------------------------- let find_FabricData = _.where(mc_productFabrics, { fabricID: colorway.primaryFabricID }); colIndex++; newrow[colIndex] = find_FabricData[0].longDescriptions[1] ? find_FabricData[0].longDescriptions[1] : ''; // //colorway body fabirc content--------------------------------------- colIndex++; newrow[colIndex] = find_FabricData[0].longDescriptions[2] ? find_FabricData[0].longDescriptions[2] : ''; // //fabric width--------------------------------------- colIndex++; newrow[colIndex] = find_FabricData[0].longDescriptions[4] ? find_FabricData[0].longDescriptions[4] : ''; //button colour--------------------------------------- colIndex++; // // let find_trims = _.where(colorway.appliedAuxiliaries, { auxiliaryType: 'T' }); // // let find_fabrics = _.where(colorway.appliedAuxiliaries, { auxiliaryType: 'F' }); // //button1 item code--------------------------------------- colIndex++; newrow[colIndex] = colorway.appliedAuxiliaries[0] ? colorway.appliedAuxiliaries[0].auxiliaryID : ''; //button2 item code--------------------------------------- colIndex++; newrow[colIndex] = colorway.appliedAuxiliaries[1] ? colorway.appliedAuxiliaries[1].auxiliaryID : ''; //button3 item code--------------------------------------- colIndex++; newrow[colIndex] = colorway.appliedAuxiliaries[2] ? colorway.appliedAuxiliaries[2].auxiliaryID : ''; //button4 item code--------------------------------------- colIndex++; newrow[colIndex] = colorway.appliedAuxiliaries[3] ? colorway.appliedAuxiliaries[3].auxiliaryID : ''; // //style fit--------------------------------------- colIndex++; newrow[colIndex] = linePlanProduct.fitName; //style collection--------------------------------------- colIndex++; newrow[colIndex] = linePlanProduct.collectionName ? linePlanProduct.collectionName + ' Collection' : ''; //style gender--------------------------------------- colIndex++; newrow[colIndex] = linePlanProduct.gender; //fabric--------------------------------------- colIndex++; newrow[colIndex] = find_FabricData[0].longDescriptions[0] ? find_FabricData[0].longDescriptions[0] : ''; //style collar--------------------------------------- colIndex++; newrow[colIndex] = linePlanProduct.collarName; //style sleeve--------------------------------------- colIndex++; newrow[colIndex] = linePlanProduct.sleeveName; //style cuff--------------------------------------- colIndex++; newrow[colIndex] = linePlanProduct.cuffName; //style washing--------------------------------------- colIndex++; //style pocket--------------------------------------- colIndex++; newrow[colIndex] = linePlanProduct.pocketName; //colorway body pattern pocket--------------------------------------- colIndex++; newrow[colIndex] = colorway.bodyPattern; //colorway body color name--------------------------------------- colIndex++; //colorway name--------------------------------------- colIndex++; newrow[colIndex] = colorway.colorway; //colorway plu--------------------------------------- colIndex++; newrow[colIndex] = colorway.pluNumber //colorway market--------------------------------------- let marketArray = []; colorway.markets.map((market) => { marketArray.push(market.marketCode); }); colIndex++; newrow[colIndex] = marketArray ? marketArray.join('/') : ''; workSheet.addRow(newrow).commit(); //setting row style // let row = workSheet.lastRow; // row.height = 120; rowIndex++; }); }); //need retry array let all_needProcessCellArray = []; let firsttime_faild_cellArray = []; let secendtime_faild_cellArray = []; //define add image function let fetchImageFun = function (workSheet, cell, CellArray) { return new Promise((resolve, reject) => { if (!cell.value) resolve('no'); // let starttime = new Date(); request.get({ url: cell.value, encoding: null, timeout: 10000 }, function (error, response, body) { if (error) { // let endtime = new Date(); // console.log(cell.address + ' Faild ' + starttime + ' ' + endtime); if (CellArray) CellArray.push(cell); resolve('err'); } if (body) { if (response.statusCode === 500) { console.log('load image error from url:' + cell.value); } else if (response.statusCode === 404) { console.log('can not load image from url:' + cell.value); } //get image let imageObject = workBook.addImage({ buffer: new Buffer(body), extension: response.headers["content-type"] }); //add image to cell let imageCellName = cell.address + ':' + cell.address; workSheet.addImage(imageObject, imageCellName); //clear value cell.value = ''; resolve('ok'); } }); }); } let functionArray = []; //add image to cell workSheet.eachRow({ includeEmpty: true }, function (row, rowNumber) { if (rowNumber > 1) { row.height = 150; row.eachCell({ includeEmpty: true }, function (cell, colNumber) { cell.alignment = { vertical: 'top', horizontal: 'left' }; // cell.border = { // top: { style: 'thin', color: { argb: 'FFFFFF00' } }, // left: { style: 'thin', color: { argb: 'FFFFFF00' } }, // bottom: { style: 'thin', color: { argb: 'FFFFFF00' } }, // right: { style: 'thin', color: { argb: 'FFFFFF00' } } // }; //add image is work if (colNumber === 2 || colNumber === 3) { all_needProcessCellArray.push(cell); } }); } }); all_needProcessCellArray.forEach((getCell) => { functionArray.push(fetchImageFun(workSheet, getCell, firsttime_faild_cellArray)); }); //add function to array //resolve function Promise.all(functionArray) .then((result) => { if (firsttime_faild_cellArray) { firsttime_faild_cellArray.forEach((getCell) => { functionArray.push(fetchImageFun(workSheet, getCell, secendtime_faild_cellArray)); }); //first time retry Promise.all(functionArray) .then((result2) => { if (secendtime_faild_cellArray) { secendtime_faild_cellArray.forEach((getCell) => { functionArray.push(fetchImageFun(workSheet, getCell)); }); //secend time retry Promise.all(functionArray) .then((result2) => { resolve(workBook); }).catch((err3) => { reject(null); }); } else { resolve(workBook); } }).catch((err2) => { reject(null); }); } else { resolve(workBook); } }).catch((err) => { reject(null); }) }); }
API路由代码
let express = require('express'); let genCSV = require("./genCSV.js"); let exceljs = require('exceljs'); let util = require('util'); let fs = require('fs'); let router = express.Router(); //post data to router router.post('/', function (req, res, next) { genCSV.getLinePlanExcelJson(req.body) .then((excelObject) => { // throw new Error('this is test error'); //return to client let fileName = 'LinePlan-' + req.body.linePlanID + '.xlsx'; let filePath = './public/temp/' + fileName; if (fs.existsSync(filePath)) { fs.unlink(filePath); } excelObject.xlsx.writeFile(filePath, { bookType: 'xlsx', bookSST: false, type: 'buffer' }) .then(() => { res.status(200).download(filePath, fileName); }).catch((err) => { throw err }); }) .catch((err) => { res.status(500).end('{"err":"' + err.message + '"}'); }); }); module.exports = router;
前台Client调用代码
fetchMethodExcel: function (apiName, method, requestObject, requestBody, callback) { fetch3(this.apiExport(apiName, requestObject), { // method: method, method: 'POST', headers: { 'Content-Type': 'application/json' }, body: JSON.stringify(requestBody), }) .then((data) => { if (data.status === 500) { throw new Error('Explort LinePlan API Error'); } else if (data.status === 404) { throw new Error('Can Not Found Explort LinePlan API'); } return data.blob(); }) .then((blob) => { let a = document.createElement('a'); let url = window.URL.createObjectURL(blob); let filename = 'LinePlan-' + requestBody.linePlanID + '.xlsx'; a.href = url; a.download = filename; a.click(); window.URL.revokeObjectURL(url); callback(); }) .catch(err => { callback(err); }); }
运行结果是
但是下载的文件却是无法打开的，一直说是文件有内容有问题，尝试恢复很久都没有返回。
解除锁定后，可以正常打开文件了
