导入\导出.xlsx表格 - EasyExcel

导入\导出.xlsx表格 - EasyExcel

EaseyExcel官网

使用版本 - 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>
            <!-- 这里是Thymeleaf框架，用其他框架的换成其他框架就行 -->
            <span th:each="school:${schoolList}">
                <option th:value="${school.id}" th:text="${school.schoolName}"></option>
            </span>
            <!-- 这里是Thymeleaf框架，用其他框架的换成其他框架就行 -->
        </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;

    @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, "导入模板");
    }
}

好了，到这里就完成了！