ioxls.java

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

package com.bean.xls;

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

import jxl.Workbook;
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.ProductoutputDAO;
import com.bean.DAO.ProductregisterDAO;
import com.bean.DTO.ProductoutputDTO;
import com.bean.DTO.ProductregisterDTO;
import com.database.DataSourceFactory;
import com.dom4j.SMSConfig;

public class IOxls {
	int flag=2;
	public void writeXLS(List<ProductregisterDTO> list1,List<ProductoutputDTO> list2,String filename){
		String path="E:/xls//"+filename+".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 title1 =new Label(0,0,"物品入库信息汇总：");
		sheet.addCell(title1 );
		Label ProductID1 =new Label(0,1,"物品ID");
		sheet.addCell(ProductID1);
		Label Productname1 =new Label(1,1,"物品名称");
		sheet.addCell(Productname1);
		Label Amount1 =new Label(2,1,"入库数量");
		sheet.addCell(Amount1);
		Label price1 =new Label(3,1,"价格");
		sheet.addCell(price1);
		Label provider1 =new Label(4,1,"供应商");
		sheet.addCell(provider1);
		Label time1 =new Label(5,1,"入库时间");
		sheet.addCell(time1);
		Label CheckerID1 =new Label(6,1,"操作员ID");
		sheet.addCell(CheckerID1);
		Label Checker1=new Label(7,1,"操作员");
		sheet.addCell(Checker1);
		Label directorID1=new Label(8,1,"负责人ID");
		sheet.addCell(directorID1);		
		for(int j=0;j<list1.size();j++){
			ProductregisterDTO dto=new ProductregisterDTO();
			dto =list1.get(j);
			sheet.addCell(new Label(0,flag,new Integer(dto.getProductID()).toString()));
			sheet.addCell(new Label(1,flag,dto.getProductname().toString()));
			sheet.addCell(new Label(2,flag,new Integer(dto.getAmount()).toString()));
			sheet.addCell(new Label(3,flag,new Float(dto.getPrice()).toString()));
			sheet.addCell(new Label(4,flag,dto.getProvider().toString()));
			sheet.addCell(new Label(5,flag,dto.getInputtime().toString()));
			sheet.addCell(new Label(6,flag,new Integer(dto.getCheckerID()).toString()));
			sheet.addCell(new Label(7,flag,dto.getChecker().toString()));
			sheet.addCell(new Label(8,flag,new Integer(dto.getDirectorID()).toString()));
			flag++;
		}
		Label title2 =new Label(0,flag,"物品出库信息汇总：");
		sheet.addCell(title2 );
		flag=flag+1;	
		sheet.addCell(new Label(0,flag,"物品ID".toString()));
		sheet.addCell(new Label(1,flag,"物品名称".toString()));
		sheet.addCell(new Label(2,flag,"出库数量".toString()));
		sheet.addCell(new Label(3,flag,"价格".toString()));
		sheet.addCell(new Label(4,flag,"客户商".toString()));
		sheet.addCell(new Label(5,flag,"出库时间".toString()));
		sheet.addCell(new Label(6,flag,"操作员ID".toString()));
		sheet.addCell(new Label(7,flag,"操作员".toString()));
		sheet.addCell(new Label(8,flag,"负责人ID".toString()));
		flag++;
		for(int j=0;j<list2.size();j++){
			ProductoutputDTO dto=new ProductoutputDTO();
			dto =list2.get(j);
			sheet.addCell(new Label(0,flag,new Integer(dto.getProductID()).toString()));
			sheet.addCell(new Label(1,flag,dto.getProductname().toString()));
			sheet.addCell(new Label(2,flag,new Integer(dto.getOutamount()).toString()));
			sheet.addCell(new Label(3,flag,new Float(dto.getTradeprice()).toString()));
			sheet.addCell(new Label(4,flag,dto.getConsumer().toString()));
			sheet.addCell(new Label(5,flag,dto.getOuttime().toString()));
			sheet.addCell(new Label(6,flag,new Integer(dto.getCheckerID()).toString()));
			sheet.addCell(new Label(7,flag,dto.getChecker().toString()));
			sheet.addCell(new Label(8,flag,new Integer(dto.getDirectorID()).toString()));
			flag++;
		}
		workbook.write();
		workbook.close();
		}catch(IOException e){
			e.printStackTrace();
		}
		 catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}	
	}
	public static void main(String[] args) {
		IOxls pbx=new IOxls();
		ProductregisterDAO bp=new ProductregisterDAO();
		ProductoutputDAO po=new ProductoutputDAO();
		Connection con;
		SMSConfig.getSMSConfig().readXML("WebRoot/WEB-INF/SMSConfig.xml");
		try {
			con = DataSourceFactory.getDataSource().getConnection();
			pbx.writeXLS(bp.getAlllist(1, con), po.getAlllist(1, con), "io");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		}
}