report.java

java swing源码 欢迎下载 有问题请联系 我一定负责到底

package com.NCL.excel;

import java.io.File;
import java.util.ArrayList;
import java.util.List;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import com.sinosoft.common.DBAccess;
import com.sinosoft.common.Data;
import com.sinosoft.common.IndexMap;

public class Report {
	
	/**
	 * 查询咨询留言信息
	 * @param id
	 * @return
	 */
	public List findAll(int id){
		String sql = "";
		DBAccess d = new DBAccess();
		sql ="select id,type,name,email,telephone,content,dealflag, makedate,dealdate " +
				" from consultationmessage where id > "+id+"  order by id";
		return d.mulparseSQL(sql, new ArrayList());
	}
	/**
	 * 查询最大id
	 * @param id
	 * @return
	 */
	public String maxID(int id){
		IndexMap im=new IndexMap();
		im.put("id", String.valueOf(id));
		String sql = "";
		DBAccess d = new DBAccess();
		sql ="select max(id) from consultationmessage where id > ? ";
		return d.executeQuery(sql, im, 0, 1);
	}
	/**
	 * 创建xls
	 * @param id
	 * @param m
	 */
	public void createXLS(int id,String m){
		try
		   {
			//	获得所有数据
			List l5=this.findAll(id); 
	if(l5!=null&&l5.size()>0){
			
			//获取上次id最大值
			String maxID=this.maxID(id);
			//打开文件
			WritableWorkbook book= Workbook.createWorkbook(new File("d:/咨询留言"+Data.getCurrentDate()+"_"+m+"_"+maxID+".xls"));
		    //生成名分别为"产品信息，售后服务，公司信息，网站功能"的工作表，参数0表示这是第一页
		    WritableSheet sheet=book.createSheet("产品信息",0);
		    WritableSheet sheet1=book.createSheet("售后服务",1);
		    WritableSheet sheet2=book.createSheet("公司信息",2);
		    WritableSheet sheet4=book.createSheet("网站功能",4);
		    //合并单元格
		    sheet.mergeCells(0, 0, 15, 0);
		    sheet1.mergeCells(0, 0, 15, 0);
		    sheet2.mergeCells(0, 0, 15, 0);
		    sheet4.mergeCells(0, 0, 15, 0);
		    //设置字体为宋体,16号字,加粗,颜色为黑色
		    WritableFont font1=new WritableFont(WritableFont.createFont("宋体"),9,WritableFont.BOLD); 
		    font1.setColour(Colour.BLACK);
		    WritableCellFormat format1=new WritableCellFormat(font1);
		    format1.setAlignment(jxl.format.Alignment.LEFT);
		    format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
		    format1.setBackground(Colour.GRAY_25);
		    //第二种风格
		    WritableFont font2=new WritableFont(WritableFont.createFont("宋体"),9,WritableFont.NO_BOLD); 
		    font2.setColour(Colour.RED);
		    WritableCellFormat format2=new WritableCellFormat(font2);
		    format2.setAlignment(jxl.format.Alignment.CENTRE);
		    format2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
		    format2.setBackground(Colour.YELLOW2);
		    //定义数组
		    String string[]={"咨询留言回复时日期格式为(yyyy-MM-dd hh:mm:ss)，不能有汉字字符；回复时只写清最后处理的时间，时间格式中不能有汉字字符","序号","类型","姓名","邮箱","电话","内容","是否处理","日期","处理日期","转办接收人","回复方式","转办接收人所在的公司代码"};
		    //定义label
		    Label lab=null;
		    Label lab_1=null;	
		    Label lab_2=null;
		    Label lab_4=null;
		    //头部信息
		    lab = new Label(0,0,string[0],format2);
		    lab_1= new Label(0,0,string[0],format2);
		    lab_2= new Label(0,0,string[0],format2);
		    lab_4= new Label(0,0,string[0],format2);
		    sheet.addCell(lab);
		    sheet1.addCell(lab_1);
		    sheet2.addCell(lab_2);
		    sheet4.addCell(lab_4);
		    //type=0,1,2,4
		    for(int i=1;i<13;i++){
		    	lab= new Label(i,1,string[i],format1);
		    	lab_1= new Label(i,1,string[i],format1);
		    	lab_2= new Label(i,1,string[i],format1);
		    	lab_4= new Label(i,1,string[i],format1);
		    	sheet.addCell(lab);
		    	sheet1.addCell(lab_1);
		    	sheet2.addCell(lab_2);
		    	sheet4.addCell(lab_4);
		    }
		    
		    Label la  = null;
		    Label la1 = null;
		    Label la2 = null;
		    Label la4 = null;
		    //报表中数值的坐标
		    int x=0,x1=0,x2=0,x4=0;
		    //生成一个保存数字的单元格
		    jxl.write.Number number = null;
		    String tmpData5[] = new String[13];
		    for(int i=0;i<l5.size();i++){
			  IndexMap im5 = (IndexMap)l5.get(i);
			  if("4".equals((String)im5.get(1))){
				  for(int j=0;j<im5.size();j++){
					  tmpData5[j]=(String)im5.get(j);
					  number = new jxl.write.Number(0,x4+2,x4+1);
					   la4 = new Label(j+1,x4+2,tmpData5[j]);
					   sheet4.addCell(la4);
					   sheet4.addCell(number);
					   if(j==im5.size()-1){
							x4++;
							}	   
				  }
			  }else if("2".equals(im5.get(1))){
				  for(int j=0;j<im5.size();j++){
					 tmpData5[j]=(String)im5.get(j);
					 number = new jxl.write.Number(0,x2+2,x2+1);
					 la2 = new Label(j+1,x2+2,tmpData5[j]);
					 sheet2.addCell(la2);
					 sheet2.addCell(number);
					   if(j==im5.size()-1){
							x2++;
							}	   
				  }
			  }else if("1".equals(im5.get(1))){
				  for(int j=0;j<im5.size();j++){
					  tmpData5[j]=(String)im5.get(j);
					  number = new jxl.write.Number(0,x1+2,x1+1);
					  la1 = new Label(j+1,x1+2,tmpData5[j]);
					  sheet1.addCell(la1);
					  sheet1.addCell(number);
					  if(j==im5.size()-1){
							x1++;
							}	   
				  }
			  }else if("0".equals(im5.get(1))) {
				  for(int j=0;j<im5.size();j++){
					  tmpData5[j]=(String)im5.get(j);
					  number = new jxl.write.Number(0,x+2,x+1);
					  la = new Label(j+1,x+2,tmpData5[j]);
					  sheet.addCell(la);
					  sheet.addCell(number);
					  if(j==im5.size()-1){
							x++;
							}	   
				  }
			  }
		   }
		    //写入数据并关闭文件
		    book.write();
		    book.close();
		    System.out.println("生成报表成功!");
	 }else{
		 System.out.println("无符合条件数据!");
	 }
		   }catch(Exception e)
		   {
		    System.out.println(e);
		   }
}
	
	public static void main(String args[]){
	}
}