result =stat.executeQuery("select top 5 * from CI_V3R3_C01.dbo.T_DImax where times<='2011-05-09' and uname ='BESA' order by times asc"); System.out.println("----------------------------------------------------------"); int i=0; while(result.next()) { i++;
System.out.println(String.valueOf(i)+" "+result.getString(1)+" "+result.getString(3)+" "+result.getString(2)+""+result.getString(4)); } System.out.println("----------------------------------------------------------"); } catch (Exception e){ e.printStackTrace(); } } /** * 获得查询条件 * @param used * @param day * @param startday * @param endday */ public void getDateTime(String used, String day, String startday, String endday){ IReaderXml xml = new ReaderXml(); List list = xml.getPramXml();//执行DI值插入数据库 SimpleDateFormat form = new SimpleDateFormat("yyyyMMddhhmmss"); Date dd = new Date(); String times = form.format(dd);// 获得当前日期 if(f==0){ if (used.equals("time")) { SimpleDateFormat form1 = new SimpleDateFormat("hhmmss"); Date di = new Date(); String datetime = form1.format(di); String[] start = startday.split("-"); String[] end = endday.split("-");
* 生成图片
*
* @param zu
* @param defaultcategorydataset
*/
@SuppressWarnings("deprecation")
public JFreeChart doReportImg(String zu,
DefaultCategoryDataset defaultcategorydataset) {
// 解决中文乱码
StandardChartTheme theme = new StandardChartTheme("unicode");
theme.setExtraLargeFont(new Font("宋体", Font.PLAIN, 20));
theme.setLargeFont(new Font("宋体", Font.PLAIN, 14));
theme.setRegularFont(new Font("宋体", Font.PLAIN, 12));
ChartFactory.setChartTheme(theme);
@SuppressWarnings("unused")
HttpServletRequest request = null;
JFreeChart jfreechart = ChartFactory.createLineChart("" + zu + "",
"日期", "值", defaultcategorydataset, PlotOrientation.VERTICAL,
true, true, false);
// Font.BOLD, 20)));//解决中文乱码
jfreechart.setBackgroundPaint(Color.white);// 图片背景色
CategoryPlot categoryplot = (CategoryPlot) jfreechart.getPlot();
categoryplot.setBackgroundPaint(Color.lightGray);// 折线图背景色
categoryplot.setRangeGridlinePaint(Color.white);// 线条颜色 // 折线点显示具体数据
LineAndShapeRenderer renderer = (LineAndShapeRenderer) categoryplot
.getRenderer();
DecimalFormat decimalformat1 = new DecimalFormat("##.##");// 数据点显示数据值的格式
renderer.setItemLabelGenerator(new StandardCategoryItemLabelGenerator(
"{2}", decimalformat1));
// 上面这句是设置数据项标签的生成器
renderer.setItemLabelsVisible(true);// 设置项标签显示
renderer.setBaseItemLabelsVisible(true);// 基本项标签显示 NumberAxis numberaxis = (NumberAxis) categoryplot.getRangeAxis();
numberaxis.setStandardTickUnits(NumberAxis.createIntegerTickUnits());
numberaxis.setAutoRangeIncludesZero(true);
// 获得renderer 注意这里是下嗍造型到lineandshaperenderer！！
LineAndShapeRenderer lineandshaperenderer = (LineAndShapeRenderer) categoryplot
.getRenderer();
lineandshaperenderer.setShapesVisible(true); // （即数据点）可见
// setSeriesStroke(1)设置点与点之间为实线
lineandshaperenderer.setSeriesStroke(1, new BasicStroke(2.0F, 1, 1,
1.0F, new float[] { 10F, 6F }, 0.0F)); // ，这里是虚线，默认是直线
// double a = this.doMax();
ValueAxis rangeAxis = categoryplot.getRangeAxis();
// pageNo=pageNo+10;
int page = (int) SqlConn.pageNo;// 找到最大的值
int page1 = (int) SqlConn.pageNo1;// 找到最小值
page = (int) (page / 0.9) + 1;
page1 = (int) (page1 * 0.9);
rangeAxis.setUpperBound(page);
rangeAxis.setLowerBound(page1);
SqlConn a = new SqlConn();
a.pageNo=0.0;
a.pageNo1=0.0;
a.groupSum=0.0;
MaxBig b= new MaxBig();
b.pageNo=0.0;
b.pageNo1=0.0;
b.i=0;
b.j=0;
// groupSum = 0.0;
// pageNo = 0.0;
// pageNo1 = 0.0;
// i = 0; return jfreechart;
}
@SuppressWarnings({ "unchecked", "rawtypes" })
public Map getMap() throws BiffException, IOException, DocumentException{
// HashMap mapGroup = new HashMap();
getDts();
HashMap mapPerson = new HashMap();
File file1 = new File("resource/zu.xls");// 读取组的xls文件
InputStream is1 = new FileInputStream(file1);
jxl.Workbook rwb1 = Workbook.getWorkbook(is1);
Sheet st1 = rwb1.getSheet(0);// 得到excel中的第1张表
for (int i = 1; i < st1.getRows(); i++) {// 判断有哪些组添加到hashmap中
Cell cgroup = st1.getCell(1, i);
String group = cgroup.getContents();
Cell cperson = st1.getCell(0, i);
String person = cperson.getContents();
// mapGroup.put(group, person);
mapPerson.put(person, group);
}
return mapPerson;
}
@SuppressWarnings({ "unchecked", "rawtypes" })
public HashMap getAddList(Map mapPerson,Set sPerson){
String ugroup="";
HashMap map = new HashMap();
for (Iterator it = sPerson.iterator(); it.hasNext();) {
String person =it.next().toString();
String group =mapPerson.get(person).toString();
map.put(group, person);
}
return map;
}
* 生成excel文件
*/
public void getExportExcel(List resultList) {
try {
conn.getJdbc();
SimpleDateFormat form = new SimpleDateFormat("yyyy-MM-dd");
Date dd = new Date(System.currentTimeMillis());
String times = form.format(dd);
File file = new File("images/Export" + times + ".xls");
// 将数据写入excel文档
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(file);// 存放路径
jxl.write.WritableSheet ws = wwb.createSheet("Di值各组显示", 10);// 创建excel文档
ws.addCell(new jxl.write.Label(0, 1, "责任人项目组"));// 字段
ws.addCell(new jxl.write.Label(1, 1, "汇总"));
for (int k = 0; k < resultList.size(); k++) {// 循环写入
Result r = (Result) resultList.get(k);
ws.addCell(new jxl.write.Label(0, k + 2, "" + r.getName()));
ws.addCell(new jxl.write.Label(1, k + 2, "" + r.getDi()));
}
wwb.write();
// 关闭Excel工作薄对象
wwb.close();
} catch (Exception e1) {
File fi = new File("../images");
fi.mkdir();
e1.printStackTrace();
} }
try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); Connection con = DriverManager.getConnection("jdbc:sqlserver://localhost","sa","sa"); System.out.println("建立连接成功!"); Statement stat=con.createStatement();
result =stat.executeQuery("select top 5 * from CI_V3R3_C01.dbo.T_DImax where times<='2011-05-09' and uname ='BESA' order by times asc"); System.out.println("----------------------------------------------------------"); int i=0;
while(result.next())
{
i++;
System.out.println(String.valueOf(i)+" "+result.getString(1)+" "+result.getString(3)+" "+result.getString(2)+""+result.getString(4)); }
System.out.println("----------------------------------------------------------"); }
catch (Exception e){
e.printStackTrace();
}
} /**
* 获得查询条件
* @param used
* @param day
* @param startday
* @param endday
*/
public void getDateTime(String used, String day, String startday,
String endday){
IReaderXml xml = new ReaderXml();
List list = xml.getPramXml();//执行DI值插入数据库
SimpleDateFormat form = new SimpleDateFormat("yyyyMMddhhmmss");
Date dd = new Date();
String times = form.format(dd);// 获得当前日期
if(f==0){
if (used.equals("time")) {
SimpleDateFormat form1 = new SimpleDateFormat("hhmmss");
Date di = new Date();
String datetime = form1.format(di);
String[] start = startday.split("-");
String[] end = endday.split("-");
startday = "";
endday = "";
for (String str2 : start) { startday += str2;
}
for (String end3 : end) {
endday += end3;
}
if(endday.length()<9){
}
startday += datetime;
endday += datetime;
}
f++;
}
Main.Create(used, day, startday, endday,times,list);
}
* 输出一个Xml文件
*/
public void CreateXml(List list) { try {
for(int j =0;j<list.size();j++){
Result r = (Result) list.get(j);
// 创建根节点 list;
Element root = new Element("di").setText(r.getDi()+"");
// 根节点添加到文档中；
Document Doc = new Document(root);
// 此处 for 循环可替换成 遍历 数据库表的结果集操作;
// 给父节点list添加user子节点;
XMLOutputter XMLOut = new XMLOutputter();
// 输出 user.xml 文件；
XMLOut.output(Doc, new FileOutputStream("xml/"+r.getName()+".xml"));
}
} catch (Exception e) {
File fi = new File("../xml");
fi.mkdir();
e.printStackTrace();
System.out.println("文件没创建，无法生成xml文件!");
}
}
/**
* 读取pram xml
*/
public List getPramXml() {
String index = "";
String meter = "";
String meter1 = "";
String meter2 = "";
String index1 = "";
String index2 = "";
HashMap mapGroup = new HashMap();
HashMap mapPerson = new HashMap();
List lists = new ArrayList();
try {
File xmlFile = new File("resource/pram.xml");// 读取条件pram.xml
SAXReader reader1 = new SAXReader();
org.dom4j.Document xmlDoc1;
xmlDoc1 = reader1.read(xmlFile);// 读取XML文件
org.dom4j.Element root = xmlDoc1.getRootElement();
for (Iterator i = root.elementIterator(); i.hasNext();) {
org.dom4j.Element element = (org.dom4j.Element) i.next();
List<org.dom4j.Element> list = element.elements();
for (org.dom4j.Element el : list) {//获得pram.xml中的条件
if (element.getName().equals("pram1")) {
if (el.getName().equals("index")) {
index = el.getText();
}
if (el.getName().equals("meter")) {
meter = el.getText();
}
}
if (element.getName().equals("pram2")) {
if (el.getName().equals("index")) {
index1 = el.getText();
}
if (el.getName().equals("meter")) {
meter1 = el.getText();
}
}
if (element.getName().equals("pram3")) {
if (el.getName().equals("index")) {
index2 = el.getText();
}
if (el.getName().equals("meter")) {
meter2 = el.getText();
}
}
}
}
} catch (Exception e) {
e.printStackTrace();
}finally{
ReadDts t = new ReadDts();
lists=t.readFile(index, index1, index2, meter, meter1, meter2);
}
return lists;
}
// response.setContentType("application/ms-excel");
// String sheetName = "exl.xls";
// sheetName = sheetName.replaceAll(":", "").replaceAll("[)]", "")
// .replaceAll("[(]", "");
// response.addHeader("Content-Disposition",
// "attachment; filename="
// + new String(sheetName.getBytes("GBK"),
// "ISO-8859-1"));
//
// OutputStream os = response.getOutputStream();
// se.ExcelSql(os, zu, list, meter, start, end);//导出到excel