使用版本 - 3.3.2
java: 8 😃
已知问题：@ColumnWidth无法达到指定列宽,会少0.62，@HeadRowHeight也无法达到指定行高，少多少看情况，已提交Issues。问题不大~
先定义所需要的实体类并加上注解：
@Data // Lombok注解
@HeadRowHeight(20) // 表头行高
@ContentRowHeight(20) // 表内容行高
@ColumnWidth(10) // 列宽
@HeadFontStyle(fontName = "微软雅黑") // 表头字体
@ContentFontStyle(fontName = "微软雅黑") // 表内容字体
public class SchoolNo {
/**
* ID
*/
@ExcelIgnore // 忽略该变量
private String id;
/**
* 学校ID
*/
@ExcelIgnore
private String schoolId;
/**
* 学生姓名
*/
@ExcelProperty("学生姓名") // 导出/导入解析名
@ColumnWidth(12) // 单独设置列宽
private String studentName;
/**
* 学号
*/
@ExcelProperty("学号")
private String studentNo;
/**
* 年级
*/
@ExcelProperty("年级")
private String grade;
}
导入/导出工具类(核心)
@Slf4j
public class TableUtils {
/**
* 上传路径
*/
private static final String UPLOAD_PATH = "/data/upload/";
/**
* 时间格式化格式
*/
private static final String DATE_PATTERN = "yyyy-MM-dd";
/**
* 最大上传次数
*/
private static final int MAX_UPLOAD_TIMES = 5;
/**
* 导出表格
*
* @param list 数据集合
* @param response HttpServletResponse响应
* @param clazz 表头
* @param sheetName 表名
* @param <T> 类对象
*/
public static <T> void exportToTable(List<T> list, HttpServletResponse response, Class<T> clazz, String sheetName) {
try (ServletOutputStream outputStream = response.getOutputStream()) {
response.setContentType("multipart/form-data");
response.setCharacterEncoding(String.valueOf(StandardCharsets.UTF_8));
response.setHeader("Content-Disposition",
"attachment;filename*=utf-8'zh-cn'" + URLEncoder.encode(sheetName + ".xlsx", String.valueOf(StandardCharsets.UTF_8)));
// 表头数据样式
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
// 表内数据样式
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.LEFT);
HorizontalCellStyleStrategy horizontalCellStyleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
EasyExcel.write(outputStream, clazz)
.registerWriteHandler(horizontalCellStyleStrategy)
.sheet(sheetName)
.doWrite(list);
} catch (IOException e) {
log.error("导出失败", e);
throw new RuntimeException(e.getMessage());
} catch (Exception e) {
log.error(e.getMessage(), e);
throw new RuntimeException(e.getMessage());
}
}
/**
* 通过文件路径读取数据
*
* @param request 请求
* @param clazz 表头
* @param <T> 类
* @return List
*/
public static <T> List<T> importFromTable(HttpServletRequest request, Class<T> clazz) {
return importFromTableBeginInRow(request, clazz, 1);
}
/**
* 指定从第row行为表头开始读取
*
* @param request 请求
* @param clazz 表头
* @param row 第row行为表头
* @param <T> 类
* @return List
*/
public static <T> List<T> importFromTableBeginInRow(HttpServletRequest request, Class<T> clazz, int row) {
List<T> analyzeList = new ArrayList<>();
try {
// 按照日期分目录
String uploadPath = UPLOAD_PATH + LocalDate.now().format(DateTimeFormatter.ofPattern(DATE_PATTERN));
createDirectories(uploadPath);
// 获取上传的文件
Part filePart = request.getPart("file");
// 获取文件名
String fileName = String.valueOf(Paths.get(filePart.getSubmittedFileName()).getFileName());
if (StringUtils.isEmpty(fileName)) {
log.error("文件名为空");
throw new RuntimeException("文件名为空");
}
String extension = fileName.substring(fileName.lastIndexOf('.'));
if (!".xlsx".equalsIgnoreCase(extension)) {
log.error("后缀名错误");
throw new RuntimeException("后缀名错误");
}
try (InputStream fileContent = filePart.getInputStream()) {
// 开始读取表格内容
EasyExcel.read(fileContent, clazz, new PageReadListener<T>(analyzeList::addAll))
.excelType(ExcelTypeEnum.XLSX)
.sheet()
.headRowNumber(row)
.doRead();
}
// 构建文件输出路径
String filePath = generateUniqueFilePath(uploadPath, fileName);
Path path = Paths.get(filePath);
// 将文件写入指定目录
try (InputStream fileContent = filePart.getInputStream()) {
// 写入文件
Files.copy(fileContent, path, StandardCopyOption.REPLACE_EXISTING);
}
return analyzeList;
} catch (ServletException e) {
log.error("文件未上传", e);
throw new ServiceException("文件未上传");
} catch (IOException e) {
log.error("读取失败", e);
throw new ServiceException("读取失败");
} catch (Exception e) {
log.error("导入失败", e);
throw new ServiceException("导入失败");
}
}
/**
* 创建目录
*
* @param directory 目录
* @throws IOException IO异常
*/
private static void createDirectories(String directory) throws IOException {
Path path = Paths.get(directory);
if (!Files.exists(path)) {
Files.createDirectories(path);
}
}
/**
* 生成唯一文件名
*
* @param directory 目录
* @param fileName 文件名
* @return String
*/
private static String generateUniqueFilePath(String directory, String fileName) {
Path filePath = Paths.get(directory, fileName);
int count = 1;
while (Files.exists(filePath)) {
String baseName = fileName.substring(0, fileName.lastIndexOf('.'));
String extension = fileName.substring(fileName.lastIndexOf('.'));
String newFileName = baseName + "(" + count + ")" + extension;
filePath = Paths.get(directory, newFileName);
count++;
if (count > MAX_UPLOAD_TIMES) {
throw new RuntimeException("文件重复次数过多，请重新上传");
}
}
return filePath.toString();
}
}
上面这个可以直接用，除了路径和最大上传次数，其他的可以不用变
导入/导出前端HTML：
<!DOCTYPE html>
<html>
<meta charset="utf-8">
<head>
<title>导入导出</title>
</head>
<body>
<script th:inline="javascript">
const schoolList = [[${schoolList}]];
</script>
<div class="col-xs-8">
<input class="form-control" id="fileInput" type="file" name="file" accept=".xlsx">
</div>
<label for="schoolId">
<select id="schoolId" name="schoolId" required>
<option value="">请选择学校</option>
<!-- 这里是Thymleaf框架，用其他框架的换成其他框架就行 -->
<span th:each="school:${schoolList}">
<option th:value="${school.id}" th:text="${school.schoolName}"></option>
</span>
<!-- 这里是Thymleaf框架，用其他框架的换成其他框架就行 -->
</select>
</label>
<button type="button" class="btn btn-primary" onclick="importFile()">
<i class="fa fa-upload" aria-hidden="true"></i> 导入
</button>
<button type="button" class="btn btn-primary" onclick="exportFile()">
<i class="fa fa-external-link" aria-hidden="true"></i> 导出模板
</button>
</body>
<script type="text/javascript" src="/js/file.js"></script>
这里指定为.xlsx文件，由于时代的原因，这里放弃支持.xls文件。
样式没有的话可以直接删了，只是为了好看，实现功能可以不需要，按需添加就行了。
导入/导出前端JS file.js：
const prefix = "/manage/schoolNo"
/**
文件上传
*/
async function importFile() {
const fileInput = document.getElementById('fileInput');
const file = fileInput.files[0];
const schoolId = $("#schoolId>option:selected").val().trim();
if (!file) {
showMessage("导入失败", "请选择至少一个文件！");
return;
}
if (!schoolId) {
showMessage("导入失败", "请选择学校！");
return;
}
const formData = new FormData();
formData.append('file', file);
formData.append('schoolId', schoolId);
fetch(`${prefix}/importFile`, {
method: 'POST',
body: formData
})
.then(response => {
if (!response.ok) {
throw new Error('网络异常！');
}
return response.json();
})
.then(data => {
if (data.code === 0) {
parent.layer.msg('导入成功');
window.location.reload();
// 获取窗口索引
const index = parent.layer.getFrameIndex(window.name);
parent.layer.close(index);
} else {
showMessage("导入失败", data.msg);
}
})
.catch(error => {
showMessage("导入失败", "操作失败；返回信息：" + error.message);
})
}
function showMessage(title, content) {
parent.layer.open({
title: title,
content: content
});
}
/**
* 导出模板
*/
function exportFile() {
const xhr = new XMLHttpRequest();
const formData = new FormData();
const filename = "导入模板.xlsx";
xhr.open('GET', `${prefix}/exportFile`);
xhr.setRequestHeader("access-token", sessionStorage.getItem('access-token'));
xhr.responseType = 'blob';
xhr.onload = () => { // 这里可以加个event，但是不知道干啥用，所以就没加，知道的可以加
if (this.status === 200) {
const blob = xhr.response;
if (window.navigator.msSaveOrOpenBlob) {
navigator.msSaveBlob(blob, filename);
} else {
downloadBlob(blob, filename);
}
layer.msg("导出成功");
layer.close(index);
} else {
layer.msg("导出失败");
layer.close(index);
}
};
xhr.send(formData);
}
function downloadBlob(blob, filename) {
const url = createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = filename;
a.style.display = 'none';
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
window.URL.revokeObjectURL(url);
}
function createObjectURL(object) {
return (window.URL || window.webkitURL).createObjectURL(object);
}
后端接口Controller：
@Slf4j
@RestController
@RequestMapping("/manage/schoolNo")
public class SchoolNoController {
@Resource
private SchoolNoService schoolNoService;
@Resource
private SchoolService schoolService;
@Resource
private UserDao userDao;
@PostMapping("/importFile")
public R importFile(HttpServletRequest request, String schoolId) {
log.info("后台导入学号");
try {
// 读取文件
List<SchoolNo> schoolNoList = TableUtils.importFromTable(request, SchoolNo.class);
log.debug("文件内容, {}", JSON.toJSONString(schoolNoList));
if (!checkAndSaveList(schoolNoList, schoolId)) {
log.error("导入失败");
return R.error("导入失败");
}
return R.ok("导入成功");
} catch (Exception e) {
log.error(e.getMessage(), e);
return R.error("系统异常，请按照模板文件格式导入数据");
}
}
/**
* 导入数据
*
* @param schoolNoList
* @return
* @throws ServiceException
*/
private boolean checkAndSaveList(List<SchoolNo> schoolNoList, String schoolId) throws ServiceException {
try {
List<SchoolNo> schoolNos = schoolNoService.batchSelect(schoolNoList, schoolId);
if (CollectionUtils.isEmpty(schoolNos)) {
for (SchoolNo schoolNo : schoolNoList) {
schoolNo.setSchoolId(schoolId);
}
int result = schoolNoService.batchInsert(schoolNoList);
return result == schoolNoList.size();
} else {
String studentNo = schoolNos.get(0).getStudentNo();
int index = 2;
for (SchoolNo schoolNo : schoolNoList) {
if (Objects.equals(schoolNo.getStudentNo(), studentNo)) {
log.error("第{}行学号已存在", index);
break;
}
index++;
}
return false;
}
} catch (Exception e) {
log.error("导入数据失败", e);
throw new RuntimeException("导入数据异常！");
}
}
/**
* 导出模板
*
* @param response HttpServletResponse
*/
@GetMapping("/exportFile")
public void exportFile(HttpServletResponse response) {
TableUtils.exportToTable(new ArrayList<>(0), response, SchoolNo.class, "导入模板");
}
}
好了，到这里就完成了！
