Java导入excel并保存到数据库

首先建立好excel表格，并对应excel表格创建数据库表。

前台jsp页面：其中包含js

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
    <base href="<%=basePath%>">
    
    <title>导入excel</title>
    
    <meta http-equiv="pragma" content="no-cache">
    <meta http-equiv="cache-control" content="no-cache">
    <meta http-equiv="expires" content="0">
    <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
    <meta http-equiv="description" content="导入excel">
    <script type="text/javascript" src="view/js/jquery-1.8.2.js"></script>
</head>
<script type="text/javascript">
    var User = function() {
        this.init = function() {
            //模拟上传excel  
            $("#uploadEventBtn").unbind("click").bind("click", function() {
                $("#uploadEventFile").click();
            });
            $("#uploadEventFile").bind("change", function() {
                $("#uploadEventPath").attr("value",    $("#uploadEventFile").val());
            });
        };
        //点击上传钮  
        this.uploadBtn = function() {
            var uploadEventFile = $("#uploadEventFile").val();
            if (uploadEventFile == '') {
                alert("请择excel,再上传");
            } else if (uploadEventFile.lastIndexOf(".xls") < 0) {//可判断以.xls和.xlsx结尾的excel  
                alert("只能上传Excel文件");
            } else {
                var url = "excel/import.do";
                var formData = new FormData($('form')[0]);
                user.sendAjaxRequest(url, "POST", formData);
            }
        };
        this.sendAjaxRequest = function(url, type, data) {
            $.ajax({
                url : url,
                type : type,
                data : data,
                dataType : "json",
                success : function(result) {
                    alert(result.message);
                },
                error : function(result) {
                    alert(result.message);
                },
                cache : false,
                contentType : false,
                processData : false
            });
        };
    };
    var user;
    $(function() {
        user = new User();
        user.init();
    });
</script>
<body>
    <form enctype="multipart/form-data" id="batchUpload"  action="/excel/import" method="post" class="form-horizontal">    
        <button class="btn btn-success btn-xs" id="uploadEventBtn" style="height:26px;"  type="button" >择文件</button>  
        <input type="file" name="file"  style="0px;height:0px;" id="uploadEventFile">  
        <input id="uploadEventPath"  disabled="disabled"  type="text" placeholder="请择excel表" style="border: 1px solid #e6e6e6; height: 26px; 200px;" />                                           
    </form>  
    <button type="button" class="btn btn-success btn-sm"  onclick="user.uploadBtn()" >上传</button> 
  </body>
</html>

后台代码：

Controller

 1 import java.util.HashMap;
 2 import java.util.Map;
 3 
 4 import javax.servlet.http.HttpServletRequest;
 5 import javax.servlet.http.HttpServletResponse;
 6 
 7 import org.springframework.beans.factory.annotation.Autowired;
 8 import org.springframework.stereotype.Controller;
 9 import org.springframework.web.bind.annotation.RequestMapping;
10 import org.springframework.web.bind.annotation.RequestMethod;
11 import org.springframework.web.bind.annotation.RequestParam;
12 import org.springframework.web.bind.annotation.ResponseBody;
13 import org.springframework.web.multipart.MultipartFile;
14 
15 import service.ImportService;
16 
17 @Controller
18 @RequestMapping("/excel")
19 public class ImportExcelController{
20     @Autowired(required=true)
21     private ImportService importService;
22     
23     //导入excel
24     @RequestMapping(value = "/import", method=RequestMethod.POST)
25     @ResponseBody
26     public Map<String, Object> importExcel(@RequestParam(value="file",required = false) MultipartFile file, HttpServletRequest request,HttpServletResponse response){
27         Map<String, Object> map = new HashMap<String, Object>();
28         String result = importService.readExcelFile(file);  
29         map.put("message", result);
30         return map;  
31     }  
32 
33 }

service:

 1 import org.springframework.web.multipart.MultipartFile;
 2 
 3 public interface ImportService {  
 4       
 5     /** 
 6      * 读取excel中的数据,生成list 
 7      */  
 8     String readExcelFile(MultipartFile file);  
 9   
10 }

serviceImpl:

 1 import java.util.List;
 2 import java.util.Map;
 3 
 4 import org.springframework.beans.factory.annotation.Autowired;
 5 import org.springframework.stereotype.Service;
 6 import org.springframework.web.multipart.MultipartFile;
 7 
 8 import service.ImportService;
 9 import controller.ReadExcel;
10 import dao.UserDao;
11 
12 @Service  
13 public class ImportServiceImpl implements ImportService {
14     @Autowired(required = true) 
15     private UserDao userDao;
16     @Override
17     public String readExcelFile(MultipartFile file) {
18         String result = "";  
19         //创建处理EXCEL的类  
20         ReadExcel readExcel = new ReadExcel();  
21         //解析excel，获取上传的事件单  
22         List<Map<String, Object>> userList = readExcel.getExcelInfo(file);  
23         //至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,  
24         for(Map<String, Object> user:userList){
25             int ret = userDao.insertUser(user.get("name").toString(), user.get("sex").toString(), Integer.parseInt(user.get("age").toString()));
26             if(ret == 0){
27                 result = "插入数据库失败";
28             }
29         }
30         if(userList != null && !userList.isEmpty()){  
31             result = "上传成功";  
32         }else{  
33             result = "上传失败";  
34         }  
35         return result;  
36     }
37 
38 }

dao:

public interface UserDao {
    public int insertUser(String name, String sex, int age);
}

daoImpl:

 1 import org.springframework.beans.factory.annotation.Autowired;
 2 import org.springframework.dao.DataAccessException;
 3 import org.springframework.jdbc.core.JdbcTemplate;
 4 import org.springframework.stereotype.Component;
 5 
 6 import dao.UserDao;
 7 
 8 @Component
 9 public class UserDaoImpl implements UserDao {
10     @Autowired(required = true) 
11     private JdbcTemplate jdbcTemplate;
12     
13     @Override
14     public int insertUser(String name, String sex, int age) {
15         String sql = "insert into user(name,sex,age) values('"+ name +"','"+ sex +"',"+age+")"; 
16         int ret = 0;
17         try {
18             ret = jdbcTemplate.update(sql);
19         } catch (DataAccessException e) {
20             e.printStackTrace();
21         }
22         return ret;
23     }
24 
25 }

ReadExcel:

  1 package controller;
  2 
  3 import java.io.IOException;
  4 import java.io.InputStream;
  5 import java.util.ArrayList;
  6 import java.util.HashMap;
  7 import java.util.List;
  8 import java.util.Map;
  9 
 10 import org.apache.poi.hssf.usermodel.HSSFCell;
 11 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 12 import org.apache.poi.ss.usermodel.Cell;
 13 import org.apache.poi.ss.usermodel.Row;
 14 import org.apache.poi.ss.usermodel.Sheet;
 15 import org.apache.poi.ss.usermodel.Workbook;
 16 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 17 import org.springframework.web.multipart.MultipartFile;
 18 
 19 /**
 20  * 
 21  * @author hewangtong
 22  * 
 23  */
 24 public class ReadExcel {
 25     // 总行数
 26     private int totalRows = 0;
 27     // 总条数
 28     private int totalCells = 0;
 29     // 错误信息接收器
 30     private String errorMsg;
 31 
 32     // 构造方法
 33     public ReadExcel() {
 34     }
 35 
 36     // 获取总行数
 37     public int getTotalRows() {
 38         return totalRows;
 39     }
 40 
 41     // 获取总列数
 42     public int getTotalCells() {
 43         return totalCells;
 44     }
 45 
 46     // 获取错误信息
 47     public String getErrorInfo() {
 48         return errorMsg;
 49     }
 50 
 51     /**
 52      * 读EXCEL文件，获取信息集合
 53      * 
 54      * @param fielName
 55      * @return
 56      */
 57     public List<Map<String, Object>> getExcelInfo(MultipartFile mFile) {
 58         String fileName = mFile.getOriginalFilename();// 获取文件名
 59 //        List<Map<String, Object>> userList = new LinkedList<Map<String, Object>>();
 60         try {
 61             if (!validateExcel(fileName)) {// 验证文件名是否合格
 62                 return null;
 63             }
 64             boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
 65             if (isExcel2007(fileName)) {
 66                 isExcel2003 = false;
 67             }
 68             return createExcel(mFile.getInputStream(), isExcel2003);
 69         } catch (Exception e) {
 70             e.printStackTrace();
 71         }
 72         return null;
 73     }
 74 
 75     /**
 76      * 根据excel里面的内容读取客户信息
 77      * 
 78      * @param is      输入流
 79      * @param isExcel2003   excel是2003还是2007版本
 80      * @return
 81      * @throws IOException
 82      */
 83     public List<Map<String, Object>> createExcel(InputStream is, boolean isExcel2003) {
 84         try {
 85             Workbook wb = null;
 86             if (isExcel2003) {// 当excel是2003时,创建excel2003
 87                 wb = new HSSFWorkbook(is);
 88             } else {// 当excel是2007时,创建excel2007
 89                 wb = new XSSFWorkbook(is);
 90             }
 91             return readExcelValue(wb);// 读取Excel里面客户的信息
 92         } catch (IOException e) {
 93             e.printStackTrace();
 94         }
 95         return null;
 96     }
 97 
 98     /**
 99      * 读取Excel里面客户的信息
100      * 
101      * @param wb
102      * @return
103      */
104     private List<Map<String, Object>> readExcelValue(Workbook wb) {
105         // 得到第一个shell
106         Sheet sheet = wb.getSheetAt(0);
107         // 得到Excel的行数
108         this.totalRows = sheet.getPhysicalNumberOfRows();
109         // 得到Excel的列数(前提是有行数)
110         if (totalRows > 1 && sheet.getRow(0) != null) {
111             this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
112         }
113         List<Map<String, Object>> userList = new ArrayList<Map<String, Object>>();
114         // 循环Excel行数
115         for (int r = 1; r < totalRows; r++) {
116             Row row = sheet.getRow(r);
117             if (row == null) {
118                 continue;
119             }
120             // 循环Excel的列
121             Map<String, Object> map = new HashMap<String, Object>();
122             for (int c = 0; c < this.totalCells; c++) {
123                 Cell cell = row.getCell(c);
124                 if (null != cell) {
125                     if (c == 0) {
126                         // 如果是纯数字,比如你写的是25,cell.getNumericCellValue()获得是25.0,通过截取字符串去掉.0获得25
127                         if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
128                             String name = String.valueOf(cell.getNumericCellValue());
129                             map.put("name", name.substring(0, name.length() - 2 > 0 ? name.length() - 2 : 1));// 名称
130                         } else {
131                             map.put("name", cell.getStringCellValue());// 名称
132                         }
133                     } else if (c == 1) {
134                         if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
135                             String sex = String.valueOf(cell.getNumericCellValue());
136                             map.put("sex",sex.substring(0, sex.length() - 2 > 0 ? sex.length() - 2 : 1));// 性别
137                         } else {
138                             map.put("sex",cell.getStringCellValue());// 性别
139                         }
140                     } else if (c == 2) {
141                         if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
142                             String age = String.valueOf(cell.getNumericCellValue());
143                             map.put("age", age.substring(0, age.length() - 2 > 0 ? age.length() - 2 : 1));// 年龄
144                         } else {
145                             map.put("age", cell.getStringCellValue());// 年龄
146                         }
147                     }
148                 }
149             }
150             // 添加到list
151             userList.add(map);
152         }
153         return userList;
154     }
155 
156     /**
157      * 验证EXCEL文件
158      * 
159      * @param filePath
160      * @return
161      */
162     public boolean validateExcel(String filePath) {
163         if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))) {
164             errorMsg = "文件名不是excel格式";
165             return false;
166         }
167         return true;
168     }
169 
170     // @描述：是否是2003的excel，返回true是2003
171     public static boolean isExcel2003(String filePath) {
172         return filePath.matches("^.+\.(?i)(xls)$");
173     }
174 
175     // @描述：是否是2007的excel，返回true是2007
176     public static boolean isExcel2007(String filePath) {
177         return filePath.matches("^.+\.(?i)(xlsx)$");
178     }
179     
180 }

参考：http://blog.csdn.net/he140622hwt/article/details/78478960

相关阅读:
PAT (Advanced Level) Practice 1100 Mars Numbers (20分)
PAT (Advanced Level) Practice 1107 Social Clusters (30分) （并查集）
PAT (Advanced Level) Practice 1105 Spiral Matrix (25分)
PAT (Advanced Level) Practice 1104 Sum of Number Segments (20分)
PAT (Advanced Level) Practice 1111 Online Map (30分) (两次迪杰斯特拉混合)
PAT (Advanced Level) Practice 1110 Complete Binary Tree (25分) （完全二叉树的判断+分享致命婴幼儿错误）
PAT (Advanced Level) Practice 1109 Group Photo (25分)
PAT (Advanced Level) Practice 1108 Finding Average (20分)
P6225 [eJOI2019]异或橙子树状数组异或位运算
 P4124 [CQOI2016]手机号码数位DP
原文地址：https://www.cnblogs.com/smart-hwt/p/8243420.html