j2ee : jxl操作Excel之导入

以 医院上报采购计划为例：

 

后台：

/**
  * 循环 往数据库中插采购
  * @throws SQLException
  * @throws NamingException
  */
 public int importExceltoDB(List<Stock> list) throws NamingException, SQLException {
  
  int countNum =0; //计数器
  
  //循环list  插入数据库
  for (int i = 0; i < list.size(); i++) {
   System.out.println("插 ：   "+ list.get(i).getLeechdomId() +"    "+ list.get(i).getHospitalId() +"  "+ " "+list.get(i).getNumber() +"   "+ list.get(i).getType());
      countNum  += this.addStock(list.get(i));
  }
  return countNum;
 }

业务：

/**
  * 导入采购药品 之 读取Excel
  * @throws SQLException
  * @throws NamingException
  */
 public List<Stock> readExcel(String fileName,int hpId)  {
  
  List<Stock> list = new ArrayList<Stock>();
  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
  
  Workbook   wb = null;
  
  try {
   
   //从输入流中构建Workbook对象
   InputStream is = new FileInputStream(fileName);
   
   wb = Workbook.getWorkbook(is);
   
   //获取第一张工作表
   Sheet sheet = wb.getSheet(0);
   //获取Sheet 表中总列数
   int columns = sheet.getColumns();
   //获取sheet表中总行数
   int rows = sheet.getRows();
   
   for (int i = 0; i < rows; i++) {
    Stock stock = new Stock();  //为每一行 做一个上报的
    for (int j = 0; j < columns; j++) {
     
     
     Cell cell = sheet.getCell(j,i);
     
     if(i>0 && j==0){
      //这是第一列编号
      stock.setLeechdomId(Integer.parseInt(cell.getContents())); //增加进药品编码
     }
     
     if(i>0 && j==11){
      //这是填写的数量了
      //cell.getContents(); //这是内容
      //如果说 填写的数量不是空的话
      if(null != cell.getContents() && !"".equals(cell.getContents())){
       int number = Integer.parseInt(cell.getContents()); //采购数量
       //采购数量必须要大于0
       if(number >0){
        stock.setNumber(number); //增加近采购数量
        stock.setHospitalId(hpId); //增加进医院编号
        stock.setDate(sdf.format(new Date())); //增加进当前时间
        stock.setType(1);  //增加进类型 备用的。
        list.add(stock);
       }
      }
     }
    }
    
   }
  } catch (FileNotFoundException e) {
   e.printStackTrace();
  } catch (BiffException e) {
   e.printStackTrace();
  } catch (IOException e) {
   e.printStackTrace();
  }finally{
   //关了她
   wb.close();
  }
  return list;
 }

 /**
  * 导入 excel进入数据库
  */
 public int importExceltoDB(String fileName,int hpId) throws NamingException,
   SQLException {
  List<Stock> list = this.readExcel(fileName, hpId);
  
  return sDao.importExceltoDB(list);
 }

 

Action ：

/**
  * 去 在线离线填报
  *
  * @param mapping
  * @param form
  * @param request
  * @param response
  * @return
  */
 public ActionForward offLineGather(ActionMapping mapping, ActionForm form,
   HttpServletRequest request, HttpServletResponse response) {
  try {
   String now = sdf.format(new Date()); // 当前时间

   Sundry els = eBiz.findElse();

   HttpSession session = request.getSession();
   Hospital hp = (Hospital) session.getAttribute("hp");

   // 会话验证
   isValiSession = this.sessionControl(request, response); // 调用会话控制

   if (isValiSession) {
    // 采购时间验证。。。。
    if (sdf.parse(now).compareTo(sdf.parse(hp.getEndTime())) < 0) { // 当前时间小于到期时间
     // 报价开始时间 小于当前并且 结束时间大于当前
     if ((sdf.parse(els.getHStartDate()).compareTo(
       sdf.parse(now)) < 0)
       && (sdf.parse(now).compareTo(
         sdf.parse(els.getHEndDate())) < 0)) {
      request.getRequestDispatcher("WEB-INF/z/h/offLine.jsp")
        .forward(request, response);
      return null;
     } else {
      request.setAttribute("error", " 填报时间是：<br>  "
        + els.getHStartDate().substring(0, 19)
        + " <br> &nbsp;&nbsp;&nbsp;&nbsp; → →  <br> "
        + els.getHEndDate().substring(0, 19));
     }
    } else {
     request.setAttribute("error", "您的账户已到期,请联系管理员");
    }
   }
  } catch (Exception e) {
   request.setAttribute("error", e.getMessage());
  }
  return mapping.findForward("error");
 }

 /**
  * 导入excel 进数据库 先上传到指定位置 再导入 再插入 在返回 就OK了
  *
  * @param mapping
  * @param form
  * @param request
  * @param response
  * @return
  */
 public ActionForward importExcel(ActionMapping mapping, ActionForm form,
   HttpServletRequest request, HttpServletResponse response) {

  try {
   HttpSession session = request.getSession();
   Hospital hp = (Hospital) session.getAttribute("hp");

   DynaActionForm daf = (DynaActionForm) form;

   FormFile file = (FormFile) daf.get("filePath");
   if (null != file) {

    FileOutputStream fos = new FileOutputStream("E:\\"
      + file.getFileName());

    fos.write(file.getFileData());
    fos.flush();
    fos.close();
   }
   // PrintWriter out = response.getWriter();
   // out.print("導入數據成功，正在為您寫入數據庫。。。。");

   int resultNum = sBiz.importExceltoDB("E:\\" + file.getFileName(),
     hp.getId());
   if (resultNum > 0) {
    request.setAttribute("suss", "为您导入了" + resultNum + "条数据");
    request
      .setAttribute("toUrl",
        "hp.dc?operator=toGratherHistory");
    return mapping.findForward("suss");
   } else {
    request.setAttribute("error", "没有写入数据,写入值:" + resultNum);
   }

  } catch (FileNotFoundException e) {
   e.printStackTrace();
   request.setAttribute("error", e.getMessage());
  } catch (IOException e) {
   e.printStackTrace();
   request.setAttribute("error", e.getMessage());
  } catch (Exception e) {
   e.printStackTrace();
   request.setAttribute("error", e.getMessage());
  }

  return mapping.findForward("error");
 }

页面：

<%@ 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: 477978220
title: j2ee : jxl操作Excel之导入
publishTime: 1252636722516
classId: 88735822
moveForm: NONE