1、需要的依赖:
<dependency> <groupId>net.sf.jxls</groupId> <artifactId>jxls-core</artifactId> <version>1.0.6</version> <scope>compile</scope> </dependency>
2、工具类:
public class ExcelUtils { public static File createFile(Map<String,Object> beans,String name, File file, String path){ XLSTransformer transformer=new XLSTransformer(); File newFile =new File(path+name); try { InputStream inputStram = new BufferedInputStream(new FileInputStream(file)); OutputStream outputStream = new FileOutputStream(newFile); Workbook workbook = transformer.transformXLS(inputStram, beans); workbook.write(outputStream); outputStream.flush(); return newFile; } catch (Exception e) { e.printStackTrace(); } return newFile; } public static void downloadFile(HttpServletResponse response,File excelFile) throws UnsupportedEncodingException { /* 设置文件ContentType类型,这样设置,会自动判断下载文件类型 */ response.setContentType("application/myexcel"); /* 设置文件头:最后一个参数是设置下载文件名 */ //response.setHeader("Content-Disposition", "attachment;filename=" + excelFile.getName()); response.setHeader("Content-disposition","attachment; filename="+new String(excelFile.getName().getBytes("GB2312"),"8859_1")+".xls"); try ( InputStream ins = new FileInputStream(excelFile); OutputStream os = response.getOutputStream() ) { byte[] b = new byte[1024]; int len; while ((len = ins.read(b)) > 0) { os.write(b, 0, len); } } catch (IOException ioe) { ioe.printStackTrace(); } } /** * 浏览器下载完成之后删除服务器生成的文件 * 也可以设置定时任务去删除服务器文件 * * @param excelFile */ public static void deleteFile(File excelFile) { excelFile.delete(); } //如果目录不存在创建目录 存在则不创建 public static void createDir(File file) { if (!file.exists()) { file.mkdirs(); } } }
3、测试:
/** * excel导出 * 1.获取数据集List 插入到map集合中 * 2.根据模板生成新的excel * 3.将新生成的excel文件从浏览器输出 * 4.删除新生成的模板文件 */
@RequestMapping(value = "/export",method = RequestMethod.GET) private void excel(HttpServletResponse response) throws UnsupportedEncodingException { List<User> userList =new ArrayList<>(); userList.add(new User("1","张三","18","男")); userList.add(new User("2","李四","19","女")); userList.add(new User("3","王五","20","男")); Map<String,Object> beans =new HashMap<>(); beans.put("list",userList); File file=null; try { file = ResourceUtils.getFile("classpath:excel/用户信息.xlsx"); } catch (FileNotFoundException e) { logger.info("模板路径未找到!"); } String path="/"; String name="用户信息"; //根据模板生成新的Excel File excelFile = ExcelUtils.createFile(beans,name, file, path); //浏览器端下载文件 ExcelUtils.downloadFile(response,excelFile); //删除该服务器生成文件 ExcelUtils.deleteFile(excelFile); }