jsp/html导出excel文件

最新推荐文章于 2021-05-27 13:56:42 发布

kanguhong

最新推荐文章于 2021-05-27 13:56:42 发布

阅读量1.2k

点赞数 1

分类专栏： java jsp 文章标签： jsphtml导出excel文件导出文件

本文链接：https://blog.csdn.net/u012255097/article/details/77848530

版权

java 同时被 2 个专栏收录

74 篇文章 0 订阅

订阅专栏

jsp

34 篇文章 0 订阅

订阅专栏

之前我在博客中也写过一篇关于导出文件的博文，但感觉限制性很大。最近做一个项目感觉此方式的导出文件比较通用，可以参考借鉴，以下为代码：

1.导出页面代码MyJsp.jsp

<%@ page language="java" import="java.util.*" pageEncoding="utf-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <base href="<%=basePath%>">
    
    <title>导出</title>
    
	<meta http-equiv="pragma" content="no-cache">
	<meta http-equiv="cache-control" content="no-cache">
	<meta http-equiv="expires" content="0">    
	<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
	<meta http-equiv="description" content="This is my page">
	<script src="scripts/jquery/jquery-1.7.1.js" charset="utf-8"></script>
	<!--
	<link rel="stylesheet" type="text/css" href="styles.css">
	-->

  </head>
  
  <body>
  <div>
     <input type="button" value="导出 " οnclick="exportExcel();"/>
  </div>
  
     <form id="exportForm" action="exportExcel.jsp" method="post">
		    	   <input type="hidden" name="exportExcelsql" id="exportsql" value=""/>
		    	   <input type="hidden" name="ids" id="ids" value=""/>
	 </form>
  </body>
  <script type="text/javascript">
    function exportExcel(){//导出文件
      $('#exportForm').submit();
    }
   
  </script>
</html>

2.导出业务处理代码exportExcel.jsp

<%@ page language="java" import="java.util.*,java.io.OutputStream" pageEncoding="utf-8"%>
<%@ page import="java.sql.*,org.apache.poi.hssf.usermodel.HSSFRichTextString,org.apache.poi.hssf.usermodel.HSSFCell,org.apache.poi.hssf.usermodel.HSSFRow,org.apache.poi.hssf.usermodel.HSSFFont,org.apache.poi.hssf.util.HSSFColor,java.io.PrintWriter,org.apache.poi.hssf.usermodel.HSSFWorkbook,org.apache.poi.hssf.usermodel.HSSFSheet,org.apache.poi.hssf.usermodel.HSSFCellStyle"%>

<%
response.setContentType("text/html;charSet=utf-8");
request.setCharacterEncoding("utf-8");
System.out.println("aaaaa///==================");
response.setContentType("octets/stream");  
//response.addHeader("Content-Disposition", "attachment;filename=test.xls");  
String excelName = "通讯录列表";//导出文件名称
//转码防止乱码  
response.setContentType("application/vnd.ms-excel;charset=GBK");
response.addHeader("Content-Disposition", "attachment;filename="+new String(excelName.getBytes("gb2312"), "ISO8859-1" )+".xls");  
String[] headers = new String[]{"部门","组","姓名","手机号码","邮箱","领域","技能","照片","身份证"};//表头信息列
try {
	List<Map<String,Object>> list=new ArrayList<Map<String,Object>>();
	//====模拟导出的数据
	 for(int i=0;i<3;i++){
		 Map map=new HashMap<String,Object>();
		    map.put("部门","测试部门"+i);
		    map.put("组","测试组"+i);
		    map.put("姓名","小张"+i);
		    map.put("手机号码","12234343434"+i);
		    map.put("邮箱","aaa@qq.com"+i);
		    map.put("领域","测试领域"+i);
		    map.put("技能","测试技能"+i);
		    map.put("照片","有");
		    map.put("身份证","有");
		 list.add(map);
	 }
	 //==============
    OutputStream ou = response.getOutputStream();  
    exportExcel(excelName,list, ou,headers);  
    ou.close();
    out.clear();
    out = pageContext.pushBody();
    //System.out.println("excel导出成功！");  
} catch (Exception e) {
        e.printStackTrace();  
}
%>


<%!
/** 
 * @Description: 生成excel并导出到客户端（本地） 
 */  
protected void exportExcel(String title,List mapList,OutputStream ou,String[] headers){  
    //声明一个工作簿  
    HSSFWorkbook workbook = new HSSFWorkbook();  
    //生成一个表格  
    HSSFSheet sheet = workbook.createSheet(title);  
    //设置表格默认列宽度为20个字符  
    sheet.setDefaultColumnWidth(20);  
    //生成一个样式，用来设置标题样式  
    HSSFCellStyle style = workbook.createCellStyle();  
    //设置这些样式  
    style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);  
    style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);  
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);  
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    //生成一个字体  
    HSSFFont font = workbook.createFont();  
    font.setColor(HSSFColor.VIOLET.index);  
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);  
    //把字体应用到当前的样式  
    style.setFont(font);  
    // 生成并设置另一个样式,用于设置内容样式  
    HSSFCellStyle style2 = workbook.createCellStyle();  
    style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);  
    style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);  
    style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);  
    style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);  
    style2.setBorderRight(HSSFCellStyle.BORDER_THIN);  
    style2.setBorderTop(HSSFCellStyle.BORDER_THIN);  
    style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);  
    style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);  
    // 生成另一个字体  
    HSSFFont font2 = workbook.createFont();  
    font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);  
    // 把字体应用到当前的样式  
    style2.setFont(font2);  
    //产生表格标题行  
    HSSFRow row = sheet.createRow(0);  
    	//Map<String,Object> map1 = (Map<String, Object>) mapList.get(0);
    	int num=0;
    	//for(String key:map1.keySet()){
    		for(int i=0;i<headers.length;i++){
    		System.out.println("key====="+headers[i]);
    		HSSFCell cell = row.createCell(num++);  
            cell.setCellStyle(style);  
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);  
            cell.setCellValue(text);
    		}
    	//内容填充
    	if(mapList!=null&&mapList.size()>0){
	      for (int i=0;i<mapList.size();i++) {
	        Map<String,Object> map = (Map<String, Object>) mapList.get(i);  
	        row = sheet.createRow(i+1);  
	        int j = 0;
	        String value = "";  
	        //for(String key:map.keySet()){
	        	for(int k=0;k<headers.length;k++){
	        	value=(String)map.get(headers[k])==null?"":(String)map.get(headers[k]);
	        	row.createCell(j++).setCellValue(value);//内容
	        }
	     }  
      }
    try {  
        workbook.write(ou);  
    } catch (Exception e) {  
        e.printStackTrace();  
    }
}  

%>

3.所需jar包

poi-3.9-20121203.jar