java中用poi读取Excel文件

 /**
 * 物资清单导入
 */

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

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

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.upload.FormFile;

import com.spsoft.framework.dao.ibatis.generator.db.StringUtil;
import com.spsoft.framework.security.AuthenticatorHolder;
import com.spsoft.framework.struts.BaseAction;
import com.spsoft.framework.util.IDUtil;
import com.spsoft.global.service.Services;
import com.spsoft.prophase.materialserve.domain.Materialmuster;
import com.spsoft.prophase.materialserve.service.MaterialService;
import com.spsoft.prophase.materialserve.web.forms.MaterialmusterForm;
/**
 * @author gjy
 *
 */
public class ImportMaterExcelAction extends BaseAction {

 /* (non-Javadoc)
  * @see com.spsoft.framework.struts.BaseAction#perform(org.apache.struts.action.ActionMapping, org.apache.struts.action.ActionForm, javax.servlet.http.HttpServletRequest, javax.servlet.http.HttpServletResponse)
  */
 public ActionForward perform(ActionMapping mapping, ActionForm form,
   HttpServletRequest request, HttpServletResponse response) {
  // TODO Auto-generated method stub
  MaterialmusterForm materialmusterForm=(MaterialmusterForm)form;  
  MaterialService materService = (MaterialService) Services
  .GetService(MaterialService.SERVICE_NAME);   
  //
  String projId=materialmusterForm.getProjid();
  String projType=materialmusterForm.getProjtype();  
  FormFile excelFile=materialmusterForm.getExcelFile();   //获得页面提交过来的Excel文件
  //保存异常信息的list
  List errorList = new ArrayList();
  try
  {
   InputStream fileStream=excelFile.getInputStream();
   POIFSFileSystem poiFs = new POIFSFileSystem(fileStream);
   HSSFWorkbook wb = new HSSFWorkbook(poiFs);

 //根据excel下标名称获得对应的sheet.也可以wb.getSheetAt("0");就是取Excel中的第一个sheet
   HSSFSheet sheet = wb.getSheet("物资清单");     

   int lastRowNum = sheet.getLastRowNum();    //获得总共有多少行数据
   for (int i = 2; i <= lastRowNum; i++)    //从第二行开始读取数据
   {
    try
    {
     Materialmuster materialmuster=new Materialmuster();
     //主键
     Long id = new Long(IDUtil.getNextLongID());     
     materialmuster.setMaterialmusterid(id);
     materialmuster.setProjid(Long.decode(projId));
     materialmuster.setProjtype(projType);
     //用户所属单位id--需用单位
     Long corpId=AuthenticatorHolder.getClientSession().getUser().getCorpID();
     materialmuster.setNeedunit(corpId);     
     //默认值
     materialmuster.setEquipmentcome("0");
     materialmuster.setState("0");
     HSSFRow row=sheet.getRow(i);
     short num=row.getLastCellNum();
     String name=null;
     String no=null;
     for(short k=0;k<num;k++)
     {
      HSSFCell cell = row.getCell(k);        //获得单元格   
       if(k==0)
       {
        String temp=this.checkCellType(cell).trim();      //checkCellType()方法中是对几个基本类型的处理
        temp=StringUtil.formatStrForXML(temp);           //StringUtil.formatStrForXML()项目中方法,去掉特殊字符
        if(temp==null||temp.equals("")){
         no=temp;
         break;          
//         throw new Exception("物资编码不能为空");         
        }
        if(temp!=null&&!temp.equals(""))
        {
         materialmuster.setMaterialno(temp);         
        }        
       }else if(k==1){
        String temp=this.checkCellType(cell).trim();
        temp=StringUtil.formatStrForXML(temp);
//        if(temp==null||temp.equals(""))
//         throw new Exception("物资名称不能为空");
        name=temp;
        if(temp!=null&&!temp.equals(""))
        {
         materialmuster.setMaterialname(temp);         
        }        
       }else if(k==2){
        String temp=this.checkCellType(cell).trim();
        temp=StringUtil.formatStrForXML(temp);
//        if(temp==null||temp.equals(""))
//         throw new Exception("规格及型号名称不能为空");        
        if(temp!=null&&!temp.equals(""))
        {
         materialmuster.setTypeandspecname(temp);         
        }
         
       }else if(k==3){
        String temp=this.checkCellType(cell).trim();
        temp=StringUtil.formatStrForXML(temp);
//        if(temp==null||temp.equals(""))
//         throw new Exception("计量单位不能为空");
        
        if(temp!=null&&!temp.equals(""))
        {
         materialmuster.setUnit(temp);         
        }        
       }else if(k==4){
        String temp=this.checkCellType(cell).trim();
        temp=StringUtil.formatStrForXML(temp);
//        if(temp==null||temp.equals(""))
//         throw new Exception("型式不能为空");        
        if(temp!=null&&!temp.equals(""))
        {
         materialmuster.setMaterialxs(temp);         
        }        
       }else if(k==5){
        String temp=this.checkCellType(cell).trim();
        temp=StringUtil.formatStrForXML(temp);
        
//        if(temp==null||temp.equals(""))
//         throw new Exception("电压等级不能为空");
        String vol=null;
        if(temp!=null&&!temp.equals(""))
        {
         temp=temp.toUpperCase();
         if (temp.equals("500KV"))
         {
          vol="1";
         }
         else if (temp.equals("220KV"))
         {
          vol="2";
         }
         else if (temp.equals("110KV"))
         {
          vol="3";
         }
         else if (temp.equals("35KV"))
         {
          vol="4";
         }
         else if (temp.equals("20KV"))
         {
          vol="5";
         }else if (temp.equals("10KV"))
         {
          vol="6";
         }else if (temp.equals("0.4KV"))
         {
          vol="7";
         }else if (temp.equals("0.22KV"))
         {
          vol="8";
         }                             
        }else{
         vol="0";
        }       
        if(vol!=null&&!vol.equals("")){
         materialmuster.setVoltagegrade(vol);
        }       
       }else if(k==6){//备注
        String temp=this.checkCellType(cell).trim();
        temp=StringUtil.formatStrForXML(temp);
//        String temp=this.checkCellType(cell).trim();
//        if(temp==null||temp.equals(""))
//          throw new Exception("备注不能为空");
        if(temp!=null&&!temp.equals(""))
        {
         materialmuster.setRemark(temp);
        }        
       }else if(k==7){
        String temp=this.checkCellType(cell).trim();
        temp=StringUtil.formatStrForXML(temp);
//        if(temp==null||temp.equals(""))
//         throw new Exception("需用技术参数不能为空");        
        if(temp!=null&&!temp.equals(""))
        {
         materialmuster.setNeedtechnicparam(temp);         
        }        
       }else if(k==8){       
        String temp=this.checkCellType(cell).trim();
        temp=StringUtil.formatStrForXML(temp);
        if(temp==null||temp.equals(""))
         throw new Exception("需用数量不能为空");
        
        if(temp!=null&&!temp.equals(""))
        {
          materialmuster.setNeedamount(Double.valueOf(temp));        
        }    
       }else if(k==9){       
        Date temp=null;
        if (cell != null)
        {
          int  cellType =cell.getCellType();   
       //此处对日期格式进行了处理,没整理放到checkCellType()中,可以自己整理下

                if(cellType==cell.CELL_TYPE_NUMERIC){  
                    if(HSSFDateUtil.isCellDateFormatted(cell)){     

                     temp   =   cell.getDateCellValue();               
                    }else{  
                     throw new Exception("交货期格式不正确");  
                    }  
                }else{
                 throw new Exception("交货期格式不正确");                
                }             
        }
        
           if(temp!=null&&!temp.equals(""))
           {      
         materialmuster.setAskconsigndate(temp);         
           }
       }else if(k==10){//要求交货地点      
        String temp=this.checkCellType(cell).trim();
        temp=StringUtil.formatStrForXML(temp);
//        if(temp==null||temp.equals(""))
//         throw new Exception("交货地点不能为空");       
        if(temp!=null&&!temp.equals(""))
        {
         materialmuster.setAskconsignaddr(temp);
        }  
       }
     }
     if(name!=null&&!name.equals(""))
     {
//      if (!materService.checkName(materialmuster))
//      {
//       throw new Exception("已有重复记录！");
//      }
//      else
//      {       
       materService.insertMaterialmuster(materialmuster);        //存入数据库
//      } 
     } 
    }catch(Exception ex)
    {
     int line = i + 1;
     errorList.add("第" + line + "行导入失败: " + ex.getMessage());
    } 
   } 
  }catch(FileNotFoundException e)
  {
   errorList.add("未找到文件！");
   e.printStackTrace();
  }
  catch (IOException e)
  {
   errorList.add("读取文件失败！");
   e.printStackTrace();
  }
  catch (Exception e)
  {
   errorList.add("未知错误！");
   e.printStackTrace();
  }
  
  materialmusterForm.setFailList(errorList);
  if (errorList.size() == 0)
   return mapping.findForward("success");
  else
   return mapping.findForward("failExcel");
 }
 //此方法对单元格类型进行处理
 public String checkCellType(HSSFCell cell)
 {
  String value = "";
  if (cell != null)
  {
   if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)       //数值类型,日期类型在上面,可以在这处理
   {
    value = String.valueOf(cell.getNumericCellValue());
   }
   else if(cell.getCellType()==HSSFCell.CELL_TYPE_STRING)    //字符串类型
   {
    value = cell.getStringCellValue();
   }
   else if(cell.getCellType()==HSSFCell.CELL_TYPE_FORMULA)  //有公式的单元格
   {

//cell.getNumericCellValue()获得公式最终的值,若要读出公式内容，可用cell.getCellFormula() 
    value=String.valueOf(cell.getNumericCellValue());   
   }
   else
   {
    value="";
   }
   return value;
  }
  else
  {
   return "";
  }
 }

}

********下面是点击"上传"按钮调用的js方法,也许对大家有帮助*************

function SubmitToAction()
{
     var zp = document.forms[0].excelFile.value;
  if(zp== "")
  {
   alert( "请选择EXECL文件" );
   document.forms[0].excelFile.focus();
   return false;
  }
  var filename = zp.substr(zp.lastIndexOf("."));
  if (filename != ".xls")
  {
   alert("上传文件类型必须为.XLS");
   return false;
  }
  document.forms[0].submit();
  document.forms[0].okButton.disabled=true;
}