使用Java+Excel统计禅道上问题处理情况

背景：部署在服务器上的禅道，查看测试报告模块不正常，无法看细节。因需要，需每日记录禅道问题处理情况，特做此脚本，每日下班前运行。
下图为每日BUG记录，每周、每月、一个版本周期的bug处理情况同样做法：

图有点拙，勿喷。
共两个步骤点：
1、连接禅道数据库并封装查询数据的函数
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.junit.Test;

import com.jxcell.CellException;
import com.jxcell.RangeRef;
import com.jxcell.View;

public class BuddyWatchBug {

private static final String URL="jdbc:mysql://47.93.124.73:3306/zentao";
private static final String NAME="buddymanage";
private static final String PASSWORD="buddy";
int sqlResult;

public void sqlresult() throws Exception{
//1.加载驱动程序
Class.forName("com.mysql.jdbc.Driver");
//2.获得数据库的连接
Connection conn = DriverManager.getConnection(URL, NAME, PASSWORD);
//3.通过数据库的连接操作数据库，实现增删改查
Statement stmt = conn.createStatement();
String sqlBuddyWatchReturn = "SELECT title,steps,openedDate,STATUS,resolution,closedDate FROM `zt_bug` WHERE product=2 AND module=12"; //要执行的SQL
ResultSet rs = stmt.executeQuery(sqlBuddyWatchReturn);//创建数据对象
while (rs.next()){
String sqlTitle = rs.getString(1);
String sqlSteps=rs.getString(2);
// String sqlopendDate=""+rs.getDate(3);
String sqlStatus=""+rs.getString(4);
String sqlResolution=""+rs.getString(5);
//String sqlClosed=""+rs.getString(6);
//System.out.print("-------------------------");
System.out.println(sqlTitle);
System.out.println(sqlSteps);
}
rs.close();
stmt.close();
conn.close();
// return sqlResult;
}

public void writeExcel(){
SimpleDateFormat dateFormater = new SimpleDateFormat("yyyy-MM-dd");
Date date=new Date(0);
SimpleDateFormat dateFm = new SimpleDateFormat("EEEE");
dateFm.format(date);
String name=""+dateFormater.format(date)+dateFm.format(date);

View m_view = new View();
RangeRef newRange = null;
m_view.getLock();
//标题 setTextAsValue(行，列，值)；
try {
m_view.setTextAsValue(1,2,"问题");
m_view.setTextAsValue(1,3,"问题信息");
m_view.setTextAsValue(1,4,"建立时间");
m_view.setTextAsValue(1,5,"状态（关闭、未关闭）");
m_view.setTextAsValue(1,6,"关闭时间");




//excel写出路径
String sheetname="F:\GITZentao\buddy-doc\测试\"+name+".xls";
m_view.write(sheetname);
System.out.println("end");
}
catch (Exception e) {
System.out.println(e.getMessage());
}
finally
{
m_view.releaseLock();
}

}

@Test
public void testsql(){
try {
sqlresult();

} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

2、使用jxcell写入表格
import java.awt.*;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.jxcell.*;

public class chartFormatDayToDay
{
public static void main(String args[])
{
//sqlzentaoClosed("1","3","'2017-12-13 00:00:00'","'2017-12-25 00:00:00'");
/* startDate :起始时间，格式：'2017-12-13 00:00:00'
endDate:结束时间，格式 ：'2017-12-25 00:00:00'*/
String productType="1";
String AndroidmoduleType="3";
String IOSmoduleType="4";
String ServermoduleType="6";
String startDate ="'2017-12-27 00:00:00'";
String endDate="'2017-12-28 :00:00'";

View m_view = new View();

newZentaoSql mysqldata=new newZentaoSql();
/* product=1 代表是成长玩伴APP
module 3代表是Android,4代表IOS，6代表Server*/

RangeRef newRange = null;
try {
m_view.getLock();
//标题 setTextAsValue(行，列，值)；
m_view.setTextAsValue(1,2,"新增");
m_view.setTextAsValue(1,3,"已解决(不包括不予解决)");
m_view.setTextAsValue(1,4,"关闭");
m_view.setTextAsValue(1,5,"未解决");


String sql=mysqldata.sqlzentaoAdd(productType, AndroidmoduleType, startDate, endDate);

m_view.setTextAsValue(2,2,""+mysqldata.sqlresult(sql));//安卓新增
m_view.setTextAsValue(2,3,""+mysqldata.sqlresult(mysqldata.sqlzentaoResol(productType, AndroidmoduleType, startDate, endDate)));//安卓解决
m_view.setTextAsValue(2,4,""+mysqldata.sqlresult(mysqldata.sqlzentaoClosed(productType, AndroidmoduleType, startDate, endDate)));//安卓关闭
m_view.setTextAsValue(2,5,""+mysqldata.sqlresult(mysqldata.sqlNotCloseAndroidToday));//安卓未解决

m_view.setTextAsValue(3,2,""+mysqldata.sqlresult(mysqldata.sqlzentaoAdd(productType, IOSmoduleType, startDate, endDate)));//IOS新增
m_view.setTextAsValue(3,3,""+mysqldata.sqlresult(mysqldata.sqlzentaoResol(productType, IOSmoduleType, startDate, endDate)));//IOS解决
m_view.setTextAsValue(3,4,""+mysqldata.sqlresult(mysqldata.sqlzentaoClosed(productType, IOSmoduleType, startDate, endDate)));//IOS关闭
m_view.setTextAsValue(3,5,""+mysqldata.sqlresult(mysqldata.sqlNotCloseIOSToday));//IOS未解决

m_view.setTextAsValue(4,2,""+mysqldata.sqlresult(mysqldata.sqlzentaoAdd(productType, ServermoduleType, startDate, endDate)));//Server新增
m_view.setTextAsValue(4,3,""+mysqldata.sqlresult(mysqldata.sqlzentaoResol(productType, ServermoduleType, startDate, endDate)));//Server解决
m_view.setTextAsValue(4,4,""+mysqldata.sqlresult(mysqldata.sqlzentaoClosed(productType, ServermoduleType, startDate, endDate)));//Server关闭
m_view.setTextAsValue(4,5,""+mysqldata.sqlresult(mysqldata.sqlNotCloseServerToday));//Server未解决

/* System.out.println("an"+mysqldata.sqlresult(mysqldata.sqlUnclosedAnd));
System.out.println("an"+mysqldata.sqlresult(mysqldata.sqlUnclosedIOS));
System.out.println("an"+mysqldata.sqlresult(mysqldata.sqlUnclosedServer));*/

//设置公式
m_view.setFormula(5, 2, "SUM(C3:C5)");
//选中单元格区域
m_view.setSelection("C6:F6");
//编辑复制 向右复制
m_view.editCopyRight();


//分项
m_view.setTextAsValue(2,1,"安卓");
m_view.setTextAsValue(3,1,"IOS");
m_view.setTextAsValue(4,1,"Server");
m_view.setTextAsValue(5,1,"总计");

//----------------------------------------------------------------------------------
//绘图区坐标addChart（左上列x，左上行y，右下列x，右下行y）
ChartShape chart = m_view.addChart(0, 9.1, 7, 24.4);
// chart.setChartType(ChartShape.TypePie);
//图标形式
//chart.setChartType(ChartShape.TypeLine);
// chart.setChartType(ChartShape.TypeBar);
/*
TypeBar:横向柱状图
TypePie:饼状图
TypeLine:线状图
TypeArea:面积图
TypeDoughnut:圈图
TypeScatter:线点图
TypeBubble:泡状图
*/

//设置连接区域
chart.setLinkRange("Sheet1!$C$2", false);

//添加第0个系列
chart.addSeries();
chart.setSeriesName(0, "Sheet1!$C$2"); //系列名字
chart.setSeriesYValueFormula(0, "Sheet1!$C$3:$C$6");//系列值
chart.setCategoryFormula("Sheet1!$B$3:$B$6");//系列分类
//添加第1个系列
chart.addSeries();
chart.setSeriesName(1, "Sheet1!$D$2");
chart.setSeriesYValueFormula(1, "Sheet1!$D$3:$D$6");
//添加第2个系列
chart.addSeries();
chart.setSeriesName(2, "Sheet1!$E$2");
chart.setSeriesYValueFormula(2, "Sheet1!$E$3:$E$6");
//添加第3个系列
chart.addSeries();
chart.setSeriesName(3, "Sheet1!$F$2");
chart.setSeriesYValueFormula(3, "Sheet1!$F$3:$F$6");
/*
//添加第4个系列
chart.addSeries();
chart.setSeriesName(4, "Sheet1!$G$2");
chart.setSeriesYValueFormula(4, "Sheet1!$G$3:$G$7");
//添加第5个系列
chart.addSeries();
chart.setSeriesName(5, "Sheet1!$H$2");
chart.setSeriesYValueFormula(5, "Sheet1!$H$3:$H$7");
//添加第6个系列
chart.addSeries();
chart.setSeriesName(6, "Sheet1!$I$2");
chart.setSeriesYValueFormula(5, "Sheet1!$I$3:$I$7");
//添加第4个系列
chart.addSeries();
chart.setSeriesName(4, "Sheet1!$G$2");
chart.setSeriesYValueFormula(4, "Sheet1!$G$3:$G$6");*/
// chart.getChart().validateData();

//设置横坐标标题
chart.setAxisTitle(ChartShape.XAxis, 0, "bug情况分类");
//设置纵坐标标题
chart.setAxisTitle(ChartShape.YAxis, 0, "bug数量");

//设置图表样式
ChartFormat cf = chart.getChartFormat();
//设置背景色
cf.setPattern((short)1);
cf.setPatternFG(Color.LIGHT_GRAY.getRGB());
chart.setChartFormat(cf);
//设置绘图区颜色
cf = chart.getPlotFormat();
cf.setPattern((short)1);
cf.setPatternFG(new Color(255, 255, 255).getRGB());
chart.setPlotFormat(cf);

//设置横坐标文字大小
cf = chart.getAxisFormat(ChartShape.XAxis, 0);
cf.setFontSizeInPoints(8.5);
chart.setAxisFormat(ChartShape.XAxis, 0, cf);

//设置纵坐标文字大小
cf = chart.getAxisFormat(ChartShape.YAxis, 0);
cf.setFontSizeInPoints(8.5);
chart.setAxisFormat(ChartShape.YAxis, 0, cf);

//设置图标内标线样式
cf = chart.getSeriesFormat(0);//第0个
cf.setLineStyle((short)1);//设置线条样式
cf.setLineWeight(3*20);//边框线条宽度
cf.setLineColor((new Color(69, 255, 128)).getRGB());//边框线颜色
cf.setMarkerAuto(false);//自动标记
cf.setMarkerStyle((short)0);
chart.setSeriesFormat(0, cf);//设置系列格式

cf = chart.getSeriesFormat(1);//第1个
cf.setLineStyle((short)1);
cf.setLineWeight(3*20);
cf.setLineColor((new Color(255, 2, 255)).getRGB());
cf.setMarkerAuto(false);
cf.setMarkerStyle((short)0);
chart.setSeriesFormat(1, cf);

cf = chart.getSeriesFormat(2);//第2个
cf.setLineStyle((short)1);
cf.setLineWeight(3*20);
cf.setLineColor((new Color(255, 25, 100)).getRGB());
cf.setMarkerAuto(false);
cf.setMarkerStyle((short)0);
chart.setSeriesFormat(2, cf);

cf = chart.getSeriesFormat(3);//第3个
cf.setLineStyle((short)1);
cf.setLineWeight(3*20);
cf.setLineColor((new Color(99, 255, 255)).getRGB());
cf.setMarkerAuto(false);
cf.setMarkerStyle((short)0);
chart.setSeriesFormat(3, cf);

cf = chart.getSeriesFormat(4);//第4个
cf.setLineStyle((short)1);
cf.setLineWeight(3*20);
cf.setLineColor((new Color(99, 55, 255)).getRGB());
cf.setMarkerAuto(false);
cf.setMarkerStyle((short)0);
chart.setSeriesFormat(3, cf);

//主格网
cf = chart.getMajorGridFormat(ChartShape.YAxis, 0);
cf.setLineStyle((short)2);//线宽
cf.setLineColor((new Color(255, 0, 0)).getRGB());//颜色
cf.setLineAuto();
chart.setMajorGridFormat(ChartShape.YAxis, 0, cf);

//图利位置
chart.setLegendPosition(ChartFormat.LegendPlacementRight);

//图利样式
cf = chart.getLegendFormat();
cf.setFontBold(true);
cf.setFontSizeInPoints(10);
chart.setLegendFormat(cf);

//excel写出路径
String sheetname="F:\GITZentao\buddy-doc\测试\禅道问题情况（APP）\版本问题情况\"+"成长玩伴APPV2.0.1(2017-12-28).xls";
m_view.write(sheetname);
System.out.println("end");
}
catch (Exception e) {
System.out.println(e.getMessage());
}
finally
{
m_view.releaseLock();
}
}
}