java使用POI实现Excel批量导入数据

1.准备工作

1.1 创建模板表头与数据库表字段一一对应，示例如下

1.2将模板放入项目中,如下图所示：

2.前端页面

2.1 使用超链接提供模板下载地址

 1 <html lang="zh_CN" xmlns:th="http://www.thymeleaf.org"
 2       xmlns:shiro="http://www.pollix.at/thymeleaf/shiro">
 3 <meta charset="utf-8">
 4 <head th:include="include::header"></head>
 5 <title>导入</title>
 6 <style type="text/css">
 7 .message .files {
 8     position: absolute;
 9     left: -1000px;
10     top: 52px;
11     heigth: 26px;
12     cursor: pointer;
13     filter: Alpha(opacity = 0);
14     -moz-opacity: 0;
15     opacity: 0;
16 }
17 </style>
18 </head>
19 <body>
20     <form id="signupForm"  method="post" enctype="multipart/form-data">
21         <input name="publishTaskId" id="publishTaskId" th:value="${publishTaskId}" hidden>
22         <div id="dpLTE" class="container-fluid tc-box">
23             <table class="form" id="form" style="table-layout: fixed;">
24                 <tr>
25                     <td colspan="2">
26                         <a id="zhCna"
27                            href='/modelExcle/fieldTemplate.xlsx'>
28                                 <button type="button" class="btn btn-default"
29                                     style="margin-bottom: 10px">
30                                     <i class="fa fa-download"></i> 下载导入模板
31                                 </button>
32                         </a>
33                         <div class="alert alert-warning">提示：请先下载批量导入模板-excel文件，按格式填写后上传提交，方可导入；
34                         </div>
35                     </td>
36                 </tr>
37                 <tr>
38                     <td><input type="text" id="txt" name="txt"
39                         class="input form-control" value="文件域" disabled="disabled" /></td>
40                     <td class="message">
41                         <input type="button"
42                         onMouseMove="f.style.pixelLeft=event.x-60;f.style.pixelTop=this.offsetTop;"
43                         value="选择文件" size="30" onClick="f.click()" class="btn btn-orange"
44                         style="margin-left: 10px"> 
45                         <input type="file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" name="SensitiveExcle" id="f"
46                                onChange="txt.value=this.value" style="height: 26px;" class="files" size="1" hidefocus>
47                     </td>
48                 </tr>
49             </table>
50         </div>
51         <div class="form-group">
52             <div class="col-sm-6 col-sm-offset-5">
53                 <button type="submit" class="btn btn-primary">提交</button>
54             </div>
55         </div>
56     </form>
57     <div th:include="include::footer"></div>
58 
59     <script src="/js/appjs/sys/auditSecPage/import.js"></script>
60 </body>
61 </html>

2.2 js中调用后台方法接收EXCEL文件流

 1 function save() {
 2     var formData = new FormData($('#signupForm')[0]);
 3     $.ajax({
 4         url : "/sys/audit/importdata",
 5         type: 'POST',
 6         data: formData,
 7         async: true,
 8         cache: false,
 9         contentType: false,
10         processData: false,
11         error : function(request) {
12             parent.layer.alert("网络超时");
13         },
14         success : function(data) {
15             if (data.code == 0) {
16                 parent.layer.msg("操作成功");
17                 parent.reLoad();
18                 var index = parent.layer.getFrameIndex(window.name);
19                 parent.layer.close(index);
20             } else {
21                 parent.layer.alert(data.msg)
22             }
23 
24         }
25     });
26 
27 }

3.对应后台业务逻辑

3.1 pom文件中引入对应依赖

 1 <!-- 文件上传组件 -->
 2         <!-- https://mvnrepository.com/artifact/commons-net/commons-net -->
 3         <dependency>
 4             <groupId>commons-net</groupId>
 5             <artifactId>commons-net</artifactId>
 6             <version>3.3</version>
 7         </dependency>
 8         <dependency>
 9             <groupId>org.apache.poi</groupId>
10             <artifactId>poi</artifactId>
11             <version>3.9</version>
12         </dependency>
13         <dependency>
14             <groupId>org.apache.poi</groupId>
15             <artifactId>poi-ooxml</artifactId>
16             <version>3.9</version>
17         </dependency>

3.2对应的工具类编写

3.2.1封装返回结果

 1 import java.util.HashMap;
 2 import java.util.Map;
 3 
 4 public class R extends HashMap<String, Object> {
 5     private static final long serialVersionUID = 1L;
 6 
 7     public R() {
 8         put("code", 0);
 9         put("msg", "操作成功");
10     }
11 
12     public static R error() {
13         return error(1, "操作失败");
14     }
15 
16     public static R error(String msg) {
17         return error(500, msg);
18     }
19 
20     public static R error(int code, String msg) {
21         R r = new R();
22         r.put("code", code);
23         r.put("msg", msg);
24         return r;
25     }
26 
27     public static R ok(String msg) {
28         R r = new R();
29         r.put("msg", msg);
30         return r;
31     }
32 
33     public static R ok(Map<String, Object> map) {
34         R r = new R();
35         r.putAll(map);
36         return r;
37     }
38 
39     public static R ok() {
40         return new R();
41     }
42 
43     @Override
44     public R put(String key, Object value) {
45         super.put(key, value);
46         return this;
47     }
48 }

3.2.2 Excel导入工具类

  1 import org.apache.commons.lang.StringUtils;
  2 import org.apache.poi.hssf.usermodel.HSSFCell;
  3 import org.apache.poi.hssf.usermodel.HSSFDateUtil;
  4 import org.apache.poi.ss.usermodel.Cell;
  5 import org.apache.poi.ss.usermodel.Row;
  6 import org.apache.poi.ss.usermodel.Sheet;
  7 import org.apache.poi.ss.usermodel.Workbook;
  8 import org.apache.poi.xssf.usermodel.XSSFCell;
  9 import org.apache.poi.xssf.usermodel.XSSFWorkbook;
 10 
 11 import java.io.FileInputStream;
 12 import java.io.FileNotFoundException;
 13 import java.io.IOException;
 14 import java.text.DateFormat;
 15 import java.text.DecimalFormat;
 16 import java.text.SimpleDateFormat;
 17 import java.util.ArrayList;
 18 import java.util.Date;
 19 import java.util.List;
 20 
 21 
 22 /**
 23  * Excel导入工具类
 24  *
 25  * @author Evan.Zhang
 26  */
 27 public class ImportExcelUtils {
 28 
 29     /**
 30      * 创建WorkBook对象
 31      *
 32      * @param filePath
 33      * @return
 34      * @throws IOException
 35      */
 36     public static final Workbook createWorkbook(String filePath) throws IOException {
 37         if (StringUtils.isBlank(filePath)) {
 38             throw new IllegalArgumentException(MassageUtils.getMessage("10011"));
 39         }
 40         if (!FileUtil.isExists(filePath)) {
 41             throw new FileNotFoundException(MassageUtils.getMessage("10012"));
 42         }
 43         if (filePath.trim().toLowerCase().endsWith("xls")) {
 44             return new XSSFWorkbook(new FileInputStream(filePath));
 45         } else if (filePath.trim().toLowerCase().endsWith("xlsx")) {
 46             return new XSSFWorkbook(new FileInputStream(filePath));
 47         } else {
 48             throw new IllegalArgumentException(MassageUtils.getMessage("10013"));
 49         }
 50     }
 51 
 52     /**
 53      * 获取Sheet页面(按名称)
 54      *
 55      * @param wb
 56      * @param sheetName
 57      * @return
 58      */
 59     public static final Sheet getSheet(Workbook wb, String sheetName) {
 60         return wb.getSheet(sheetName);
 61     }
 62 
 63     /**
 64      * 获取Sheet页面(按页标)
 65      *
 66      * @param wb
 67      * @param index
 68      * @return
 69      */
 70     public static final Sheet getSheet(Workbook wb, int index) {
 71         return wb.getSheetAt(index);
 72     }
 73 
 74     /**
 75      * 获取Sheet页内容
 76      *
 77      * @param sheet
 78      * @return
 79      */
 80     public static final List<Object[]> listFromSheet(Sheet sheet) {
 81 
 82         List<Object[]> list = new ArrayList<Object[]>();
 83         for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
 84             Row row = sheet.getRow(r);
 85             if (row == null || row.getPhysicalNumberOfCells() == 0) continue;
 86             Object[] cells = new Object[row.getLastCellNum()];
 87             for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) {
 88                 Cell cell = row.getCell(c);
 89                 if (cell == null) continue;
 90                 //判断是否为日期类型
 91                 if (HSSFDateUtil.isCellDateFormatted(cell)) {
 92                 //用于转化为日期格式
 93                     Date d = cell.getDateCellValue();
 94                     DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
 95                     cells[c] = formater.format(d);
 96                 } else {
 97                     cells[c] = getValueFromCell(cell);
 98                 }
 99 
100 
101             }
102             list.add(cells);
103         }
104         return list;
105     }
106 
107 
108     /**
109      * 获取单元格内信息
110      *
111      * @param cell
112      * @return
113      */
114     public static final Object getValueFromCell(Cell cell) {
115         if (cell == null) {
116             System.out.println("Cell is null !!!");
117             return null;
118         }
119         Object result = null;
120         if (cell instanceof HSSFCell) {
121             if (cell != null) {
122                 // 单元格类型：Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
123                 int cellType = ((HSSFCell) cell).getCellType();
124                 switch (cellType) {
125                     case HSSFCell.CELL_TYPE_STRING:
126                         result = ((HSSFCell) cell).getStringCellValue();
127                         break;
128                     case HSSFCell.CELL_TYPE_NUMERIC:
129                         DecimalFormat df = new DecimalFormat("###.####");
130                         result = df.format(((HSSFCell) cell).getNumericCellValue());
131                         break;
132                     case HSSFCell.CELL_TYPE_FORMULA:
133                         result = ((HSSFCell) cell).getNumericCellValue();
134                         break;
135                     case HSSFCell.CELL_TYPE_BOOLEAN:
136                         result = ((HSSFCell) cell).getBooleanCellValue();
137                         break;
138                     case HSSFCell.CELL_TYPE_BLANK:
139                         result = null;
140                         break;
141                     case HSSFCell.CELL_TYPE_ERROR:
142                         result = null;
143                         break;
144                     default:
145                         System.out.println("枚举了所有类型");
146                         break;
147                 }
148             }
149         } else if (cell instanceof XSSFCell) {
150             if (cell != null) {
151                 // 单元格类型：Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
152                 int cellType = ((XSSFCell) cell).getCellType();
153                 switch (cellType) {
154                     case XSSFCell.CELL_TYPE_STRING:
155                         result = ((XSSFCell) cell).getRichStringCellValue().getString();
156                         break;
157                     case XSSFCell.CELL_TYPE_NUMERIC:
158                         DecimalFormat df = new DecimalFormat("###.####");
159                         result = df.format(((XSSFCell) cell).getNumericCellValue());
160                         break;
161                     case XSSFCell.CELL_TYPE_FORMULA:
162                         result = ((XSSFCell) cell).getNumericCellValue();
163                         break;
164                     case XSSFCell.CELL_TYPE_BOOLEAN:
165                         result = ((XSSFCell) cell).getBooleanCellValue();
166                         break;
167                     case XSSFCell.CELL_TYPE_BLANK:
168                         result = null;
169                         break;
170                     case XSSFCell.CELL_TYPE_ERROR:
171                         result = null;
172                         break;
173                     default:
174                         System.out.println("枚举了所有类型");
175                         break;
176                 }
177             }
178         }
179         return result;
180     }
181 
182 
183     /**
184      * 根据Sheet页导入Excel信息
185      *
186      * @param filePath   文件路径
187      * @param sheetIndex Sheet页下标
188      * @param startRow   开始列 ：默认第一列
189      * @param startLine  开始行 ：默认第一行
190      * @throws Exception
191      */
192     public static final List<Object[]> importExcelBySheetIndex(String filePath, int sheetIndex
193             , int startRow, int startLine) throws Exception {
194 
195         List<Object[]> resultList = null;
196 
197         //创建WorkBook对象
198         Workbook wb = createWorkbook(filePath);
199 
200         // 获取Sheet
201         Sheet sheet = ImportExcelUtils.getSheet(wb, sheetIndex);
202 
203         // 判断Sheet是否为空
204         if (sheet != null) {
205 
206             // 遍历Sheet
207             List<Object[]> list = ImportExcelUtils.listFromSheet(sheet);
208             if (list != null && list.size() > 0) {
209                 resultList = new ArrayList<Object[]>();
210                 if (startLine <= list.size()) {
211                     for (int i = startLine; i < list.size(); i++) {
212                         int nullCount = 0;
213                         Object[] rows = list.get(i);
214                         if (rows != null && rows.length > 0) {
215                             List<Object> resultObjects = new ArrayList<Object>();
216                             for (int n = startRow; n < rows.length; n++) {
217                                 if (IsNullUtils.isEmpty(rows[n])) {
218                                     nullCount++;
219                                 }
220                                 resultObjects.add(rows[n]);
221                             }
222 
223                             //判断空的单元格个数
224                             if (nullCount >= rows.length) {
225                                 break;
226                             } else {
227                                 resultList.add(resultObjects.toArray());
228                             }
229                         }
230                     }
231                 }
232             }
233         }
234         return resultList;
235     }
236 }

3.2.3 封装判空方法工具类

 1 import java.util.List;
 2 import java.util.Map;
 3 import java.util.Set;
 4 
 5 /**
 6  * 判空方法工具类
 7  * 
 8  */
 9 public class IsNullUtils {
10 
11     
12     /**
13      * 对象是否为空
14      * @param o String,List,Map,Object[],int[],long[]
15      * @return
16      */
17     @SuppressWarnings("rawtypes")
18     public static boolean isEmpty(Object o) {
19         if (o == null) {
20             return true;
21         }
22         if (o instanceof String) {
23             if (o.toString().trim().equals("")) {
24                 return true;
25             }
26             if (o.equals("null") || o.equals("NULL")) {
27                 return true;
28             }
29         } else if (o instanceof List) {
30             if (((List) o).size() == 0) {
31                 return true;
32             }
33         } else if (o instanceof Map) {
34             if (((Map) o).size() == 0) {
35                 return true;
36             }
37         } else if (o instanceof Set) {
38             if (((Set) o).size() == 0) {
39                 return true;
40             }
41         } else if (o instanceof Object[]) {
42             if (((Object[]) o).length == 0) {
43                 return true;
44             }
45         } else if (o instanceof int[]) {
46             if (((int[]) o).length == 0) {
47                 return true;
48             }
49         } else if (o instanceof long[]) {
50             if (((long[]) o).length == 0) {
51                 return true;
52             }
53         } 
54         return false;
55     }
56 
57 
58 }

3.2.4 文件上传方法

 1 import org.springframework.web.multipart.MultipartFile;
 2 import javax.servlet.http.HttpServletRequest;
 3 import java.io.File;
 4 import java.io.IOException;
 5 
 6 /**
 7  * 文件上传
 8  */
 9 public class UploadFile {
10 
11     /**
12      * 文件上传方法
13      */
14 
15     public static boolean fileUpLoad(MultipartFile[] files, HttpServletRequest request, String path)
16             throws IOException {
17 
18         if (files != null && files.length > 0) {
19             for (int i = 0; i < files.length; i++) {
20                 MultipartFile file = files[i];
21                 // 保存文件
22                 return saveFile(request, file, path);
23             }
24         }
25         return false;
26     }
27 
28     /**
29      * 保存上传文件
30      * 
31      * @param request
32      * @param file
33      * @return
34      */
35 
36     public static boolean saveFile(HttpServletRequest request, MultipartFile file, String path) {
37 
38         if (!file.isEmpty()) {
39             try {
40                 File saveDir = new File(path);
41                 if (!saveDir.getParentFile().exists())
42                     saveDir.getParentFile().mkdirs();
43                 // 转存文件
44                 file.transferTo(saveDir);
45                 return true;
46             } catch (Exception e) {
47                 e.printStackTrace();
48             }
49         }
50         return false;
51     }
52 
53 }

3.3控制层接收文件流

1 /*
2     * 批量导入数据
3     * */
4     @ResponseBody
5     @PostMapping("/importdata")
6     R importdata(@RequestParam("publishTaskId")    String publishTaskId,@RequestParam("SensitiveExcle") MultipartFile[] files,HttpServletRequest request)throws Exception {
7         return checkFieldInfoService.importData(publishTaskId,files,request);
8     }

3.4 编写接口

1 import org.springframework.web.multipart.MultipartFile;
2 
3 import javax.servlet.http.HttpServletRequest;
4 
5 public interface CheckFieldInfoService {
6     R importData(String publishTaskId,MultipartFile[] files, HttpServletRequest request);
7 }

3.5 实现插入业务逻辑

 1 package com.system.service.impl;
 2 
 3 
 4 import com.system.dao.CheckFieldInfoMapper;
 5 import com.system.domain.audit.CheckFieldInfo;
 6 import com.system.service.CheckFieldInfoService;
 7 import com.common.utils.R;
 8 import com.system.utils.ImportExcelUtils;
 9 import com.system.utils.IsNullUtils;
10 import com.system.utils.UploadFile;
11 import org.apache.poi.ss.usermodel.Sheet;
12 import org.apache.poi.ss.usermodel.Workbook;
13 import org.springframework.beans.factory.annotation.Autowired;
14 import org.springframework.stereotype.Service;
15 import org.springframework.web.multipart.MultipartFile;
16 import javax.servlet.http.HttpServletRequest;
17 import java.io.IOException;
18 import java.util.*;
19 
20 @Service
21 public class CheckFieldInfoServiceImpl implements CheckFieldInfoService {
22 
23     @Autowired
24     private CheckFieldInfoMapper checkFieldInfoMapper;
25     @Override
26     public R importData(String publishTaskId, MultipartFile[] files, HttpServletRequest request) {
27         int count = 0;
28         /*上传路径*/
29         String path = "/file/fileExcle/" + files[0].getOriginalFilename();
30         try {
31             boolean status = UploadFile.fileUpLoad(files, request, path);
32             if (!status) {
33                 return R.error("文件上传失败！");
34             }
35         } catch (IOException e1) {
36             // TODO Auto-generated catch block
37             e1.printStackTrace();
38         }
39         Workbook workbook = null;  //工作簿
40         Sheet sheet = null;         //工作表
41         String[] headers = null;   //表头信息
42 
43         try {
44             workbook = ImportExcelUtils.createWorkbook(path);
45         } catch (Exception e) {
46             e.printStackTrace();
47         }
48         sheet = ImportExcelUtils.getSheet(workbook, 0);
49         List<Object[]> oList = ImportExcelUtils.listFromSheet(sheet);
50         if (!IsNullUtils.isEmpty(oList)) {
51             headers = Arrays.asList(oList.get(0)).toArray(new String[0]);
52             if (!headers[0].replaceAll(" ", "").equals("ID")) {
53                 return R.error("请选择正确模板导入！");
54             }
55         }
56 
57         List<CheckFieldInfo> senList = new ArrayList<CheckFieldInfo>();
58         if (!IsNullUtils.isEmpty(oList.get(1))) {
59             for (int s = 1; s < oList.size(); s++) {
60                 String[] rows = null;
61                 rows = Arrays.asList(oList.get(s)).toArray(new String[0]);
62                 R r = null;
63                 CheckFieldInfo checkFieldInfo = new CheckFieldInfo();
64                 checkFieldInfo.setFieldId(UUID.randomUUID().toString());
65                 checkFieldInfo.setPublishTaskId(publishTaskId);
66                 checkFieldInfo.setId(Integer.parseInt(rows[0]));
67                 checkFieldInfo.setFieldname(rows[1]);
68                 checkFieldInfo.setFieldtype(rows[2]);
69                 checkFieldInfo.setLenPrecision(rows[3]);
70                 checkFieldInfo.setLenScala(rows[4]);
71                 checkFieldInfo.setFieldformat(rows[5]);
72                 checkFieldInfo.setChecknull(rows[6]);
73                 checkFieldInfo.setCheckrepeat(rows[7]);
74                 checkFieldInfo.setCheckenum(rows[8]);
75                 checkFieldInfo.setEnumvalue(rows[9]);
76                 senList.add(checkFieldInfo);
77             }
78             if (senList.size() > 0) {
79                 for (CheckFieldInfo c : senList) {
80                     count = checkFieldInfoMapper.insertData(c);
81                     if (count<=0){
82                         R.error("批量导入异常");
83                     }
84 
85                 }
86 
87             }
88         }
89         return R.ok();
90     }
91 }

完成以上操作即可简单实现POI方式使用Excel表格实现数据批量导入功能