`

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

JSPJavaSQLExcelOS 
阅读更多

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">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
</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&amp;useUnicode=true&amp;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();
  
}

}

 

3
顶
1
踩
分享到：
| html table td边框效果 【转】
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics