SSM中利用POI导出数据库到excel表格

最新推荐文章于 2021-06-19 16:40:21 发布

原创最新推荐文章于 2021-06-19 16:40:21 发布 · 2.4k 阅读

13 ·

CC 4.0 BY-SA版权

SSM 专栏收录该内容

4 篇文章

订阅专栏

本文介绍如何在Spring、SpringMVC和MyBatis（SSM）框架下，结合Apache POI库将数据库中的数据导出到Excel表格。主要涉及Controller层的处理和JSP视图的交互。

jsp:

<span><a href="#" onclick="export_data()">导出数据</a></span>

js:

<script type="text/javascript">
	function export_data(){
		window.location.href="${ctx}/swipeRecord/reprotRecord";
	}

</script>

controller:

/**
	 * 导出数据
	 * 
	 * @param response
	 * @return
	 * @throws IOException
	 */
	@RequestMapping("/reprotRecord")
	public String reprotRecord(HttpServletResponse response) throws IOException {

		// 文件名称
		String fileName = URLEncoder.encode("刷卡记录.xls", "utf-8");
		// 通过response设置Content-Type、Content-Disposition
		response.setContentType("application/vnd.ms-excel");
		response.setHeader("Content-Disposition",
				"attachment;filename*=utf-8'zh_cn'" + fileName);
		
		//生成workBook
	//	HSSFWorkbook workbook = createWorkbook();
		
		OutputStream outputStream = null;
		HSSFWorkbook workBook = null;

		try {
			// 获取输出流
			outputStream = response.getOutputStream();
			// 生成workBook
			workBook = createWorkbook();
			workBook.write(outputStream);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			//关闭
			if (outputStream!=null) {
				outputStream.close();
			}
		}

		return null;
	}
	public HSSFWorkbook createWorkbook() {
		// 创建表格
		HSSFWorkbook workBook = new HSSFWorkbook();
		// 创建工作簿
		HSSFSheet sheet = workBook.createSheet("我的工作簿01");
		// 样式
		HSSFCellStyle style = workBook.createCellStyle();
		// 创建Font
		HSSFFont font = workBook.createFont();
		// 设置字体
		font.setColor(HSSFFont.COLOR_NORMAL);
		style.setFont(font);

		// 创建行(表头)
		HSSFRow row = sheet.createRow(0);
		//创建列
		HSSFCell cell_01 = row.createCell(0);
		cell_01.setCellValue("id");
		cell_01.setCellStyle(style);
		HSSFCell cell_02 = row.createCell(1);
		cell_02.setCellValue("员工卡号");
		cell_02.setCellStyle(style);
		HSSFCell cell_03 = row.createCell(2);
		cell_03.setCellValue("持卡类型");
		cell_03.setCellStyle(style);
		HSSFCell cell_04 = row.createCell(3);
		cell_04.setCellValue("打卡时间");
		cell_04.setCellStyle(style);
		HSSFCell cell_05 = row.createCell(4);
		cell_05.setCellValue("手机号码");
		cell_05.setCellStyle(style);
		HSSFCell cell_06 = row.createCell(5);
		cell_06.setCellValue("图片");
		cell_06.setCellStyle(style);
		HSSFCell cell_07 = row.createCell(6);
		cell_07.setCellValue("时间");
		cell_07.setCellStyle(style);

		// 内容 真实环境查询数据库List，进行for遍历
		List<SwipeRecord> listSwipeRecord = swipeRecordService.listSwipeRecord();
		for (int i = 0; i < listSwipeRecord.size(); i++) {
			HSSFRow row1 = sheet.createRow(i+1);
			row1.setHeight((short) 300);
			SwipeRecord swipeRecord = listSwipeRecord.get(i);
			HSSFCell c1 = row1.createCell(0);
			c1.setCellValue(swipeRecord.getId());
			HSSFCell c2 = row1.createCell(1);
			c2.setCellValue(swipeRecord.getCardsNumber());
			HSSFCell c3 = row1.createCell(2);
			c3.setCellValue(swipeRecord.getCardType());
			HSSFCell c4 = row1.createCell(3);
			c4.setCellValue(swipeRecord.getSwipeTime());
			HSSFCell c5 = row1.createCell(4);
			c5.setCellValue(swipeRecord.getPhone());
			HSSFCell c6 = row1.createCell(5);
			c6.setCellValue(swipeRecord.getImage());
			HSSFCell c7 = row1.createCell(6);
			c7.setCellValue(swipeRecord.getRecordDate());
		}
		return workBook;

	}