本文描述一种获取excel中数据，并保存数据库的方法，其实现的主要功能为：将excel上传服务器，得到该excel的绝对地址，解析excel中的内容，获取到行与列的内容，保存到mysql数据库。
 
    为实现该功能主要有两种方式：
 
    （1）使用ajax异步上传。
 
    （2）使用form表单提交。
 
   在第（1）中方法中，通过如下代码获取file文件的路径时，会遇到如下图中的隐藏式的路径，该路径并无法实现上传文件。
 
var file = document.getElementById("file").value;
 
 
    针对上述问题，笔者查阅了相当多的解决方法，但是未能解决，始终无法获取真实的绝对路径，从而无法实现上传文件，因为读者若执着使用该方法，可能会花费大量时间，且无法解决，若有人解决请在评论区加以解释，不胜感激！！！
 
    因此在本文中，主要陈述第（2）种方法实现获取excel数据存入MySQL的方法。
 
    本文中excel中主要存储的数据如下图所示（测试数据）。
 
 
    技术路线如下：
 
    获取excel文件（form提交）-------->上传服务器--------->获取excel文件在服务器的路劲-------->读取excel数据--------->存入MySQL
 
    实现代码如下：
     页面上传jsp代码：
 
 
<form action="${pageContext.request.contextPath}/UploadFile" enctype="multipart/form-data" method="post">  
        上传文件：<input type="file" name="file2"><br/>
           <input type="submit" value="提交"/>
    </form>
 
    获取页面数据的servlet，名称为UploadFile，其主要代码如下：
 
package com.njau.servlet;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.List;

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.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;

import com.njau.dao.ExcelOperate;
import com.njau.dao.QikanDao;
import com.njau.po.Qikan;


public class UploadFile extends HttpServlet {

	/**
	 * The doGet method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to get.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	private static final long serialVersionUID = 1L;
	final static String uploadAddres = System.getProperty("catalina.home")+"\\webapps\\Science\\uploadFile\\";
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		response.setContentType("text/html;charset=utf-8");
		request.setCharacterEncoding("UTF-8");
        //上传服务器部分
		String fileName = null;
			//1、创建一个DiskFileItemFactory工厂
	        DiskFileItemFactory factory = new DiskFileItemFactory();
	        //2、创建一个文件上传解析器
	        ServletFileUpload upload = new ServletFileUpload(factory);
	        //解决上传文件名的中文乱码
	        upload.setHeaderEncoding("UTF-8"); 
			factory.setSizeThreshold(1024 * 500);//设置内存的临界值为500K
			File linshi = new File("E:\\linshi");//当超过500K的时候，存到一个临时文件夹中
			factory.setRepository(linshi);
			upload.setSizeMax(1024 * 1024 * 5);//设置上传的文件总的大小不能超过5M
			try {
				// 1. 得到 FileItem 的集合 items
				List<FileItem> /* FileItem */items = upload.parseRequest(request);
	 
				// 2. 遍历 items:
				for (FileItem item : items) {
					// 若是一个一般的表单域, 打印信息
					if (item.isFormField()) {
						String name = item.getFieldName();
						String value = item.getString("utf-8");
	 
						System.out.println(name + ": " + value);
						
						
					}
					// 若是文件域则把文件保存到 e:\\files 目录下.
					else {
						fileName = item.getName();
						long sizeInBytes = item.getSize();
						//System.out.println(fileName);
						//System.out.println(sizeInBytes);
	 
						InputStream in = item.getInputStream();
						byte[] buffer = new byte[1024];
						int len = 0;
	 
						fileName = uploadAddres + fileName;//文件最终上传的位置
						System.out.println(fileName);
						OutputStream out = new FileOutputStream(fileName);
	 
						while ((len = in.read(buffer)) != -1) {
							out.write(buffer, 0, len);
						}
	 
						out.close();
						in.close();
					}
				}
	 
			} catch (FileUploadException e) {
				e.printStackTrace();
			}
        //解析excel，并存入数据库部分
		File file = new File(fileName);//获取excel文件
		ExcelOperate eo = new ExcelOperate();
	       String[][] result = eo.getData(file, 1);//调用解析方法，存入二维数组result

	       int rowLength = result.length;

	       for(int i=0;i<rowLength;i++) {

	           for(int j=0;j<result[i].length;j++) {
	 
	              //System.out.print(result[i][j]+"\t\t");//打印excel文件中数据
	              Qikan qk = new Qikan();
	              qk.setName(result[i][1]);
				  qk.setFangxiang(result[i][0]);
				  qk.setSx(result[i][2]);
				  qk.setLink(result[i][3]);
				  int type = QikanDao.getMark(qk);//检查数据库中是否以存在需上传数据
				 // System.out.println(type);
				  if(type==0){
					  QikanDao.saveQikan(qk);//存入MySQL数据库
				  }
				  
				  break;//跳出循环
	           }

	           System.out.println();

	       }
	}

	/**
	 * The doPost method of the servlet. <br>
	 *
	 * This method is called when a form has its tag value method equals to post.
	 * 
	 * @param request the request send by the client to the server
	 * @param response the response send by the server to the client
	 * @throws ServletException if an error occurred
	 * @throws IOException if an error occurred
	 */
	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		response.setContentType("text/html");
		doGet(request, response);

	}

}
 
    Java获取excel数据代码，名称为ExcelOperate，即上述servlet中调用的getData（）方法，具体实现代码如下：
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;


public class ExcelOperate {

    public static void main(String[] args) throws Exception {
        File file = new File("D:\\guide.csv");
        String[][] result = getData(file, 1);
        int rowLength = result.length;
        for (int i = 0; i < rowLength; i++) {
            System.out.println(result[i]);
            for (int j = 0; j < result[i].length; j++) {
                System.out.print(result[i][j] + "\t\t");
            }
            System.out.println();
        }
    }

    /**
     * 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
     *
     * @param file       读取数据的源Excel
     * @param ignoreRows 读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
     * @return 读出的Excel中数据的内容
     * @throws FileNotFoundException
     * @throws IOException
     */
    public static String[][] getData(File file, int ignoreRows)
            throws FileNotFoundException, IOException {
        List<String[]> result = new ArrayList<String[]>();
        int rowSize = 0;
        BufferedInputStream in = new BufferedInputStream(new FileInputStream(
                file));
        // 打开HSSFWorkbook
        POIFSFileSystem fs = new POIFSFileSystem(in);
        HSSFWorkbook wb = new HSSFWorkbook(fs);
        HSSFCell cell = null;
        for (int sheetIndex = 0; sheetIndex < wb.getNumberOfSheets(); sheetIndex++) {
            HSSFSheet st = wb.getSheetAt(sheetIndex);
            // 第一行为标题，不取
            for (int rowIndex = ignoreRows; rowIndex <= st.getLastRowNum(); rowIndex++) {
                HSSFRow row = st.getRow(rowIndex);
                if (row == null) {
                    continue;
                }
                int tempRowSize = row.getLastCellNum() + 1;
                if (tempRowSize > rowSize) {
                    rowSize = tempRowSize;
                }
                String[] values = new String[rowSize];
                Arrays.fill(values, "");
                boolean hasValue = false;
                for (short columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
                    String value = "";
                    cell = row.getCell(columnIndex);
                    if (cell != null) {
                        // 注意：一定要设成这个，否则可能会出现乱码,后面版本默认设置
                        //cell.setEncoding(HSSFCell.ENCODING_UTF_16);
                        switch (cell.getCellType()) {
                            case HSSFCell.CELL_TYPE_STRING:
                                value = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    Date date = cell.getDateCellValue();
                                    if (date != null) {
                                        value = new SimpleDateFormat("yyyy-MM-dd")
                                                .format(date);
                                    } else {
                                        value = "";
                                    }
                                } else {
                                    value = new DecimalFormat("0").format(cell

                                            .getNumericCellValue());
                                }
                                break;
                            case HSSFCell.CELL_TYPE_FORMULA:
                                // 导入时如果为公式生成的数据则无值
                                if (!cell.getStringCellValue().equals("")) {
                                    value = cell.getStringCellValue();
                                } else {
                                    value = cell.getNumericCellValue() + "";
                                }
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                break;
                            case HSSFCell.CELL_TYPE_ERROR:
                                value = "";
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                value = (cell.getBooleanCellValue() == true ? "Y"

                                        : "N");
                                break;
                            default:
                                value = "";
                        }
                    }
                    if (columnIndex == 0 && value.trim().equals("")) {
                        break;
                    }
                    values[columnIndex] = rightTrim(value);
                    hasValue = true;
                }
                if (hasValue) {
                    result.add(values);
                }
            }
        }
        in.close();
        String[][] returnArray = new String[result.size()][rowSize];
        for (int i = 0; i < returnArray.length; i++) {
            returnArray[i] = (String[]) result.get(i);
        }
        return returnArray;
    }


    /**
     * 去掉字符串右边的空格
     *
     * @param str 要处理的字符串
     * @return 处理后的字符串
     */

    public static String rightTrim(String str) {
        if (str == null) {
            return "";
        }
        int length = str.length();
        for (int i = length - 1; i >= 0; i--) {
            if (str.charAt(i) != 0x20) {
                break;
            }
            length--;
        }
        return str.substring(0, length);
    }
}
 
    Java，检查数据库中是否已存在需上传数据，调用函数名为getMark（）方法，代码如下所示：
 
public static int getMark(Qikan qk) {
		int mark = 0;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Connection conn = null;
		String name = qk.getName();
		String sql="select name from qk where name='"+name+"'";//��ѯ���?
		try {
			conn = DB.getConn();
			pstmt = DB.getPStmt(conn, sql);
			rs = pstmt.executeQuery();
			if(rs.next()){
				mark=1;
			}
	}catch (SQLException e) {
		e.printStackTrace();
	} finally {
		DB.closeRs(rs);
		DB.closeStmt(pstmt);
		DB.closeConn(conn);
	}
	return mark;
	}
}
 
    存入MySQL数据库代码如下：
 
public static void saveQikan(Qikan qk) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = DB.getConn();
			String sql = "insert into qk value (?,?,?,?,?) "; 
			pstmt = DB.getPStmt(conn, sql);
			pstmt.setObject(1, null);
			pstmt.setString(2, qk.getFangxiang());
			pstmt.setString(3, qk.getName());
			pstmt.setString(4, qk.getSx());
			pstmt.setString(5, qk.getLink());
			
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DB.closeStmt(pstmt);
			DB.closeConn(conn);
		}
		
	}
 
注：文中使用jdbc方法访问数据库，具体的工具类如下：
 
package com.njau.common;

import java.sql.*;


public class DB {
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");//锟斤拷锟斤拷锟斤拷菘锟斤拷锟斤拷锟斤拷Class.forName(锟斤拷锟斤拷锟斤拷锟�
		} catch (ClassNotFoundException e) {
			e.printStackTrace();//锟斤拷锟斤拷撇锟斤拷锟斤拷诨锟斤拷锟斤拷锟矫伙拷屑锟斤拷锟�
		}
	}

	private DB() {
	}

	public static Connection getConn() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/sciencenew?useUnicode=true&characterEncoding=utf-8", "root", "123456");//通锟斤拷锟矫伙拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷拥锟街凤拷锟饺★拷锟捷匡拷锟斤拷锟接讹拷锟斤拷DriverManager.getConnection(锟斤拷锟接碉拷址,锟矫伙拷锟斤拷,锟斤拷锟斤拷)
		} catch (SQLException e) {
			e.printStackTrace();//锟斤拷莸锟街凤拷锟斤拷锟斤拷锟斤拷锟矫伙拷锟斤拷锟斤拷锟斤拷写锟斤拷
		}
		return conn;
	}

	public static void closeConn(Connection conn) {//锟截憋拷锟斤拷锟斤拷菘锟斤拷锟斤拷锟斤拷
		try {
			if (conn != null) {
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static Statement getStmt(Connection conn) {
		Statement stmt = null;
		try {
			stmt = conn.createStatement();//锟斤拷锟斤拷Statement实锟斤拷,Statement stmt = conn.createStatement()锟斤拷Statement锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷SQL锟斤拷锟斤拷锟斤拷锟藉，锟斤拷为Java锟斤拷锟斤拷锟睫凤拷锟斤拷锟斤拷锟斤拷要锟斤拷锟斤拷锟斤拷菘锟斤拷锟斤拷锟斤拷锟斤拷锟�
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stmt;
	}

	public static PreparedStatement getPStmt(Connection conn, String sql) {//使锟斤拷PreparedStatement实锟斤拷锟斤拷删锟侥诧拷
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pstmt;
	}

	public static void closeStmt(Statement stmt) {
		try {
			if (stmt != null) {
				stmt.close();
				stmt = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public static ResultSet executeQuery(Statement stmt, String sql) {//执锟叫诧拷询SQL锟斤拷洌拷锟斤拷锟斤拷亟锟斤拷ResultSet rs = stmt.executeQuery(sql)
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	public static void closeRs(ResultSet rs) {//锟截憋拷锟斤拷锟接ｏ拷rs.close()锟斤拷stmt.close()锟斤拷conn.close()
		try {
			if (rs != null) {
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static ResultSet executeQuery(Connection conn, String sql) {
		ResultSet rs = null;
		try {
			rs = conn.createStatement().executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

}
 
参考https://www.cnblogs.com/winner-0715/p/6690094.html

一、电商系统和办公系统时常会用到Excel的导入与导出，在JAVA代码实现时，通常使用POI来处理，今天用一个demo为大家介绍POI上传excel文件并将数据导入数据库的实现过程。demo是一个jsp/servlet+maven的web项目。
 
二、环境：
 
    数据库：mysql
 
    excel：*.xls
 
    工具：IDEA
 
三、介绍MAVEN配置文件，引入poi，jstl（因为页面展示用到了el表达式），mysql
 
 
<dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>
    <dependency><groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.5-FINAL</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.32</version>
    </dependency>
    <dependency>
      <groupId>javax.servlet</groupId>
      <artifactId>jstl</artifactId>
      <version>1.2</version>
    </dependency>
    <dependency>
      <groupId>taglibs</groupId>
      <artifactId>standard</artifactId>
      <version>1.1.2</version>
    </dependency>
  </dependencies>
 
 

 
 四、各个页面

     前端页面： 

 
 
    前端代码：
 
 
<%@ page import="java.io.File" %>
<%@ page import="java.util.ArrayList" %>
<%@ page import="java.util.List" %>
<%@page language="java" contentType="text/html;charset=UTF-8" pageEncoding="utf-8" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@ page isELIgnored="false" %>
<%--显示文件夹中已上传的文件--%>
<%
    String savePath = getClass().getResource("/").getPath();
    File file = new File(savePath);
    File[] arr = file.listFiles();
    List<String> array = new ArrayList<String>();
    for(int m=0;m<arr.length;m++){
        if(arr[m].getName().endsWith(".xls")) {
            array.add(arr[m].getName());
        }
    }
    System.out.println(array.size());

%>
<html>
<body>
<h2>Hello World!</h2>
<meta charset="utf-8">

<h3>上传</h3>
<form action="UploadServlet" enctype="multipart/form-data" method="post">
<input type="file" id="excel" name="excel" />
<input  type="submit" value="Upload">
</form>

<hr>
<h3>下载目录</h3>
<ul>
    <%--这里是下载操作--%>
<c:forEach var="i" items="<%=array%>">
    <a href="DownloadServlet?filename=${i}"><li>${i}</li></a>
</c:forEach>
</ul>
</body>
</html>
    Excel文件： 
 

 
 
    数据库表结构：
 

 
 

 
 
五、代码实现
 
JDBC连接数据库：
 
PropertiesUtils.java
 
 
public class PropertiesUtils {

    static Properties prop = new Properties();
    /**
     * @param fileName 需要加载的properties文件，文件需要放在src根目录下
     * 是否加载成功
     */
    public static boolean loadFile(String fileName){
        try {
            prop.load(PropertiesUtils.class.getClassLoader().getResourceAsStream(fileName));
        } catch (IOException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }
    /**
     * 根据KEY取回相应的value

     */
    public static String getPropertyValue(String key){
        return prop.getProperty(key);
    }
}
 
 

 
 BaseDao.java 
 
public class BaseDao {

    protected Connection conn;

    protected PreparedStatement ps;

    protected Statement stmt;

    protected ResultSet rs;

    //获取数据库连接
    public boolean getConnection(){
        //读取配置信息
        PropertiesUtils.loadFile("config.properties");

        String url = PropertiesUtils.getPropertyValue("url");
        String username = PropertiesUtils.getPropertyValue("username");
        String password = PropertiesUtils.getPropertyValue("password");
        String driver = PropertiesUtils.getPropertyValue("driver");

        //加载jdbc驱动
        try{
            Class.forName(driver);
            //与数据库建立连接
            conn = DriverManager.getConnection(url,username,password);
        }catch(ClassNotFoundException e){
            e.printStackTrace();
            return false;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return false;
        }
        return true;
    }
    //增删改
    public int executeUpdate(String sql,Object[] params){
        int updateRows = 0;
        getConnection();
        try{
            ps = conn.prepareStatement(sql);
            for(int i= 0;i<params.length;i++){
                ps.setObject(i+1, params[i]);
            }
            updateRows = ps.executeUpdate();
        }catch(SQLException e){
            e.printStackTrace();
        }
        return updateRows;
    }

    //查询
    public ResultSet executeSQL(String sql,Object[] params){
        getConnection();
        try{
            ps = conn.prepareStatement(sql);
            for(int i=0;i<params.length;i++){
                ps.setObject(i+1, params[i]);
            }
            rs = ps.executeQuery();
        }catch(SQLException e){
            e.printStackTrace();
        }
        return rs;
    }
    //关闭资源
    public boolean close(){
        if(rs!=null){
            try{
                rs.close();
            }catch(SQLException E){
                E.printStackTrace();
                return false;
            }
        }
        if(ps !=null){
            try{
                ps.close();
            }catch(SQLException E){
                E.printStackTrace();
                return false;
            }
        }
        if(stmt!=null){
            try{
                stmt.close();
            }catch(SQLException E){
                E.printStackTrace();
                return false;
            }
        }
        if(conn!=null){
            try{
                conn.close();
            }catch(SQLException E){
                E.printStackTrace();
                return false;
            }
        }
        return true;
    }

}
 
 

 
 UserDao.java 
 
public class UserDao extends BaseDao{
//用于上传excel时向数据库添加用户的方法
    public int addAllUser(List<User> users){
        int n = 0;
        for(User user:users){
            try{
                String sql = "insert into user_table values(?,?,?,?)";
                Object[] params = {user.getUid(),user.getUsername(),user.getAge(),user.getText()};
                int ex = this.executeUpdate(sql,params);
                n+=ex;
            }catch (Exception e){
                e.printStackTrace();
            }

        }
        return n;
    }
//用于下载导出excel时用的数据库查询方法
    public List<User> getAllUser() throws SQLException {
        List<User> users = new ArrayList<User>();
        User user = null;
        try{
            String sql = "select * from user_table";
            Object[] params = {};
            ResultSet rs = this.executeSQL(sql,params);
            while(rs.next()){
                user = new User();
                user.setUid(rs.getInt(0));
                user.setUsername(rs.getString(1));
                user.setAge(rs.getInt(2));
                user.setText(rs.getString(3));
                users.add(user);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return users;
    }
}
 
 

 
 config.properties 
 
##jdbc config

driver = com.mysql.jdbc.Driver
url = jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8
username = root
password = 123456
 
 

 
 Upload.java（关键servlet） 
 
import Dao.UserDao;
import Entity.User;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import java.io.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.MultipartConfig;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.Part;

/**
 * Created by qianbei476 on 2017/6/22.
 */
@MultipartConfig
@WebServlet(urlPatterns="/UploadServlet")
public class Upload extends HttpServlet {

    private POIFSFileSystem fs;
    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private HSSFRow row;

    UserDao userDao = new UserDao();

    public String savePath = getClass().getResource("/").getPath();
    public static String excelname;
    //上传文件保存目录

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        request.setCharacterEncoding("utf-8");
        getFile(request, response);  //上传文件

        List<User> users = getUser(excelname);  //读取excel中的users,生成user_table.xls

        int exi = userDao.addAllUser(users);  //将读取的List<User>插入数据库

        response.setCharacterEncoding("UTF-8");
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter pw = response.getWriter();
        pw.print("<script>添加成功，一共有:"+exi+"条数据</script>");

    }

//传入文件路径读取excel，得到List<User>
    public List<User> getUser(String fileurl) throws IOException {

        InputStream is = new FileInputStream(fileurl);
        fs = new POIFSFileSystem(is);
        wb = new HSSFWorkbook(fs);
        sheet = wb.getSheetAt(0);
        row = sheet.getRow(0);

        int rowNum = sheet.getLastRowNum();
        System.out.println("excel总行数：" + (rowNum+1));

        System.out.println("标题为：" + row.getCell(0));
        List<User> userList = new ArrayList<User>();
        User user = null;
        int i = 1;  //控制读取数
        while (i<rowNum) {
                user = new User();
                i++;
                user.setUid((int) (sheet.getRow(i).getCell(0).getNumericCellValue()));
                user.setUsername(sheet.getRow(i).getCell(1).getStringCellValue());
                user.setAge((int) (sheet.getRow(i).getCell(2).getNumericCellValue()));
                user.setText(sheet.getRow(i).getCell(3).getStringCellValue());
                userList.add(user);
            }

        return userList;

    }



//将上传的excel文件保存为文件，可选进行备份操作。
    public void getFile(HttpServletRequest request, HttpServletResponse response) throws IOException, ServletException {

        request.getInputStream();
        Part part = request.getPart("excel");
        String cd = part.getHeader("Content-Disposition");
        String[] cds = cd.split(";");
        String filename = cds[2].split("=")[1].replace("\"","");
        System.out.println("文件名:"+filename);

        InputStream is = part.getInputStream();
        filename = savePath+filename;
        System.out.println("路径:"+filename);
        excelname = filename;
        File file = new File(filename);
        FileOutputStream fos = new FileOutputStream(file); //生成excel文件
        byte[] b = new byte[1024];
        int len = 0;
        while((len = is.read(b)) != -1){
            fos.write(b,0,len);
        }
        is.close();
        fos.close();


    }
}
 
 

 
 
六、结果演示
 

 
 

 
 
七、注意
 
（1）Excel上传与下载时都容易出现的中文乱码问题，应该注意几点
 
    jsp页面显示乱码问题：
 
 
<%@page language="java" contentType="text/html;charset=UTF-8" pageEncoding="utf-8" %>
<meta charset="utf-8">

     servlet获取后乱码问题： 
 
 
request.setCharacterEncoding("utf-8");
    servlet返回后乱码问题： 
 
 
response.setCharacterEncoding("UTF-8");
response.setContentType("text/html;charset=UTF-8");
response.setContentType("application/vnd.ms-excel;charset=gb2312");
 
 
     数据库操作乱码问题： 
 
 
url = jdbc:mysql://localhost:3306/mydb?useUnicode=true&characterEncoding=utf8
 
 

 
 
 

 
 

 

关于前后台分离，vue+ springboot+ oracle实现上传excel和下载excel
 
Excel上传
 
上传
 
上传主要的就是如何将excel表中的数据取出来，有一下几个语句来实现取出excel的数据
 
1.处理Excel文件的类
 
XSSFWorkbook book = new XSSFWorkbook(inputStream);//inputSteam为字节输入流
 
2.拿到了Excel文件中第一个表
 
XSSFSheet sheet = book.getSheetAt(0);
 
3.获取excel中最大行数
 
int rownum = sheet.getPhysicalNumberOfRows()
 
4.获取excel中获得该行的最大列数
 
firstrow.getLastCellNum()
 
5.获取excel中单元格的内容转成字符串
 
 firstrow.getCell(i).toString()
 
2.总体的解析excel的代码
 
 System.out.println("开始解析excel");
        try {
        //存放字段的列表
            List<String> key=new ArrayList<>();
            //这里存放数据的类
            User user = new User();
            //filename为文件名    file为上传上来的文件
            //通过上传的文件获取上传的文件的输入流：因为解析excel文件时需要这个文件的输入流
            InputStream inputStream = file.getInputStream();
            //开始解析excel文件
            XSSFWorkbook book = new XSSFWorkbook(inputStream);//处理Excel文件的类
            XSSFSheet sheet = book.getSheetAt(0); //相当与拿到了Excel文件中第一个表
            int rownum = sheet.getPhysicalNumberOfRows();//获取excel表中的最大行数

            /*获取表头的字段列*/
            /*获取第一行*/
            Row firstrow=sheet.getRow(0);
            /*所有列的字段*/
            for (int i = 0; i < firstrow.getLastCellNum(); i++) {
                //获取当前单元格的内容
                /*System.out.println(row.getCell(i).toString());*/
                key.add(firstrow.getCell(i).toString());
            }
            for (int i = 1; i < rownum; i++) {
                Row row=  sheet.getRow(i);
                /*获取每一行的列数   添加到pd中*/
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    pd.put(key.get(j),row.getCell(j).toString());
                }
                System.out.println(pd);
                /*将取出来的数据添加到数据库中*/
                 //这里将取出的数据存到excel中

            }

            System.out.println("插入完成");


        } catch (Exception e) {
            e.printStackTrace();
        }
 
3.vue
 
上传文件的el-upload从element官网找到的这个组件在这里插入代码片
 
<el-upload
      ref="upload"//
      :on-preview="handlePreview"//
      :on-remove="handleRemove"//
      :before-remove="beforeRemove"//
      :limit="1"//属性上传文件的数量
      :on-exceed="handleExceed"//绑定如果超出了这个规定的数量调用的函数
      :file-list="file"//
      :auto-upload="false"//自动上传
      action=""//这里写上传的后台的路径
      enctype="multipart/form-data"//是指用于有文件上传 设置mime格式
      accept=".xls,.xlsx" //控制文件的类型
      multiple>//是指用于有文件上传 设置mime格式
      <el-button slot="trigger" size="small" type="primary">选取文件</el-button>

      <el-button style="margin-left: 10px;" size="small" type="success" @click="submitUpload">上传</el-button>
      <!--文本格式的提示使用该组件-->
      <div slot="tip" class="el-upload__tip">选取xlsx/xls文件，且不超过10M</div>
    </el-upload>
    //上传文件的函数
    refreshChange() {
                this.getList(this.page)
            },
 
这些属性官网都有可以通过element官网查找到

文件上传
 

public String uploadFile(CommonsMultipartFile file,String uploadPath,String realUploadPath){
		InputStream is = null;
		OutputStream os = null;
		Calendar calendar = Calendar.getInstance();//获取时间
		long excelName = calendar.getTime().getTime();
		
		try {
			is = file.getInputStream();
			String des = realUploadPath + "/"+Long.toString(excelName)+file.getOriginalFilename();
			os = new FileOutputStream(des);
			
			byte[] buffer = new byte[1024];
			int len = 0;
			
			while((len = is.read(buffer))>0){
				os.write(buffer);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(is!=null){
				try{
					is.close();
				}catch (Exception e2){
					e2.printStackTrace();
				}
			}
			
			if(os!=null){
				try{
					os.close();
				}catch (Exception e2){
					e2.printStackTrace();
				}
			}
		}
		//返回路径
		return uploadPath + "/"+Long.toString(excelName)+file.getOriginalFilename();
	}
	

 常用的Excel解析方式有两种JXL,POI
 
jxl用起来相对简单，但只支持Excel2003版本，也就是说jxl无法解析.xlsx的Excel文件，而POI会识别Excel版本进行解析，所以大部分人更倾向于POI
 
jxl使用时需要在项目中导入jxl.jar包
 
poi需要导入
 
 poi-3.14-20160307.jar 
 
poi-ooxml-3.14-20160307.jar
 
poi-ooxml-schemas-3.14-20160307.jar
 
 
commons-io-1.4.jar
 
commons-fileupload-1.2.1.jar
 
 
jxl解析代码
public String readExcel(CommonsMultipartFile file,HttpServletRequest request)throws IOException, WriteException{
		
		StringBuffer sb = new StringBuffer();//将读取的内容存入StringBUffer中
		try {
			Workbook book = Workbook.getWorkbook(file.getInputStream());
			try{
	            Sheet sheet = book.getSheet(0);
	            for(int i = 0 ; i < 3 ; i++){//i表示行数
	                for(int j = 0 ; j < 4 ; j++){//j表示列数
	                    sb.append(sheet.getCell(j, i).getContents()+"\t");
	                }
	                sb.append("\n");
	            }
	            System.out.println(sb);
	        }finally{
	            if(book != null){
	                book.close();
	            }
	        }
		} catch (BiffException e) {
	        System.err.println(e+"");
	    } catch (IOException e) {
	        System.err.println(e+"文件读取错误");
	    }
		return "";
	}
poi代码
 

    private POIFSFileSystem fs;
    private HSSFWorkbook wb;	
    private HSSFSheet sheet;	
    private HSSFRow row;
    /*读取标题excel第一行内容*/
	public String[] readExcelTitle(InputStream is) {
        try {
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        row = sheet.getRow(0);
        // 标题总列数
        int colNum = row.getPhysicalNumberOfCells();
        System.out.println("colNum:" + colNum);
        String[] title = new String[colNum];
        for (int i = 0; i < colNum; i++) {
            //title[i] = getStringCellValue(row.getCell((short) i));
            title[i] = getCellFormatValue(row.getCell((short) i));
        }
        return title;
    }
	/*读取内容*/
	 public void readExcelContent(InputStream is) {
        Map<Integer, ModelCourse> content = new HashMap<Integer, ModelCourse>();
        ModelCourse model=new ModelCourse();
        try {
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sheet = wb.getSheetAt(0);
        // 得到总行数
        int rowNum = sheet.getLastRowNum();
        row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();
        
        // 正文内容从第二行开始,第一行为表头的标题
        for (int i = 1; i <=rowNum; i++) {
            row = sheet.getRow(i);
            int j = 0;
           
           
            while (j < colNum) {
               
             if(j==1){
            		model.setCourse_id(getCellFormatValue(row.getCell((short) j)));
            	}
            	else if(j==2){
            		model.setCourse_name(getCellFormatValue(row.getCell((short) j)));
            	}
            	else if(j==3){
            		model.setCourse_time(getCellFormatValue(row.getCell((short) j)));
            	}
            	else if(j==4){
            		model.setCourse_place(getCellFormatValue(row.getCell((short) j)));
            	}
                j++;
               
            }
            content.put(i, model);
            addCourse(model);           
        }   
    }