JSP 导出Excel表格

ES6语法传入一个table的id,然后在导出excel按钮上加入一个<a href="#" id="buttonId">导出Excel</a>放里面，是用来修改导出的文件名，就可以导出Excel；具体方法就下面这一个就够了

// tableId 传null 表示自动获取页面的第一格table，buttonId 传null 表示自动生成，fileName表示导出的文件名（Excel文件名）
export function tableToExcel(tableId, buttonId, fileName) {
  let table = document.getElementById(tableId)
  if(table === null || table === undefined) {
    table = document.getElementsByTagName('table')[0]
  }
  // 克隆（复制）此table元素，这样对复制品进行修改（如添加或改变table的标题等），导出复制品，而不影响原table在浏览器中的展示。
  // table = table.cloneNode(true)
  const uri = 'data:application/vnd.ms-excel;base64,'
  const template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><?xml version="1.0" encoding="UTF-8" standalone="yes"?><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table style="vnd.ms-excel.numberformat:@">{table}</table></body></html>'
  const base64 = function(s) {
    return window.btoa(unescape(encodeURIComponent(s)))
  }
  const format = function(s, c) {
    return s.replace(/{(\w+)}/g, function(m, p) {
      return c[p]
    })
  }
  if(!table.nodeType) table = document.getElementById(table)
  const ctx = {
    worksheet: '',
    table: table.innerHTML
  }
  // window.location.href = uri + base64(format(template, ctx))
  if(buttonId === null || buttonId === undefined) {
    const dom_a = document.createElement('a') // 1、创建元素
    dom_a.style.visibility = 'hidden'
    table.insertBefore(dom_a, table.childNodes[0]) // 插入到最左边
    dom_a.href = uri + base64(format(template, ctx))
    dom_a.download = fileName
    dom_a.click()
  } else {
    document.getElementById(buttonId).href = uri + base64(format(template, ctx))
    document.getElementById(buttonId).download = fileName
  }
}

对应一个JS的版本：

function export(tableId){

  var table = document.getElementById(mytalbe);

    // 克隆（复制）此table元素，这样对复制品进行修改（如添加或改变table的标题等），导出复制品，而不影响原table在浏览器中的展示。

    table = table.cloneNode(true);

    var uri = 'data:application/vnd.ms-excel;base64,',

        template = '<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns="http://www.w3.org/TR/REC-html40"><head><!--[if gte mso 9]><?xml version="1.0" encoding="UTF-8" standalone="yes"?><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>{worksheet}</x:Name><x:WorksheetOptions><x:DisplayGridlines/></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head><body><table style="vnd.ms-excel.numberformat:@">{table}</table></body></html>',

        base64 = function(s) {

            return window.btoa(unescape(encodeURIComponent(s)));

        },

        format = function(s, c) {

            return s.replace(/{(\w+)}/g, function(m, p) {

                return c[p];

            });

        };

    if(!table.nodeType) table = document.getElementById(table);

    var ctx = {

        worksheet: name || 'Worksheet',

        table: table.innerHTML

    };

    window.location.href = uri + base64(format(template, ctx));

}

===========下面是历史方法（OUT）==============================================================

java 后台返回一个ModelAndView 对象，然后加入这2行设置

response.setContentType("application/vnd.ms-excel");

response.setHeader("Content-disposition","attachment;filename=" + URLEncoder.encode("会员列表.xls", "UTF-8"));

业可以把这二行设置放入JSP中

在jsp代码如下：

<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>

<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>

<%

    response.setContentType("application/vnd.ms-excel");

    response.setHeader("Content-disposition","attachment;filename=" + java.net.URLEncoder.encode("会员列表.xls", "UTF-8"));

%>

<!DOCTYPE html>

<html>

<head>

    <title>CMS-会员管理</title>

    <meta charset="utf-8" />

    <meta http-equiv="pragma" content="no-cache"/>

    <meta http-equiv="cache-control" content="no-cache"/>

    <meta http-equiv="expires" content="0"/>

</head>

<body>

<table id="sample-table-1" border="1" cellpadding="2" cellspacing="1">

    <thead>

    <tr>

        <th nowrap width="15%">序号</th>

        <th nowrap width="5%">卡号</th>

        <th nowrap width="5%">姓名</th>

        <th nowrap width="5%">年龄</th>

    </tr>

    </thead>

    <tbody>

    <c:forEach items="${list}"  var="model" varStatus="status" >

        <tr>

            <td>${model.id}</td>

            <td>${model.cardNo}</td>

            <td>${model.name}</td>

            <td>${model.age}</td>

        </tr>

    </c:forEach>

    </tbody>

</table>

</body>

</html>

巴特西

JSP 导出Excel表格

最新文章

热门文章