java+layui的Excel导入导出
html:
<button class="layui-btn" onclick="exportData();">导出</button> <button class="layui-btn" onclick="downloadTemplate();">模板下载</button> <button id="importData" class="layui-btn" onclick="importData()">导入</button>
js:
//导入 用layui upload插件
layui.use([ "element", "laypage", "layer", "upload"], function() { var element = layui.element; var laypage = layui.laypage; var layer = layui.layer; var upload = layui.upload;//主要是这个 layui.upload.render({ elem: "#importData",//导入id url: "/xx/importData", size: '3072', accept: "file", exts: 'xls|xlsx|xlsm|xlt|xltx|xltm', done: function (result) { if (result.status == 0) { refreshTable() } if (result.message != null) { refreshTable(); layer.msg(result.message) } } }); refreshTable() });
//导出 function exportData() { $.ajax({ type: "post", url: "/xx/exportData", data: {}, success: function (result) { if (result.status == 0) { window.open(result.data) } if (result.message != null) { layer.msg(result.message) } }, error: function (XMLHttpRequest, textStatus, errorThrown) { layer.msg('{"status":"' + XMLHttpRequest.status + '","readyState":"' + XMLHttpRequest.readyState + '","textStatus":"' + textStatus + '","errorThrown":"' + errorThrown + '"}') } }) }; //模板下载 function downloadTemplate() { $.ajax({ type: "post", url: "/xx/downloadTemplate", data: {}, success: function (result) { if (result.status == 0) { window.open(result.data) } if (result.message != null) { layer.msg(result.message) } }, error: function (XMLHttpRequest, textStatus, errorThrown) { layer.msg('{"status":"' + XMLHttpRequest.status + '","readyState":"' + XMLHttpRequest.readyState + '","textStatus":"' + textStatus + '","errorThrown":"' + errorThrown + '"}') } }) }
java后端:
// 导入 @PostMapping(value = "importData") ResultJson importData(@RequestParam MultipartFile file) { ResultJson resultJson = new ResultJson(); List<ProjectJson> importData = null; try { importData = ExcelUtil.importExcel(file.getInputStream(), ProjectJson.class); } catch (IOException e) { e.printStackTrace(); } if (null == importData) { resultJson.setStatus(1); resultJson.setMessage("导入失败!"); return resultJson; } int num = 0; for (ProjectJson projectJson : importData) { ProjectManageEntity projectManageEntity = new ProjectManageEntity(); num++; if (CommonUtil.isEmpty(projectJson.getNumber())) { resultJson.setMessage("导入失败!第" + num + "行的编号不能为空"); resultJson.setStatus(1); return resultJson; } if (projectService.findByprojectNumber(projectJson.getNumber()) != null) { resultJson.setStatus(1); resultJson.setMessage("导入失败!第" + num + "行的编号重复"); resultJson.setData(null); return resultJson; } projectManageEntity.setCreateDate(new Date()); projectManageEntity.setUpdateDate(new Date()); projectManageEntity.setName(projectJson.getName()); projectManageEntity.setNumber(projectJson.getNumber()); projectManageEntity.setType(projectJson.getType()); projectManageEntity.setDeleteMark(false); projectManageEntity.setCode("PROJECT_MANAGE" + UUID.randomUUID()); projectService.save(projectManageEntity); } resultJson.setStatus(0); resultJson.setMessage("导入成功!"); resultJson.setData(null); return resultJson; } //导出 @PostMapping(value = "exportData") ResultJson exportData() { String excelTitle = "项目管理"; String path = "/export/company/excel/" + DateUtil.getEightDateFormat().format(new Date()); String realPath = CommonUtil.createFolderPath(propertyUtil.getUploadPath() + path); return ExcelUtil.exportExcel(excelTitle, realPath, path, ProjectJson.class, projectService.getAll()); } //下载模板 @PostMapping(value = "downloadTemplate") public ResultJson downloadTemplate() { String excelTitle = "项目管理模板"; String path = "/export/company/excel/" + DateUtil.getEightDateFormat().format(new Date()); String realPath = CommonUtil.createFolderPath(propertyUtil.getUploadPath() + path); return ExcelUtil.exportExcel(excelTitle, realPath, path, ProjectJson.class, projectService.getDownloadTemplate()); }
/** * 创建指定目录 * * @param folderPath 目录地址 * @return 目录地址 */ public static final String createFolderPath(String folderPath) { File file = new File(folderPath); if (System.getProperty("os.name").toLowerCase().startsWith("win")) { String[] diskNames = {"A:", "B:", "C:", "D:", "E:", "F:", "G:", "H:", "I:", "J:", "K:", "L:", "M:", "N:", "O:", "P:", "Q:", "R:", "S:", "T:", "U:", "V:", "W:", "X:", "Y:", "Z:"}; for (int i = 0; i < diskNames.length; i++) { if (i > 0) { folderPath = folderPath.replace(diskNames[i - 1], diskNames[i]); } else { folderPath = diskNames[0] + folderPath; } file = new File(folderPath); if (!file.exists()) { file.mkdirs(); } if (file.exists()) { return folderPath; } } return null; } else { if (!file.exists()) { file.mkdirs(); } if (file.exists()) { return folderPath; } return null; } }
public class ExcelUtil { /** * 导入Excel * * @param inputstream * Excel数据流 * @param clazz * 注解的实体类 * @return 导入的数据 */ public static <T> List<T> importExcel(InputStream inputstream, Class<T> clazz) { ImportParams params = new ImportParams(); params.setTitleRows(1); params.setNeedSave(true); try { return ExcelImportUtil.importExcel(inputstream, clazz, params); } catch (Exception e) { e.printStackTrace(); return null; } } /** * 导出Excel * * @param excelTitle * Excel标题 * @param realPath * 真实路径 * @param path * 响应路径 * @param clazz * 注解的实体类 * @param excels * 注解的实体类集合 * @return 响应结果 */ public static <T> ResultJson exportExcel(String excelTitle, String realPath, String path, Class<T> clazz, List<T> excels) { ResultJson resultJson = new ResultJson(); try { Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams(excelTitle, excelTitle), clazz, excels); String filename = excelTitle + "-" + DateUtil.getEightDateFormat().format(new Date()) + ".xls"; File dirPath = new File(realPath); if (!dirPath.exists()) { dirPath.mkdirs(); } try { FileOutputStream outputStream = new FileOutputStream(realPath + "/" + filename); workbook.write(outputStream); resultJson.setStatus(0); resultJson.setData(path + "/" + filename); } catch (Exception e) { e.printStackTrace(); resultJson.setStatus(1); return resultJson; } } catch (Exception e) { e.printStackTrace(); resultJson.setStatus(1); return resultJson; } return resultJson; } }