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

 

项目用的是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