jsp/html导出excel文件

来源：互联网发布：空姐缪洁晶知乎编辑：程序博客网时间：2024/06/05 00:38

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

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="导出 " onclick="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

阅读全文

0 0