将Excel文件导入数据库(POI+Excel+MySQL)(jsp页面导入)

版权声明:本文为博主Lynn&Lingfei原创文章,未经博主允许不得转载。 https://blog.csdn.net/meng564764406/article/details/52426634

现在正在做一个将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;
     }
 
 }

student.java
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;
     }
 
 }
common.java

package com.app.common;

public class Common {
	// connect the database
	    public 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;
	    
	    // common
	    public static final String EXCEL_PATH = "lib/student_info.xls";
	    
	    // sql
	    public 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 ";
}

readExcel.java

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<Student_1> readXls(String path) throws IOException {
         InputStream is = new FileInputStream(path);
         HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
         Student_1 student = null;
         List<Student_1> list = new ArrayList<Student_1>();
         // 循环工作表Sheet
         for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
             HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
             if (hssfSheet == null) {
                 continue;
             }
             // 循环行Row
             for (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());
             }
         }
 }
saveDB.java
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<Student_1> 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!");
             }
         }
     }
 }

Upload.java
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字节,也就是1MB
                    upload.setFileSizeMax(1024*1024);
                    //设置上传文件总量的最大值,最大值=同时上传的多个文件的大小的最大值的和,目前设置为10MB
                    upload.setSizeMax(1024*1024*10);
                    
                    //
                   
                    
                    //4、使用ServletFileUpload解析器解析上传数据,解析结果返回的是一个List<FileItem>集合,每一个FileItem对应一个Form表单的输入项
                    List<FileItem> 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--15
        int 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();
		}
	}
}
Web.xml
	<servlet>
	    <servlet-name>Upload</servlet-name>
	    <servlet-class>com.app.action.UpLoad</servlet-class>
	</servlet>
	
	<servlet-mapping>
	    <servlet-name>Upload</servlet-name>
	    <url-pattern>/fileUpload/UploadServlet</url-pattern>
	</servlet-mapping>
以上就是配置后台文件

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

	<div class="htmleaf-container">
		<div class="container kv-main">
            <!-- <div class="page-header">
            <h2>单张上传 <small></h2>
            </div> -->
            <!-- <form enctype="multipart/form-data" id="uploadForm"  method = "post"> -->
                <input id="fileUpload" class="file" type="file" name="fileUpload"  multiple  data-show-preview="true">
            <!-- </form> -->
        </div>
	</div>
	
    <script>
	    $("#fileUpload").fileinput({
	        language : "zh",//设置语言
	        uploadUrl: "${pageContext.request.contextPath}/fileUpload/UploadServlet",//上传地址
	        uploadAsync: true,//同步还是异步
	        showCaption:false,//是否显示标题
	        showUpload: true,//是否显示上传按钮
	        browseClass: "btn btn-primary", //按钮样式 
	        allowedFileExtensions : ['jpg', 'png','gif','xls'],//接收的文件后缀
	        allowedFileTypes: ['image', 'video', 'flash','excel'],//接收的文件类型['image', 'html', 'text', 'video', 'audio', 'flash','object']
	        maxFileCount: 6,//最大上传文件数限制
	        overwriteInitial: false,
	        maxFileSize: 1000, 
	        msgFilesTooMany: "选择上传的文件数量({n}) 超过允许的最大数值{m}!",
	        previewFileIcon: '<i class="glyphicon glyphicon-file"></i>', 
	        enctype: 'multipart/form-data',
	        /* allowedPreviewTypes: null, */
	        previewFileIconSettings: {
                'docx': '<i class="glyphicon glyphicon-file"></i>',
                'xlsx': '<i class="glyphicon glyphicon-file"></i>',
                'pptx': '<i class="glyphicon glyphicon-file"></i>',
                'jpg': '<i class="glyphicon glyphicon-picture"></i>',
                'pdf': '<i class="glyphicon glyphicon-file"></i>',
                'zip': '<i class="glyphicon glyphicon-file"></i>'
            }
});
四、源文件已经也好了,开始运行吧。

浏览器选择文件,点击会显示上传进度。



后台会对这里的数据保存到数据库。

数据库设计

数据库中数据如下


第二行就是存进来的数据了。
现在应该整个流程都走完了。

还有更详细的教程,请持续关注我的博客。

转载其他文章的出处:

http://www.cnblogs.com/hongten/p/java_poi_excel.html

展开阅读全文

没有更多推荐了,返回首页