需要导入的依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>com.yanzhenjie.apache</groupId>
<artifactId>fileupload</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.4</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.freemarker/freemarker -->
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
<version>2.3.23</version>
</dependency>
Controller层
// 跳转到导入Excel表的页面
@RequestMapping("excel")
public String UploadUI() {
return "uploadExcel";
}
/**
* 导入excel
*/
@RequestMapping("/import")
@ResponseBody
public String excelImport(@RequestParam(value="filename") MultipartFile file){
int result = 0;
try {
result = accService.ExcelAdd(file);
} catch (Exception e) {
e.printStackTrace();
}
if(result > 0){
return "excel文件数据导入成功!";
}else{
return "请查看文件内容是否有误,确认无误后请继续上传!!";
}
service层
// 根据controller层的错误提示进行代码的编写
int ExcelAdd(MultipartFile file) throws IOException, ParseException;
serviceImpl实现层
@Override
public int ExcelAdd(MultipartFile file) throws IOException, ParseException {
int result = 0;
//存放excel表中所有账号的信息
List<Account> accountList = new ArrayList<>();
/**
*
* 判断文件版本
*/
String fileName = file.getOriginalFilename();
String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
InputStream ins = file.getInputStream();
Workbook wb = null;
if (suffix.equals("xlsx")) {
wb = new XSSFWorkbook(ins);
} else {
wb = new HSSFWorkbook(ins);
}
/**
* 获取excel表单
*/
Sheet sheet = wb.getSheetAt(0);
/**
* line = 1:从表的第二行开始获取记录
*
*/
if (null != sheet) {
for (int line = 1; line <= sheet.getLastRowNum(); line++) {
Account account = new Account();
Row row = sheet.getRow(line);
if (null == row) {
continue;
}
//将从Excel表中读取的内容转成String格式(如果Excel表格中纯数字,不执行一下操作,会报错,建议一下操作全部执行。)
row.getCell(0).setCellType(CellType.STRING);
row.getCell(1).setCellType(CellType.STRING);
row.getCell(2).setCellType(CellType.STRING);
row.getCell(3).setCellType(CellType.STRING);
row.getCell(4).setCellType(CellType.STRING);
row.getCell(5).setCellType(CellType.STRING);
row.getCell(6).setCellType(CellType.STRING);
row.getCell(7).setCellType(CellType.STRING);
/**
* 获取第一个单元格的内容
*/
String aid = row.getCell(0).getStringCellValue();
/**
* 获取第二个单元格的内容
*/
String aname = row.getCell(1).getStringCellValue();
String bandphone = row.getCell(2).getStringCellValue();
String bandemail = row.getCell(3).getStringCellValue();
//Date类型 需要注意,Excel表里的单元格式,我用的是文本格式,其他的我不太晓得方法!
String receive_date = row.getCell(4).getStringCellValue();
Date date2;
//下面的日期格式需要和实体类中的日期格式一样,Excel表的日期也要这样写,否则会报日期格式不对的错误
DateFormat format2 = new SimpleDateFormat("yyyy-MM-dd");
date2 = format2.parse(receive_date);
String aplatform = row.getCell(5).getStringCellValue();
String anature = row.getCell(6).getStringCellValue();
String username = row.getCell(7).getStringCellValue();
account.setAid(null);
account.setAname(aname);
account.setBandphone(bandphone);
;
account.setBandemail(bandemail);
account.setAdate(date2);
account.setAplatform(aplatform);
account.setAnature(anature);
account.setUsername(username);
accountList.add(account);
}
for (Account account : accountList) {
result = accMapper.ExcelAdd(account);
}
}
return result;
}
后面就是mapper层
int ExcelAdd(Account account);
XML文件,实现SQL语句进行对数据的插入
<insert id="ExcelAdd" parameterType="Account">
insert into account (aname, bandphone, bandemail, adate, aplatform, anature, username)
values (#{aname}, #{bandphone}, #{bandemail}, #{adate}, #{aplatform}, #{anature}, #{username});
</insert>
实体类
@Data
public class Account implements Serializable {
private Long aid;
private String aname;
private String bandphone;
private String bandemail;
@DateTimeFormat(pattern = "yyyy-MM-dd")
private Date adate;
private String aplatform;
private String anature;
private String username;
}
前端页面
<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
<meta charset="UTF-8">
<title>文件上传</title>
<link rel="icon" th:href="@{images/favicon.ico}">
<link rel="stylesheet" th:href="@{/layui/css/layui.css}" media="all">
<link rel="stylesheet" th:href="@{/css/layuimini.css?v=2.0.4.2}" media="all">
<link rel="stylesheet" th:href="@{/css/themes/default.css}" media="all">
<link rel="stylesheet" th:href="@{/lib/font-awesome-4.7.0/css/font-awesome.min.css}" media="all">
<script th:src="@{/webjars/jquery/3.5.1/jquery.min.js}" charset="utf-8"></script>
</head>
<body>
<h1>导入Excel表</h1>
<br/>
<form>
<button type="button" class="btn-primary layui-btn layui-btn-normal">导入</button>
<input class="form-input" type="file" name="filename"/>
<a id="result0"></a>
</form>
<br>
<br><a href="/matter/file">
<button type="button" class="layui-btn layui-btn-normal btn-c">导入内容信息表</button>
</a>
<br>
<br><a href="/ddata/ddatafile">
<button type="button" class="layui-btn layui-btn-normal btn-c">导入数据信息表</button>
</a>
<script>
$(".btn-primary").click(function () {
var fileobj = $(".form-input")[0].files[0];
var form = new FormData();
if (fileobj == null) {
alert("您还没有选中任何文件哦")
} else {
form.append("filename", fileobj);
$.ajax({
type: 'post',
url: '/account/import',
data: form,
processData: false,
contentType: false,
success: function (response) {
alert(response)
window.location.reload();
}
});
}
})
</script>
</body>
</html>