导出EXCEL

package com.wms.util;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import oracle.jdbc.driver.OracleTypes;
import org.apache.log4j.Logger;
import com.wms.webapp.bean.PageBean;
import com.wms.webapp.dao.BaseDAO;

/**
* @classfuction 导出EXCEL DAO
* @author huangqixing
* @date 2007-6-12
*/
public class DataOutExcelDao extends BaseDAO{
/**
* 申明变量
*/
private ArrayList arraylist = null; //总集合
private ArrayList columnvalueslist = null; //数据集合
String[] columnname = null;//列名
String[] columnvalues = null;//列数据集
HashMap hm = new HashMap();

private Logger log = Logger.getLogger(DataOutExcelDao.class); //输出日志

/**
* @methodname getBaseFeelist<费用信息>
* @return hm<HashMap>
*
*/
public HashMap getBaseFeelist(){
String plsql = "{call WMS_BASE_FEE_SELE(?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";
getConnection();
if (conn != null) {
try {
cst = conn.prepareCall(plsql);
cst.setString(1, null);// 信息seq
cst.setString(2, null);//费用代码
cst.setString(3, null);//费用名称
cst.setString(4, null);//计费方式
cst.setString(5, null);//是否可用
cst.setString(6, "A");// 查询类型 L:默认列表 Q:查询列表 V:查看详细
cst.setString(7, null);//排序字段
cst.setString(8, null);//排序模式 ASC:升序 DESC:降序
cst.setFloat(9, 0); //当前记录行数

//返回值
cst.registerOutParameter(10, OracleTypes.NUMBER);// 返回总记录数
cst.registerOutParameter(11, OracleTypes.NUMBER);// Oracle系统错误号
cst.registerOutParameter(12, OracleTypes.NUMBER);// 业务返回值
// 1:成功 2:警告
// -1:失败
cst.registerOutParameter(13, OracleTypes.VARCHAR);// 程序编号
// WMS_BASE_FEE
cst.registerOutParameter(14, OracleTypes.CURSOR);// 返回游标
cst.execute();
int maxRowCount = Integer.parseInt(cst.getObject(10).toString());
if (maxRowCount == 0) {
return null;
}
rset = (ResultSet) cst.getObject(14);
columnvalueslist = new ArrayList(); //数据集合
ResultSetMetaData metadata = rset.getMetaData();
int colcount = metadata.getColumnCount();
columnname = new String[colcount];

while (rset.next()) {
/*
* 遍历<列集>
*/
columnvalues = new String[colcount];
columnvalues[0]=rset.getString(2);
columnvalues[1]=rset.getString(3);
columnvalues[2]=rset.getString(7);
columnvalues[3]=rset.getString(8);
columnvalues[4]=rset.getString(9);
columnvalues[5]=rset.getString(10);
columnvalueslist.add(columnvalues);
}
/*
* 获取<表头>
*/
columnname[0]= metadata.getColumnName(2);
columnname[1]= metadata.getColumnName(3);
columnname[2]= metadata.getColumnName(7);
columnname[3]= metadata.getColumnName(8);
columnname[4]= metadata.getColumnName(9);
columnname[5]= metadata.getColumnName(10);

/*
* 用hm<HaspMap>存放列名,以及列集
*/
hm.put(0, columnname);
hm.put(1, columnvalueslist);
}
catch (Exception e) {
e.printStackTrace();
} finally {
CloseResultSet();
CloseCallableStatement();
disConnection();
}

}
return hm;
}

package com.wms.util;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import jxl.*;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import java.io.*;

//导入EXCEL
/******导入EXCEL-执行端******/
public class DataOutExcelCon {

public int WriteDataToExcel(String opseq,String tablepath,String tablename) throws RowsExceededException, WriteException, IOException{
ArrayList listall = null; //表数据集合
HashMap totallist = new HashMap(); //总数据集
ArrayList listsub = null;//子集合
String [] title = null;//表头
int flag = -1;

DataOutExcelService does = new DataOutExcelService();
System.out.print("opseq="+opseq);
totallist = does.getDataAndTitle(opseq);
System.out.println("totallist.size"+totallist.size());
System.out.println("tablename"+tablename);
title = (String[]) totallist.get(0); //获取表头
listall = (ArrayList) totallist.get(1);//获取数据集
System.out.println("---------------"+listall.size());
//表名:tablename
WritableWorkbook workbook = Workbook.createWorkbook(new File(tablepath));
//工作薄:tablename
WritableSheet sheet = workbook.createSheet(tablename,0);
for(int i = 0; i<title.length;i++){
Label label=new Label(i,0,title[i]);
//将定义好的单元格添加到工作表中
sheet.addCell(label);
}
int rows = 1; //行数
Iterator iter = listall.iterator();
while (iter.hasNext()) {
String[] value = (String[]) iter.next();
for (int column = 0 ; column < value.length; column++) {
sheet.addCell(new Label(column, rows, value[column]));
}
rows++;
}
workbook.write();
workbook.close();
flag = 1;
return flag;
}
}

package com.wms.util;

import java.util.ArrayList;
import java.util.HashMap;

/*****导入EXCEL-提取数据（通用）****/
public class DataOutExcelService {
HashMap listall = new HashMap();//费用集合:数据集和表头集合
public HashMap getDataAndTitle(String opseq){
int seq = Integer.parseInt(opseq);
switch(seq){

/*************费用信息********************/
case 1:{
//费用信息title
DataOutExcelDao doed = new DataOutExcelDao();
listall=doed.getBaseFeelist();
break;
}

/**********运输站点信息*******************/
case 2:{
DataOutExcelDao doed = new DataOutExcelDao();
listall=doed.getBasePortlist();
break;
}
/**********公司信息*******************/
case 3:{
DataOutExcelDao doed = new DataOutExcelDao();
listall=doed.getBaseCompanylist();
break;
}
/**********货币信息*******************/
case 4:{
DataOutExcelDao doed = new DataOutExcelDao();
listall=doed.getCurrencylist();
break;
}
/**********标准代码信息*******************/
case 5:{
DataOutExcelDao doed = new DataOutExcelDao();
listall=doed.getBaseStandardlist();
break;
}
/**********库区信息*******************/
case 6:{
DataOutExcelDao doed = new DataOutExcelDao();
listall=doed.getBasePlacelist();
break;
}
/**********仓库信息*******************/
case 7:{
DataOutExcelDao doed = new DataOutExcelDao();
listall=doed.getWareHouselist();
break;
}
/**********运输线路信息*******************/
case 8:{
DataOutExcelDao doed = new DataOutExcelDao();
listall=doed.getBaseLinelist();
break;
}
/**********货品信息*******************/
case 9:{
DataOutExcelDao doed = new DataOutExcelDao();
listall=doed.getGoodsInfolist();
break;
}
/**********货品包装信息*******************/
case 10:{
DataOutExcelDao doed = new DataOutExcelDao();
listall=doed.getGoodsPacklist();
break;
}
/***************************************/
/**********库位信息**********************/
case 11:{
DataOutExcelDao doed = new DataOutExcelDao();
listall=doed.getLocationlist();
break;
}
/***************************************/
default : System.out.println("没有定义此条件");

}



return listall;
}
}