使用poi实现excel文件的上传

1. 首先 到包，poi-3.X.jar包

   从 底层写起，

             DbMag:

package com.dzy.base;

import java.beans.Statement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class DBManager {
	private String url="jdbc:mysql://localhost:3306/test?characterEncoding=UTF-8";
	private String user="root";
	private String password="sa";
	private Connection sqlcon=null;
	private PreparedStatement pst=null;
	private java.sql.Statement sta=null;
	/**
	 * ��ȡ���Ӷ���
	 * @return
	 * @throws SQLException
	 */

	private Connection GetConnection() throws SQLException{
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		return DriverManager.getConnection(url, user, password);
	}
	
	/**
	 * ִ��insert,update,delete SQL���
	 * @param sql
	 * @param objs
	 * @return
	 * @throws SQLException
	 */
	public int MyExecuteUpdate(String sql,Object ...objs) throws SQLException{
		sqlcon=GetConnection();
		pst=sqlcon.prepareStatement(sql);
		if(objs!=null){
			if(objs.length>0){
				int idx=1;
				for(Object obj : objs){
					pst.setObject(idx, obj);
					idx++;
				}
			}
		}
		int iline = pst.executeUpdate();
		if(pst!=null){
			pst.close();
		}
		if(sqlcon!=null){
			sqlcon.close();
		}
		return iline;
	}
	
	public int MyCreateTable(String sql) throws SQLException{
		sqlcon=GetConnection();
		sta=sqlcon.createStatement();
		sta.executeUpdate(sql);
		int myline = sta.executeUpdate(sql);
		if(sta!=null){
			sta.close();
		}
		if(sqlcon!=null){
			sqlcon.close();
		}
		return myline;
	}
	
	public ResultSet GetResultSet(String sql,Object ...objs) throws SQLException{
		sqlcon=GetConnection();
		sta=sqlcon.createStatement();
		pst=sqlcon.prepareStatement(sql);
		if(objs!=null){
			if(objs.length>0){
				int idx=1;
				for(Object obj : objs){
					pst.setObject(idx, obj);
					idx++;
				}
						
			}
		}
		ResultSet rst = pst.executeQuery();
		CloseAll();
		return rst;
	}
	
	/**
	 * �ر����ݿ��������Ӷ����ͷ����ݿ�������Դ
	 * @throws SQLException
	 */
	public void CloseAll() throws SQLException{
		if(pst!=null){
			pst.close();
		}
		if(sqlcon!=null){
			sqlcon.close();
		}
	}
	
	/**
	 * ִ�ж���sql��� ʹ��������
	 * @param sqlList
	 * @param objsList
	 * @return
	 */
	public int MyTransaction(List<String> sqlList,List<Object[]> objsList){
		int iline=-1;
		try{
			sqlcon=GetConnection();//��ȡ���Ӷ���
			sqlcon.setAutoCommit(false);//ȡ��������Զ��ύ����
			if(sqlList.size()>0){
				for(int i=0;i<sqlList.size();i++){
					String sql=sqlList.get(i);//��ȡҪִ�е�sql���
					Object [] objs=objsList.get(i);//��ȡҪִ�е�sql���IJ���
					pst=sqlcon.prepareStatement(sql);//����pst����
					if(objs!=null){//Ϊsql���IJ�����ֵ
						if(objs.length>0){
							int idx=1;
							for(Object obj : objs){
								pst.setObject(idx, obj);
								idx++;
							}
						}
					}
					pst.execute();//ִ��sql���
				}
			}
			iline=1;
			sqlcon.commit();//���sql���ִ�гɹ������ύ����
		}catch(SQLException e){
			if(sqlcon!=null){
				try {
					sqlcon.rollback();//���sql���ִ��ʧ�ܣ���ع�����
				} catch (SQLException e1) {
					e1.printStackTrace();
				}
			}
			e.printStackTrace();
		}finally{
			try{
				if(pst!=null){
					pst.close();
				}
				if(sqlcon!=null){
					sqlcon.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		
		return iline;
	}

	
	

}

2. ModeluserDao:

public interface DeptDao {
   public int Add(Dept dept) throws SQLException;

}

实现类：

public class DeptDaoImpl implements DeptDao {
    DBManager db=new DBManager();
   @Override
   public int Add(Dept entity) throws SQLException {
      String sql="insert into oc_shop_dept (id, dept_code, english_name,chinese_name,type)values (default,?,?,?,?)";
      Object [] objs={entity.getDeptCode(),entity.getEnglishName(),entity.getChineseName(),entity.getType()};
      return db.MyExecuteUpdate(sql, objs);
   }

}

3.service层：

public interface IReadExcelXlsServiceDao {
   public List<Dept> readXls(String savePath) throws IOException;
   public List<User> readXlsUser(String savePath) throws IOException;

}

serviceIMpl:

public class ReadExcelXlsServiceDaoImpl implements IReadExcelXlsServiceDao {
   public List<Dept> readXls(String savePath) throws IOException {
      InputStream is = new FileInputStream(savePath);
      HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
      List<Dept> list = new ArrayList<Dept>();
      for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
         HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);// 这是一行
         if (hssfSheet == null) {
            continue;
         }
         for (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            HSSFRow hssfRow = hssfSheet.getRow(rowNum);  // 这是一行里面的  每一个列
            if (hssfRow != null) {
               Dept dept = new Dept();
               HSSFCell deptcode = hssfRow.getCell(0);
               HSSFCell en = hssfRow.getCell(1);
               HSSFCell cn = hssfRow.getCell(2);
               HSSFCell ph = hssfRow.getCell(3);
               HSSFCell pd = hssfRow.getCell(4);
               dept.setDeptCode(getValue(deptcode));
               dept.setEnglishName(getValue(en));
               dept.setChineseName(getValue(cn));
               if(getValue(pd).equals("Y")){
               dept.setType(1);
               }
               list.add(dept);
            }
         }
      }
      return list;
   }
   
    @SuppressWarnings("static-access")
   private String getValue(HSSFCell hssfCell) {
                if (hssfCell.getCellType() == hssfCell.CELL_TYPE_BOOLEAN) {
                       return String.valueOf(hssfCell.getBooleanCellValue());
                   } else if (hssfCell.getCellType() == hssfCell.CELL_TYPE_NUMERIC) {
                      return String.valueOf(hssfCell.getNumericCellValue());
                  } else {
                       return String.valueOf(hssfCell.getStringCellValue());
               }
            }

然后就是  使用层;   可以 把文件的 地址 换成是 从input 框里面 获得 的 结果， 

public class Testpoi {

   public static void main(String[] args) throws SQLException {
      IReadExcelXlsServiceDao dao=new ReadExcelXlsServiceDaoImpl();
      DeptDao dao2=new DeptDaoImpl();
      try {
         List<Dept> list= dao.readXls("C:/Users/Administrator/Desktop/11.xls");
         for (Dept dept : list) {
            System.out.println(dept.getDeptCode()+"///////"+dept.getChineseName()+"///////"+dept.getEnglishName());
            dao2.Add(dept);
         }
         System.out.println("OKOKOK!!!");
      } catch (IOException e) {
         e.printStackTrace();
      }

   }

}