java poi 导出.xls的Excel报表 IE11导出文件名称后缀是.action,待修复

Franciswmf

浏览: 797278 次
性别:
来自: 上海

最近访客更多访客>>

gglzf4

fengqingyebai

kouok

xubbsun

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

poi-导入导出

//
	public void exportStationWaterQualityData(){
		
		List<Object> list=treeService.getExportStationWaterQualityData(stcd, startTm, endTm);
		if (null!=list&&list.size()>0) {
		List<Map<String,String>> factorList = (List<Map<String,String>>)list.get(0);//表头因子项
		List<Object[]> ItemList=(List<Object[]>)list.get(1);//数据项
		  // Excel 文件存放在服务器的相对路径下   
		  HttpServletRequest request = ServletActionContext.getRequest();
		  HttpServletResponse response=ServletActionContext.getResponse();
		  response.reset();
		  try {
		   HSSFWorkbook workbook = new HSSFWorkbook();   
		   HSSFSheet sheet = workbook.createSheet("Sheet1");   
		   HSSFCellStyle cellStyleMiddle = workbook.createCellStyle();   
		   cellStyleMiddle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
		   HSSFCellStyle cellStyleLeft = workbook.createCellStyle();   
		   cellStyleLeft.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居左
		   HSSFRow row;
		   HSSFCell cell;
		   //1、创建标题行
		   row=sheet.createRow((int)0);
		   cell=row.createCell(0);
		   int sizeLen=factorList.size();
		   sheet.addMergedRegion(new CellRangeAddress(0, 0, (short)0, (short)sizeLen)); 
		   cell.setCellValue(reportTitle);//标题
		   cell.setCellStyle(cellStyleMiddle);//居中
		   //2、创建说明（图例）行
		   row=sheet.createRow((int)1);
		   cell=row.createCell(0);
		   sheet.addMergedRegion(new CellRangeAddress(1, 1, (short)0, (short)sizeLen));
		   cell=row.createCell(0);
		   cell.setCellValue("注意：。。。");//标题
		   cell.setCellStyle(cellStyleLeft);//居左
		   //3、创建动态表头
		   row=sheet.createRow(2);//第三行
		   cell=row.createCell(0);
		   cell.setCellValue("时间");
		   cell.setCellStyle(cellStyleMiddle);//居中
		   //第三行第一列后面的动态读取
		   for (int i = 0; i < factorList.size(); i++) {
			   cell=row.createCell(i+1);
				if (null!=factorList.get(i).get("sampling_unit")&&!"".equals(factorList.get(i).get("sampling_unit"))) {
					cell.setCellValue(factorList.get(i).get("name")+"("+factorList.get(i).get("sampling_unit")+")");
				}else {
					cell.setCellValue(factorList.get(i).get("name"));
				}
			   cell.setCellStyle(cellStyleMiddle);//居中
		   }
		   //4、设置表体数据
		   Object[] arr;
		   for (int i = 0; i < ItemList.size(); i++) {
			   arr=ItemList.get(i);//行数据准备
			   row=sheet.createRow(i+3);//增加行，第四行开始......
			   for (int j = 0; j < arr.length; j++) {
				   cell=row.createCell(j);
				   cell.setCellValue(arr[j].toString());
				   cell.setCellStyle(cellStyleMiddle);//设置样式  
			}
		    }
		   //5、设置自定义导出路径
		   response.setContentType("octets/stream");
	       String excelName = "评估表";
	       //转码防止乱码
	       response.addHeader("Content-Disposition", "attachment;filename="+new String( excelName.getBytes("gb2312"), "ISO8859-1" )+".xls");
		   OutputStream os = response.getOutputStream();
		   workbook.write(os);
		   os.close();
		   System.out.println("excel导出成功");
		  } catch (Exception ioexception) {   
		   request.setAttribute("message", "excel导出失败");   
		  }   
		}

		
		
		
		
	}

第二版，依然存在IE11多次导出文件后缀名是.action的问题，待进一步解决：
	List<Object> list=treeService.getExportStationWaterQualityData(stcd, startTm, endTm);
		if (null!=list&&list.size()>0) {
		List<Map<String,String>> factorList = (List<Map<String,String>>)list.get(0);//表头因子项
		List<Object[]> ItemList=(List<Object[]>)list.get(1);//数据项
		List<Object[]> ItemStyleList=(List<Object[]>)list.get(2);//样式项
		  // 
		  HttpServletRequest request = ServletActionContext.getRequest();
		  HttpServletResponse response=ServletActionContext.getResponse();
		  response.reset();
		  try {
		   HSSFWorkbook workbook = new HSSFWorkbook();   
		   HSSFSheet sheet = workbook.createSheet("Sheet1");   
		   //title
		   HSSFCellStyle cellStyleMiddleTitle = workbook.createCellStyle();   
		   cellStyleMiddleTitle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//字体、居中
		   HSSFFont hssfFontTitle = workbook.createFont();
		   hssfFontTitle.setFontName("微软雅黑");
		   hssfFontTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗
		   hssfFontTitle.setFontHeightInPoints((short)10);//
		   cellStyleMiddleTitle.setFont(hssfFontTitle);
		   //remark
		   HSSFCellStyle cellStyleMiddleRemark = workbook.createCellStyle();   
		   cellStyleMiddleRemark.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中
		   //
		   HSSFCellStyle cellStyleLeft = workbook.createCellStyle();   
		   cellStyleLeft.setAlignment(HSSFCellStyle.ALIGN_LEFT);//居左
		   HSSFRow row;
		   HSSFCell cell;
		   //1、创建标题行
		   row=sheet.createRow((int)0);
		   cell=row.createCell(0);
		   int sizeLen=factorList.size();
		   sheet.addMergedRegion(new CellRangeAddress(0, 0, (short)0, (short)sizeLen)); 
		   cell.setCellValue(reportTitle);//标题
		   cell.setCellStyle(cellStyleMiddleTitle);//居中
		   //2、创建说明（图例）行
		   row=sheet.createRow((int)1);
		   cell=row.createCell(0);
		   sheet.addMergedRegion(new CellRangeAddress(1, 1, (short)0, (short)sizeLen));
		   cell=row.createCell(0);
		   cell.setCellValue("（注：。。。。。。）");//标题
		   cell.setCellStyle(cellStyleLeft);//居左
		   //3、创建动态表头
		   row=sheet.createRow(2);//第三行
		   cell=row.createCell(0);
		   cell.setCellValue("采样时间");
		   cell.setCellStyle(cellStyleMiddleRemark);//居中
		   //第三行第一列后面的动态读取
		   for (int i = 0; i < factorList.size(); i++) {
			   cell=row.createCell(i+1);
				if (null!=factorList.get(i).get("sampling_unit")&&!"".equals(factorList.get(i).get("sampling_unit"))) {
					cell.setCellValue(factorList.get(i).get("name")+"("+factorList.get(i).get("sampling_unit")+")");
				}else {
					cell.setCellValue(factorList.get(i).get("name"));
				}
			  
		   }
		   //4、设置表体数据
		   //渲染数据
		   Object[] arr;
		   for (int i = 0; i < ItemList.size(); i++) {
			   arr=ItemList.get(i);//行数据准备
			   row=sheet.createRow(i+3);//增加行，第四行开始......
			   for (int j = 0; j < arr.length; j++) {
				   cell=row.createCell(j);
				   cell.setCellValue(arr[j].toString());   
			}
		    }
		   //给数据设置评估颜色
		 
		    //绿色
		   HSSFCellStyle cellStyle_Green = workbook.createCellStyle();   
		   HSSFFont hssfFont_Green = workbook.createFont();
		   hssfFont_Green.setColor(HSSFColor.GREEN.index);
		   cellStyle_Green.setFont(hssfFont_Green);
		   cellStyle_Green.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		   //蓝色
		   HSSFCellStyle cellStyle_Blue = workbook.createCellStyle();  
		   HSSFFont hssfFont_Blue = workbook.createFont();
		   hssfFont_Blue.setColor(HSSFColor.BLUE.index);
		   cellStyle_Blue.setFont(hssfFont_Blue);
		   cellStyle_Blue.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		   //红色
		   HSSFCellStyle cellStyle_Red = workbook.createCellStyle(); 
		   HSSFFont hssfFont_Red = workbook.createFont();
		   hssfFont_Red.setColor(HSSFColor.RED.index);
		   cellStyle_Red.setFont(hssfFont_Red);
		   cellStyle_Red.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		   //黑色
		   HSSFCellStyle cellStyle_Black = workbook.createCellStyle();   
		   HSSFFont hssfFont_Black = workbook.createFont();
		   hssfFont_Black.setColor(HSSFColor.BLACK.index);
		   cellStyle_Black.setFont(hssfFont_Black);
		   cellStyle_Black.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		   //
		   Object[] obj;
		   for (int i = 0; i < ItemStyleList.size(); i++) {
			   obj=ItemStyleList.get(i);//obj=[green;0;5, green;0;6, green;0;7, green;0;8, blue;0;12]=颜色-行数-列数
			  // System.out.println("样式输出obj："+obj[0].toString());
			   if (null!=obj&&obj.length>0) {
				   for (int j = 0; j < obj.length; j++) {
						String styleObjStr=(String)obj[j];
						String[] arra=styleObjStr.split(";");
						row=sheet.getRow(Integer.parseInt(arra[1])+3);//行数
						cell=row.getCell(Integer.parseInt(arra[2]));//列数
						if ("green".equals(arra[0])) {
							 cell.setCellStyle(cellStyle_Green);//设置样式  
						} else if ("blue".equals(arra[0])) {
							 cell.setCellStyle(cellStyle_Blue);//设置样式  
						} else if ("red".equals(arra[0])) {
							 cell.setCellStyle(cellStyle_Red);//设置样式  
						}else {
							 cell.setCellStyle(cellStyle_Black);//设置样式  
						}
					}
			} else {

				//do nothing
			}
			  
			
		}
		   //5、设置自定义导出路径
		   response.setContentType("octets/stream");
	       String excelName = "。。。。表";
	       //转码防止乱码
	       response.addHeader("Content-Disposition", "attachment;filename="+new String( excelName.getBytes("gb2312"), "ISO8859-1" )+".xls");
		   OutputStream os = response.getOutputStream();
		   workbook.write(os);
		   os.close();
		   System.out.println("excel导出成功");
		  } catch (Exception ioexception) {   
		   request.setAttribute("message", "excel导出失败");   
		  }   
		}

下面两种写法运行效果一样，有待进一步完善：
/*
			   response.setContentType("octets/stream");
		       String excelName = "。。。报表";
		       response.addHeader("Content-Disposition", "attachment;filename="+new String( excelName.getBytes("gb2312"), "ISO8859-1" )+".xls");
			   OutputStream os = response.getOutputStream();
			   workbook.write(os);
			   os.flush();
			   os.close();
			   */
			  String excelName = "。。。报表";
		      response.setContentType("application/vnd.ms-excel");
		      response.setHeader("Content-disposition", "attachment;filename=" +new String( excelName.getBytes("gb2312"), "ISO8859-1" )+".xls");
		      OutputStream ouputStream = response.getOutputStream();
		      workbook.write(ouputStream);
		      ouputStream.flush();
		      ouputStream.close();

分享到：