将Excel文件导入数据库（POI+Excel+MySQL）（jsp页面导入）

现在正在做一个将Excel导入进数据库的一个程序，现在已经实现了一种方法，借鉴了很多。下面详细说一下，功能如何实现的。

优化版本：第一次优化

目前这个文章的实现方法：

浏览器端选择Excel文件，点击上传，服务器目录会多出一个Excel的文件，然后再在服务器端读取这个文件，保存到数据库。

由于篇幅有限，csdn的上传在60M内，jar包加起来就超过了、所以在这里留一个百度云的链接，可以直接下载，导入即可运行。

源码最新地址

由于篇幅问题，这里只保存了关键的源代码，详细的源代码可以去百度云下载，若链接过时，可以给我评论，或者给我发邮箱，qq均可（点击我的头像获取）。

一、介绍一下我们的这个程序的流程

软件环境，win7 + Myeclipse +MySQL+tomcat

测试浏览器：火狐

其他：jQuery+bootstrap+poi

二、首先看一下包的导入，这里引用下他人的文章内容(jar包下载)

项目结构

三、我们对上面的五个文件开始写吧。

DbUtil.java

package com.app.util;import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;import com.app.common.Common;
import com.app.po.Student_1;public class DbUtil {/*** @param sql*/public static void insert(String sql, Student_1 student) throws SQLException {Connection conn = null;PreparedStatement ps = null;try {Class.forName(Common.DRIVER);conn = DriverManager.getConnection(Common.URL, Common.USERNAME, Common.PASSWORD);ps = conn.prepareStatement(sql);ps.setString(1, student.getNo());ps.setString(2, student.getName());ps.setString(3, student.getAge());ps.setString(4, String.valueOf(student.getScore()));boolean flag = ps.execute();if(!flag){System.out.println("Save data : No. = " + student.getNo() + " , Name = " + student.getName() + ", Age = " + student.getAge() + " succeed!");}} catch (Exception e) {e.printStackTrace();} finally {if (ps != null) {ps.close();}if (conn != null) {conn.close();}}}@SuppressWarnings({ "unchecked", "rawtypes" })public static List selectOne(String sql, Student_1 student) throws SQLException {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;List list = new ArrayList();try {Class.forName(Common.DRIVER);conn = DriverManager.getConnection(Common.URL, Common.USERNAME, Common.PASSWORD);ps = conn.prepareStatement(sql);rs = ps.executeQuery();while(rs.next()){if(rs.getString("no").equals(student.getNo()) || rs.getString("name").equals(student.getName())|| rs.getString("age").equals(student.getAge())){list.add(1);}else{list.add(0);}}} catch (Exception e) {e.printStackTrace();} finally {if (rs != null) {rs.close();}if (ps != null) {ps.close();}if (conn != null) {conn.close();}}return list;}public static ResultSet selectAll(String sql) throws SQLException {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;try {Class.forName(Common.DRIVER);conn = DriverManager.getConnection(Common.URL, Common.USERNAME, Common.PASSWORD);ps = conn.prepareStatement(sql);rs = ps.executeQuery();} catch (Exception e) {e.printStackTrace();} finally {if (rs != null) {rs.close();}if (ps != null) {ps.close();}if (conn != null) {conn.close();}}return rs;}}

package com.app.po;public class Student_1 {/*** id   */private Integer id;/*** 学号*/private String no;/*** 姓名*/private String name;/*** 学院*/private String age;/*** 成绩*/private String score;public Integer getId() {return id;}public void setId(Integer id) {this.id = id;}public String getNo() {return no;}public void setNo(String no) {this.no = no;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getAge() {return age;}public void setAge(String age) {this.age = age;}public String getScore() {return score;}public void setScore(String score) {this.score = score;}}

package com.app.common;public class Common {// connect the databasepublic static final String DRIVER = "com.mysql.jdbc.Driver";public static final String DB_NAME = "test";public static final String USERNAME = "root";public static final String PASSWORD = "123456";public static final String IP = "localhost";public static final String PORT = "3306";public static final String URL = "jdbc:mysql://" + IP + ":" + PORT + "/" + DB_NAME;// commonpublic static final String EXCEL_PATH = "lib/student_info.xls";// sqlpublic static final String INSERT_STUDENT_SQL = "insert into t_student(no, name, age, score) values(?, ?, ?, ?)";public static final String UPDATE_STUDENT_SQL = "update t_student set no = ?, name = ?, age= ?, score = ? where id = ? ";public static final String SELECT_STUDENT_ALL_SQL = "select id,no,name,age,score from t_student";public static final String SELECT_STUDENT_SQL = "select * from t_student where name like ";
}

package com.app.excel;import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;import java.util.List;import org.apache.poi.hssf.usermodel.HSSFCell;import org.apache.poi.hssf.usermodel.HSSFRow;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import com.app.common.Common;import com.app.po.Student_1;public class ReadExcel {public List readXls(String path) throws IOException {InputStream is = new FileInputStream(path);HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);Student_1 student = null;List list = new ArrayList();// 循环工作表Sheetfor (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);if (hssfSheet == null) {continue;}// 循环行Rowfor (int rowNum = 1; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {HSSFRow hssfRow = hssfSheet.getRow(rowNum);if (hssfRow != null) {student = new Student_1();HSSFCell no = hssfRow.getCell(0);HSSFCell name = hssfRow.getCell(1);HSSFCell age = hssfRow.getCell(2);HSSFCell score = hssfRow.getCell(3);student.setNo(getValue(no));student.setName(getValue(name));student.setAge(getValue(age));student.setScore(getValue(score));list.add(student);}}}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());}}}

package com.app.excel;import java.io.IOException;
import java.sql.SQLException;
import java.util.List;import com.app.common.Common;import com.app.util.DbUtil;import com.app.po.Student_1;public class SaveData2DB {@SuppressWarnings({ "rawtypes" })public void save(String path) throws IOException, SQLException {ReadExcel xlsMain = new ReadExcel();Student_1 student = null;List list = xlsMain.readXls(path);for (int i = 0; i < list.size(); i++) {student = list.get(i);List l = DbUtil.selectOne(Common.SELECT_STUDENT_SQL + "'%" + student.getName() + "%'", student);if (!l.contains(1)) {DbUtil.insert(Common.INSERT_STUDENT_SQL, student);} else {System.out.println("The Record was Exist : No. = " + student.getNo() + " , Name = " + student.getName() + ", Age = " + student.getAge() + ", and has been throw away!");}}}}

package com.app.action;import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.List;
import java.util.UUID;import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadBase;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;import com.adtec.framework.common.util.JsonUtil;
import com.app.excel.SaveData2DB;import net.sf.json.JSONObject;public class UpLoad extends HttpServlet {/*** */private static final long serialVersionUID = 1L;private final Logger logger = LoggerFactory.getLogger(this.getClass());public void doGet(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {System.out.println("coming.......");//得到上传文件的保存目录，将上传的文件存放于WEB-INF目录下，不允许外界直接访问，保证上传文件的安全String savePath = this.getServletContext().getRealPath("/WEB-INF/upload");//上传时生成的临时文件保存目录String tempPath = this.getServletContext().getRealPath("/WEB-INF/temp");File tmpFile = new File(tempPath);if (!tmpFile.exists()) {//创建临时目录tmpFile.mkdir();}//消息提示String message = "";try{//使用Apache文件上传组件处理文件上传步骤：//1、创建一个DiskFileItemFactory工厂DiskFileItemFactory factory = new DiskFileItemFactory();//设置工厂的缓冲区的大小，当上传的文件大小超过缓冲区的大小时，就会生成一个临时文件存放到指定的临时目录当中。factory.setSizeThreshold(1024*100);//设置缓冲区的大小为100KB，如果不指定，那么缓冲区的大小默认是10KB//设置上传时生成的临时文件的保存目录factory.setRepository(tmpFile);//2、创建一个文件上传解析器ServletFileUpload upload = new ServletFileUpload(factory);//监听文件上传进度/*upload.setProgressListener(new ProgressListener(){public void update(long pBytesRead, long pContentLength, int arg2) {System.out.println("文件大小为：" + pContentLength + ",当前已处理：" + pBytesRead);}});*///解决上传文件名的中文乱码upload.setHeaderEncoding("UTF-8"); //3、判断提交上来的数据是否是上传表单的数据if(!ServletFileUpload.isMultipartContent(request)){//按照传统方式获取数据return;}//设置上传单个文件的大小的最大值，目前是设置为1024*1024字节，也就是1MBupload.setFileSizeMax(1024*1024);//设置上传文件总量的最大值，最大值=同时上传的多个文件的大小的最大值的和，目前设置为10MBupload.setSizeMax(1024*1024*10);////4、使用ServletFileUpload解析器解析上传数据，解析结果返回的是一个List集合，每一个FileItem对应一个Form表单的输入项List list = upload.parseRequest(request);for(FileItem item : list){//如果fileitem中封装的是普通输入项的数据if(item.isFormField()){String name = item.getFieldName();//解决普通输入项的数据的中文乱码问题String value = item.getString("UTF-8");
//                            String value = item.getString("gbk");//value = new String(value.getBytes("iso8859-1"),"UTF-8");System.out.println(name + "=" + value);}else{//如果fileitem中封装的是上传文件//得到上传的文件名称，String filename = item.getName();System.out.println(filename+"..");if(filename==null || filename.trim().equals("")){continue;}//注意：不同的浏览器提交的文件名是不一样的，有些浏览器提交上来的文件名是带有路径的，如：  c:\a\b\1.txt，而有些只是单纯的文件名，如：1.txt//处理获取到的上传文件的文件名的路径部分，只保留文件名部分filename = filename.substring(filename.lastIndexOf("\\")+1);//得到上传文件的扩展名String fileExtName = filename.substring(filename.lastIndexOf(".")+1);//如果需要限制上传的文件类型，那么可以通过文件的扩展名来判断上传的文件类型是否合法System.out.println("上传的文件的扩展名是："+fileExtName);//获取item中的上传文件的输入流InputStream in = item.getInputStream();//得到文件保存的名称String saveFilename = makeFileName(filename);//得到文件的保存目录String realSavePath = makePath(saveFilename, savePath);//创建一个文件输出流FileOutputStream out = new FileOutputStream(realSavePath + "\\" + saveFilename);//创建一个缓冲区byte buffer[] = new byte[1024];//判断输入流中的数据是否已经读完的标识int len = 0;StringBuffer sb = new StringBuffer();//循环将输入流读入到缓冲区当中，(len=in.read(buffer))>0就表示in里面还有数据while((len=in.read(buffer))>0){//使用FileOutputStream输出流将缓冲区的数据写入到指定的目录(savePath + "\\" + filename)当中
//                            	System.out.println(realSavePath);
//                            	System.out.println();sb.append(new String(buffer,0,len));
//                                logger.info(sb.toString());
//                                System.out.println(sb.toString());
//                                sb.setLength(0);out.write(buffer, 0, len);}
//                            System.out.println(sb.toString()+"-----");String ss = sb.toString();//                            System.out.println(sb.);//关闭输入流in.close();//关闭输出流out.close();//删除处理文件上传时生成的临时文件//item.delete();SaveData2DB saveData2DB = new SaveData2DB();saveData2DB.save(realSavePath + "\\" + saveFilename);System.out.println("end");message = "success";}}}catch (FileUploadBase.FileSizeLimitExceededException e) {e.printStackTrace();message = "单个文件超出最大值！！！";/*request.setAttribute("message", "单个文件超出最大值！！！");*//* request.getRequestDispatcher("/message.jsp").forward(request, response);*/return;}catch (FileUploadBase.SizeLimitExceededException e) {e.printStackTrace();message = "上传文件的总的大小超出限制的最大值！！！";/*request.setAttribute("message", "上传文件的总的大小超出限制的最大值！！！");*//*request.getRequestDispatcher("/message.jsp").forward(request, response);*/return;}catch (Exception e) {message= "文件上传失败！";e.printStackTrace();}/*request.setAttribute("message",message);*/returnResultJson(response,message);/*request.getRequestDispatcher("/message.jsp").forward(request, response);*/}private String makeFileName(String filename){  //2.jpg//为防止文件覆盖的现象发生，要为上传文件产生一个唯一的文件名return UUID.randomUUID().toString() + "_" + filename;}private String makePath(String filename,String savePath){//得到文件名的hashCode的值，得到的就是filename这个字符串对象在内存中的地址int hashcode = filename.hashCode();int dir1 = hashcode&0xf;  //0--15int dir2 = (hashcode&0xf0)>>4;  //0-15//构造新的保存目录String dir = savePath + "\\" + dir1 + "\\" + dir2;  //upload\2\3  upload\3\5//File既可以代表文件也可以代表目录File file = new File(dir);//如果目录不存在if(!file.exists()){//创建目录file.mkdirs();}return dir;}public void doPost(HttpServletRequest request, HttpServletResponse response)throws ServletException, IOException {doGet(request, response);}private void returnResultJson(HttpServletResponse response,Object obj) {PrintWriter pw = null;try {pw = response.getWriter();JSONObject resultmessage = JsonUtil.generate(obj);response.setCharacterEncoding("UTF-8");response.setContentType("application/json");response.setHeader("Cache-Control", "no-cache");pw.write(resultmessage.toString());} catch (Exception e) {pw.write("系统异常，请联系管理员");} finally {pw.flush();pw.close();}}
}

	Uploadcom.app.action.UpLoadUpload/fileUpload/UploadServlet

现在配置jsp页面吧(jquery),这样的页面插件很多，可以网上搜索你喜欢的。