POI导出Execle前后台实例

xiaoxiao2021-03-25 107

1、后台代码

@SuppressWarnings({ "rawtypes", "resource" }) @RequestMapping(value = "/exportLinkAnalysis.do", method = RequestMethod.POST) public void exportLinkAnalysis(LinkAnalysis linkAnalysis,HttpServletRequest request, HttpServletResponse response) { try { response.setCharacterEncoding("UTF-8"); //创建workbook HSSFWorkbook workbook = new HSSFWorkbook(); //创建sheet页 HSSFSheet sheet = workbook.createSheet("同环比分析"); //设置列宽 sheet.setDefaultColumnWidth(15); HSSFCellStyle style = workbook.createCellStyle(); // 样式对象 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平 //查询参数获取 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); SimpleDateFormat sdf2 = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss"); String timeStr = sdf.format(linkAnalysis.getBeginTime()) + " 00:00:00"; linkAnalysis.setBeginTime(sdf2.parse(timeStr)); String timeStr2 = sdf.format(linkAnalysis.getEndTime()) + " 23:59:59"; linkAnalysis.setEndTime(sdf2.parse(timeStr2)); List<Map> m = linkAnalysisService.queryPage(linkAnalysis); //插入表头 //创建单元格 //设定表格第一行 HSSFRow row = sheet.createRow(0); HSSFCell c0 = row.createCell(0); c0.setCellValue(new HSSFRichTextString("部门")); HSSFCell c1 = row.createCell(1); c1.setCellValue(new HSSFRichTextString("水表总数")); HSSFCell c2 = row.createCell(2); c2.setCellValue(new HSSFRichTextString("总水量（T）")); HSSFCell c3 = row.createCell(3); c3.setCellValue(new HSSFRichTextString("同比递增")); HSSFCell c4 = row.createCell(7); c4.setCellValue(new HSSFRichTextString("同比递减")); HSSFCell c5 = row.createCell(11); c5.setCellValue(new HSSFRichTextString("环比递增")); HSSFCell c6 = row.createCell(15); c6.setCellValue(new HSSFRichTextString("环比递减")); //设置单元格样式 c3.setCellStyle(style); c4.setCellStyle(style); c5.setCellStyle(style); c6.setCellStyle(style); //设定表格第二行 HSSFRow row1 = sheet.createRow(1); for(int i = 0; i < 4; i ++){ HSSFCell c7 = row1.createCell(i*4+3); c7.setCellValue(new HSSFRichTextString("30%")); c7.setCellStyle(style); } for(int i = 0; i < 4; i ++){ HSSFCell c8 = row1.createCell(i*4+5); c8.setCellValue(new HSSFRichTextString("30%以上")); c8.setCellStyle(style); } //设定表格第三行 HSSFRow row2 = sheet.createRow(2); for(int i = 0; i < 8; i ++){ HSSFCell c9 = row2.createCell(i*2+3); c9.setCellValue(new HSSFRichTextString("水表数量")); } for(int i = 0; i < 8; i ++){ HSSFCell c9 = row2.createCell(i*2+4); c9.setCellValue(new HSSFRichTextString("水量(T)")); } //第一行的位置格式，firstRow, lastRow, firstCol, lastCol CellRangeAddress region1 = new CellRangeAddress(0, 2, 0, 0); CellRangeAddress region2 = new CellRangeAddress(0, 2, 1, 1); CellRangeAddress region3 = new CellRangeAddress(0, 2, 2, 2); CellRangeAddress region4 = new CellRangeAddress(0, 0, 3, 6); CellRangeAddress region5 = new CellRangeAddress(0, 0, 7, 10); CellRangeAddress region6 = new CellRangeAddress(0, 0, 11, 14); CellRangeAddress region7 = new CellRangeAddress(0, 0, 15, 18); //第二行的位置格式 CellRangeAddress region8 = new CellRangeAddress(1, 1, 3, 4); CellRangeAddress region9 = new CellRangeAddress(1, 1, 5, 6); CellRangeAddress region10 = new CellRangeAddress(1, 1, 7, 8); CellRangeAddress region11 = new CellRangeAddress(1, 1, 9, 10); CellRangeAddress region12 = new CellRangeAddress(1, 1, 11, 12); CellRangeAddress region13 = new CellRangeAddress(1, 1, 13, 14); CellRangeAddress region14 = new CellRangeAddress(1, 1, 15, 16); CellRangeAddress region15 = new CellRangeAddress(1, 1, 17, 18); sheet.addMergedRegion(region1); //调用格式 sheet.addMergedRegion(region2); //调用格式 sheet.addMergedRegion(region3); //调用格式 sheet.addMergedRegion(region4); //调用格式 sheet.addMergedRegion(region5); //调用格式 sheet.addMergedRegion(region6); //调用格式 sheet.addMergedRegion(region7); //调用格式 sheet.addMergedRegion(region8); //调用格式 sheet.addMergedRegion(region9); //调用格式 sheet.addMergedRegion(region10); //调用格式 sheet.addMergedRegion(region11); //调用格式 sheet.addMergedRegion(region12); //调用格式 sheet.addMergedRegion(region13); //调用格式 sheet.addMergedRegion(region14); //调用格式 sheet.addMergedRegion(region15); //调用格式 //插入数据 for(int k = 0; k < m.size(); k++){ HSSFRow row3 = sheet.createRow(3+k);//设定第三开始行 HSSFCell c10 = row3.createCell(0); c10.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("areaName")))); HSSFCell c11 = row3.createCell(1); c11.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("meterTotal")))); HSSFCell c12 = row3.createCell(2); c12.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("waterTotal")))); HSSFCell c13 = row3.createCell(3); c13.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Ynum0")))); HSSFCell c14 = row3.createCell(4); c14.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("YweterTotal0")))); HSSFCell c15 = row3.createCell(5); c15.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Ynum1")))); HSSFCell c16 = row3.createCell(6); c16.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("YweterTotal1")))); HSSFCell c17 = row3.createCell(7); c17.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Ynum2")))); HSSFCell c18 = row3.createCell(8); c18.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("YweterTotal2")))); HSSFCell c19 = row3.createCell(9); c19.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Ynum3")))); HSSFCell c20 = row3.createCell(10); c20.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("YweterTotal3")))); HSSFCell c21 = row3.createCell(11); c21.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Mnum0")))); HSSFCell c22 = row3.createCell(12); c22.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("MweterTotal0")))); HSSFCell c23 = row3.createCell(13); c23.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Mnum1")))); HSSFCell c24 = row3.createCell(14); c24.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("MweterTotal1")))); HSSFCell c25 = row3.createCell(15); c25.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Mnum2")))); HSSFCell c26 = row3.createCell(16); c26.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("MweterTotal2")))); HSSFCell c27 = row3.createCell(17); c27.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("Mnum3")))); HSSFCell c28 = row3.createCell(18); c28.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("MweterTotal3")))); } String titleName="同环比分析"; String fileName =titleName+DateUtil.generateStandardDateTimeFormat2(new Date()) + ".xls"; response.reset(); response.setContentType("application/x-msdownload"); response.setHeader("Content-Disposition","attachment; filename="+java.net.URLEncoder.encode(fileName, "UTF-8")); ServletOutputStream outStream=null; try{ outStream = response.getOutputStream(); workbook.write(outStream); }catch(Exception e) { e.printStackTrace(); }finally{ outStream.close(); } } catch (Exception e) { e.printStackTrace(); } } /** * * @Title: exportLinkAnalysisDetail * @Description: 同环比分析详情导出 * @return void * @author Long jun * @date 2017年3月2日上午11:46:27 */ @SuppressWarnings({ "rawtypes", "resource", "deprecation" }) @RequestMapping(value = "/exportLinkAnalysisDetail.do", method = RequestMethod.POST) public void exportLinkAnalysisDetail(LinkAnalysis linkAnalysis,HttpServletRequest request, HttpServletResponse response) { try { response.setCharacterEncoding("UTF-8"); //创建workbook HSSFWorkbook workbook = new HSSFWorkbook(); //创建sheet页 HSSFSheet sheet = workbook.createSheet("同环比分析详情"); sheet.setDefaultColumnWidth(15); HSSFCellStyle style = workbook.createCellStyle(); // 样式对象 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// 垂直 style.setAlignment(HSSFCellStyle.ALIGN_CENTER);// 水平 //查询参数获取 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd"); SimpleDateFormat sdf2 = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss"); String beginTime = sdf.format(linkAnalysis.getBeginTime()); String timeStr = beginTime + " 00:00:00"; linkAnalysis.setBeginTime(sdf2.parse(timeStr)); String endTime = sdf.format(linkAnalysis.getEndTime()); String timeStr2 = endTime + " 23:59:59"; linkAnalysis.setEndTime(sdf2.parse(timeStr2)); List<Map> m = new ArrayList<Map>(); if(linkAnalysis.getQueryId() == null || linkAnalysis.getQueryId().length() < 2){ m = linkAnalysisService.queryDetailPage(linkAnalysis); }else if(linkAnalysis.getQueryId().equals("Mnum0")||linkAnalysis.getQueryId().equals("Mnum1")||linkAnalysis.getQueryId().equals("Mnum2")||linkAnalysis.getQueryId().equals("Mnum3")){ m = linkAnalysisService.queryAnMeter(linkAnalysis); }else{ m = linkAnalysisService.queryMomMeter(linkAnalysis); } //获取上年同比的开始时间和结束时间 String beginTime1 = Utils.getSpecifiedDateBefore(timeStr, 1, "year"); String endTime1 = Utils.getSpecifiedDateBefore(timeStr2, 1, "year"); //间隔天数 int differentDays = Utils.differentDays(sdf2.parse(timeStr), sdf2.parse(timeStr2)); //获取上期环比的开始时间和结束时间 String beginTime2 = Utils.getSpecifiedDateBefore(timeStr,differentDays,"day"); String endTime2 = beginTime; //插入表头 //创建单元格 //设定表格第一行 HSSFRow row = sheet.createRow(0); HSSFCell c0 = row.createCell(0); c0.setCellValue(new HSSFRichTextString("水表编号")); HSSFCell c1 = row.createCell(1); c1.setCellValue(new HSSFRichTextString("水表名称")); HSSFCell c2 = row.createCell(2); c2.setCellValue(new HSSFRichTextString("本期水量（T）")); HSSFCell c3 = row.createCell(3); c3.setCellValue(new HSSFRichTextString("上年同比"+beginTime1+"~"+endTime1)); HSSFCell c4 = row.createCell(6); c4.setCellValue(new HSSFRichTextString("上期环比"+beginTime2+"~"+endTime2)); //设置单元格样式 c3.setCellStyle(style); c4.setCellStyle(style); //设定表格第二行 HSSFRow row1 = sheet.createRow(1); HSSFCell c5 = row1.createCell(3); c5.setCellValue(new HSSFRichTextString("同比水量(T)")); for(int i = 0; i < 2; i ++){ HSSFCell c6 = row1.createCell(i*3+4); c6.setCellValue(new HSSFRichTextString("正负(T)")); } for(int i = 0; i < 2; i ++){ HSSFCell c7 = row1.createCell(i*3+5); c7.setCellValue(new HSSFRichTextString("比例(%)")); } HSSFCell c8 = row1.createCell(6); c8.setCellValue(new HSSFRichTextString("环比水量(T)")); //第一行的位置格式 Region region1 = new Region(0, (short)0, 1, (short)0); Region region2 = new Region(0, (short)1, 1, (short)1); Region region3 = new Region(0, (short)2, 1, (short)2); Region region4 = new Region(0, (short)3, 0, (short)5); Region region5 = new Region(0, (short)6, 0, (short)8); sheet.addMergedRegion(region1); //调用格式 sheet.addMergedRegion(region2); //调用格式 sheet.addMergedRegion(region3); //调用格式 sheet.addMergedRegion(region4); //调用格式 sheet.addMergedRegion(region5); //调用格式 //插入数据 for(int k = 0; k < m.size(); k++){ HSSFRow row3 = sheet.createRow(2+k);//设定第三开始行 HSSFCell c10 = row3.createCell(0); c10.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("meterCode")))); HSSFCell c11 = row3.createCell(1); c11.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("meterName")))); HSSFCell c12 = row3.createCell(2); c12.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("waterTotal")))); HSSFCell c13 = row3.createCell(3); c13.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("waterTotalY")))); HSSFCell c14 = row3.createCell(4); c14.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("zf1")))); HSSFCell c15 = row3.createCell(5); c15.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("rateY")))); HSSFCell c16 = row3.createCell(6); c16.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("waterTotalM")))); HSSFCell c17 = row3.createCell(7); c17.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("zf2")))); HSSFCell c18 = row3.createCell(8); c18.setCellValue(new HSSFRichTextString(String.valueOf(m.get(k).get("rateM")))); } String titleName="同环比分析详情"; String fileName =titleName+DateUtil.generateStandardDateTimeFormat2(new Date()) + ".xls"; response.reset(); response.setContentType("application/x-msdownload"); response.setHeader("Content-Disposition","attachment; filename="+java.net.URLEncoder.encode(fileName, "UTF-8")); ServletOutputStream outStream=null; try{ outStream = response.getOutputStream(); workbook.write(outStream); }catch(Exception e) { e.printStackTrace(); }finally{ outStream.close(); } } catch (Exception e) { e.printStackTrace(); } }

2、前台代码

//导出同环比分析 function linkAnalysisExport(){ //设置高级查询条件 var param = $('#grid_linkAnalysis').datagrid('options').queryParams; var form = $("<form method='post'></form>"); form.attr("action",getRootPath()+"/runindex/linkAnalysis/exportLinkAnalysis.do"); $.each(param,function(key,value){ var attr=null; if(value){ attr = $("<input>").attr("type","hidden").attr("name",key).attr("value",value); }else{ attr = $("<input>").attr("type","hidden").attr("name",key).attr("value",""); } if(attr!=null){ form.append(attr); } }); form.appendTo("body").submit(); }

3、导出效果

转载请注明原文地址: https://ju.6miu.com/read-12294.html

技术

最新回复(0)