springboot+poi导出指定格式Excel模板详解+Demo

欢迎扫码加入Java高知群交流

项目用的是springboot框架，非常的简单，只需导入项目即可运行，先看看效果：

资源下载地址：

http://download.csdn.net/download/tjcyjd/9998721

具体步骤如下：

1、引入依赖jar包。

在pom.xml中引入两个依赖的包即可：

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi --><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>3.17</version></dependency><dependency>   <groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml</artifactId>    <version>3.17</version></dependency>

2、把需要导出的Excel模板放在webapp下的excel文件夹中。

如下目录，建立两个Excel一个2003的，一个2007的，user_model.xls、user_model.xlsx。

其中Excel需要导出的内容格式如下：

3、导出代码实现。

废话少说，直接上代码，拿来就用。

导出Excel2003：

@RequestMapping(value = "/export2003.do", method = RequestMethod.GET)public void export2003(HttpServletRequest request, HttpServletResponse response) {List<User> list = new ArrayList<User>();User user1 = new User();user1.setId(100000000L);user1.setUsername("张三");user1.setHead("http://qzapp.qlogo.cn/qzapp/101357640/3C94155CAB4E28517D8435BF404B52F1/100");user1.setSex(0);user1.setPhone("18800000000");User user2 = new User();user2.setId(100000001L);user2.setUsername("李四");user2.setHead("http://q.qlogo.cn/qqapp/1105676675/9DA6D356F4FE1DF0E63BD07334680BF2/100");user2.setSex(0);user2.setPhone("18800000001");list.add(user1);list.add(user2);HSSFWorkbook wb = null;try {// excel模板路径String basePath = request.getSession().getServletContext().getRealPath("/");String excel = basePath + "/excel/user_model.xls";File fi = new File(excel);POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fi));// 读取excel模板wb = new HSSFWorkbook(fs);// 读取了模板内所有sheet内容HSSFSheet sheet = wb.getSheetAt(0);// 在相应的单元格进行赋值int rowIndex = 1;for (User user : list) {HSSFRow row = sheet.getRow(rowIndex);if (null == row) {row = sheet.createRow(rowIndex);}HSSFCell cell0 = row.getCell(0);if (null == cell0) {cell0 = row.createCell(0);}cell0.setCellValue(user.getId());// 标识HSSFCell cell1 = row.getCell(1);if (null == cell1) {cell1 = row.createCell(1);}cell1.setCellValue(user.getUsername());// 用户名HSSFCell cell2 = row.getCell(2);if (null == cell2) {cell2 = row.createCell(2);}cell2.setCellValue(user.getHead());// 头像HSSFCell cell3 = row.getCell(3);if (null == cell3) {cell3 = row.createCell(3);}cell3.setCellValue(user.getSex() == 0 ? "女" : "男");// 性别HSSFCell cell4 = row.getCell(4);if (null == cell4) {cell4 = row.createCell(4);}cell4.setCellValue(user.getPhone());// 手机rowIndex++;}String fileName = "用户信息";ByteArrayOutputStream os = new ByteArrayOutputStream();wb.write(os);byte[] content = os.toByteArray();InputStream is = new ByteArrayInputStream(content);// 设置response参数，可以打开下载页面response.reset();response.setContentType("application/vnd.ms-excel;charset=utf-8");response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xls").getBytes(), "iso-8859-1"));ServletOutputStream sout = response.getOutputStream();BufferedInputStream bis = null;BufferedOutputStream bos = null;try {bis = new BufferedInputStream(is);bos = new BufferedOutputStream(sout);byte[] buff = new byte[2048];int bytesRead;// Simple read/write loop.while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {bos.write(buff, 0, bytesRead);}} catch (Exception e) {logger.error("导出excel出现异常:", e);} finally {if (bis != null)bis.close();if (bos != null)bos.close();}} catch (Exception e) {logger.error("导出excel出现异常:", e);}}

导出Excel2007：

@RequestMapping(value = "/export2007.do", method = RequestMethod.GET)public void export2007(HttpServletRequest request, HttpServletResponse response) {List<User> list = new ArrayList<User>();User user1 = new User();user1.setId(100000000L);user1.setUsername("张三");user1.setHead("http://qzapp.qlogo.cn/qzapp/101357640/3C94155CAB4E28517D8435BF404B52F1/100");user1.setSex(0);user1.setPhone("18800000000");User user2 = new User();user2.setId(100000001L);user2.setUsername("李四");user2.setHead("http://q.qlogo.cn/qqapp/1105676675/9DA6D356F4FE1DF0E63BD07334680BF2/100");user2.setSex(0);user2.setPhone("18800000001");list.add(user1);list.add(user2);XSSFWorkbook wb = null;try {// excel模板路径String basePath = request.getSession().getServletContext().getRealPath("/");String excel = basePath + "/excel/user_model.xlsx";File fi = new File(excel);// 读取excel模板wb = new XSSFWorkbook(new FileInputStream(fi));// 读取了模板内所有sheet内容XSSFSheet sheet = wb.getSheetAt(0);// 在相应的单元格进行赋值int rowIndex = 1;int j = 1;for (User user : list) {XSSFRow row = sheet.getRow(rowIndex);if (null == row) {row = sheet.createRow(rowIndex);}XSSFCell cell0 = row.getCell(0);if (null == cell0) {cell0 = row.createCell(0);}cell0.setCellValue(user.getId());// 标识XSSFCell cell1 = row.getCell(1);if (null == cell1) {cell1 = row.createCell(1);}cell1.setCellValue(user.getUsername());// 用户名XSSFCell cell2 = row.getCell(2);if (null == cell2) {cell2 = row.createCell(2);}cell2.setCellValue(user.getHead());// 头像XSSFCell cell3 = row.getCell(3);if (null == cell3) {cell3 = row.createCell(3);}cell3.setCellValue(user.getSex() == 0 ? "女" : "男");// 性别XSSFCell cell4 = row.getCell(4);if (null == cell4) {cell4 = row.createCell(4);}cell4.setCellValue(user.getPhone());// 手机rowIndex++;}String fileName = "用户信息";ByteArrayOutputStream os = new ByteArrayOutputStream();wb.write(os);byte[] content = os.toByteArray();InputStream is = new ByteArrayInputStream(content);// 设置response参数，可以打开下载页面response.reset();response.setContentType("application/vnd.ms-excel;charset=utf-8");response.setHeader("Content-Disposition", "attachment;filename=" + new String((fileName + ".xlsx").getBytes(), "iso-8859-1"));ServletOutputStream sout = response.getOutputStream();BufferedInputStream bis = null;BufferedOutputStream bos = null;try {bis = new BufferedInputStream(is);bos = new BufferedOutputStream(sout);byte[] buff = new byte[2048];int bytesRead;// Simple read/write loop.while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {bos.write(buff, 0, bytesRead);}} catch (Exception e) {logger.error("导出excel出现异常:", e);} finally {if (bis != null)bis.close();if (bos != null)bos.close();}} catch (Exception e) {logger.error("导出excel出现异常:", e);}}

导出的结果如下：

导出成功！

资源下载地址：

http://download.csdn.net/download/tjcyjd/9998721

欢迎扫码加入Java高知群交流