毕设的项目涉及到了excel的模板下载和导入 记录一下
maven仓库
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>3.17</version>
</dependency>
一、下载模板
// excel模板的路径:webapp/template/excel模板.xls
private String excelTemplate = "/template/\u0065\u0078\u0063\u0065\u006c\u6a21\u677f.xls";
@GetMapping("/excel/download")
public ResponseEntity<byte[]> download(HttpServletRequest request) throws IOException {
Path path = Paths.get(request.getServletContext().getRealPath(excelTemplate));
if (!path.toFile().exists()) {
throw new FileNotFoundException("文件: " + path + "未被找到.");
}
byte[] body = Files.readAllBytes(path);
HttpHeaders headers = new HttpHeaders();
headers.add("Content-Disposition",
"attchement;filename=" + URLEncoder.encode(FilenameUtils.getName(excelTemplate), "UTF-8"));
ResponseEntity<byte[]> entity = new ResponseEntity<byte[]>(body, headers, HttpStatus.OK);
return entity;
}
二、导入
@PostMapping("/excel/import")
@ResponseBody
public String importFile(MultipartFile file) {
if (file == null || file.isEmpty()) {
return "文件不能为空";
}
if (!StringUtil.isExcelFileName(file.getOriginalFilename())) {
return "只支持xlsx或xls文件";
}
String result = importing(file);
return result;
}
@Transactional(rollbackFor = Exception.class)
public String importing(MultipartFile file) {
try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
Sheet sheet = workbook.getSheetAt(0);
int rowNum = sheet.getLastRowNum();
List<User> results = new ArrayList<>();
Integer startRow = 0;
for (int i = 1; i <= rowNum; i++) {
Row row = sheet.getRow(i);
if (row == null || i <= startRow) {
continue;
}
User user = new User();
user.setUsername(getStringValue(row.getCell(0)));
user.setName(getStringValue(row.getCell(1)));
results.add(user);
}
if (results.isEmpty()) {
return "导入数据为空";
}
// 对导入的数据进行操作 例如插入到数据库中等
return "success";
} catch (EncryptedDocumentException | InvalidFormatException | IOException e) {
e.printStackTrace();
throw new RuntimeException();
}
}
供参考的导入包
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsIiclRnblN2XjlGcjAzNfRHLGZkRGZkRfJ3bs92YsYTMfVmepNHL1kEROh3YE1ENNpHW4Z0MMBjVtJWd0ckW65UbM5WOHJWa5kHT20ESjBjUIF2X0hXZ0xCMx81dvRWYoNHLrdEZwZ1Rh5WNXp1bwNjW1ZUba9VZwlHdssmch1mclRXY39CXldWYtlWPzNXZj9mcw1ycz9WL49zZuBnLzADOyQTMzMjM5ATMxkTMwIzLc52YucWbp5GZzNmLn9Gbi1yZtl2Lc9CX6MHc0RHaiojIsJye.png)
jsp
<button class="btn btn-primary" data-toggle="modal" data-target="#import">
一键导入
</button>
<div class="modal fade" id="import" tabindex="-1" role="dialog">
<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>
</div>
<div class="modal-body">
<a class="btn btn-primary" href="./excel/download">下载模板</a>
<hr>
<div class="alert alert-danger" role="alert" style="display: none;"></div>
<form action="./excel/import" method="post" enctype="multipart/form-data">
<div class="form-group">
<label for="file">选择文件</label> <input type="file"
class="form-control-file" name="file" id="importNoFile">
</div>
</form>
<div class="card">
<div class="card-header">导入说明</div>
<div class="card-body">
<p>请下载导入模板按标准填写</p>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-dismiss="modal">关闭</button>
<button type="button" class="btn btn-primary" onclick="importing()">确定</button>
</div>
</div>
</div>
</div>
<script>
function importing() {
var formData = new FormData();
formData.append('file', $('#importNoFile')[0].files[0]);
$.post({
url: '.',
data: formData,
contentType: false,
processData: false,
cache: false,
success: function(data) {
if (data == 'success') {
$('#import').modal('toggle');
$('#importNoFile').val('');
$('#import .alert').hide();
} else {
$('#import .alert').text(data).show();
}
},
error: function() {
alert('错误');
}
});
}
</script>