1.准备工作
1.1 创建模板表头与数据库表字段一一对应，示例如下
1.2将模板放入项目中,如下图所示：
2.前端页面
2.1 使用超链接提供模板下载地址
<html lang="zh_CN" xmlns:th="http://www.thymeleaf.org"
xmlns:shiro="http://www.pollix.at/thymeleaf/shiro">
<meta charset="utf-8">
<head th:include="include::header"></head>
<title>导入</title>
<style type="text/css">
.message .files {
position: absolute;
left: -1000px;
top: 52px;
heigth: 26px;
cursor: pointer;
filter: Alpha(opacity = 0);
-moz-opacity: 0;
opacity: 0;
}
</style>
</head>
<body>
<form id="signupForm" method="post" enctype="multipart/form-data">
<input name="publishTaskId" id="publishTaskId" th:value="${publishTaskId}" hidden>
<div id="dpLTE" class="container-fluid tc-box">
<table class="form" id="form" style="table-layout: fixed;">
<tr>
<td colspan="2">
<a id="zhCna"
href='/modelExcle/fieldTemplate.xlsx'>
<button type="button" class="btn btn-default"
style="margin-bottom: 10px">
<i class="fa fa-download"></i> 下载导入模板
</button>
</a>
<div class="alert alert-warning">提示：请先下载批量导入模板-excel文件，按格式填写后上传提交，方可导入；
</div>
</td>
</tr>
<tr>
<td><input type="text" id="txt" name="txt"
class="input form-control" value="文件域" disabled="disabled" /></td>
<td class="message">
<input type="button"
onMouseMove="f.style.pixelLeft=event.x-60;f.style.pixelTop=this.offsetTop;"
value="选择文件" size="30" onClick="f.click()" class="btn btn-orange"
style="margin-left: 10px">
<input type="file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" name="SensitiveExcle" id="f"
onChange="txt.value=this.value" style="height: 26px;" class="files" size="1" hidefocus>
</td>
</tr>
</table>
</div>
<div class="form-group">
<div class="col-sm-6 col-sm-offset-5">
<button type="submit" class="btn btn-primary">提交</button>
</div>
</div>
</form>
<div th:include="include::footer"></div> <script src="/js/appjs/sys/auditSecPage/import.js"></script>
</body>
</html>
2.2 js中调用后台方法接收EXCEL文件流
function save() {
var formData = new FormData($('#signupForm')[0]);
$.ajax({
url : "/sys/audit/importdata",
type: 'POST',
data: formData,
async: true,
cache: false,
contentType: false,
processData: false,
error : function(request) {
parent.layer.alert("网络超时");
},
success : function(data) {
if (data.code == 0) {
parent.layer.msg("操作成功");
parent.reLoad();
var index = parent.layer.getFrameIndex(window.name);
parent.layer.close(index);
} else {
parent.layer.alert(data.msg)
}
}
});
}
3.对应后台业务逻辑
3.1 pom文件中引入对应依赖
<!-- 文件上传组件 -->
<!-- https://mvnrepository.com/artifact/commons-net/commons-net -->
<dependency>
<groupId>commons-net</groupId>
<artifactId>commons-net</artifactId>
<version>3.3</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
3.2对应的工具类编写
3.2.1封装返回结果
import java.util.HashMap;
import java.util.Map; public class R extends HashMap<String, Object> {
private static final long serialVersionUID = 1L; public R() {
put("code", 0);
put("msg", "操作成功");
} public static R error() {
return error(1, "操作失败");
} public static R error(String msg) {
return error(500, msg);
} public static R error(int code, String msg) {
R r = new R();
r.put("code", code);
r.put("msg", msg);
return r;
} public static R ok(String msg) {
R r = new R();
r.put("msg", msg);
return r;
} public static R ok(Map<String, Object> map) {
R r = new R();
r.putAll(map);
return r;
} public static R ok() {
return new R();
} @Override
public R put(String key, Object value) {
super.put(key, value);
return this;
}
}
3.2.2 Excel导入工具类
import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook; import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List; /**
* Excel导入工具类
*
* @author Evan.Zhang
*/
public class ImportExcelUtils { /**
* 创建WorkBook对象
*
* @param filePath
* @return
* @throws IOException
*/
public static final Workbook createWorkbook(String filePath) throws IOException {
if (StringUtils.isBlank(filePath)) {
throw new IllegalArgumentException(MassageUtils.getMessage("10011"));
}
if (!FileUtil.isExists(filePath)) {
throw new FileNotFoundException(MassageUtils.getMessage("10012"));
}
if (filePath.trim().toLowerCase().endsWith("xls")) {
return new XSSFWorkbook(new FileInputStream(filePath));
} else if (filePath.trim().toLowerCase().endsWith("xlsx")) {
return new XSSFWorkbook(new FileInputStream(filePath));
} else {
throw new IllegalArgumentException(MassageUtils.getMessage("10013"));
}
} /**
* 获取Sheet页面(按名称)
*
* @param wb
* @param sheetName
* @return
*/
public static final Sheet getSheet(Workbook wb, String sheetName) {
return wb.getSheet(sheetName);
} /**
* 获取Sheet页面(按页标)
*
* @param wb
* @param index
* @return
*/
public static final Sheet getSheet(Workbook wb, int index) {
return wb.getSheetAt(index);
} /**
* 获取Sheet页内容
*
* @param sheet
* @return
*/
public static final List<Object[]> listFromSheet(Sheet sheet) { List<Object[]> list = new ArrayList<Object[]>();
for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
Row row = sheet.getRow(r);
if (row == null || row.getPhysicalNumberOfCells() == 0) continue;
Object[] cells = new Object[row.getLastCellNum()];
for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell == null) continue;
//判断是否为日期类型
if (HSSFDateUtil.isCellDateFormatted(cell)) {
//用于转化为日期格式
Date d = cell.getDateCellValue();
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd");
cells[c] = formater.format(d);
} else {
cells[c] = getValueFromCell(cell);
} }
list.add(cells);
}
return list;
} /**
* 获取单元格内信息
*
* @param cell
* @return
*/
public static final Object getValueFromCell(Cell cell) {
if (cell == null) {
System.out.println("Cell is null !!!");
return null;
}
Object result = null;
if (cell instanceof HSSFCell) {
if (cell != null) {
// 单元格类型：Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
int cellType = ((HSSFCell) cell).getCellType();
switch (cellType) {
case HSSFCell.CELL_TYPE_STRING:
result = ((HSSFCell) cell).getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
DecimalFormat df = new DecimalFormat("###.####");
result = df.format(((HSSFCell) cell).getNumericCellValue());
break;
case HSSFCell.CELL_TYPE_FORMULA:
result = ((HSSFCell) cell).getNumericCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
result = ((HSSFCell) cell).getBooleanCellValue();
break;
case HSSFCell.CELL_TYPE_BLANK:
result = null;
break;
case HSSFCell.CELL_TYPE_ERROR:
result = null;
break;
default:
System.out.println("枚举了所有类型");
break;
}
}
} else if (cell instanceof XSSFCell) {
if (cell != null) {
// 单元格类型：Numeric:0,String:1,Formula:2,Blank:3,Boolean:4,Error:5
int cellType = ((XSSFCell) cell).getCellType();
switch (cellType) {
case XSSFCell.CELL_TYPE_STRING:
result = ((XSSFCell) cell).getRichStringCellValue().getString();
break;
case XSSFCell.CELL_TYPE_NUMERIC:
DecimalFormat df = new DecimalFormat("###.####");
result = df.format(((XSSFCell) cell).getNumericCellValue());
break;
case XSSFCell.CELL_TYPE_FORMULA:
result = ((XSSFCell) cell).getNumericCellValue();
break;
case XSSFCell.CELL_TYPE_BOOLEAN:
result = ((XSSFCell) cell).getBooleanCellValue();
break;
case XSSFCell.CELL_TYPE_BLANK:
result = null;
break;
case XSSFCell.CELL_TYPE_ERROR:
result = null;
break;
default:
System.out.println("枚举了所有类型");
break;
}
}
}
return result;
} /**
* 根据Sheet页导入Excel信息
*
* @param filePath 文件路径
* @param sheetIndex Sheet页下标
* @param startRow 开始列 ：默认第一列
* @param startLine 开始行 ：默认第一行
* @throws Exception
*/
public static final List<Object[]> importExcelBySheetIndex(String filePath, int sheetIndex
, int startRow, int startLine) throws Exception { List<Object[]> resultList = null; //创建WorkBook对象
Workbook wb = createWorkbook(filePath); // 获取Sheet
Sheet sheet = ImportExcelUtils.getSheet(wb, sheetIndex); // 判断Sheet是否为空
if (sheet != null) { // 遍历Sheet
List<Object[]> list = ImportExcelUtils.listFromSheet(sheet);
if (list != null && list.size() > 0) {
resultList = new ArrayList<Object[]>();
if (startLine <= list.size()) {
for (int i = startLine; i < list.size(); i++) {
int nullCount = 0;
Object[] rows = list.get(i);
if (rows != null && rows.length > 0) {
List<Object> resultObjects = new ArrayList<Object>();
for (int n = startRow; n < rows.length; n++) {
if (IsNullUtils.isEmpty(rows[n])) {
nullCount++;
}
resultObjects.add(rows[n]);
} //判断空的单元格个数
if (nullCount >= rows.length) {
break;
} else {
resultList.add(resultObjects.toArray());
}
}
}
}
}
}
return resultList;
}
}
3.2.3 封装判空方法工具类
import java.util.List;
import java.util.Map;
import java.util.Set; /**
* 判空方法工具类
*
*/
public class IsNullUtils { /**
* 对象是否为空
* @param o String,List,Map,Object[],int[],long[]
* @return
*/
@SuppressWarnings("rawtypes")
public static boolean isEmpty(Object o) {
if (o == null) {
return true;
}
if (o instanceof String) {
if (o.toString().trim().equals("")) {
return true;
}
if (o.equals("null") || o.equals("NULL")) {
return true;
}
} else if (o instanceof List) {
if (((List) o).size() == 0) {
return true;
}
} else if (o instanceof Map) {
if (((Map) o).size() == 0) {
return true;
}
} else if (o instanceof Set) {
if (((Set) o).size() == 0) {
return true;
}
} else if (o instanceof Object[]) {
if (((Object[]) o).length == 0) {
return true;
}
} else if (o instanceof int[]) {
if (((int[]) o).length == 0) {
return true;
}
} else if (o instanceof long[]) {
if (((long[]) o).length == 0) {
return true;
}
}
return false;
} }
3.2.4 文件上传方法
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import java.io.File;
import java.io.IOException; /**
* 文件上传
*/
public class UploadFile { /**
* 文件上传方法
*/ public static boolean fileUpLoad(MultipartFile[] files, HttpServletRequest request, String path)
throws IOException { if (files != null && files.length > 0) {
for (int i = 0; i < files.length; i++) {
MultipartFile file = files[i];
// 保存文件
return saveFile(request, file, path);
}
}
return false;
} /**
* 保存上传文件
*
* @param request
* @param file
* @return
*/ public static boolean saveFile(HttpServletRequest request, MultipartFile file, String path) { if (!file.isEmpty()) {
try {
File saveDir = new File(path);
if (!saveDir.getParentFile().exists())
saveDir.getParentFile().mkdirs();
// 转存文件
file.transferTo(saveDir);
return true;
} catch (Exception e) {
e.printStackTrace();
}
}
return false;
} }
3.3控制层接收文件流
/*
* 批量导入数据
* */
@ResponseBody
@PostMapping("/importdata")
R importdata(@RequestParam("publishTaskId") String publishTaskId,@RequestParam("SensitiveExcle") MultipartFile[] files,HttpServletRequest request)throws Exception {
return checkFieldInfoService.importData(publishTaskId,files,request);
}
3.4 编写接口
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
public interface CheckFieldInfoService {
R importData(String publishTaskId,MultipartFile[] files, HttpServletRequest request);
}
3.5 实现插入业务逻辑
package com.system.service.impl; import com.system.dao.CheckFieldInfoMapper;
import com.system.domain.audit.CheckFieldInfo;
import com.system.service.CheckFieldInfoService;
import com.common.utils.R;
import com.system.utils.ImportExcelUtils;
import com.system.utils.IsNullUtils;
import com.system.utils.UploadFile;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.util.*; @Service
public class CheckFieldInfoServiceImpl implements CheckFieldInfoService { @Autowired
private CheckFieldInfoMapper checkFieldInfoMapper;
@Override
public R importData(String publishTaskId, MultipartFile[] files, HttpServletRequest request) {
int count = 0;
/*上传路径*/
String path = "/file/fileExcle/" + files[0].getOriginalFilename();
try {
boolean status = UploadFile.fileUpLoad(files, request, path);
if (!status) {
return R.error("文件上传失败！");
}
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
Workbook workbook = null; //工作簿
Sheet sheet = null; //工作表
String[] headers = null; //表头信息 try {
workbook = ImportExcelUtils.createWorkbook(path);
} catch (Exception e) {
e.printStackTrace();
}
sheet = ImportExcelUtils.getSheet(workbook, 0);
List<Object[]> oList = ImportExcelUtils.listFromSheet(sheet);
if (!IsNullUtils.isEmpty(oList)) {
headers = Arrays.asList(oList.get(0)).toArray(new String[0]);
if (!headers[0].replaceAll(" ", "").equals("ID")) {
return R.error("请选择正确模板导入！");
}
} List<CheckFieldInfo> senList = new ArrayList<CheckFieldInfo>();
if (!IsNullUtils.isEmpty(oList.get(1))) {
for (int s = 1; s < oList.size(); s++) {
String[] rows = null;
rows = Arrays.asList(oList.get(s)).toArray(new String[0]);
R r = null;
CheckFieldInfo checkFieldInfo = new CheckFieldInfo();
checkFieldInfo.setFieldId(UUID.randomUUID().toString());
checkFieldInfo.setPublishTaskId(publishTaskId);
checkFieldInfo.setId(Integer.parseInt(rows[0]));
checkFieldInfo.setFieldname(rows[1]);
checkFieldInfo.setFieldtype(rows[2]);
checkFieldInfo.setLenPrecision(rows[3]);
checkFieldInfo.setLenScala(rows[4]);
checkFieldInfo.setFieldformat(rows[5]);
checkFieldInfo.setChecknull(rows[6]);
checkFieldInfo.setCheckrepeat(rows[7]);
checkFieldInfo.setCheckenum(rows[8]);
checkFieldInfo.setEnumvalue(rows[9]);
senList.add(checkFieldInfo);
}
if (senList.size() > 0) {
for (CheckFieldInfo c : senList) {
count = checkFieldInfoMapper.insertData(c);
if (count<=0){
R.error("批量导入异常");
} } }
}
return R.ok();
}
}
完成以上操作即可简单实现POI方式使用Excel表格实现数据批量导入功能