poi导出代码思路
java+layui实现导出
思路：先将后台列表上传到本地盘符里面，上传成功之后返回一个本地盘符路径，在根据这个实际路径输出流的形式返回前端，实现下载
1.前端
// 导出
function exportData() {
$.ajax({
type : "post",
url : "/backend/report/stockAnalyse/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
+ '"}')
}
})
};
2.后端 controller层
// 导出 @ResponseBody @PostMapping(value = "exportData") ResultJson exportData() { String excelTitle = "核心报表-库龄分析"; String path = "/smart/export/stock/excel/" + new SimpleDateFormat("yyyyMMddHH-mm-ss").format(new Date()); String realPath = CommonUtil.createFolderPath(path); ResultJson resultJson = new ResultJson(); resultJson.setStatus(0); resultJson.setMessage("导出成功！"); resultJson.setData(ExcelUtil.exportExcel(excelTitle, realPath, path, CompanyStaffJson.class, companyStaffService.findAllForExcel())); return resultJson; }
3.工具类：遍历本地盘符新建文件夹
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; } }
3. 将查出来的list通过流的形式写入本地文件夹并且返回一个下载路径给前端layui
/** * 导出Excel * * @param excelTitle Excel标题 * @param realPath 真实路径 * @param path 响应路径 * @param clazz 注解的实体类 * @param excels 注解的实体类集合 * @return 完整的响应路径 */ public static <T> String exportExcel(String excelTitle, String realPath, String path, Class<T> clazz, List<T> excels) { try { Workbook workbook = ExcelExportUtil .exportExcel(new ExportParams(excelTitle, excelTitle), clazz, excels); String filename = excelTitle + "-" + DateUtil.formatDateTimeSerial(null) + ".xls"; File dirPath = new File(realPath); if (!dirPath.exists()) { dirPath.mkdirs(); } FileOutputStream outputStream = new FileOutputStream(realPath + "/" + filename); workbook.write(outputStream); outputStream.close(); return path + "/" + filename; } catch (Exception e) { e.printStackTrace(); return null; } }
4.前端请求的下载路径被监听到，然后通过方法redirectFile获取本地Excel所保持的路径，并进行下载
@Controller @RequestMapping(value = "/") public class IndexController { @Autowired private UserService userService; @Autowired private PropertyUtil propertyUtil; @RequestMapping(value = "") String index() { return "redirect:/backend"; } @ResponseBody @RequestMapping(value = "registerUser") ResultJson registerUser(@Valid SystemUser systemUser, BindingResult result) { ResultJson resultJson = new ResultJson(); if (result.hasErrors()) { resultJson.setStatus(1); resultJson.setMessage(result.getAllErrors().get(0).getDefaultMessage()); resultJson.setData(null); return resultJson; } SystemUser findUser = userService.findByEntityName(systemUser.getEntityName()); if (findUser != null) { resultJson.setStatus(1); resultJson.setMessage("该用户已存在！"); resultJson.setData(null); return resultJson; } if (systemUser.getPassword().equals("password")) { resultJson.setStatus(1); resultJson.setMessage("禁止使用默认密码！"); resultJson.setData(null); return resultJson; } systemUser.setCreateDate(new Date()); systemUser.setPassword(StringUtil.md5Encode(systemUser.getPassword())); userService.save(systemUser); SecurityContextHolder.getContext().setAuthentication( new UsernamePasswordAuthenticationToken(systemUser.getEntityName(), systemUser.getPassword())); resultJson.setStatus(0); resultJson.setMessage("注册成功！"); resultJson.setData(null); return resultJson; } @ResponseBody @RequestMapping(value = "repassUser") ResultJson repassUser(String newPass) { ResultJson resultJson = new ResultJson(); if (CommonUtil.isEmpty(newPass)) { resultJson.setStatus(1); resultJson.setMessage("新密码不能为空！"); resultJson.setData(null); return resultJson; } SystemUser systemUser = BackendUtil.getLoginUser(); if (systemUser == null) { resultJson.setStatus(1); resultJson.setMessage("用户登录失效！"); resultJson.setData(null); return resultJson; } systemUser.setPassword(StringUtil.md5Encode(newPass)); systemUser.setUpdateDate(new Date()); userService.save(systemUser); resultJson.setStatus(0); resultJson.setMessage("改密成功！"); resultJson.setData(null); return resultJson; } @RequestMapping(value = {"/smart/**", "/upload/images/**", "/upload/photos/**", "/redirectFile/**", "/downloadFile/**", "/export/**", "/ueditor/jsp/upload/**", "/upload/attachmentFiles/**"}) public void redirectFile(HttpServletRequest request, HttpServletResponse response) throws IOException { String parseName = request.getParameter("parseName"); String filePath = URLDecoder.decode(request.getRequestURI(), "UTF-8"); // String folderPath = CommonUtil.checkFolderPath(propertyUtil.getUploadPath() + filePath); String folderPath; if (filePath.startsWith("/smart")) { folderPath = CommonUtil.checkFolderPath(filePath); } else { folderPath = CommonUtil.checkFolderPath(propertyUtil.getUploadPath() + filePath); } if (folderPath == null) { return; } File file = new File(folderPath); if (!file.exists()) { return; } String fileName = null; if (!StringUtil.isEmpty(parseName)) { fileName = parseName; } else { fileName = StringUtil.getFileNameWithSuffix(filePath); } response.setContentType(request.getServletContext().getMimeType(fileName)); FileInputStream fileInputStream = null; if (filePath.startsWith("/downloadFile") || filePath.startsWith("/export") || filePath.startsWith("/upload/attachmentFiles")) { if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) { response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1")); } else { response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1")); } } fileInputStream = new FileInputStream(file); OutputStream outputStream = response.getOutputStream(); byte[] buffer = new byte[512]; int count; while ((count = fileInputStream.read(buffer)) > 0) { outputStream.write(buffer, 0, count); } outputStream.flush(); outputStream.close(); fileInputStream.close(); } @RequestMapping(value = {"/upload/basicEnclosure/**"}) public void basicEnclosure(HttpServletRequest request, HttpServletResponse response) throws IOException { String parsePdf = request.getParameter("parsePdf"); String parseName = request.getParameter("parseName"); String filePath = URLDecoder.decode(request.getRequestURI(), "UTF-8"); String folderPath = CommonUtil.checkFolderPath(propertyUtil.getUploadPath() + filePath); if (folderPath == null) { return; } File file = new File(folderPath); if (!file.exists()) { return; } String fileName = null; if (!StringUtil.isEmpty(parseName)) { fileName = parseName; } else { fileName = StringUtil.getFileNameWithSuffix(filePath); } response.setContentType(request.getServletContext().getMimeType(fileName)); FileInputStream fileInputStream = null; if (parsePdf != null && (folderPath.endsWith("xls") || folderPath.endsWith("xlsx") || folderPath.endsWith("ppt") || folderPath.endsWith("pptx") || folderPath.endsWith("doc") || folderPath.endsWith("docx") || folderPath.endsWith("pdf"))) { response.setContentType("application/pdf"); response.setHeader("Content-Disposition", "inline;fileName=" + new String(fileName.getBytes("GBK"), "ISO8859-1") + ".pdf"); String output = null; if (!folderPath.endsWith("pdf")) { output = FilenameUtils.separatorsToSystem(FilenameUtils.getFullPath(folderPath) + FilenameUtils.getBaseName(folderPath) + ".pdf"); } else { output = folderPath; } File testFile = new File(output); if (testFile.exists()) { fileInputStream = new FileInputStream(testFile); } else { fileInputStream = new FileInputStream(PdfUtils.convertToPdf(folderPath, output)); } } else { if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) { response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1")); } else { response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1")); } fileInputStream = new FileInputStream(file); } OutputStream outputStream = response.getOutputStream(); byte[] buffer = new byte[512]; int count; while ((count = fileInputStream.read(buffer)) > 0) { outputStream.write(buffer, 0, count); } outputStream.flush(); outputStream.close(); fileInputStream.close(); } @RequestMapping(value = {"/downLoad"}) public void downLoad(HttpServletRequest request, HttpServletResponse response) throws IOException { String downPath = request.getParameter("file"); String parseName = request.getParameter("parseName"); String fileName = null; if (!StringUtil.isEmpty(parseName)) { fileName = parseName; } else { fileName = StringUtil.getFileNameWithSuffix(downPath); } InputStream inputStream = HttpUtil.urlToStream(downPath); response.setContentType(request.getServletContext().getMimeType(fileName)); if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) { response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("GBK"), "ISO8859-1")); } else { response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO8859-1")); } OutputStream outputStream = response.getOutputStream(); byte[] buffer = new byte[512]; int count; while ((count = inputStream.read(buffer)) > 0) { outputStream.write(buffer, 0, count); } outputStream.flush(); outputStream.close(); inputStream.close(); } }
