20171027 java 学习总结——excel导出功能

<div>
            <form id="fpForm" action="${request.contextPath}/monthlyreport/downloadMonthlyreport" method="post">
                <input id="date1" name="date1" type="hidden"  /><input id="date2" name="date2" type="hidden"  />
                <input type="hidden" id="unitName" name="unitName" />
            </form>
        </div>

  //导出月报
        function exportExcel(){
            $('#date1').val($('#selYear').combobox("getValue"));
            $('#date2').val($('#selMonth').combobox("getValue"));
            $('#unitName').val(unitName);
            $('#fpForm').submit();
        }

@RequestMapping("/downloadMonthlyreport")
	public void downloadMonthVegetables(String date1,String date2,String unitName,HttpServletRequest request,HttpSession session,
			HttpServletResponse response) throws IOException {
		if(StrUtil.isNotBlank(date1) && StrUtil.isNotBlank(date2) && StrUtil.isNotBlank(unitName)){
			HSSFWorkbook work = new ExportExcel().exportExcel(date1+"年"+date2+"月"+unitName+"工作报表", getHeaderKey(),
					getHeaders(), exportlist);
			OutputStream out = FileUtil.downloadExcel(unitName+"_"+date1+"-"+date2+".xls", response);
			work.write(out);
			out.close();
		}
	}

　　

public HSSFWorkbook exportExcel(String title, String[] headerKey,
            Map<String, String> headers, List<Map<String, Object>> dataset) {
        return exportExcel(title, headerKey, headers, dataset, "yyyy-MM-dd");
    }

public HSSFWorkbook exportExcel(String title, String[] headerKey,
			Map<String, String> headers, List<Map<String, Object>> dataset,
			String pattern) {
		// 声明一个工作薄
		HSSFWorkbook workbook = new HSSFWorkbook();
		// 生成一个表格
		HSSFSheet sheet = workbook.createSheet(title);
		// 设置表格默认列宽度为15个字节
		//sheet.setDefaultColumnWidth((short)50);
		sheet.autoSizeColumn((short)0);
		sheet.setColumnWidth((short)1, (int)((150 + 0.72) * 256)); 
		Map<String, CellStyle> styles = createStyles(workbook);
		// 声明一个画图的顶级管理器
		// HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		// // 定义注释的大小和位置,详见文档
		// HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
		// 0, 0, 0, (short) 4, 2, (short) 6, 5));
		// // 设置注释内容
		// comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
		// // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
		// comment.setAuthor("hzh");

		// 产生表格标题行
		HSSFRow row = sheet.createRow(0);
		for (int i = 0; i < headerKey.length; i++) {
			HSSFCell cell = row.createCell(i);
			cell.setCellStyle(styles.get("header"));
			HSSFRichTextString text = new HSSFRichTextString(
					headers.get(headerKey[i]));
			cell.setCellValue(text);
		}
		Iterator<Map<String, Object>> ite = dataset.iterator();
		int index = 1;
		while (ite.hasNext()) {
			row = sheet.createRow(index);
			Map<String, Object> item = ite.next();
			index++;
			for (int i = 0; i < headerKey.length; i++) {
				HSSFCell cell = row.createCell(i);
				cell.setCellStyle(styles.get("cell"));
				Object value = item.get(headerKey[i]);
				if(value==null) value="";
				// 判断值的类型后进行强制类型转换
				String textValue = value.toString();
				textValue=HTMLSpirit.stripHtml(textValue);
				// 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
				if (textValue != null) {
					Pattern p = Pattern.compile("^//d+(//.//d+)?$");
					Matcher matcher = p.matcher(textValue);
					if (matcher.matches()) {
						// 是数字当作double处理
						cell.setCellValue(Double.parseDouble(textValue));
					} else {
						textValue = DeleteHtml.getTextFromTHML(textValue);//将导出的excel去除格式标签
						HSSFRichTextString richString = new HSSFRichTextString(textValue);
						HSSFFont font3 = workbook.createFont();
						font3.setColor(HSSFColor.BLUE.index);
						richString.applyFont(font3);
						cell.setCellValue(richString);
					}
				}
			}
		}
		return workbook;
	}

　　

/**
	 * 导出excle文件 downloadExcle
	 * 
	 * @author hanzh
	 * @param filename
	 * @param response
	 * @return OutputStream
	 */
	public static OutputStream downloadExcel(String filename,
			HttpServletResponse response) {
		OutputStream toClient = null;
		try {
			// 清空response
			response.reset();
			// 设置response的Header
			response.addHeader("Content-Disposition", "attachment;filename="
					+ new String(filename.getBytes("gbk"), "iso-8859-1")); 
			toClient = new BufferedOutputStream(response.getOutputStream());
			response.setContentType("application/vnd.ms-excel;charset=gb2312");
		} catch (IOException ex) {
			ex.printStackTrace();
		}
		return toClient;
	}