其中url地址自行填充
底层工具类：
public List<Map<String, Object>> excelImport(final MultipartFile file, final String extension, final int startRom,
final String[] fieldNames) throws IOException {
Map<String, Object> map = null;
final List<Map<String, Object>> mapList = new LinkedList<Map<String, Object>>();
if (extension.equals("xls")) {
HSSFWorkbook workBook = new HSSFWorkbook(file.getInputStream());
final HSSFSheet sheet = workBook.getSheetAt(0);
final int rows = sheet.getPhysicalNumberOfRows();
for (int i = startRom; i < rows; i ++ ) {
final HSSFRow row = sheet.getRow(i);
if (row != null) {
map = new HashMap<String, Object>();
final int cells = row.getPhysicalNumberOfCells();
for (int j = 0; j < cells; j ++ ) {
final HSSFCell cell = row.getCell(j);
if (cell != null) {
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_FORMULA :
switch (cell.getCachedFormulaResultType()) {
case XSSFCell.CELL_TYPE_NUMERIC :
map.put(fieldNames[j], cell.getNumericCellValue() + "");
break;
case XSSFCell.CELL_TYPE_STRING :
map.put(fieldNames[j], cell.getStringCellValue());
break;
default :
map.put(fieldNames[j], "");
break;
}
break;
case HSSFCell.CELL_TYPE_NUMERIC :
if (String.valueOf(cell.getNumericCellValue()).matches(".*[E|e].*")) {
final DecimalFormat df = new DecimalFormat("#.#");
// 指定最长小数点位为10
df.setMaximumFractionDigits(10);
map.put(fieldNames[j],
StringUtils.trimToEmpty(df.format(cell.getNumericCellValue())));
} else if (HSSFDateUtil.isCellDateFormatted(cell)) {
// add by dgx 2017-08-31 for
// 获取单元格内容为日期格式的数据
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
if (null != cell.getDateCellValue()
&& ! cell.getDateCellValue().toString().equals("")) {
final Date date = cell.getDateCellValue();
map.put(fieldNames[j], sdf.format(date));
} else {
map.put(fieldNames[j], "");
}
// add end by dgx
} else {
String value = cell.getNumericCellValue() + "";
final String[] item = value.split("[.]");
if (item.length > 1 && "0".equals(item[1])) {
value = item[0];
}
map.put(fieldNames[j], StringUtils.trimToEmpty(value));
}
break;
case HSSFCell.CELL_TYPE_STRING :
map.put(fieldNames[j], StringUtils.trimToEmpty(cell.getStringCellValue()));
break;
default :
map.put(fieldNames[j], "");
break;
}
}
}
mapList.add(map);
}
}
workBook = null;
} else if (extension.equals("xlsx")) {
XSSFWorkbook workBook = new XSSFWorkbook(file.getInputStream());
final XSSFSheet sheet = workBook.getSheetAt(0);
final int rows = sheet.getPhysicalNumberOfRows();
for (int i = startRom; i < rows; i ++ ) {
final XSSFRow row = sheet.getRow(i);
if (row != null) {
map = new HashMap<String, Object>();
final int cells = row.getPhysicalNumberOfCells();
for (int j = 0; j < cells; j ++ ) {
final XSSFCell cell = row.getCell(j);
if (cell != null) {
switch (cell.getCellType()) {
case XSSFCell.CELL_TYPE_FORMULA :
switch (cell.getCachedFormulaResultType()) {
case XSSFCell.CELL_TYPE_NUMERIC :
map.put(fieldNames[j], cell.getNumericCellValue() + "");
break;
case XSSFCell.CELL_TYPE_STRING :
map.put(fieldNames[j], cell.getStringCellValue());
break;
default :
map.put(fieldNames[j], "");
break;
}
break;
case HSSFCell.CELL_TYPE_NUMERIC :
if (String.valueOf(cell.getNumericCellValue()).matches(".*[E|e].*")) {
final DecimalFormat df = new DecimalFormat("#.#");
// 指定最长小数点位为10
df.setMaximumFractionDigits(10);
map.put(fieldNames[j],
StringUtils.trimToEmpty(df.format(cell.getNumericCellValue())));
} else if (HSSFDateUtil.isCellDateFormatted(cell)) {
// add by dgx 2017-08-31 for
// 获取单元格内容为日期格式的数据
SimpleDateFormat sdf = null;
if (cell.getCellStyle().getDataFormat() == HSSFDataFormat
.getBuiltinFormat("h:mm")) {
sdf = new SimpleDateFormat("HH:mm");
} else {
sdf = new SimpleDateFormat("yyyy-MM-dd");
}
if (null != cell.getDateCellValue()
&& ! cell.getDateCellValue().toString().equals("")) {
final Date date = cell.getDateCellValue();
map.put(fieldNames[j], sdf.format(date));
} else {
map.put(fieldNames[j], "");
}
// add end by dgx
} else {
String value = cell.getNumericCellValue() + "";
final String[] item = value.split("[.]");
if (item.length > 1 && "0".equals(item[1])) {
value = item[0];
}
map.put(fieldNames[j], StringUtils.trimToEmpty(value));
}
break;
case HSSFCell.CELL_TYPE_STRING :
map.put(fieldNames[j], StringUtils.trimToEmpty(cell.getStringCellValue()));
break;
default :
map.put(fieldNames[j], "");
break;
}
}
}
mapList.add(map);
}
}
workBook = null;
}
return mapList;
}Controller方法：
@RequestMapping(value = "")
@ResponseBody
public Object importExcel(@RequestParam(value = "file", required = false) final MultipartFile file,
HttpServletRequest request, HttpServletResponse response) {
SystemUser systemUser = getSessionUser(request);
// 获取文件后缀
String extension = FilenameUtils.getExtension(file.getOriginalFilename());
// 起始行数
int startRom = 1;
// 规定字段名称
final String[] fieldNames = {"type_name", "sid", "active_time", "active_place", "active_title", "active_level","remark"};
// 循环列表
List<Map<String, Object>> reList;
Map<String, Object> map = new HashMap<String, Object>();
// 数据列表
List<XgAssociatActivity> activityList = new ArrayList<>();
// 循环行数
int j = 0;
// 返回信息
String msg = "";
try {
// 调用封装导入方法
reList = new ExcelImport().excelImport(file, extension, startRom, fieldNames);
// 循环Excel中内容
for (final Map<String, Object> rd : reList) {
++ startRom;
String sid = "";
if (rd.get("sid").toString() != null && !rd.get("sid").toString().equals("")) {
XjXsxx xsInfo = xsService.selectById(rd.get("sid").toString());
if (xsInfo == null) {
msg += "行数:" + startRom + ",学号不存在！<br>";
} else {
sid = rd.get("sid").toString();
}
} else {
msg += "行数:" + startRom + ",学号不能为空！<br>";
}
String typeName = "";
if (rd.get("type_name") != null) {
typeName = rd.get("type_name").toString();
}
String activeTime = "";
if (rd.get("active_time") != null) {
String pattern = "\\d{4}[年]\\d{1,2}[月]\\d{1,2}[日]";
if (Pattern.matches(pattern, rd.get("active_time").toString())) {
activeTime = rd.get("active_time").toString();
} else {
msg += "行数:" + startRom + ",日期格式不正确！<br>";
}
} else {
msg += "行数:" + startRom + ",时间不能为空！<br>";
}
String activePlace = "";
if (rd.get("active_place") != null) {
activePlace = rd.get("active_place").toString();
}
String activeTitle = "";
if (rd.get("active_title") != null) {
activeTitle = rd.get("active_title").toString();
}
String activeLevel = "";
if (rd.get("active_level") != null) {
activeLevel = rd.get("active_level").toString();
}
String remark = "";
if (rd.get("remark") != null) {
remark = rd.get("remark").toString();
}
XgAssociatActivity activityInfo = new XgAssociatActivity(typeName, sid, activeTime, activePlace,
activeTitle, activeLevel, remark);
activityList.add(activityInfo);
}
// 如果msg为空证明无错直接插入
if (msg.isEmpty()) {
for (XgAssociatActivity info : activityList) {
info.setInputName(systemUser.getName());
service.insert(info);
// 先将行数自增 方便存入信息
j ++ ;
}
map.put("state", 1);
} else {
map.put("state", 2);
map.put("msg", msg);
}
map.put("sum", j);
} catch (final Exception e) {
e.printStackTrace();
}
return renderSuccess(map);
}html代码：
<!-- 导入start -->
<div class="modal fade" id="exportModal" tabindex="-1" role="dialog" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
<h4 class="modal-title">导入活动人员</h4>
</div>
<div class="modal-body">
<form id="exportFrm" class="form-horizontal m-t" enctype="multipart/form-data">
<input type="hidden" id="id" name="id">
<div class="form-group">
<label class="col-sm-3 control-label">上传文件：</label>
<div class="col-sm-8">
<input id="file" name="file" type="file" class="form-control" required>
</div>
</div>
<div class="form-group" id="errorMsg">
</div>
</form>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">取消</button>
<button id="btn" type="button" class="btn btn-primary">提交</button>
</div>
</div>
</div>
</div>
<!-- 导入end -->js代码：
//导入
function importExcel(){
$('#exportModal').modal('toggle');
$("#errorMsg").empty();
$("#exportFrm #file").val('');
}
//上传按钮事件
$("#exportModal #btn").click(function(){
var fileName = $("#exportFrm #file").val();
if (fileName == "") {
swal("提示信息", "请选择上传文件", "error");
return false;
}
var regexp = /.\.(xls|xlsx)$/;
if (!regexp.test(fileName)) {
swal("提示信息", "请导入Excel文件", "error");
return false;
}
var formData = new FormData($('#exportFrm')[0]);
$.ajax({
url : "",
type : "post",
data : formData,
cache : false,
processData : false,
contentType : false,
dataType : 'json',
success : function(data) {
if(data.data.state == 1){
$('#exportModal').modal('toggle');
swal("提示信息","导入成功,共计"+ data.data.sum +"条","success");
}else{
var html='<label class="col-sm-3 control-label">错误信息：</label><div class="col-sm-8" style="font-size: 18px;color: red;">'+ data.data.msg +'</div>';
$("#errorMsg").html(html);
}
}
});
});
版权声明：本文为qq_35766860原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接和本声明。