refundxls.java

仓库管理系统,适合各种行业的仓库管理系统

package com.bean.xls;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.util.List;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import com.bean.DAO.ProductDAO;
import com.bean.DAO.RefundDAO;
import com.bean.DTO.ProductDTO;
import com.bean.DTO.RefundDTO;

public class RefundXLS {
	private Connection con;
	
	public RefundXLS(Connection con) {
		this.con=con;
		
	}
	public void writeXLS(List<RefundDTO> list,String name){
		String path="E://"+name+".xls";
		File file = new File(path);
		if(!file.isAbsolute())
			try {
				file.createNewFile();
			} catch (IOException e1) 
			{
				e1.printStackTrace();
			}
		try{
		WritableWorkbook workbook =Workbook.createWorkbook(file);
		WritableSheet sheet = workbook.createSheet("第一页",0);
		for(int i=0;i<5;i++){
			sheet.setColumnView(i,10);
			}
		Label projectID =new Label(0,0,"采购项目ID");
		sheet.addCell(projectID);
		Label othfee =new Label(1,0,"其他费用");
		sheet.addCell(othfee);
		Label totalfee =new Label(2,0,"总费用");
		sheet.addCell(totalfee);
		Label refundtime =new Label(3,0,"报销时间");
		sheet.addCell(refundtime);
		Label operateID =new Label(4,0,"费用操作方式");
		sheet.addCell(operateID);
		Label checkerID =new Label(5,0,"申报人ID");
		sheet.addCell(checkerID);
		Label directorID=new Label(6,0,"申报部门主管ID");
		sheet.addCell(directorID);
		Label stateID=new Label(7,0,"状态ID");
		sheet.addCell(stateID);
		
	
		
		int i=1;
		for(int j=0;j<list.size();j++){
			RefundDTO dto=new RefundDTO();
			dto =list.get(j);
			sheet.addCell(new Label(0,i,new Integer(dto.getProjectID()).toString()));
			sheet.addCell(new Label(1,i,new Float(dto.getOthfee()).toString()));
			sheet.addCell(new Label(2,i,new Float(dto.getTotalfee()).toString()));
			sheet.addCell(new Label(3,i,dto.getRefundtime()));
			sheet.addCell(new Label(4,i,new Integer(dto.getOperateID()).toString()));
			sheet.addCell(new Label(5,i,new Integer(dto.getCheckerID()).toString()));
			sheet.addCell(new Label(6,i,new Integer(dto.getDirectorID()).toString()));
			sheet.addCell(new Label(7,i,new Integer(dto.getStateID()).toString()));
			i++;
		}
		workbook.write();
		workbook.close();
		}catch(IOException e){
			e.printStackTrace();
		}
		 catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}	
	}
	public void readSQL(String name) {
		String path="E://"+name+".xls";
		File file = new File(path);
		Workbook book;
		try {
				book = Workbook.getWorkbook(file);
				Sheet sheet = book.getSheet(0);
				int row = sheet.getRows();
				int column = sheet.getColumns();
				for (int i = 1; i < row; i++) {
					for (int j = 0; j < column; j++) {
						RefundDTO dto=new RefundDTO();
						dto.setProjectID(Integer.parseInt(sheet.getCell(j++, i).getContents()));
						dto.setOthfee(Float.parseFloat(sheet.getCell(j++, i).getContents()));
						dto.setTotalfee(Float.parseFloat(sheet.getCell(j++, i).getContents()));
						dto.setRefundtime(sheet.getCell(j++, i).getContents());
						dto.setOperateID(Integer.parseInt(sheet.getCell(j++, i).getContents()));
						dto.setCheckerID((Integer.parseInt(sheet.getCell(j++, i).getContents())));
						dto.setDirectorID((Integer.parseInt(sheet.getCell(j++, i).getContents())));
						dto.setStateID(Integer.parseInt(sheet.getCell(j++, i).getContents()));
						
						RefundDAO dao=new RefundDAO(con);
						dao.insertData(dto);
					}
				}
				book.close();
			} catch (BiffException e) {
				e.printStackTrace();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}				
	}
}