最近接了个小需求，要求excel的导入导出，以前写过servlet的，ssh的，ssm的，这次来个spring-boot的小demo
1、jar包依赖：
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.poi</groupId>
<artifactId>testpoi</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>
<name>testpoi</name>
<description>Demo project for Spring Boot</description>
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.0.3.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
<java.version>1.8</java.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>jstl</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-tomcat</artifactId>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.apache.tomcat.embed</groupId>
<artifactId>tomcat-embed-jasper</artifactId>
<!--<scope>provided</scope>-->
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!--热部署-->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-devtools</artifactId>
<optional>true</optional>
<scope>true</scope>
</dependency>
<!--POI-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<dependency>
<groupId>commons-io</groupId>
<artifactId>commons-io</artifactId>
<version>2.4</version>
</dependency>
<!-- https://mvnrepository.com/artifact/fr.myprysm.vertx/vertx-json -->
<dependency>
<groupId>fr.myprysm.vertx</groupId>
<artifactId>vertx-json</artifactId>
<version>0.1.3</version>
</dependency>
<!-- https://mvnrepository.com/artifact/net.sf.json-lib/json-lib -->
<dependency>
<groupId>net.sf.json-lib</groupId>
<artifactId>json-lib</artifactId>
<version>2.4</version>
</dependency>
</dependencies>
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
</build>
</project>
2、实体类：
package com.ultrapower.monitor.model;
public class User {
private Integer id;
private String uname;
private String phone;
private String address;
private String enrol_date;
private String des;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
public String getDes() {
return des;
}
public void setDes(String des) {
this.des = des;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
public String getEnrol_date() {
return enrol_date;
}
public void setEnrol_date(String enrol_date) {
this.enrol_date = enrol_date;
}
}
3、dao层也可以说是mapper层：
package com.ultrapower.monitor.dao;
import com.ultrapower.monitor.model.User;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository
@Component
public interface UserMapper {
List<User> selectUsers();
void updateUserByName(User user);
void addUser(User user);
int selectByName(String name);
}
4、service层：
package com.ultrapower.monitor.service;
import com.ultrapower.monitor.model.User;
import org.springframework.web.multipart.MultipartFile;
import java.util.List;
public interface UserService {
List<User> selectUsers();
boolean batchImport(String fileName, MultipartFile file) throws Exception;
}
5、serviceImpl:
package com.ultrapower.monitor.service;
import com.ultrapower.monitor.common.MyException;
import com.ultrapower.monitor.dao.UserMapper;
import com.ultrapower.monitor.model.User;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;
@Override
public List<User> selectUsers() {
return userMapper.selectUsers();
}
@Transactional(readOnly = false,rollbackFor = Exception.class)
@Override
public boolean batchImport(String fileName, MultipartFile file) throws Exception {
boolean notNull = false;
List<User> userList = new ArrayList<>();
if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
throw new MyException("上传文件格式不正确");
}
boolean isExcel2003 = true;
if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
isExcel2003 = false;
}
InputStream is = file.getInputStream();
Workbook wb = null;
if (isExcel2003) {
wb = new HSSFWorkbook(is);
} else {
wb = new XSSFWorkbook(is);
}
Sheet sheet = wb.getSheetAt(0);
if(sheet!=null){
notNull = true;
}
User user;
for (int r = 2; r <= sheet.getLastRowNum(); r++) {//r = 2 表示从第三行开始循环 如果你的第三行开始是数据
Row row = sheet.getRow(r);//通过sheet表单对象得到 行对象
if (row == null) {
continue;
}
//sheet.getLastRowNum() 的值是 10，所以Excel表中的数据至少是10条；不然报错 NullPointerException
user = new User();
if (row.getCell(0) != null) {
row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
if (row.getCell(0).getCellType() != 1) {//循环时，得到每一行的单元格进行判断
throw new MyException("导入失败(第" + (r + 1) + "行,用户名请设为文本格式)");
}
String name = row.getCell(0).getStringCellValue();//得到每一行第一个单元格的值
if (name == null || name.isEmpty()) {//判断是否为空
throw new MyException("导入失败(第" + (r + 1) + "行,用户名未填写)");
}
row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
String phone = row.getCell(1).getStringCellValue();
if (phone == null || phone.isEmpty()) {
throw new MyException("导入失败(第" + (r + 1) + "行,电话未填写)");
}
row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
String address = row.getCell(2).getStringCellValue();
if (phone == null || phone.isEmpty()) {
throw new MyException("导入失败(第" + (r + 1) + "行,地址未填写)");
}
row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
String data = row.getCell(3).getStringCellValue();
if (phone == null || phone.isEmpty()) {
throw new MyException("导入失败(第" + (r + 1) + "行,日期未填写)");
}
row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);//得到每一行的 第二个单元格的值
String des = row.getCell(4).getStringCellValue();
if (phone == null || phone.isEmpty()) {
throw new MyException("导入失败(第" + (r + 1) + "行,描述未填写)");
}
//完整的循环一次 就组成了一个对象
user.setUname(name);
user.setPhone(phone);
user.setAddress(address);
user.setEnrol_date(data);
user.setDes(des);
userList.add(user);
}
}
for (User userResord : userList) {
String name = userResord.getUname();
int cnt = userMapper.selectByName(name);
if (cnt == 0) {
userMapper.addUser(userResord);
System.out.println(" 插入 "+userResord);
} else {
userMapper.updateUserByName(userResord);
System.out.println(" 更新 "+userResord);
}
}
return notNull;
}
}
6、异常类：
package com.ultrapower.monitor.common;
public class MyException extends RuntimeException {
private static final long serialVersionUID = 1L;
/**
* 错误编码
*/
private String errorCode;
/**
* 消息是否为属性文件中的Key
*/
private boolean propertiesKey = true;
/**
* 构造一个基本异常.
*
* @param message
* 信息描述
*/
public MyException(String message)
{
super(message);
}
/**
* 构造一个基本异常.
*
* @param errorCode
* 错误编码
* @param message
* 信息描述
*/
public MyException(String errorCode, String message)
{
this(errorCode, message, true);
}
/**
* 构造一个基本异常.
*
* @param errorCode
* 错误编码
* @param message
* 信息描述
*/
public MyException(String errorCode, String message, Throwable cause)
{
this(errorCode, message, cause, true);
}
/**
* 构造一个基本异常.
*
* @param errorCode
* 错误编码
* @param message
* 信息描述
* @param propertiesKey
* 消息是否为属性文件中的Key
*/
public MyException(String errorCode, String message, boolean propertiesKey)
{
super(message);
this.setErrorCode(errorCode);
this.setPropertiesKey(propertiesKey);
}
/**
* 构造一个基本异常.
*
* @param errorCode
* 错误编码
* @param message
* 信息描述
*/
public MyException(String errorCode, String message, Throwable cause, boolean propertiesKey)
{
super(message, cause);
this.setErrorCode(errorCode);
this.setPropertiesKey(propertiesKey);
}
/**
* 构造一个基本异常.
*
* @param message
* 信息描述
* @param cause
* 根异常类（可以存入任何异常）
*/
public MyException(String message, Throwable cause)
{
super(message, cause);
}
public String getErrorCode()
{
return errorCode;
}
public void setErrorCode(String errorCode)
{
this.errorCode = errorCode;
}
public boolean isPropertiesKey()
{
return propertiesKey;
}
public void setPropertiesKey(boolean propertiesKey)
{
this.propertiesKey = propertiesKey;
}
}
7、mapper映射xml：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.ultrapower.monitor.dao.UserMapper">
<resultMap id="BaseResultMap" type="com.ultrapower.monitor.model.User">
<id column="id" property="id" jdbcType="INTEGER"/>
<result column="uname" property="uname" jdbcType="VARCHAR"/>
<result column="phone" property="phone" jdbcType="VARCHAR"/>
<result column="address" property="address" jdbcType="VARCHAR"/>
<result column="enrol_date" property="enrol_date" jdbcType="VARCHAR"/>
<result column="des" property="des" jdbcType="VARCHAR"/>
</resultMap>
<sql id="column_List">
id,uname,phone,address,enrol_date,des
</sql>
<select id="selectUsers" resultMap="BaseResultMap">
SELECT
<include refid="column_List"/>
FROM user
</select>
<update id="updateUserByName" parameterType="com.ultrapower.monitor.model.User">
update user set phone=#{phone},address=#{address},des=#{des},enrol_date=#{enrol_date} where uname = #{uname}
</update>
<insert id="addUser" parameterType="com.ultrapower.monitor.model.User">
INSERT INTO USER (uname,phone,address,enrol_date,des) VALUES (#{uname},#{phone},#{address},#{enrol_date},#{des})
</insert>
<select id="selectByName" resultType="Integer">
SELECT count(*) FROM user WHERE uname=#{uname}
</select>
</mapper>
8、前端页面：
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>监控配置页面</title>
<link th:href="@{bootstrap-3.3.7-dist/css/bootstrap.min.css}" rel="stylesheet"/>
<link th:href="@{bootstrap-table-1.12.1-dist/bootstrap-table.min.css}" rel="stylesheet"/>
<link th:href="@{font-awesome-4.7.0/css/font-awesome.min.css}" rel="stylesheet">
<link th:href="@{css/index.css}" rel="stylesheet">
<!-- 1.Jquery组件引用 -->
<script th:src="@{jquery/jquery-3.2.1.js}"></script>
<!-- 2.bootstrap组件引用 -->
<script th:src="@{bootstrap-3.3.7-dist/js/bootstrap.js}"></script>
<!-- 3.bootstrap table组件以及中文包的引用-->
<script th:src="@{bootstrap-table-1.12.1-dist/bootstrap-table.min.js}"></script>
<script th:src="@{bootstrap-table-1.12.1-dist/bootstrap-table-zh-CN.min.js}"></script>
<!-- 4.自定义js -->
<script th:src="@{js/common/bootbox.min.js}"></script>
<script th:src="@{js/common/common.js}"></script>
<script th:src="@{js/index.js}"></script>
</head>
<body>
<div class="panel-heading" style="padding-bottom: 5px">
<img th:src="@{/images/logo.png}" style="height: 85px; width: 400px"/>
<img th:src="@{/images/logo_st.png}" style="height: 85px; width: 85px">
</div>
<div class="panel-body" style="padding-bottom:0; padding-top: 5px;">
<!-- 查询部分-->
<div class="panel panel-default" style="margin-bottom:5px;">
<div class="panel-heading">查询条件</div>
<div class="panel-body">
<form id="formSearch" class="form-horizontal">
<div class="form-group" style="margin-top:15px">
<label class="control-label col-sm-1" for="txt_search_name">监控名称</label>
<div class="col-sm-2">
<input type="text" class="form-control" id="txt_search_name" title="请输入要查询的关键字，支持模糊查询">
</div>
<label class="control-label col-sm-1" for="txt_search_appname">系统名称</label>
<div class="col-sm-2">
<input type="text" class="form-control" id="txt_search_appname" title="请输入要查询的关键字，支持模糊查询">
</div>
<br/><br/><br/><br/>
<label class="control-label col-sm-1" for="txt_search_type">监控类型</label>
<div class="col-sm-2">
<select class="form-control" id="txt_search_type">
<option value="">请选择</option>
<option value="url">URL页面监控</option>
<option value="interface">URL接口监控</option>
<option value="db">数据库监控</option>
<option value="tcp">tcp连接监控</option>
<option value="file">文件监控</option>
<option value="email">邮件监控</option>
<option value="task">定时任务监控</option>
</select>
</div>
<label class="control-label col-sm-1" for="txt_search_state">监控状态</label>
<div class="col-sm-2">
<select class="form-control" id="txt_search_state">
<option value="">请选择</option>
<option value="Y">运行</option>
<option value="N">停止</option>
</select>
</div>
<div class="col-sm-3" style="text-align:left;">
<button type="button" style="margin-left:100px;width: 100px" id="btn_query" class="btn btn-primary">
查询
</button>
</div>
</div>
</form>
</div>
</div>
<!-- 操作部分-->
<div id="toolbar" class="btn-group">
<button id="btn_add" type="button" class="btn btn-default">
<span class="glyphicon glyphicon-plus" aria-hidden="true">新增</span>
</button>
<button id="btn_edit" type="button" class="btn btn-default">
<span class="glyphicon glyphicon-pencil" aria-hidden="true">修改</span>
</button>
<button id="btn_delete" type="button" class="btn btn-default">
<span class="glyphicon glyphicon-remove" aria-hidden="true">删除</span>
</button>
<button id="btn_batch_import" type="button" class="btn btn-default">
<span class="glyphicon glyphicon-plus" aria-hidden="true">批量导入</span>
</button>
</div>
<!-- 展示部分-->
<table id="tb_departments" style="table-layout: fixed;"></table>
<!--批量导入弹出框-->
<div class="modal fade" id="myModal1" tabindex="-1" role="dialog" aria-labelledby="myModalLabel1" aria-hidden="true" data-backdrop="static">
<div class="modal-dialog" style="width: 60%;">
<div class="modal-content">
<!-- 弹出面板头部-->
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal1" aria-hidden="true">
X
</button>
<h4 class="modal-title" id="myModalLabel1"><!-- 标题--></h4>
</div>
<!--<!– 公共部分–>-->
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<!--<form enctype="multipart/form-data" method="post" action="/upload/excel">-->
<!--<div class="col-sm-6 length-4">-->
<form class="form-horizontal" id="form_table" action="/importExcel" enctype="multipart/form-data" method="post">
<br/>
<br/>
<button type="submit" class="btn btn-primary">导入</button>
<input class="form-input" type="file" name="filename"></input>
</form>
<!--</div>-->
<!--</form>-->
</div>
<!-- 弹出面板底部-->
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal" id="close1">
关闭
</button>
<!--<button type="button" class="btn btn-primary" id="save1" οnclick="save();">-->
<!--保存-->
<!--</button>-->
</div>
</div>
</div>
</div>
<!-- 弹出框-->
<div class="modal fade" id="myModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true" data-backdrop="static">
<div class="modal-dialog" style="width: 60%;">
<div class="modal-content">
<!-- 弹出面板头部-->
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-hidden="true">
X
</button>
<h4 class="modal-title" id="myModalLabel"><!-- 标题--></h4>
</div>
<!-- 弹出面板中部-->
<div class="modal-body">
<form id="task" class="form-horizontal">
<div class="form-group" style="margin-top:15px">
<!-- 公共部分-->
<div style="margin-bottom:0" id="base">
<div class="col-sm-12" style="margin-top:5px ;padding-left: 2%">
<input type="hidden" id="id">
<label class="control-label col-sm-2 length-1" for="name"><span class = "must">*</span>监控名称</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="name" title="监控名称">
</div>
<label class="control-label col-sm-2 length-1" for="type"><span class = "must">*</span>监控类型</label>
<div class="col-sm-2 length-2">
<select class="form-control" id="type">
<option value="">请选择</option>
<option value="url">URL页面监控</option>
<option value="interface">URL接口监控</option>
<option value="db">数据库监控</option>
<option value="tcp">tcp连接监控</option>
<option value="file">文件监控</option>
<option value="email">邮件监控</option>
<option value="task">定时任务监控</option>
</select>
</div>
<label class="control-label col-sm-2 length-1" for="rate"><span class = "must">*</span>监控频率</label>
<div class="input-group col-sm-2 length-6" style="padding-left:15px">
<input type="text" class="form-control" id="rate" title="单位:分钟"
οnkeyup="if(this.value.length===1){this.value=this.value.replace(/[^1-9]/g,'')}else{this.value=this.value.replace(/\D/g,'')}"
onafterpaste="if(this.value.length==1){this.value=this.value.replace(/[^1-9]/g,'')}else{this.value=this.value.replace(/\D/g,'')}">
<span class="input-group-addon">分钟</span>
</div>
</div>
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-2 length-1" for="sys_name"><span class = "must">*</span>所属应用系统</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="sys_name" title="所属应用系统名称">
</div>
<label class="control-label col-sm-2 length-1" for="linkperson"><span class = "must">*</span>负责人</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="linkperson" title="所属应用系统的负责人">
</div>
<label class="control-label col-sm-2 length-1" for="timeout">超时时间</label>
<div class="input-group col-sm-2 length-6" style="padding-left:15px">
<input type="text" class="form-control" id="timeout" title="单位:毫秒"
οnkeyup="if(this.value.length===1){this.value=this.value.replace(/[^1-9]/g,'')}else{this.value=this.value.replace(/\D/g,'')}"
onafterpaste="if(this.value.length==1){this.value=this.value.replace(/[^1-9]/g,'')}else{this.value=this.value.replace(/\D/g,'')}">
<span class="input-group-addon">毫秒</span>
</div>
</div>
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-2 length-1" for="repeatflag">重复确认</label>
<div class="col-sm-2 length-2">
<select class="form-control" id="repeatflag" title="当发生异常时，如开启重复确认功能不会立即告警，会按照确认频率和确认次数，重复确认后发送告警。否则立即告警">
<option value="N">否</option>
<option value="Y">是</option>
</select>
</div>
<div id="repeatInfo" style="margin-bottom:0;display: none">
<label class="control-label col-sm-2 length-1" for="repeat_num"><span class = "must">*</span>确认次数</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="repeat_num" title="单位:次"
οnkeyup="if(this.value.length===1){this.value=this.value.replace(/[^1-9]/g,'')}else{this.value=this.value.replace(/\D/g,'')}"
onafterpaste="if(this.value.length==1){this.value=this.value.replace(/[^1-9]/g,'')}else{this.value=this.value.replace(/\D/g,'')}">
</div>
<label class="control-label col-sm-2 length-1" for="repeat_rate"><span class = "must">*</span>确认频率</label>
<div class="input-group col-sm-2 length-6" style="padding-left:15px">
<input type="text" class="form-control" id="repeat_rate" title="单位:秒/次"
οnkeyup="if(this.value.length===1){this.value=this.value.replace(/[^1-9]/g,'')}else{this.value=this.value.replace(/\D/g,'')}"
onafterpaste="if(this.value.length==1){this.value=this.value.replace(/[^1-9]/g,'')}else{this.value=this.value.replace(/\D/g,'')}">
<span class="input-group-addon" style="width: 10px;">秒</span>
</div>
</div>
</div>
</div>
<!-- url部分 -->
<div style="margin-bottom:0;display: none" id="urlInfo">
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-2 length-1" for="url_protocol">访问协议</label>
<div class="col-sm-2 length-2">
<select class="form-control" id="url_protocol">
<option value="http">HTTP</option>
<option value="https">HTTPS</option>
</select>
</div>
<label class="control-label col-sm-2 length-1" for="url_url"><span class = "must">*</span>监控地址</label>
<div class="input-group col-sm-6 length-3" style="padding-left:15px">
<span class="input-group-addon" id="prefix">http://</span>
<input type="text" class="form-control" id="url_url" title="例：www.csair.com/cn/index.shtml">
</div>
</div>
</div>
<!-- 数据库部分-->
<div style="margin-bottom:0;display: none" id="dbInfo">
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-2 length-1" for="db_username"><span class = "must">*</span>用户名</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="db_username" title="例:请输入数据库用户名">
</div>
<label class="control-label col-sm-2 length-1" for="db_url"><span class = "must">*</span>数据库地址</label>
<div class="col-sm-2 length-4">
<input type="text" class="form-control" id="db_url" title="例:jdbc:mysql://192.168.180.92:3306/even">
</div>
</div>
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-2 length-1" for="db_password"><span class = "must">*</span>密码</label>
<div class="col-sm-2 length-2">
<input type="password" class="form-control" id="db_password" title="请输入数据库密码">
</div>
<label class="control-label col-sm-2 length-1" for="db_sql"><span class = "must">*</span>执行语句</label>
<div class="col-sm-2 length-4">
<input type="text" class="form-control" id="db_sql" title="仅支持查询语句，例: select * from user，">
</div>
</div>
</div>
<!-- tcp部分-->
<div style="margin-bottom:0;display: none" id="tcpInfo">
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-2 length-1" for="tcp_ip"><span class = "must">*</span>监控ip/域名</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="tcp_ip" title="例：192.168.30.32 或 www.csair.com">
</div>
<label class="control-label col-sm-2 length-1" for="tcp_port"><span class = "must">*</span>端口号</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="tcp_port" title="例：21">
</div>
</div>
</div>
<!-- 文件部分-->
<div style="margin-bottom:0;display: none" id="fileInfo">
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-2 length-1" for="file_file_type">文件类型</label>
<div class="col-sm-2 length-2">
<select class="form-control" id="file_file_type">
<option value="1">远程文件</option>
<option value="2">本地文件</option>
</select>
</div>
<label class="control-label col-sm-2 length-1" for="file_url"><span class = "must">*</span>文件夹路径</label>
<div class="col-sm-6 length-4">
<input type="text" class="form-control" id="file_url"
title="待检测文件所在的绝对路径，例：/user/ 或D:/temp/">
</div>
</div>
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-2 length-1" for="file_ip"><span class = "must">*</span>文件所在服务器</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="file_ip" title="例：192.168.30.32 或 www.csair.com">
</div>
<label class="control-label col-sm-2 length-1" for="file_retention"><span class = "must">*</span>文件滞留时间</label>
<div class="input-group col-sm-2 length-6" style="padding-left:15px">
<input type="text" class="form-control" id="file_retention" title="单位:分钟"
οnkeyup="if(this.value.length===1){this.value=this.value.replace(/[^1-9]/g,'')}else{this.value=this.value.replace(/\D/g,'')}"
onafterpaste="if(this.value.length==1){this.value=this.value.replace(/[^1-9]/g,'')}else{this.value=this.value.replace(/\D/g,'')}">
<span class="input-group-addon" style="width: 10px;">分钟</span>
</div>
</div>
<div id="ftpMessage">
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-2 length-1" for="file_port"><span class = "must">*</span>ftp端口号</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="file_port" title="请输入ftp端口号，例：21">
</div>
<label class="control-label col-sm-2 length-1" for="file_username"><span class = "must">*</span>ftp用户名</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="file_username" title="请输入ftp用户名">
</div>
<label class="control-label col-sm-2 length-1" for="file_password"><span class = "must">*</span>ftp密码</label>
<div class="col-sm-2 length-2">
<input type="password" class="form-control" id="file_password" title="请输入ftp密码">
</div>
</div>
</div>
</div>
<!-- 邮件部分-->
<div style="margin-bottom:0;display: none" id="emailInfo">
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-2 length-1" for="email_ip"><span class = "must">*</span>发件服务器</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="email_ip" title="例：smtp.163.com">
</div>
<label class="control-label col-sm-2 length-1" for="email_port">端口号</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="email_port" title="例：465">
</div>
<label class="control-label col-sm-1 length-1" for="email_send_ssl">使用ssl加密</label>
<div class="col-sm-2 length-2">
<select class="form-control" id="email_send_ssl">
<option value="N">不使用</option>
<option value="Y">使用</option>
</select>
</div>
</div>
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-1 length-1" for="email_receive_ip"><span class = "must">*</span>收件服务器</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="email_receive_ip" title="例：pop3.163.com">
</div>
<label class="control-label col-sm-2 length-1" for="email_receive_port">端口号</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="email_receive_port" title="例：110">
</div>
<label class="control-label col-sm-2 length-1" for="email_receive_ssl">使用ssl加密</label>
<div class="col-sm-2 length-2">
<select class="form-control" id="email_receive_ssl">
<option value="N">不使用</option>
<option value="Y">使用</option>
</select>
</div>
</div>
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-2 length-1" for="email_username"><span class = "must">*</span>邮箱用户名</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="email_username" title="例：even@163.com">
</div>
<label class="control-label col-sm-2 length-1" for="email_password"><span class = "must">*</span>邮箱密码</label>
<div class="col-sm-2 length-2">
<input type="password" class="form-control" id="email_password">
</div>
</div>
</div>
<!-- 定时任务部分-->
<div style="margin-bottom:0;display: none" id="taskInfo">
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-1 length-1" for="task_ip"><span class = "must">*</span>定时任务服务器</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="task_ip" title="定时任务所在服务器域名/ip">
</div>
<label class="control-label col-sm-1 length-1" for="task_other_sys_id"><span class = "must">*</span>定时任务编号</label>
<div class="col-sm-2 length-2">
<input type="text" class="form-control" id="task_other_sys_id" title="定时任务唯一标识">
</div>
</div>
</div>
<!-- 公共部分-->
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-1 length-1" for="plan">应急方案</label>
<div class="col-sm-10 length-5">
<textarea class="form-control" rows="2" id=plan></textarea>
</div>
</div>
<div class="col-sm-12" style="padding-top:10px;padding-left: 2%">
<label class="control-label col-sm-1 length-1" for="remark">监控备注</label>
<div class="col-sm-10 length-5">
<textarea class="form-control" rows="4" id=remark></textarea>
</div>
</div>
</div>
</form>
</div>
<!-- 弹出面板底部-->
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal" id="close">
关闭
</button>
<button type="button" class="btn btn-danger" id="test" οnclick="test();">
测试
</button>
<button type="button" class="btn btn-primary" id="save" οnclick="save();">
保存
</button>
<button type="button" class="btn btn-primary" id="update" οnclick="update();">
保存修改
</button>
</div>
</div>
</div>
</div>
</div>
</body>
</html>
9、工具类：
package com.ultrapower.monitor.common.utils;
public class ExcelImportUtils {
// @描述：是否是2003的excel，返回true是2003
public static boolean isExcel2003(String filePath) {
return filePath.matches("^.+\\.(?i)(xls)$");
}
//@描述：是否是2007的excel，返回true是2007
public static boolean isExcel2007(String filePath) {
return filePath.matches("^.+\\.(?i)(xlsx)$");
}
/**
* 验证EXCEL文件
* @param filePath
* @return
*/
public static boolean validateExcel(String filePath){
if (filePath == null || !(isExcel2003(filePath) || isExcel2007(filePath))){
return false;
}
return true;
}
}
参考：https://blog.csdn.net/qq_38593865/article/details/80888229
154
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
