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="width:0px;height:0px;" id="uploadEventFile">  
        <input id="uploadEventPath"  disabled="disabled"  type="text" placeholder="请择excel表" style="border: 1px solid #e6e6e6; height: 26px;width: 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

posted @ 2018-01-08 16:35  smart_hwt  阅读(36858)  评论(3)  编辑  收藏  举报