jsp表格数据导出到Execl

1.关于“下载”

需要设置页面header的一个属性为：Content-Disposition: attachment; filename=下载的文件.txt

如：

<a href="down.jsp">下载</a>

down.jsp:
<%
        response.setHeader("Content-Disposition", "attachment;fileName=download.txt");
    %>

2.关于Execl

使用jxl的

jxl地址：http://www.andykhan.com/jexcelapi/download.html

这里面有jar包和api文档，还有一些例子。

下面看正文了。

一、写个页面index.jsp：

<%@ page language="java" contentType="text/html; charset=utf-8"
    pageEncoding="utf-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<title>导出Execl</title>
<script type="text/javascript" src="jquery-1.10.2.min.js"></script>
<script type="text/javascript">
    function toExecl(){
        //解析表格
        var arrData=new Array();
        var jsonData = "[";
        var objTable=document.getElementById("tab");
        if(objTable)
        {
           for(var i=0;i<objTable.rows.length;i++)//行
           {
              //每一行开始
              var rowData = "{";
              for(var j=0;j<objTable.rows[i].cells.length;j++)//列
              {
                  //每一行的数据
                  if(j > 0){
                      rowData = rowData + "," + objTable.rows[i].cells[j].innerText;
                  }else{
                      rowData = rowData + objTable.rows[i].cells[j].innerText;
                  }
              }
              //每一行结尾
              if(i < (objTable.rows.length -1)){
                  rowData = rowData + "},"
              }else{
                  rowData = rowData + "}"
              }
                 //添加每一行到json字符串中
                 jsonData = jsonData + rowData;
           }
        }
        jsonData = jsonData + "]";
        //alert(jsonData);
        
        /* //发送ajax请求
        $.ajax({
                    url: "toExecl",
                    type: "POST",
                    data: {"jsonData": jsonData},
                    dataType: "text",
                    cache: false,
                    success: function (result) {
                        alert("success");
                    }
                }); */
                
         var form=$("<form>");//定义一个form表单
         form.attr("style","display:none");
         form.attr("target","");
         form.attr("method","post");
         form.attr("action","toExecl");//跳转到servlet
         var input1=$("<input>");
         input1.attr("type","hidden");
         input1.attr("name","jsonData");
         input1.attr("value",jsonData);
         $("body").append(form);//将表单放置在web中
         form.append(input1);

         form.submit();//表单提交
    }
    
    //下载
    function downloadDoc(filePath,fileName){  
        var path = filePath+fileName;  
        var contextLength = "<%=request.getContextPath()%>";  
        var sp = path.substring(contextLength.length,path.length);  
        document.getElementById("path").value = sp;  
        document.getElementById("fileName").value = fileName;  
        //down_frame.location.href = path;decodeURI(path);encodeURIComponent  
        var sForm1 = document.form1;  
        sForm1.action = "<%=request.getContextPath()%>/com/icss/mdm/usermanual/servlet/StandardDocDownServlet";  
        sForm1.submit();  
         
    } 
</script>
</head>
<body>
    <div id="tableExcel">

        <table id="tab">
            <tr height="18" style="height: 13.5pt">
                <td height="18" width="72" style="height: 13.5pt; width: 54pt">姓名</td>
                <td width="72" style="width: 54pt">性别</td>
                <td width="72" style="width: 54pt">年龄</td>
            </tr>
            <tr height="18" style="height: 13.5pt">
                <td height="18" style="height: 13.5pt">杨xx</td>
                <td>男</td>
                <td align="right">27</td>
            </tr>
            <tr height="18" style="height: 13.5pt">
                <td height="18" style="height: 13.5pt">测试1</td>
                <td>女</td>
                <td align="right">18</td>
            </tr>
            <tr height="18" style="height: 13.5pt">
                <td height="18" style="height: 13.5pt">测试2</td>
                <td>女</td>
                <td align="right">18</td>
            </tr>
            <tr height="18" style="height: 13.5pt">
                <td height="18" style="height: 13.5pt">测试3</td>
                <td>女</td>
                <td align="right">18</td>
            </tr>
            <tr height="18" style="height: 13.5pt">
                <td height="18" style="height: 13.5pt">刘德华</td>
                <td>男</td>
                <td align="right">48</td>
            </tr>
        </table>
    </div>
    <a href="javascript:void(0)" onclick="toExecl()">导出execl</a>
    
    <a href="down.jsp">下载</a>
    
</body>
</html>

二、链接地址跳转到一个servlet

web.xml

<servlet>
      <servlet-name>toExecl</servlet-name>
      <servlet-class>com.mlxs.execl.servlet.ToExecl</servlet-class>
  </servlet>
  <servlet-mapping>
      <servlet-name>toExecl</servlet-name>
      <url-pattern>/toExecl</url-pattern>
  </servlet-mapping>

ToExeclServlet:

package com.mlxs.execl.servlet;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

public class ToExecl extends HttpServlet{

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        this.doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        resp.setContentType("multipart/form-data");
        resp.setHeader("Content-Disposition", "attachment;fileName=text.xls");
        //处理前台传的数据
        String dataStr = req.getParameter("jsonData");
        dataStr = dataStr.replace("[{", "").replace("}]", "").replace("{", "");
        String[] dataArr = dataStr.split("},");
        
        try {
            //1.创建一个workbook(对应Excel文件 )
            WritableWorkbook workbook = Workbook.createWorkbook(resp.getOutputStream());
            //2.在workbook中添加一个sheet(Excel文件中的sheet)
            WritableSheet sheet = workbook.createSheet("导出数据", 0);
            //3.创建表头单元格，并设置文字格式
            WritableFont wf = new WritableFont(WritableFont.ARIAL, 12, WritableFont.NO_BOLD);
            WritableCellFormat wcf = new WritableCellFormat(wf);
            wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
            wcf.setAlignment(Alignment.CENTRE);
            //4.设置行数
            sheet.setRowView(1, 100);
            
            //5.设置数据
            for (int i = 0; i < dataArr.length; i++) {
                //每一行的数据
                System.out.println("data:"+dataArr[i]);
                String[] str = dataArr[i].split(",");
                //每一列
                for (int j = 0; j < str.length; j++) {
                    if(i==0){
                        sheet.addCell(new Label(j, i, str[j], wcf));//new Label(列号, 行号, 数据, 单元格)
                        continue;
                    }
                    sheet.addCell(new Label(j, i, str[j]));
                }
                
            }
            //6.end
            workbook.write();
            workbook.close();
        } catch (RowsExceededException e) {
            e.printStackTrace();
        } catch (WriteException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
}

三、效果图：

posted @ 2016-07-04 16:50 艺言弈行阅读(2846) 评论(8) 编辑收藏举报

刷新页面返回顶部

Yzl1990

曲则全，枉则直。夫唯不争，故天下莫能与之争

jsp表格数据导出到Execl

一、写个页面index.jsp：

二、链接地址跳转到一个servlet

三、效果图：