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

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-Dispositionresponse.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();// 生成workBookworkBook = createWorkbook();workBook.write(outputStream);} catch (IOException e) {// TODO Auto-generated catch blocke.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();// 创建FontHSSFFont 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;}