excelmaker.java

这是本人曾经在公司里用的,内部开发框架,基于struts+hibernate今天分享给大家

/**
 * 
 */
package cn.bway.common;

import java.sql.Connection;
import java.util.Vector;

import javax.servlet.http.HttpServletResponse;

import org.hibernate.Session;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

/**
 * @author Kson
 *
 */
public class ExcelMaker {

	public ExcelMaker(){
		}
	
	public  WritableWorkbook makeExcel(HttpServletResponse res,String sheetName,Vector data,Vector columnName) {
		WritableWorkbook book=null;//浏览器输出方�?
		try{
			book=Workbook.createWorkbook(res.getOutputStream());
			WritableSheet sheet=book.createSheet(sheetName,0);
			//System.out.println("================================" + columnName.size());
			//--列名
//			if(columnName.size()>0){
//				//for(int j=0;j<columnName.size();j++){
//				for(int i =0;i<columnName.size();i++)
//				{
//					PropertysBean name=(PropertysBean)(columnName.get(i));
//					System.out.println("===" + name);
//					Label label = new Label(i,0,name.getContent());
//					sheet.addCell(label);
//				}
//			}
			
			System.out.println("data.size()=="+data.size());
			if(data.size()>0){
				for(int i=0; i<data.size(); i++)
				{
					String[] list = (String[])data.get(i);
					for(int j=0;j<list.length;j++) {
						try{
							Label label = new Label(j,i,String.valueOf(list[j]));
							sheet.addCell(label);
						}catch(Exception e){
							e.printStackTrace();
						}
					}
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return book;		
	}
	
	public void operatorExcel(String tmpStr,Connection conn,Session sess){
		String sql="";
		System.out.println(" get vector == "+tmpStr);
		System.out.println(" the length =="+tmpStr.length());
		//String[] open = new String[10];
		try{
			
			String open[] = Util.split(tmpStr,"|");
			System.out.println(" ExcelMaker == "+open[0]);
			System.out.println(" ExcelMaker == "+open[1]);
			System.out.println(" ExcelMaker == "+open[2]);
			System.out.println(" ExcelMaker == "+open[3]);
			System.out.println(" ExcelMaker == "+open[4]);
			System.out.println(" ExcelMaker == "+open[5]);
			System.out.println(" ExcelMaker == "+open[6]);
			System.out.println(" ExcelMaker == "+open[7]);
			System.out.println(" ExcelMaker == "+open[8]);
			System.out.println(" ExcelMaker == "+open[9]);
			
			sql="insert into fingerprintsattend" +
			"(deptname,staffname,attenddate,weeks,status,funshcar,partoneontime,partoneofftime" +
			",parttwoontime,parttwoofftime,partthreeontime,partthreeofftime,leavetimes,absenteenumber," +
			"onworktime,addworktimes,latenumber,latetime,earlynumber,earlytime,weekendsovertime,holidayovertime" +
			",attendstatus)" +
			" values" +
			"('"+open[0]+"','"+open[1]+"','"+open[2]+"','"+open[3]+"','"+open[4]+"','"+open[5]+"','"+open[6]+"'" +
			",'"+open[7]+"','"+open[8]+"','"+open[9]+"','"+open[10]+"','"+open[11]+"','"+open[12]+"'," +
			"'"+open[13]+"','"+open[14]+"','"+open[15]+"','"+open[16]+"','"+open[17]+"','"+open[18]+"'" +
			",'"+open[19]+"','"+open[20]+"','"+open[21]+"','"+open[22]+"')";
			
			System.out.println("the update sql:   "+sql);
			
//			deptname	varchar(20) not null,		--部门名称
//			staffname	varchar(20) not null,		--员工姓名
//			attenddate	varchar(20) not null,		--考勤日期
//			weeks		varchar(20) not null,		--星期
//			status		int(11) not null,		--状�??
//			funshcar	varchar(20) not null,		--刷卡时间
//			partoneontime	varchar(20) not null,		--段一上班
//			partoneofftime	varchar(20) not null,		--段一下班
//			parttwoontime	varchar(20) not null,		--段二上班
//			parttwoofftime	varchar(20) not null,		--段二下班
//			partthreeontime	varchar(20) not null,		--段三上班
//			partthreeofftime varchar(20) not null,		--段三下班
//			leavetimes	varchar(20) not null,		--请假天数
//			absenteenumber  varchar(20) not null,		--旷工天数
//			onworktime	varchar(20) not null,		--实际上班时长
			
//			addworktimes	varchar(20) not null,		--加班时长
//			latenumber	varchar(20) not null,		--迟到次数
//			latetime	varchar(20) not null,		--迟到时长
//			earlynumber	varchar(20) not null,		--早�??次数
//			earlytime	varchar(20) not null,		--早�??时长
//			weekendsovertime  varchar(20) not null,		--周末加班
//			holidayovertime  varchar(20) not null,		--节日加班	
//			attendstatus	int(11) not null,		--考勤状�??
			
//			Fingerprintsattend pvo=new Fingerprintsattend();
//			pvo.setDeptname(open[0]);
//			pvo.setStaffname(open[1]);
//			pvo.setAttenddate(open[2]);
//			pvo.setWeeks(open[3]);
//			pvo.setStatus(open[4]);
//			pvo.setFunshcar(open[5]);
//			pvo.setPartoneontime(open[6]);
//			pvo.setPartoneofftime(open[7]);
//			pvo.setParttwoontime(open[8]);
//			pvo.setParttwoofftime(open[9]);
//			pvo.setPartthreeontime(open[10]);
//			pvo.setPartthreeofftime(open[11]);
//			pvo.setLeavetimes(open[12]);
//			pvo.setAbsenteenumber(open[13]);
//			pvo.setOnworktime(open[14]);
//			pvo.setAddworktimes(open[15]);
//			pvo.setLatenumber(open[16]);
//			pvo.setLatetime(open[17]);
//			pvo.setEarlynumber(open[18]);
//			pvo.setEarlytime(open[19]);
//			pvo.setWeekendsovertime(open[20]);
//			pvo.setHolidayovertime(open[21]);
//			pvo.setAttendstatus(open[22]);
			
//			sess.save(pvo);
			
		}catch(Exception e){
			e.printStackTrace();
		}
	}
	
}