jsp页面导出用jxl方式导出数据到excel表格并下载保存(第二种直接用java类实现)

时间:2022-12-17 22:13:53

在做通过jsp页面动态取数据到excel表格,点击按钮下载保存功能,通过网上资源学习,自己用了简单的方式实现了功能。

保存数据到excel表格可以用POI以及jxl,我用的是jxl实现的。

1.下载jxl.rar包,复制后粘贴到工程lib目录下,会自动引入到Referenced Libraries外部包目录。

2.建一个点击按钮页面excelExportTest.jsp,这个很简单。

    <%@ page language="java" import="java.util.*" pageEncoding="GBK"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
  <head>
    <title>导出excel表格</title>
<script type="text/javascript">
   function hpExport(obj){
      obj.href = "excelExport.jsp";
    return true;
  }
</script>
  </head>
  <body>
    <a href="#" id="hpExport" onClick="return hpExport(this)">导出数据</a>
  </body>
</html>

3.新建导出excel表格操作页面excelExport.jsp

<%@ page language="java" import="java.util.*,java.io.*,jxl.write.WritableWorkbook,
jxl.Workbook,jxl.write.WritableSheet,jxl.write.Label,com.bestnet.dao.HotDao,com.bestnet.dao.HotVo" pageEncoding="GBK"%>
<%
try{
String city=request.getParameter("city");
String grade=request.getParameter("grade");

response.setContentType("application/vnd.ms-excel");  //保证不乱码

String fileName=city+"excel表格数据.xls";

//response.addHeader("Content-Disposition","attachment; filename="+city+excel表格数据.xls","ISO-8859-1");
response.setHeader("Content-Disposition", "attachment;"
+ " filename="
+ new String(fileName.getBytes(), "ISO-8859-1"));

OutputStream os = response.getOutputStream();
out.clear();
out = pageContext.pushBody();    //这2句一定要,不然会报错。

WritableWorkbook wwb = Workbook.createWorkbook(os);

WritableSheet ws = wwb.createSheet(city,0);

HotDao hotdao=new HotDao();
List hotlist=new ArrayList();

if(Integer.parseInt(grade)==2){
hotlist=hotdao.list();
}else if(Integer.parseInt(grade)==3){
hotlist=hotdao.citylist(city);
}else if(Integer.parseInt(grade)==4){
hotlist=hotdao.subcitylist(city);
}


Label label = new Label(0,0,"热点名称");
ws.addCell(label);
label = new Label(1,0,"热点地址");
ws.addCell(label);
label = new Label(2,0,"覆盖范围");
ws.addCell(label);

for(int i=0;i<hotlist.size();i++){
HotVo hotvo= (HotVo) hotlist.get(i);
label = new Label(0,(i+1),hotvo.getHOTNAME());
ws.addCell(label);
label = new Label(1,(i+1),hotvo.getADDRESS());
ws.addCell(label);
label = new Label(2,(i+1),hotvo.getAREA());
ws.addCell(label);
System.out.println(hotvo.getHOTNAME()+"+++++++++++++++++++++++++++++++++++++++++++++");
}

wwb.write();
wwb.close();
os.close();

}catch(Exception e){
System.out.println("生成信息表(Excel格式)时出错:");
    e.printStackTrace();
}jsp页面导出用jxl方式导出数据到excel表格并下载保存(第二种直接用java类实现)

%>








二。

单独使用java类实现:

package common;


import java.io.*;


import jxl.Workbook;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;


public class ExcelExport {
public static void excelExport(OutputStream os){
try{
//WritableWorkbook wwb=Workbook.createWorkbook(new File(targetfile));

WritableWorkbook wwb = Workbook.createWorkbook(os);

WritableSheet ws = wwb.createSheet("第一页",0);

Label label = new Label(0,0,"会员姓名");
ws.addCell(label);
label = new Label(1,0,"卡号");
ws.addCell(label);
label = new Label(2,0,"联系地址");
ws.addCell(label);
label = new Label(3,0,"邮编");
ws.addCell(label);
label = new Label(4,0,"联系电话");
ws.addCell(label);

wwb.write();
wwb.close();



// WritableFont wf = new WritableFont(WritableFont.TIMES,18,WritableFont.BOLD,true);
//    WritableCellFormat wcf = new WritableCellFormat(wf);
// Label labelcf = new Label(1,0,"this is a label test",wcf);
// ws.addCell(labelcf);
//  
// WritableFont wfc = new WritableFont(WritableFont.ARIAL,10,WritableFont.NO_BOLD,false,
//  UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.RED);
//    WritableCellFormat wcfFC = new WritableCellFormat(wfc);
//        Label labelCF = new Label(1,0,"This is a Label Cell",wcfFC);
//    ws.addCell(labelCF);
//    
//    Number labelN = new Number(0,1,3.1415926);
//    ws.addCell(labelN);






}catch(Exception e){
System.out.println("生成信息表(Excel格式)时出错:");
   e.printStackTrace();


}


}


public static void main(String[] args)
 {
 try
 {

 File fileWrite = new File("D:/testWrite.xls");
 fileWrite.createNewFile();
 OutputStream os = new FileOutputStream(fileWrite);
 ExcelExport.excelExport(os);


 }
 catch(Exception e)
 {
 e.printStackTrace();
 }


 }
}