j2ee：jxl 操作Excel之导出(Export)

后台：

 package com.dong.dao.impl;

import java.io.IOException;
import java.io.OutputStream;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import javax.naming.NamingException;

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;
import jxl.write.biff.RowsExceededException;

import com.dong.dao.MadeExcelDAO;

public class MadeExcelDAOImpl extends CurrencyDAO implements MadeExcelDAO {

 /**
  * 根据 sql 生成excel
  */
 public void madeExcel(OutputStream os, String sql,String[] titles) {
  Statement stmt = null;
  try {
   conn = this.getConnection();
    stmt = conn.createStatement();
   rs = stmt.executeQuery(sql);
   
   //得到数据结果集合
   ResultSetMetaData rmeta = rs.getMetaData();
   //数据总的行数
   int numColumns = rmeta.getColumnCount();
   
   
   //开始往下 写
    WritableWorkbook wwb = Workbook.createWorkbook(os);
    WritableSheet ws  = wwb.createSheet("Sheet", 0);
    
    Label labelC;

    WritableFont wf = new WritableFont(WritableFont.ARIAL,10,WritableFont.BOLD);
    WritableCellFormat wcf = new WritableCellFormat(wf);
    
    //String [] args = {"标题"," 标再提","标再在题"};
    for(int i = 0;i<titles.length;i++){
     labelC = new Label(i,0,titles[i],wcf);
     ws.addCell(labelC);
    }
    
    //从第一行 开始写数据库中读的数据
    int j= 1;
    
    while(rs.next()){
     for (int i = 0; i < numColumns; i++) {
      labelC = new Label(i,j,rs.getString(i+1));
     
       ws.addCell(labelC);
     }
     j++;
    }
    wwb.write();
    wwb.close();
   
  } catch (RowsExceededException e) {
   e.printStackTrace();
  } catch (WriteException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  }catch (NamingException e) {
   e.printStackTrace();
  } catch (SQLException e) {
   e.printStackTrace();
  }finally{
   this.closeAll(rs, stmt, conn);
   
  }
 }

}

经过业务层，到了Action：

package com.dong.web.action;

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URL;

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

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.actions.DispatchAction;

import com.dong.biz.MadeExcelBiz;
import com.dong.biz.SundryBiz;
import com.dong.biz.impl.MadeExcelBizImpl;
import com.dong.biz.impl.SundryBizImpl;
import com.dong.entity.Sundry;

/**
 * 专门用于导入导出Excel
 * @author dongAdmin
 *
 */
public class ExcelAction extends DispatchAction {
 
 MadeExcelBiz meBiz = new MadeExcelBizImpl(); // 关于excel问题
 SundryBiz sunBiz = new SundryBizImpl(); // 杂项参数 业务

 
 // 导出excel
 public ActionForward exportExcel(ActionMapping mapping, ActionForm form,
   HttpServletRequest request, HttpServletResponse response) {
  String type = request.getParameter("eid"); // 要导出的内容
  String sTime = request.getParameter("sTime");
  String eTime = request.getParameter("eTime");
  
  try {
   // 参数非空验证
   if (null != type && !"".equals(type)) {
    
    Sundry sundry = sunBiz.findElse();
    if( (null == sTime || "".equals(sTime)) && (null == eTime || "".equals(eTime))){
     sTime = sundry.getHStartDate();
    }
    
    String sql = "";
    String title = ""; //这是导出文件的名字
    String[] titles =new  String[20]; //这是第一行的数组
    File f = new File("E://ee.xls"); // 目录
    System.out.println("创建文件  " + f.createNewFile());

    // 导出药品目录
    if ("lee".equals(type)) {
     sql = "select * from leechdom order by id";
     title = " 药品目录";
     titles[0] = new String("药品编号");
     titles[1] = new String("药品名称");
     titles[2] = new String("药品规格");
     titles[3] = new String("中标价");
     titles[4] = new String("单位");
     titles[5] = new String("生产厂家");
     titles[6] = new String("简码");
     titles[7] = new String("新农合(1:是;0:否)");
     titles[8] = new String("商品名");
     titles[9] = new String("剂型");
     titles[10] = new String("质量层次");
     titles[11] = new String("填写采购数量");
     
    }else if("collect".equals(type)){
     sql = "select v.id,v.leechdomId,lee.name,lee.spec,lee.unit,lee.madeFactory,lee.farmerType,v.allNum from v_bid as v inner join leechdom as lee on v.leechdomId=lee.id order by v.leechdomId";
     title = "采购汇总";
     titles[0] = new String("数据标号");
     titles[1] = new String("药品编码");
     titles[2] = new String("药品名称");
     titles[3] = new String("药品规格");
     titles[4] = new String("单位");
     titles[5] = new String("生产厂家");
     titles[6] = new String("新农合(1:是;0:否)");
     titles[7] = new String("采购数量");
     titles[8] = new String("填报价格");
     
    }else if("allBid".equals(type)){
     sql="select q.leechdomId,q.name,q.spec,q.lastPrice,q.unit,q.madeFactory,q.farmerType,q.price,s.name,q.date from (select v.leechdomId,lee.name,lee.spec,lee.lastPrice,lee.unit,lee.madeFactory,lee.farmerType,v.price,v.supplyId,v.date from v_findQuote as v inner join leechdom as lee on v.leechdomId = lee.id where date>'"+sTime+"' ) as q inner join supply as s on q.supplyId=s.id order by q.leechdomId";
     title = "供货商报价";
     titles[0] = new String("药品编号");
     titles[1] = new String("药品名称");
     titles[2] = new String("药品规格");
     titles[3] = new String("中标价");
     titles[4] = new String("单位");
     titles[5] = new String("生产厂家");
     
     titles[6] = new String("新农合(1:是;0:否)");
     
     titles[7] = new String("报价金额");
     titles[8] = new String("供货商");
     titles[9] = new String("时间");
     
    }else if("allGather".equals(type)){
     sql ="select g.leechdomId,g.name,g.spec,g.lastPrice,g.unit,g.madeFactory,g.farmerType,g.number,h.name,g.date from (select s.leechdomId,lee.name,lee.spec,lee.lastPrice,lee.unit,lee.madeFactory,lee.farmerType,s.number,s.date,s.hospitalId from stock  as s inner join leechdom as lee on s.leechdomId=lee.id where date>'"+sTime+"')as g inner join hospital as h on g.hospitalId=h.id order by g.leechdomId";
     title = "医院采购";
     titles[0] = new String("药品编号");
     titles[1] = new String("药品名称");
     titles[2] = new String("药品规格");
     titles[3] = new String("中标价");
     titles[4] = new String("单位");
     titles[5] = new String("生产厂家");
     titles[6] = new String("新农合(1:是;0:否)");
     titles[7] = new String("采购数量");
     titles[8] = new String("医院");
     titles[9] = new String("时间");
    }
    meBiz.madeExcel(new FileOutputStream(f), sql, titles);
    System.out.println("写 excel  成功。。。。。");

    response.reset();
    // 下载
    OutputStream os = response.getOutputStream();
    
    //WritableWorkbook wwb = Workbook.createWorkbook(os);
    
    response.setContentType("application/vnd.ms-excel");
    response.addHeader("Content-Disposition", "inline; filename="
      + new String(title.getBytes("GB2312"), "iso-8859-1")
      + ".xls");
    
    
    URL url = new URL(f.toURL(), "ee.xls");
    InputStream is =  url.openConnection().getInputStream();   //读取流
    byte[] b = new byte[10*1024];  //定义b用于承载流
    int i = is.read(b);
    
    String str = "";
    while(i != -1){
     os.write(b, 0, i);
     str += new String(b,0,i);
     i = is.read(b);
    }
    os.flush();
    os.close();
    
    System.out.println("删除文件  " + f.delete());
    return null;

   } else {
    request.setAttribute("error", "参数错误");
   }
  } catch (Exception e) {
   request.setAttribute("error", e.getMessage());
  }
  return null;
 }
}

页面：

<%@ page language="java" pageEncoding="GBK"%>
<%
 String path = request.getContextPath();
 String basePath = request.getScheme() + "://"
   + request.getServerName() + ":" + request.getServerPort()
   + path + "/";
%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
 <head>
  <base href="<%=basePath%>">

  <title>离线填报</title>
  <script language="javascript" src="images/leechdom.js"></script>
  <script type="text/javascript">
     function changeImport(){
    var divIm = document.getElementById("divImport");
    
    if(divIm.style.display == "none"){
     document.getElementById("divImport").style.display="block";
    }else{
     document.getElementById("divImport").style.display="none";
    }
   }
   
  function checkFile(){
     var str = document.frmImport.filePath;
     if(str.value == ""){
      alert("不能为空");
     }else{
   var pos = str.value.lastIndexOf("."); 
   var lastName = str.value.substring(pos,str.length);
   if(lastName.toLowerCase() == ".xls"){
    return true;
   }else{
    alert("文件必须为.xls类型");
   }
   
  }
  return false;
  }
    </script>
  <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>
  <br>
  <br>
  <br>
  <br>
  <div align="center">
   <form name="frmExport" action="ex.dc?operator=exportExcel"
    method="post" target="_blank">
    <input type="hidden" name="eid" id="eid" value="lee">
    <input type="submit" name="Submit3" value="导出EXCEL">
   </form>
   <br>
   <br>
   <form name="frmImport" action="hpim.dc?operator=importExcel"
    method="post" enctype="multipart/form-data"
    onsubmit="return checkFile();">

    <input type="button" name="Submit3" value="导入EXCEL"
     onclick="changeImport();">
    <br>
    <br>
    <div id="divImport" style="display: none">
     <input type="file" name="filePath"">
     <input type="submit" value="上传">
    </div>
   </form>
   <font size=-1 color=red> 注意：导出EXCEL时请使用普通保存，请勿使用下载工具下载！！！<br>
    导出文档后必须在指定位置填写数据，对于恶意删改及上传者，给予封号处理。 </font>
  </div>
 </body>
</html>

id: 471072922
title: j2ee：jxl 操作Excel之导出(Export)
publishTime: 1251358312284
classId: 88735822
moveForm: NONE