excelutil.java

使用WEBWORK,SPRING,HIBERNATE编写的简单的添加

/**
 *文件功能： 
 */
package com.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;

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

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * @作者 徐建协
 * @日期 Apr 7, 2008
 */
public class ExcelUtil {
	public static  Log log = LogFactory.getLog(ExcelUtil.class);
	/**
	 * 从EXCEL文件中获取数据
	 * @param path String   文件的路径
	 * @return 二维数组
	 */
	public static String[][] GetArrays(String path){
		try{
			InputStream is =new FileInputStream(path);
			Workbook wb=Workbook.getWorkbook(is);//从输入流创建Workbook
			Sheet rs = wb.getSheet(0);//获取第一张Sheet表
			int row=0;
			int col=0;
			row=rs.getRows();
			col=rs.getColumns();
			String[][] tmp=new String[row][col];
			for(int i=0;i<rs.getRows();i++){
				for(int j=0;j<rs.getColumns();j++){
					tmp[i][j]=rs.getCell(j,i).getContents();					
				}
			}
			ArrayList list=new ArrayList();
			for(int i=0;i<row;i++){
				if (tmp[i][0].trim().length()>0){
					list.add(tmp[i]);
				}
			}
			String[][] tmp2=new String[list.size()][col];
			for(int i=0;i<list.size();i++){
				tmp2[i]=(String[])list.get(i);
			}
			rs=null;
			wb.close();
			is.close();
			wb=null;
			is=null;
			return tmp2;			
		}catch(Exception e){
			System.out.println(e.toString());
			return null;
		}
	}	
	/**
	 * 从EXCEL文件中获取数据
	 * @param is InputStream 输入流
	 * @return 二维数组
	 */
	public static String[][] GetArrays(InputStream is){
		try{
			Workbook wb=Workbook.getWorkbook(is);//从输入流创建Workbook
			Sheet rs = wb.getSheet(0);//获取第一张Sheet表
			int row=0;
			int col=0;
			row=rs.getRows();
			col=rs.getColumns();
			String[][] tmp=new String[row][col];
			for(int i=0;i<rs.getRows();i++){
				for(int j=0;j<rs.getColumns();j++){
					tmp[i][j]=rs.getCell(j,i).getContents();					
				}
			}
			ArrayList list=new ArrayList();
			for(int i=0;i<row;i++){
				if (tmp[i][0].trim().length()>0){
					list.add(tmp[i]);
				}
			}
			String[][] tmp2=new String[list.size()][col];
			for(int i=0;i<list.size();i++){
				tmp2[i]=(String[])list.get(i);
			}
			rs=null;
			wb.close();
			is.close();
			wb=null;
			is=null;
			return tmp2;		
		}catch(Exception e){
			System.out.println(e.toString());
			return null;
		}
	}	
	public static void WriteXls(OutputStream os,Excel excel){
		int i=0;
		int j=0;
		int n=5;
		try{
			WritableWorkbook wwb=Workbook.createWorkbook(os);
			WritableSheet  ws=wwb.createSheet(excel.getTitle(),0);//创建第一个工作表
			WritableCellFormat wcfHead=CellStyle.getWcf(20,false,2,2,false,false);
			//创建标题字体及颜色
			WritableCellFormat wcfTitle=CellStyle.getWcf(12,false,2,2,false,false);
			//创建数据框的字体及颜色
			WritableCellFormat wcfData=CellStyle.getWcf(12,false,2,2,false,false);
			
			if(excel.getTitle()!=null){
				Label label=new Label(0,0,excel.getTitle(),wcfHead);
				ws.addCell(label);
			}
			//设置列宽
			int[] colsWidth=new int[excel.getColNames().length];
			//初始化列宽
			for(i=0;i<colsWidth.length;i++){
				colsWidth[i]=0;
			}
			if(excel.getColNames()!=null){
				for(i=0;i<excel.getColNames().length;i++){
					Label label=new Label(i,1,excel.getColNames()[i],wcfTitle);
					ws.addCell(label);
					if(excel.getColNames()[i].length()*n>colsWidth[i]){
						colsWidth[i]=excel.getColNames()[i].length()*n;
					}
				}
			}
			BeanUtilsBean bean=BeanUtilsBean.getInstance();
			//添加表格数据
			String value="";
			if(excel.getList()!=null){
				for(i=0;i<excel.getList().size();i++){					
					for(j=0;j<excel.getGetterNames().length;j++){
						value=bean.getProperty(excel.getList().get(i),excel.getGetterNames()[j]);
						if(value==null){
							value="";
						}
						Label lable=new Label(j,i+2,value,wcfData);
						ws.addCell(lable);
						if(value.length()*n>colsWidth[j]){
							colsWidth[j]=value.length()*n;
						}
					}
				}
			}
			//设置行高
			if(excel.getList()!=null){
				for(i=0;i<excel.getList().size()+2;i++){					
					ws.setRowView(i,400);
				}
			}


			//设置列宽
			for(i=0;i<colsWidth.length;i++){
				ws.setColumnView(i,colsWidth[i]);
			}
			//合并标题列
			ws.mergeCells(0,0,colsWidth.length-1,0);
			wwb.write();
			wwb.close();
			ws=null;
			wwb=null;
		}catch(Exception ex){
			log.error(ex.toString());
		}
	}
	/**
	 * @param args
	 */
	public static void main(String[] args) throws Exception{
		// TODO Auto-generated method stub
		/*Excel excel=new Excel();
		excel.setTitle("行业信息");
		List<Hyxx> list=new ArrayList<Hyxx>();
		Hyxx hyxx=new Hyxx();
		hyxx.setBgbz(new Long("0"));
		hyxx.setDwmc("单位名称");
		list.add(hyxx);
		excel.setColNames(new String[]{"公司名称","变更标识"});
		excel.setGetterNames(new String[]{"dwmc","bgbz"});
		excel.setList(list);
		
		OutputStream out=new FileOutputStream(new File("c:/1.xls"));
		ExcelUtil.WriteXls(out,excel);*/
		InputStream in=new FileInputStream("c:/jg.xls");
		String[][] data=ExcelUtil.GetArrays(in);//从流读取
		//String[][] data=ExcelUtil.GetArrays("c:/jg.xls");//从文件读取
		String strOut="";
		OutputStream out=new FileOutputStream(new File("c:/sql.txt"));
		for(int i=1;i<data.length;i++){
			strOut="第"+i+"行数据:";
			for (int j=1;j<data[i].length;j++){
				//System.out.println(data[i][0]+","+data[i][1]);
				if (j==1){
				  strOut+=data[i][j];
				}else{
				  strOut+=","+data[i][j];
				}
			}
			System.out.println(strOut);
		}
	}
}