//前端设置下载方法
function f_exportGrid(){ var para = $("#form1").serializeArray() var url = action+'!exportGrid.action?'; for(var i=0;i<para.length;i++){ url += para[i].name+'='+para[i].value+'&';//加入参数 } var pageNo = gridManager.options.page; var pageSize = gridManager.options.pageSize; url += 'pageNo='+pageNo; url += '&pageSize='+pageSize; location.href=url;//下载行为 }
//后台
public void exportGrid1(List list) throws IOException { // 生成Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("数据"); HSSFCellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中 cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中 cellStyle.setBorderTop(BorderStyle.THIN);// 上边框 细边线 cellStyle.setBorderBottom(BorderStyle.THIN);// 下边框 细边线 cellStyle.setBorderLeft(BorderStyle.THIN);// 左边框 细边线 cellStyle.setBorderRight(BorderStyle.THIN);// 右边框 细边线 // 设置字体样式 Font titleFont = wb.createFont(); titleFont.setFontHeightInPoints((short) 13); // 字体高度 titleFont.setFontName("宋体"); // 字体样式 titleFont.setBold(true); cellStyle.setFont(titleFont); String name = ""; int start=0; int num = 0 ; for (int i = 0; i < list.size(); i++) { Map map = (Map)list.get(i); String name1 = map.get("UserName").toString(); if(!name1.equals(name) ){ if(StringUtils.isBlank(name)){ name=name1; }else{ if(num != 1){ int end = num+start-1; sheet.addMergedRegion(new CellRangeAddress(start,end,0,0)); //设置合并列 sheet.addMergedRegion(new CellRangeAddress(start,end,3,3));//设置合并列 } name = name1; } start = i+1; num=1; }else{ num ++ ; if(name1.equals(name) && i==list.size()-1){ int end = num+start-1; sheet.addMergedRegion(new CellRangeAddress(start,end,0,0)); sheet.addMergedRegion(new CellRangeAddress(start,end,3,3)); } //start = i; } } // 表头 String[] heararr = {"人员","点位","时长(单位：分钟)","总时长(单位：分钟)"}; //System.err.println("heararr: "+heararr.length); HSSFRow headRow = sheet.createRow(0); for (int i = 0; i < heararr.length; i++) { HSSFCell cell = headRow.createCell(i); cell.setCellValue(heararr[i]); cell.setCellStyle(cellStyle); sheet.autoSizeColumn(i);// 自动设置宽度 } HSSFCellStyle dataStyle = wb.createCellStyle(); dataStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中 dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中 dataStyle.setBorderTop(BorderStyle.THIN);// 上边框 细边线 dataStyle.setBorderBottom(BorderStyle.THIN);// 下边框 细边线 dataStyle.setBorderLeft(BorderStyle.THIN);// 左边框 细边线 dataStyle.setBorderRight(BorderStyle.THIN);// 右边框 细边线 Font dataFont = wb.createFont(); dataFont.setFontHeightInPoints((short) 11); // 字体高度 titleFont.setFontName("宋体"); // 字体样式 dataStyle.setFont(dataFont); dataStyle.setWrapText(true);//设置自动换行 for (int i = 0; i < list.size(); i++) { HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1); Map map = (Map)list.get(i); HSSFCell cell1 = dataRow.createCell(0); cell1.setCellValue(map.get("UserName").toString()); cell1.setCellStyle(dataStyle); sheet.autoSizeColumn(0);// 自动设置宽度 cell1 = dataRow.createCell(1); cell1.setCellValue(map.get("OutfallsName").toString()); cell1.setCellStyle(dataStyle); sheet.autoSizeColumn(1);// 自动设置宽度 cell1 = dataRow.createCell(2); cell1.setCellValue(map.get("Num").toString()); cell1.setCellStyle(dataStyle); sheet.autoSizeColumn(2);// 自动设置宽度 cell1 = dataRow.createCell(3); cell1.setCellValue(map.get("Sum").toString()); cell1.setCellStyle(dataStyle); sheet.autoSizeColumn(3);// 自动设置宽度 } // 下载导出 // 设置头信息 ServletActionContext.getResponse().setContentType( "application/vnd.ms-excel"); String filename = "任务人员.xls"; HttpServletRequest request = ServletActionContext.getRequest(); filename = processFileName(request,filename); ServletActionContext.getResponse().setHeader("Content-Disposition", "attachment;filename=" + filename); ServletOutputStream outputStream = ServletActionContext.getResponse() .getOutputStream(); wb.write(outputStream); wb.close(); } //解决设置名称时的乱码 public static String processFileName(HttpServletRequest request, String fileNames) { String codedfilename = null; try { String agent = request.getHeader("USER-AGENT"); if (null != agent && -1 != agent.indexOf("MSIE") || null != agent && -1 != agent.indexOf("Trident")) {// ie String name = java.net.URLEncoder.encode(fileNames, "UTF8"); codedfilename = name; } else if (null != agent && -1 != agent.indexOf("Mozilla")) {// 火狐,chrome等 codedfilename = new String(fileNames.getBytes("UTF-8"), "iso-8859-1"); } } catch (Exception e) { e.printStackTrace(); } return codedfilename; }
//复杂表头
// 表头 String[] hear0 = {"罐号","物料名称","容积","安高m","检验状态","昨日库存","昨日库存","昨日库存","14:00" ,"14:00","22:00","22:00","6:00","6:00","6:00","6:00","库存差量","移动" ,"备注"}; String[] headnum0 = {"0,1,0,0","0,1,1,1","0,1,2,2","0,1,3,3","0,1,4,4","0,0,5,7", "0,0,8,9","0,0,10,11","0,0,12,15","0,1,16,16","0,1,17,17","0,1,18,18"}; String[] hear1 = {"罐号","物料名称","容积","安高m","检验状态","昨日库存","液位m","质量t", "液位m","质量t","液位m","质量t","液位m","质量t" ,"物料合计量","库存差量（单罐）","库存差量","移动","备注"}; String[] dataNames = {"tankCode","tankAlias","vol","safeHight","testState","preStock", "lastLvl","lastMass","lvl14","mass14","lvl22","mass22","lvl6","mass6","stock", "stockDiff","numStockDiff","moveState","moveStateEdit"}; HSSFRow headRow = sheet.createRow(0); for (int i = 0; i < hear0.length; i++) { Cell cell = headRow.createCell(i); cell.setCellStyle(cellStyle); cell.setCellValue(hear0[i]); } // 动态合并单元格 for (int i = 0; i < headnum0.length; i++) { // sheet.autoSizeColumn(i, true); String[] temp = headnum0[i].split(","); Integer startrow = Integer.parseInt(temp[0]); Integer overrow = Integer.parseInt(temp[1]); Integer startcol = Integer.parseInt(temp[2]); Integer overcol = Integer.parseInt(temp[3]); sheet.addMergedRegion(new CellRangeAddress(startrow, overrow, startcol, overcol)); } headRow = sheet.createRow(1);// 创建表头2 for (int i = 0; i < hear1.length; i++) { Cell cell = headRow.createCell(i); cell.setCellStyle(cellStyle); cell.setCellValue(hear1[i]); }