- 前提须知:poi一键导出excel
相关配置
web.xml部分
- 在web.xml内的servlet内加入以下代码
<multipart-config>
<max-file-size>52428800</max-file-size>
<max-request-size>52428800</max-request-size>
</multipart-config>
pom.xml部分
<!--fileupload-->
<dependency>
<groupId>commons-fileupload</groupId>
<artifactId>commons-fileupload</artifactId>
<version>1.3.1</version>
</dependency>
<!--poi-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
applicationContext.xml部分
<!--文件上传解析器 id必须是multipartResolver-->
<bean id="multipartResolver" class="org.springframework.web.multipart.commons.CommonsMultipartResolver">
<!--最大上传文件大小 10M-->
<property name="maxUploadSize" value="#{1024*1024*10}"/>
</bean>
设定模板位置供模态框下载
- 存在webapp下
前端部分
- 样式用的是bootstrap框架
按钮
<a href="#" class="btn btn-warning btn-import ">
<span class="glyphicon glyphicon-upload"></span> 导入
</a>
模态框
- 点击按钮后,会弹出模态框,可下载要导入数据的excel模板,和上传你的excel文件给后端进行数据导入
- 模态框内下载模板部分中的a标签,填写后端项目webapp里excel模板的路径,点击即可下载
<!-- 导入数据模态框部分-->
<div class="modal fade" id="importModal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
<div class="modal-dialog" role="document">
<div class="modal-content">
<div class="modal-header">
<button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">×</span></button>
<h4 class="modal-title" id="myModalLabel">导入</h4>
</div>
<form class="form-horizontal" action="/employee/importXls" enctype="multipart/form-data" method="post" id="importForm">
<div class="modal-body">
<div class="form-group" style="margin-top: 10px;">
<label for="name" class="col-sm-3 control-label"></label>
<div class="col-sm-6">
<!-- 文件上传框 -->
<input type="file" name="file" accept="application/vnd.ms-excel" >
</div>
</div>
<div class="form-group" style="margin-top: 10px;">
<div class="col-sm-3"></div>
<div class="col-sm-6">
<a href="/xls/employee_import.xls" class="btn btn-success" >
<span class="glyphicon glyphicon-download"></span> 下载模板
</a>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-default" data-dismiss="modal">取消</button>
<button type="submit" class="btn btn-primary btn-submit">保存</button>
</div>
</form>
</div>
</div>
</div>
js部分
// 导入按钮,点击后显示模态框
$('.btn-import').click(function () {
$('#importModal').modal('show');
})
//提交导入的表单即excel表
$('#importForm').ajaxForm(function (data) {
if (data.success){
Swal.fire({
text: '导入成功',
icon: 'success',
confirmButtonText: 'OK',
}).then((result)=>{
if (result.value){
window.location.reload();
}
})
}else {
Swal.fire(
data.msg,
'',
'error'
)
}
})
控制器部分
- 模态框内上传文件,将文件进入此进行业务逻辑
@RequiredPermission(name = "员工导入", expression = "employee:importXls")
@RequestMapping("/importXls")
@ResponseBody
public JsonResult importXls(MultipartFile file) throws IOException {
employeeService.importXls(file);
return new JsonResult();
}
业务层实现类方法
-
employService.importXls(file)
-
这里对接收的用户名进行判断了,因为用户名是唯一的,不能重复导入已有用户名数据
-
(1)判断文件后缀名是xls,还是xlsx
-
(2)如果是xls,使用HSSFWorkbook;如果是xlsx则使用XSSFWorkbook
-
如果不需要前端传过来,直接用本地的excel文件,以下代码前部分可写成` File file = new File(“D:\aka_work\word.xlsx”);
FileInputStream fileInputStream = null;
fileInputStream = new FileInputStream(file);MultipartFile multipartFile = new MockMultipartFile(file.getName(), file.getName(), ContentType.APPLICATION_OCTET_STREAM.toString(), fileInputStream); Workbook wb = new XSSFWorkbook(multipartFile.getInputStream());`
@Autowired
private EmployeeMapper employeeMapper;
@Autowired
private DepartmentMapper departmentMapper;
public void importXls(MultipartFile file) throws IOException {
// 把文件输入流传给poi读取
Workbook wb = new HSSFWorkbook(file.getInputStream());
// 读取第一张表格
Sheet sheet = wb.getSheetAt(0);
// 获取最后一行的索引
int lastRowNum = sheet.getLastRowNum();
// 标题行不读,所以从1开始
for (int i = 1; i <= lastRowNum; i++) {
Employee employee = new Employee();
// 一行一行读
Row row = sheet.getRow(i);
// 读单元格
String username = row.getCell(0).getStringCellValue();
Employee employee2 = employeeMapper.selectByUsername(username);
if (employee2 != null) {
throw new LoginException(username + "用户名已存在");
}
employee.setUsername(row.getCell(0).getStringCellValue());
employee.setName(row.getCell(1).getStringCellValue());
employee.setEmail(row.getCell(2).getStringCellValue());
double age = row.getCell(3).getNumericCellValue();
employee.setAge((int) age);
// 拿到部门名称,查询部门
String deptName = row.getCell(4).getStringCellValue();
Department department = departmentMapper.selectByName(deptName);
if (department != null) {
employee.setDept(department);
}
employeeMapper.insert(employee);
}
}
效果图演示
- 点击导入按钮实现后出现模态框,放入模板的数据即可