用Node.js 將bugzilla上的bug列表匯入到excel表格裡

標籤：error   bugs   ica   pre   turn   項目   數組   when   標識   

公司用bugzilla管理產品bug，最近用Node.js做了個東西，方便能夠把bug的相關資訊匯入到excel表格裡，好做後續的管理分析。

直接貼代碼，寫上注釋好了。轉載請註明出處。

var request = require("request")var cheerio = require("cheerio");var Excel = require(‘exceljs‘);var colors = require("colors");var program = require("commander");var readlineSync = require(‘readline-sync‘);var Agent = require(‘agentkeepalive‘);var ProgressBar = require(‘progress‘);var fs = require(‘fs‘);//需要用到這些模組
var putError = console.log;global.console.error = function(error) {putError(colors.red("[Error]: " + error));}//只是為了輸出錯誤資訊比較顯眼而又有統一標識
program.on(‘--help‘, function() {console.log(‘  Examples:\n‘);console.log(‘    node app.js -u "http://..." -p Name‘);})
//commander模組自動有協助資訊，覺得加個例子會比較清楚program.option(‘-u, --url ‘, ‘Url of bug list to generate.‘).option(‘-s, --specifyName ‘, ‘Specify string of file name.‘).parse(process.argv);//on 和 option 要在 parse 前，讀入命令參數
var fileName = "BugList-" + (new Date()).toLocaleDateString() +  (program.specifyName? "-"+program.specifyName : "");//-s 參數為了儲存檔案比較好區分，非必需
var url = "";if(!program.url) {var count = 0;while(url == "") {if(++count > 3) {            　　　　　　program.outputHelp();process.exit(1);}url = readlineSync.question(‘Please input the url of bug list: ‘).trim().replace(/^"(.*)"$/g, "$1");}}//-u 參數必需，如果三次為空白就列印協助資訊並退出else {url = program.url;}url = decodeURIComponent(url);url = encodeURI(url);//url地址的轉換，比如從瀏覽器直接複製帶中文地址會出錯
var urlIndex = url.indexOf("/bugzilla3/");if(urlIndex != -1) {var root = url.slice(0, urlIndex+11); //公司bugzilla放在這個目錄下，額外做個判斷}else{var root = url.replace(/^((https?:\/\/)?[^\/]*\/).*/ig, "$1"); //取網域名稱    　　root = (/^http:\/\//ig.test(root)? root: "http://"+root); //如果沒有http://，就加上它}var bugUrl = root + "show_bug.cgi?ctype=xml&id="; //每個bug的id加上這個地址，就是顯示bug資訊，以xml文檔顯示，好讀資訊Agent = (root.toLowerCase().indexOf("https://") != -1)? Agent.HttpsAgent: Agent; //https就要用支援https的位址集區，因為bugzilla用的長串連，不用位址集區會出錯var keepaliveAgent = new Agent({maxSockets: 100,maxFreeSockets: 10,timeout: 60000,freeSocketKeepAliveTimeout: 30000});
//位址集區配置var option = {    agent: keepaliveAgent,    headers: {"User-Agent": "NodeJS", Host: url.replace(/^((https?:\/\/)?([^\/]*)\/).*/g, "$3")},url: url};
//request的配置
//用最下面寫好的getFunc發送請求，返回promise對象getFunc(option, function(url, $){var bugs = new Array();var td = $("table.bz_buglist tr td.bz_id_column a");td.each(function(key) {bugs.push(td.eq(key).text());})if(bugs.length > 0) { //擷取bug的ID 列表並初始化進度條console.log("\nTotal number of bugs: " + bugs.length);global.bar = new ProgressBar(‘Getting Bugs [:bar] :percent | ETA: :etas | :current/:total‘, {    complete: "-",    incomplete: " ",    width: 25,clear: false,    total: bugs.length,  });}else {console.error("No bugs can be found.");process.exit(1);}return bugs; //bugs 這個值通過resolve函數傳遞給then裡面的函數}).then(function(bugs) {var done = 0;　　　　//用map對ID數組做每個bug 資訊的取回，每個請求返回的都是一個promise對象，這些promise對象組成map返回數組的項當作Promise.all的參數，當
　　　　//裡面所有的promise對象都成功之後，Promise.all返回的promise對象就算是都resolve了
return Promise.all(bugs.map(function(eachBug, index) {option.url = bugUrl + eachBug;var promiseGetOne = getFunc(option, function(url, $) {var oneInfo = new Object(); //用cheerio取需要的資訊oneInfo.url = url.replace(/ctype=xml&/ig, "");oneInfo.id = $("bug_id").text();oneInfo.summary = $("short_desc").text();oneInfo.reporter = $("reporter").text();oneInfo.product = $("product").text();oneInfo.component = $("component").text();oneInfo.version = $("version").text();oneInfo.status = $("bug_status").text();oneInfo.priority = $("priority").text();oneInfo.security = $("bug_security").text();oneInfo.assign = $("assigned_to").text();oneInfo.comment = new Array();var comments = $("long_desc"); //第一條評論當作bug描述comments.each(function(key) {var who = comments.eq(key).find("who").text();var when = comments.eq(key).find("bug_when").text();when = when.replace(/([^\s]+)\s.*$/g, "$1");var desc = comments.eq(key).find("thetext").text();if(key == 0 && who == oneInfo.reporter) {oneInfo.detail = desc;return true;}oneInfo.comment.push({‘who‘: who, ‘when‘: when, ‘desc‘: desc});})return oneInfo;})promiseGetOne.then(function() {done ++;bar.tick(); //更新進度條if(done == bugs.length) {console.log("\n");}})return promiseGetOne;}))}).then(function(bugLists) {var workbook = new Excel.Workbook(); //建立excel文檔    　　var productNum = 0;for(var i in bugLists) {bugInfo = bugLists[i];        var sheet = workbook.getWorksheet(bugInfo.product); //根據項目，如果沒有工作表，就建立一個    if(sheet === undefined) {    sheet = workbook.addWorksheet(bugInfo.product);            productNum ++;        }        try {    sheet.getColumn("id"); //如果沒有標題列，就添加標題列    }        catch(error) {            sheet.columns = [            { header: ‘Bug ID‘, key: ‘id‘ },            { header: ‘Summary‘, key: ‘summary‘, width: 35 },        { header: ‘Bug Detail‘, key: ‘detail‘, width: 75 },            { header: ‘Priority‘, key: ‘priority‘, width: 8 },        { header: ‘Version‘, key: ‘version‘, width: 15 },        { header: ‘Status‘, key: ‘status‘, width: 15 },        { header: ‘Component‘, key: ‘component‘, width: 15 },        { header: ‘Comments‘, key: ‘comment‘, width: 60 },        { header: ‘Assign To‘, key: ‘assign‘, width: 20 },        { header: ‘Reporter‘, key: ‘reporter‘, width: 20 },        ];        }var comment = "";for(var j in bugInfo.comment) {comment += bugInfo.comment[j].who + " (" + bugInfo.comment[j].when + " ):\r\n";comment += bugInfo.comment[j].desc.replace(/\n/gm, "\r\n") + "\r\n";comment += "-------------------------------------------------------\r\n"}sheet.addRow({ //每個bug添加一行id: {text: bugInfo.id, hyperlink: bugInfo.url},summary: bugInfo.summary,detail: bugInfo.detail.replace(/\n/gm, "\r\n"),priority: bugInfo.priority,version: bugInfo.version,status: bugInfo.status,component: bugInfo.component,comment: comment,assign: bugInfo.assign,reporter: bugInfo.reporter,});        sheet.eachRow(function(Row, rowNum) { //設定對齊等    Row.eachCell(function(Cell, cellNum) {    if(rowNum == 1)    Cell.alignment = {vertical: ‘middle‘, horizontal: ‘center‘, size: 25, wrapText: true}    else    Cell.alignment = {vertical: ‘top‘, horizontal: ‘left‘, wrapText: true}    })    })}    fileName = ((productNum > 1)? "" : bugInfo.product+"-") + fileName + ".xlsx";    var files = fs.readdirSync("./");    var postfix = 1;    while(files.indexOf(fileName) != -1) {  //如果檔案重名，就在後面添加(1)等數字，直至沒有重名，否則會直接覆蓋掉重名檔案        fileName = fileName.replace(/(\(\d+\))?\.xlsx$/g, "("+ (postfix++) +").xlsx");        if(postfix > 99) {            console.warn("It may occur somethins wrong.");            break;        }    }return workbook.xlsx.writeFile(fileName);}).then(function() {console.log("Generate xlsx file successfully. File name is " + colors.cyan(fileName)); //結束，告訴使用者產生的檔案名稱}).catch(function(err) {console.error(err);process.exit(1);})function getFunc(getOption, parseFunc) {return new Promise(function(resolve, reject) {request.get(getOption, function(error, response, body) {if(!error && response.statusCode == 200) {var $ = cheerio.load(body);var result = parseFunc(getOption.url, $);resolve(result);}else {reject(error);}})})}

貼兩個測試地址：
1. http://chinajr.com/buglist.cgi?product=%E5%8D%B3%E5%AE%89%E5%88%86%E5%8D%95&query_format=advanced&resolution=---
2. https://bugzilla.mozilla.org/buglist.cgi?order=Importance&resolution=---&query_format=advanced&product=Add-on%20SDK

產生的文檔：

用Node.js 將bugzilla上的bug列表匯入到excel表格裡