downloadfile.java

中应用程序的访问权限对Java Web Console 中应用程序的访问权限 成功登录 Web 控制台后,可能无法自动访问在该控制台中注册的所有应用程序。通常,必须安装应用程序,才能让所有的用户在控制

package edu.yinhe.mis.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import edu.yinhe.system.common.AppException;
import edu.yinhe.system.common.ConnectionManager;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * 
 * @author 甘丽
 * @version 
 *
 */
public class DownLoadFile {

	private String tableName = null;
	private File file= null;
	private List alist =  null;
	private String head[]=null;
	private String [] strinfo = null;
	private Connection conn = null;
	private String []tableItem = null;
	private String [][]str =null;
	private String url = null;
	private String sheet = null;
	private boolean isHead = false;
	/**
	 * 验证文件名的后缀是否为xls，
	 * @param file 文件名
	 * @return 返回false表示文件的后缀不为xls，返回true表示未见的后缀为xls
	 * @throws AppException 当文件的后缀部位xls时，抛出异常
	 */
	private boolean setFile(String file) throws AppException{
		boolean flag = false;
		if("xls".equals(file.substring(file.lastIndexOf(".")+1, file.length()))){
			flag = true;

		}else{
			throw new AppException("文件类型不匹配！");
		}
		return flag;
	}
	/**
	 * 构造方法
	 * @param fileSource 目的文件的路径
	 * @throws AppException 文件不匹配异常
	 */
	public DownLoadFile(String fileSource) throws AppException{
		if(this.setFile(fileSource)){
			file = new File(fileSource);
		}
	}
	/**
	 * 构造方法
	 * @param url 源文件的路径
	 * @param fileSource 目的文件的路径
	 * @throws AppException 文件不匹配异常
	 */
	public DownLoadFile(String url,String fileSource) throws AppException{
		if(this.setFile(url)){
			this.url = url;
		}
		if(this.setFile(fileSource)){
			file = new File(fileSource);
		}
	}
	/**
	 * 构造方法
	 * @param tableName 表名。如果要从数据库中得到数据并且通过excel文件下载下来，那么就需要给一个表名。
	 * @param fileSource 目的文件的路径
	 * @param conn 数据连接
	 * @throws AppException 文件不匹配异常
	 */
	public DownLoadFile(String tableName,String fileSource,Connection conn) throws AppException{
		this.conn = conn;
		if(this.setFile(fileSource)){
			file = new File(fileSource);
		}
		this.tableName = tableName;
		
	}
	/**
	 * 构造方法
	 * @param tableName 表名。如果要从数据库中得到数据并且通过excel文件下载下来，那么就需要给一个表名。
	 * @param fileSource 目的文件的路径
	 * @param conn 数据连接
	 * @param sheet excel文件的工作表
	 * @throws AppException 文件不匹配异常
	 */
	public DownLoadFile(String tableName,String fileSource,Connection conn,String sheet) throws AppException{
		this.conn = conn;
		if(this.setFile(fileSource)){
			file = new File(fileSource);
		}
		this.tableName = tableName;
		this.sheet = sheet;
	}
	/**
	 * 开始执行下载功能
	 */
	public void executeDownLoad() {
		if(url == null ||"".equals(url)){
			this.visitDB();
			try {
				WritableWorkbook wwb = Workbook.createWorkbook(file);
				WritableSheet ws = wwb.createSheet(sheet==null||"".equals(sheet)?"Sheet1":sheet, 0);
				jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
						UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
				jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
				Label lable = null;
				Cell cell = null;
				for(int i = 0;i<head.length;i++){
					lable = new Label(i,0,head[i],wcfFC);
					ws.addCell(lable);
				}
				for(int i = 1;i<=alist.size();i++){
					strinfo = (String[]) alist.get(i-1);
					for(int j=0;j<strinfo.length;j++){
						lable = new Label(j, i, strinfo[j]);
						ws.addCell(lable);
					}
				}
				wwb.write();
				wwb.close();
			} catch (IOException e) {
				e.printStackTrace();
			} catch (RowsExceededException e) {
				e.printStackTrace();
			} catch (WriteException e) {
				e.printStackTrace();
			}finally{
				tableName = null;
				file= null;
				alist =  null;
				head=null;
				strinfo = null;
			}
		}else{
			this.down();
		}
		
		
		
	}
	/**
	 * 从源文件拷贝到目的文件里
	 */
	private void down(){
		try {
			WritableWorkbook wwb = Workbook.createWorkbook(file);
			WritableSheet ws = wwb.createSheet(sheet==null||"".equals(sheet)?"Sheet1":sheet, 0);
			jxl.write.WritableFont wfc = new jxl.write.WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.RED);
			jxl.write.WritableCellFormat wcfFC = new jxl.write.WritableCellFormat(wfc);
			Label lable = null;
			Cell cell = null;
			for(int i = 0;i<head.length;i++){
				lable = new Label(i,0,head[i],wcfFC);
				ws.addCell(lable);
			}
			for(int i = 1;i<=alist.size();i++){
				strinfo = (String[]) alist.get(i-1);
				for(int j=0;j<strinfo.length;j++){
					lable = new Label(j, i, strinfo[j]);
					ws.addCell(lable);
				}
			}
			wwb.write();
			wwb.close();
		} catch (IOException e) {
			e.printStackTrace();
		} catch (RowsExceededException e) {
			e.printStackTrace();
		} catch (WriteException e) {
			e.printStackTrace();
		}finally{
			tableName = null;
			file= null;
			alist =  null;
			head=null;
			strinfo = null;
		}
	}
	/**
	 * 访问数据库，查找出表的内容
	 */
	private void visitDB(){
		String SQL = "SELECT * FROM "+tableName;
		int flag = 0;
		PreparedStatement ps = null;
		ResultSetMetaData pmd = null;
		ResultSet rst = null;
		
		try {
			ps = conn.prepareStatement(SQL);
			rst = ps.executeQuery();
			pmd = ps.getMetaData();
			if(tableItem != null){
				for(int i=0;i<tableItem.length;i++){
					for(int j = 0;j<pmd.getColumnCount();j++){
						if(tableItem[i].equals(pmd.getColumnName(j+1))){
							if(flag == 0){
								SQL = "SELECT ";
								flag = 1;
							}
							SQL = SQL + tableItem[i] +",";
						}
					}
				}
				if(flag == 0){
					SQL = SQL +"* FROM "+tableName;
				}else if(flag == 1){
					SQL = SQL.substring(0,SQL.lastIndexOf(","))+" FROM "+tableName;
				}
				ps = conn.prepareStatement(SQL);
				rst = ps.executeQuery();
				pmd = ps.getMetaData();
			}
			alist = new ArrayList();
			while(rst.next()){
				strinfo = new String [pmd.getColumnCount()];
				for(int i = 0 ;i<strinfo.length;i++){
					strinfo[i]=rst.getString(i+1);
				}
				alist.add(strinfo);
			}
			
			if(head == null){
				head = new String [pmd.getColumnCount()];
				
				for(int i =0;i<pmd.getColumnCount();i++){
					head[i] = pmd.getColumnName(i+1);
				}
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			try {
				rst.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	/**
	 * 设置EXCEL表头信息,如：setFormat(new String[]{"编号","用户名"});
	 * @param format
	 */
	public void setFormat(String [] format){
		this.head = format;
	}
	/**
	 * 设置所选表的列名，如：setitem(new String[]{"ID","USER_NAME"});
	 * @param tableItem
	 */
	public void setitem(String [] tableItem){
		this.tableItem = tableItem;
	}
	public String[][] beginUpload(){
		jxl.Workbook rwb=null;
		Sheet[] sheet = null;
		int trows = 0;
		int tcols = 0;
		
		try{
			InputStream is = new FileInputStream(file);
			rwb = Workbook.getWorkbook(is);
			if(this.sheet==null||"".equals(this.sheet)){
				
				sheet = rwb.getSheets();
			}else{
				sheet = new Sheet[1];
				sheet[0]=rwb.getSheet(this.sheet);
			}
			for(int i=0;i<sheet.length&&sheet[i]!=null;i++){
				trows = sheet[i].getRows();
				tcols = sheet[i].getColumns();
				str = new String[tcols][trows];
				for(int row=0;row<trows;row++){
					for(int col=0;col<tcols;col++){
						Cell Ccol_row = sheet[i].getCell(col, row);
						str[col][row] = Ccol_row.getContents();
					}
				}
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			rwb.close();
		}
		
		
		return str;
	}
	
	public void setSheet(String sheet){
		this.sheet=sheet;
	}
	
	public void insert(){
		String SQL = "SELECT * FROM "+tableName;
		int flag = 0;
		PreparedStatement ps = null;
		ResultSetMetaData pmd = null;
		ResultSet rst = null;
		
		try {
			ps = conn.prepareStatement(SQL);
			rst = ps.executeQuery();
			pmd = ps.getMetaData();
			if(tableItem != null){
				for(int j=0;j<tableItem.length;j++){
					for(int k = 1;k<=pmd.getColumnCount();k++){
					if(tableItem[j].equalsIgnoreCase(pmd.getColumnName(k))&&!pmd.getColumnName(k).equalsIgnoreCase("ID")){
						if(flag == 0){
							SQL = "INSERT  INTO "+tableName+"(";
							flag = 1;
						}
						SQL = SQL + tableItem[j] +",";
					}
					}
				}
				SQL = SQL.substring(0,SQL.lastIndexOf(","))+") VALUES(";
				for(int j=0;j<tableItem.length;j++){
					for(int k = 1;k<=pmd.getColumnCount();k++){
							if(tableItem[j].equalsIgnoreCase(pmd.getColumnName(k))&&!pmd.getColumnName(k).equalsIgnoreCase("ID")){
							SQL = SQL + "?,";
						}
					}
				}
				SQL = SQL.substring(0,SQL.lastIndexOf(",")) + ")";
				ps = conn.prepareStatement(SQL);
				System.out.println(str.length);
				int count = str[0].length;
				if(isHead==true){
					count = str[0].length-1;
				}
				for(int n = 0;n<count;n++){
				for(int j=0;j<tableItem.length;j++){

					for(int k = 1;k<=pmd.getColumnCount();k++){
						if(tableItem[j].equals("ID")){
						}else{
							if(tableItem[j].equals(pmd.getColumnName(k))){
								if(pmd.getColumnTypeName(k).startsWith("INTEGER")){
									if(isHead==true){
										ps.setInt(j+1, Integer.parseInt(str[j][n+1]));
									}else{
										ps.setInt(j+1, Integer.parseInt(str[j][n]));
									}
								}
								if(pmd.getColumnTypeName(k).startsWith("VARCHAR")){
									if(isHead==true){
										ps.setString(j+1, str[j][n+1]);
									}else{
										ps.setString(j+1, str[j][n]);
									}
								}
							}
						}
					}
				}
				ps.executeUpdate();
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally{
			try {
				rst.close();
				ps.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}
	
	public void isHead(boolean isHead){
		this.isHead =isHead;
	}
	/**
	 * 用于导出指定数据，List里面应该是一个String数组类型，
	 * 在导出之前需调用DownLoadFile(String fileSource)构造方法，来制定导出的目标路径
	 * @param list 将要导出的数据
	 */
	public void executeDownLoad(List list) {
		if(list!=null){
			this.alist=list;
		}
		this.down();
	}
	public static void main(String [] args){
		DownLoadFile dlf = null;
		try {
			dlf = new DownLoadFile("G:\\asd.xls");
		} catch (AppException e) {
			e.printStackTrace();
		}
//		String []format={"名字","代号","内容","类别","代码号","备注"};
//		dlf.isHead(true);
//		dlf.setFormat(format);
//		List list = new ArrayList();
//		for(int i = 0;i<5;i++){
//			list.add(new String[]{"aa","bb","nn","gg","ff","dd"});
//		}
//		dlf.executeDownLoad();
//		dlf.executeDownLoad(list);
//		dlf.beginUpload();
//		dlf.insert();
//		System.out.println(dlf.beginUpload());
		dlf.setSheet("Sheet1");
		String [][] a = (String[][])dlf.beginUpload();
		for(int i = 0;i<a.length;i++){
			for(int j = 0;j<a[0].length;j++){
				System.out.print(a[i][j]+"    ");
			}
			System.out.println(" ");
		}
	}
}