用Node.js 将bugzilla上的bug列表导入到excel表格里
公司用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;
//因为bugzilla用的长连接，不用连接池发送十个请求左右就会出错，https就要用支持https的连接池
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发送get请求，返回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 ID的集合通过getFunc里的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 = ""; //生成评论字符串，用\r\n替换后在excel表格里才能换行
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); //生成excel文档，写入数据，返回promise对象
}).then(function() {
console.log("Generate xlsx file successfully. File name is " + colors.cyan(fileName)); //结束，告诉使用者生成的文件名
}).catch(function(err) {
console.error(err); //捕获前面所有promise对象的出错信息
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);
}
})
})
}
});
贴两个测试地址，也可以百度搜索bugzilla首页，也能找到能测试的地址：
- http://chinajr.com/buglist.cgi?product=即安分单&query_format=advanced&resolution=—
- https://bugzilla.mozilla.org/buglist.cgi?order=Importance&resolution=—&query_format=advanced&product=Add-on%20SDK
生成的excel效果：
