导入excel

页面代码：

 1 <html>
 2 <head>
 3 <base href="<%=basePath%>">
 4  
 5 <title>导入excel</title>
 6  
 7 <meta http-equiv="pragma" content="no-cache">
 8 <meta http-equiv="cache-control" content="no-cache">
 9 <meta http-equiv="expires" content="0">
10 <meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
11 <meta http-equiv="description" content="导入excel">
12 <script type="text/javascript" src="view/js/jquery-1.8.2.js"></script>
13 </head>
14 <body>
15        <form enctype="multipart/form-data" id="batchUpload"  action="/excel/import" method="post" class="form-horizontal">    
16            <button class="btn btn-success btn-xs" id="uploadEventBtn" style="height:26px;"  type="button" >选择文件</button>  
17            <input type="file" name="file"  style="width:0px;height:0px;" id="uploadEventFile">  
18            <input id="uploadEventPath"  disabled="disabled"  type="text" placeholder="请选择excel表" style="border: 1px solid #e6e6e6; height: 26px;width: 200px;" />                                           
19        </form><button type="button" class="btn btn-success btn-sm"  onclick="user.uploadBtn()" >上传</button> 
20 </body>
21 </html>

js代码：

 1 <script type="text/javascript">
 2     var User = function() {
 3         this.init = function() {
 4             //模拟上传excel  
 5             $("#uploadEventBtn").unbind("click").bind("click", function() {
 6                 $("#uploadEventFile").click();
 7             });
 8             $("#uploadEventFile").bind("change", function() {
 9                 $("#uploadEventPath").attr("value",    $("#uploadEventFile").val());
10             });
11         };
12         //点击上传钮  
13         this.uploadBtn = function() {
14             var uploadEventFile = $("#uploadEventFile").val();
15             if (uploadEventFile == '') {
16                 alert("请择excel,再上传");
17             } else if (uploadEventFile.lastIndexOf(".xls") < 0) {//可判断以.xls和.xlsx结尾的excel  
18                 alert("只能上传Excel文件");
19             } else {
20                 var url = "excel/import.do";//此处改成自己后台控制层方法
21                 var formData = new FormData($('form')[0]);
22                 user.sendAjaxRequest(url, "POST", formData);
23             }
24         };
25         this.sendAjaxRequest = function(url, type, data) {
26             $.ajax({
27                 url : url,
28                 type : type,
29                 data : data,
30                 dataType : "json",
31                 success : function(result) {
32                     alert(result.message);
33                 },
34                 error : function(result) {
35                     alert(result.message);
36                 },
37                 cache : false,
38                 contentType : false,
39                 processData : false
40             });
41         };
42     };
43     var user;
44     $(function() {
45         user = new User();
46         user.init();
47     });
48 </script>

后台代码：controller(可将方法单独放入要导入的自己已有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:(按自己项目结构分层是否决定有这个接口层,如项目内没有接口层,则将serviceImpl方法直接写在service层)

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

serviceImpl:

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

持久层DAO层:(主要用于插入数据)如用的是mybais则省略此操作,使用mybatis方法进行插入数据

DAO:

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

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 :excel模板实体类,注意模板要与该对象的key匹配

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

posted @ 2019-07-03 14:14 火源阅读(253) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

火源

导入excel

公告