在Springmvc中导出报表下载Excel文件
来源:互联网 发布:淘宝修真记小说 编辑:程序博客网 时间:2024/06/07 18:45
controller层
@Controllerpublic class UseCaseController{ @Autowired private UseCaseService useCaseService;/***进入到useCase_excel页面*/ @RequestMapping(value="useCase_excel.xhtml",method="RequestMethod.GET") public ModelAndView download(){ ModelAndView model = new ModelAndView("useCase_excel"); }/***实现下载导出excel功能*/@RequestMapping(value="useCase_excel_download",method="RequestMethod.GET") publice String download(HttpServletRequest request,HttpServletResponse response,String requirementId,String status,String stage){ String fileName="测试案例报表"; List<UseCase> useCases = useCaseService.createData(requirement,status,stage); List<Map<String,Object>> list =createExcelRecord(useCases); //列名 String columnNames[]={"测试案例编号","需求编号","测试案例负责人","测试人","功能描述","完成率","状态","阶段","计划执行日期","更新日期","建立日期","备注"}; //Map中的key String keys[]={"useCaseId","requirementId","useCaseOwner","useCaseTester","functionDesc","percent","status","stage","startTimeDesc","updateTimeDesc","createTimeDesc","remark"}; ByteArrayOutputStream os = new ByteArrayOutputStream(); try{ UseCaseExcelUtil.createWorkBook(list,keys,columnNames).write(os); }catch(IOException e){ e.printStackTrace(); } byte content = os.toByteArray(); InputStream is = new ByteArrayInputStream(content); 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 out = response.getOutputStream(); BufferedInputStream bis = null; BufferedOutputStream bos = null; try{ bis = new BufferedInputStream(is); bos = new BufferesOutputStream(out); byte[] buff = new byte[2048]; int bytesRead; while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) { bos.write(buff, 0, bytesRead); } }catch (final IOException e) { throw e; } finally { if (bis != null) bis.close(); if (bos != null) bos.close(); } return null; } private List<Map<String, Object>> createExcelRecord(List<UseCase> useCases) { List<Map<String, Object>> listmap = new ArrayList<Map<String, Object>>(); Map<String, Object> map = new HashMap<String, Object>(); map.put("sheetName", "sheet1"); listmap.add(map); UseCase useCase=null; for (int j = 0; j < useCases.size(); j++) { useCase=useCases.get(j); Map<String, Object> mapValue = new HashMap<String, Object>(); mapValue.put("useCaseId", useCase.getUseCaseId()); mapValue.put("requirementId", useCase.getRequirementId()); mapValue.put("useCaseOwner", useCase.getUseCaseOwner()); mapValue.put("useCaseTester", useCase.getUseCaseTester()); mapValue.put("functionDesc", useCase.getfunctionDesc()); mapValue.put("percent", useCase.getPercent()); mapValue.put("status", useCase.getStatus()); mapValue.put("stage", useCase.getStage()); mapValue.put("startTimeDesc", useCase.getStartTimeDesc()); mapValue.put("updateTimeDesc", useCase.getUpdateTimeDesc()); mapValue.put("createTimeDesc", useCase.getCreateTimeDesc()); mapValue.put("remark", useCase.getRemark()); listmap.add(mapValue); } return listmap; }}
Service层
@Servicepublic class UseCaseService{ @Autowired public UseCaseDao useCaseDao; public List<UseCase> createData(String requirementId,String status,String stage) { Map<String, Object> params = new HashMap<String, Object>(); if (StringUtils.isNotEmpty(requirementId)) { params.put("requirementId", requirementId); } if (StringUtils.isNotEmpty(status)) { params.put("status", status); } if (StringUtils.isNotEmpty(stage)) { params.put("stage", stage); } List<UseCase> useCaseList = useCaseDao.selectUseCase(params); return useCaseList; }}
Dao层
@Repositorypublic class UseCaseDao{ @Autowired private SqlSession sqlSession; public List<UseCase> selectUseCase(Map<String, Object> parameter) { return this.sqlSession.selectList("selectUseCase", parameter); }}
Mapper.Xml
<select id="selectUseCase" resultMap="BaseResultMap" parameterType="java.util.Map">select<include refid="Base_Column_List" />from USE_CASEwhere DEL_FLAG != 1<if test="requirementId !=null">and REQUIREMENT_ID like #{requirementId}</if><if test="status !=null">and STATUS like #{status} </if><if test="stage != null">and STAGE like #{stage}</if>
Excel工具
public class UseCaseExcelUtil{ public static Workbook createWorkBook(List<Map<String, Object>> list,String []keys,String columnNames[]) { // 创建excel工作簿 Workbook wb = new HSSFWorkbook(); // 创建第一个sheet(页),并命名 Sheet sheet = wb.createSheet(list.get(0).get("sheetName").toString()); // 手动设置列宽。第一个参数表示要为第几列设;,第二个参数表示列的宽度,n为列高的像素数。 for(int i=0;i<keys.length;i++){ sheet.setColumnWidth((short) i, (short) (35.7 * 150)); } // 创建第一行 Row row = sheet.createRow((short) 0); // 创建两种单元格格式 CellStyle cs = wb.createCellStyle(); CellStyle cs2 = wb.createCellStyle(); // 创建两种字体 Font f = wb.createFont(); Font f2 = wb.createFont(); // 创建第一种字体样式(用于列名) f.setFontHeightInPoints((short) 10); f.setColor(IndexedColors.BLACK.getIndex()); f.setBoldweight(Font.BOLDWEIGHT_BOLD); // 创建第二种字体样式(用于值) f2.setFontHeightInPoints((short) 10); f2.setColor(IndexedColors.BLACK.getIndex());// Font f3=wb.createFont();// f3.setFontHeightInPoints((short) 10);// f3.setColor(IndexedColors.RED.getIndex()); // 设置第一种单元格的样式(用于列名) cs.setFont(f); cs.setBorderLeft(CellStyle.BORDER_THIN); cs.setBorderRight(CellStyle.BORDER_THIN); cs.setBorderTop(CellStyle.BORDER_THIN); cs.setBorderBottom(CellStyle.BORDER_THIN); cs.setAlignment(CellStyle.ALIGN_CENTER); // 设置第二种单元格的样式(用于值) cs2.setFont(f2); cs2.setBorderLeft(CellStyle.BORDER_THIN); cs2.setBorderRight(CellStyle.BORDER_THIN); cs2.setBorderTop(CellStyle.BORDER_THIN); cs2.setBorderBottom(CellStyle.BORDER_THIN); cs2.setAlignment(CellStyle.ALIGN_CENTER); //设置列名 for(int i=0;i<columnNames.length;i++){ Cell cell = row.createCell(i); cell.setCellValue(columnNames[i]); cell.setCellStyle(cs); } //设置每行每列的值 for (short i = 1; i < list.size(); i++) { // Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的 // 创建一行,在页sheet上 Row row1 = sheet.createRow((short) i); // 在row行上创建一个方格 for(short j=0;j<keys.length;j++){ Cell cell = row1.createCell(j); cell.setCellValue(list.get(i).get(keys[j]) == null?" ": list.get(i).get(keys[j]).toString()); cell.setCellStyle(cs2); } } return wb; }}
jsp页面
<%@ page language="java" contentType="text/html; charset=utf-8" pageEncoding="utf-8"%><%@ taglib uri="http://java.sun.com/jstl/core_rt" prefix="c"%><!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><jsp:include page="/html/includes.html" flush="true" /><!DOCTYPE html><html><head> <meta charset="UTF-8"> <title>Basic Form - jQuery EasyUI Demo</title> <link rel="stylesheet" type="text/css" href="../../themes/default/easyui.css"> <link rel="stylesheet" type="text/css" href="../../themes/icon.css"> <link rel="stylesheet" type="text/css" href="../demo.css"> <script type="text/javascript" src="../../jquery.min.js"></script> <script type="text/javascript" src="../../jquery.easyui.min.js"></script></head><body> <!-- <h2>Basic Form</h2> <p>Fill the form and submit it.</p> --> <div style="margin:20px 0;"></div> <div class="easyui-panel" title="生成报表" style="width:400px"> <div style="padding:10px 60px 20px 60px"> <form id="create" method="post"> <table cellpadding="5"> <tr> <td>需求编号: </td> <td><select id="requirementId" name="requirementId" class="easyui-combobox" panelHeight="auto" style="width:150px"> <option value="">请选择</option> <c:forEach items="${crfList}" var="crfItem"> <option value="${crfItem.requirementId}">${crfItem.requirementId}</option> </c:forEach> </select> </td> </tr> <tr> <td>阶段:</td> <td> <select class="easyui-combobox" id="stage"> <option value="">请选择</option> <option value="dev">dev</option> <option value="sit">sit</option> <option value="uat">uat</option> <option value="prd">prd</option> </select> </td> </tr> <tr> <td>状态:</td> <td> <select class="easyui-combobox" id="status"> <<option value="">请选择</option> <option value="Unexecute">未执行</option> <option value="Pass">通过</option> <option value="Refused">不通过</option> </td> </tr> </table> </form> <div style="text-align:center;padding:5px"> <a id="print" href="javascript:void(0)" class="easyui-linkbutton" onclick="submitForm()">生成报表</a> </div> </div> </body><script type="text/javascript"> //初始化 $(document).ready(function() { $("#print").click(function() { var requirementId = $("#requirementId").combobox('getValue'); var status = $("#status").combobox('getValue'); var stage = $("#stage").combobox('getValue'); var useCaseTester = $("#useCaseTester").val(); window.location.href = "useCase_excel_download.xhtml?requirementId="+ requirementId + "&status=" + status+"&stage="+stage+"&useCaseTester"+useCaseTester; }); });</script></html>
1 0
- 在Springmvc中导出报表下载Excel文件
- SpringMVC+POI下载文件模板和导出Excel
- SpringMvc下载excel文件
- SpringMvc下载excel文件
- SpringMvc下载excel文件
- Cognos导出报表时在 EXCEL中进行分页
- springMvc生成导出excel文件
- 在django中导出excel文件
- 在springmvc项目中使用poi导入导出excel
- 在springmvc项目中使用poi导入导出excel
- 在springmvc项目中使用poi导入导出excel
- 在springmvc项目中使用poi导入导出excel
- springMVC方法,导出/下载文件。
- springMVC中导出excel案例
- springmvc导出excel并弹出下载框
- springmvc导出excel并弹出下载框
- java导出excel 下载文件
- Jxls导出excel的若干方式总结(十)-- 在导出报表中插入单张图片
- Android 之 传感器 应用
- 【FlexPaper】参数以及使用
- C/C++中extern关键字详解
- APUE学习笔记【4】
- Android开发之MVP模式(根据google的demo的修改版)
- 在Springmvc中导出报表下载Excel文件
- vc++经典技巧总结
- EasyUI之Combobox 数据加载完之后执行
- CodeForces 471A
- 一次旅途的启程
- 一步步实现带动画效果的下拉刷新
- 关于iOS提交审核的IDFA
- 扣丁学堂——显示网络图片到手机中
- 安卓DisplayMetrics获取屏幕分辨率