利用jsp把数据导出到Excle【原创】

最新推荐文章于 2021-02-12 22:50:13 发布

最新推荐文章于 2021-02-12 22:50:13 发布 · 133 阅读

文章标签：

#JSP #Java #SQL #Excel #OS

servlet/jsp 专栏收录该内容

49 篇文章

订阅专栏

本文介绍了一种使用JSP页面实现Excel文件导出的方法。通过设置HTTP响应头和使用jxl库来创建并填充Excel工作簿，实现了从JSP页面导出带有数据的工作表的功能。

jsp页面里面只需要：

<%@ page language="java" import="java.util.*" pageEncoding="gb2312"%>
<%@ page import = "java.io.*"%>
<%@ page import = "Excel.*"%>

<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

<%
String fname = "excle标题";
OutputStream os = response.getOutputStream();//取得输出流
response.reset();//清空输出流

response.setCharacterEncoding("UTF-8");
fname = java.net.URLEncoder.encode(fname,"UTF-8");
response.setHeader("Content-Disposition","attachment; filename="+ new String(fname.getBytes("UTF-8"),"GBK") + ".xls");
response.setContentType("application/msexcel");//定义输出类型
ivyExcel sw = new ivyExcel();
sw.creatExcel(os);

<html>
<head>
    <base href="<%=basePath%>">

    <title>My JSP 'index.jsp' starting page</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">

</head>

<body>
    This is my JSP page. <br>
</body>
</html>

引用的那个导出类：

package Excel;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class ivyExcel {

public void creatExcel(OutputStream os) throws WriteException,IOException{

   WritableWorkbook workbook = Workbook.createWorkbook(os);//创建工作薄

    WritableSheet sheet = workbook.createSheet("First Sheet", 0);//创建新的一页
    String[] titles = {"title1","title2","ttle3","title4"};

    for (int i=0; i< titles.length;i++){//设置格式和把titile的内容填写上去
     WritableFont bold = new WritableFont(WritableFont.ARIAL , 10, WritableFont.BOLD);
     WritableCellFormat titleFormate = new WritableCellFormat(bold);
     titleFormate.setAlignment(jxl.format.Alignment.CENTRE);
     titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
     sheet.setColumnView(i, 18);
     Label title = new Label(i,0,titles[i],titleFormate);
     sheet.addCell(title);
    }

    try{
     Class.forName("com.mysql.jdbc.Driver");
     }
      catch(java.lang.ClassNotFoundException e)
     {
       System.err.print(e.getMessage());
     }
    String url="jdbc:mysql://IP?autoReconnect=true&useUnicode=true&characterEncoding=utf-8";
    try {
     Connection conn=DriverManager.getConnection(url,"root","mm");
     String sql1 = " ";
     String sql2 =

     String sql = sql2+sql1+")";

     System.out.println("sql :"+sql);

     PreparedStatement prpdStmt = conn.prepareStatement(sql);
     ResultSet rs = prpdStmt.executeQuery();//收集到要的数据了
     int k = 1;//开始设置字体等格式
     WritableFont nobold = new WritableFont(WritableFont.ARIAL , 9, WritableFont.NO_BOLD);
     WritableCellFormat cotentFormate = new WritableCellFormat(nobold);
     cotentFormate.setAlignment(jxl.format.Alignment.CENTRE);
     cotentFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
     while(rs.next()){
      for (int j =1 ; j<=titles.length;j++){
       Label labelC = new Label(j - 1, k, rs.getString(j),cotentFormate);
       sheet.addCell(labelC);
      }
      k++;//某一行完成了，过行继续
     }
     rs.close();
     prpdStmt.close();
     conn.close();
    } catch (SQLException e) {
     e.printStackTrace();
    }

    workbook.write();
    workbook.close();
    os.close();

}