jfinal上传excel文件

前台

checkData函数来检查input类型是否为xls或者xlsx类型的文件。

<div class="fl info-item clearfix">
		<label class="label">浏览：</label>
          <input id="FileUpload1" type="file" style="width:350px" name="FileUpload1"/>
  </div>

function checkData(){  
	var fileDir = $("#FileUpload1").val();  
	var suffix = fileDir.substr(fileDir.lastIndexOf("."));  
	if(".xls" != suffix && ".xlsx" != suffix){ 
		alert("请选中xls或xlsx类型文档！");  
	    return false;  
    }  
   return true;  
}  

通过ajaxsubmit函数将文件传到后台，success回调函数判断返回值，因为我写的controller返回值为文本，因此可以直接与responseTest作比较判断。

$("#upTable").ajaxSubmit({ 
	url:"sdsdb/saveFile",
	dataType: 'text',  
    success:succ
}); 
function succ(responseTest){
	if("no" == responseTest){
		alert("导入失败，请将工号输入完整！");
		$("#mask_daoru").hide();
    	window.location.reload();
	}else{
		$("#success-cg").show();
	}
}

jfinal通过UploadFile来获取文件对象，并且UploadFile需放在第一行，获取到文件后，执行service方法里的importExcel方法。

public void saveFile() throws FileNotFoundException {
	UploadFile uploadFile=this.getFile(); 
    String fileName=uploadFile.getOriginalFileName();
    String hz = fileName.substring(fileName.lastIndexOf(".")+1);//文件后缀
    File file=uploadFile.getFile();    
    BufferedInputStream fis = new BufferedInputStream(new FileInputStream(file));
    String str = "";
    if("xls".equals(hz)){
    	str = dbService.importExcel(fis);	   
    }else{
    	str = dbxlsxService.importExcel(ssyf, fis);   
    }   
    if("no".equals(str)){
    	 renderText("no");
    	 return;
     }else{
    	 renderText("ok");
    	 return;
     }		
   }

importExcel方法的实现，需要引入poi的jar包。

import java.io.BufferedInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
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.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Model;
import com.jfinal.plugin.activerecord.Record;
import com.nbgl.common.model.T_COM_GRSD;
import com.nbgl.common.model.T_COM_PERSON;
public class DbService {
	public static final DbService me = new DbService();
	
	private T_COM_GRSD dao = new T_COM_GRSD().dao();
	
	public String importExcel(BufferedInputStream bis){
  		Object[] insertvalue = new Object[48];
  		int lastRowNum = 0;
  		int physicalRowNum = 0;
  		String ghflag = "ok";	
  		HSSFSheet st = null;
  		POIFSFileSystem fs = null;
  		HSSFWorkbook wb = null;
  		try {
  			fs = new POIFSFileSystem(bis);   //对文件进行解析
  			wb = new HSSFWorkbook(fs);   //创建一个新的excel
  			st = wb.getSheetAt(0);   //创建一个新的sheet页
  		} catch (IOException e) {
  			e.printStackTrace();
  		}
  		
  		lastRowNum = st.getLastRowNum();// getLastRowNum方法能够正确返回最后一行的位置
  		physicalRowNum = st.getPhysicalNumberOfRows();// getPhysicalNumberOfRows方法能够正确返回物理的行数
  		System.out.println("lastRowNum=" + lastRowNum + " physicalRowNum="
  				+ physicalRowNum);
  		
  		for (int r = 1; r <= st.getLastRowNum(); r++) {
  			HSSFRow row = st.getRow(r);  //获取一行
  			if (row != null) {//这里只判断行是不是空的额，没办法判断单元格的空串
  				String adj = notNeed(insertvalue, row,r-1);
  				if(adj == "no"){
  					ghflag = "no";
  					break;
  				}
  			}
  		}

  		return ghflag;
	}
	
	private String notNeed(Object[] insertvalue, HSSFRow row,int order) {
		HSSFCell cell0 = row.getCell(0);
	    for (int i=0;i<10;i++){
	    	HSSFCell cell1 = row.getCell(i);	
	    	cell1.setCellType(CellType.STRING);
	    	insertvalue[i] = (cell1 !=null && cell1.getStringCellValue() != null && !cell1.getStringCellValue().equals(""))?cell1.getStringCellValue():"";
		}
	 
		String grgh  = (insertvalue[0]+"").trim() ==  null ? "" : (insertvalue[0]+"").trim() ;
		String grname   = (insertvalue[1]+"").trim() ==  null ? "" : (insertvalue[1]+"").trim() ;
		String zzlx   = (insertvalue[2]+"").trim() ==  null ? "" : (insertvalue[2]+"").trim() ;
		String zzhm   = (insertvalue[3]+"").trim() ==  null ? "" : (insertvalue[3]+"").trim() ;
		String skssqq   = (insertvalue[4]+"").trim() ==  null ? "" : (insertvalue[4]+"").trim() ;
		String skssqz   = (insertvalue[5]+"").trim() ==  null ? "" : (insertvalue[5]+"").trim() ;
		String sdxm    = (insertvalue[6]+"").trim() ==  null ? "" : (insertvalue[6]+"").trim() ;
		String bqsr    = (insertvalue[7]+"").trim() ==  null ? "" : (insertvalue[7]+"").trim() ;
		String bqfy    = (insertvalue[8]+"").trim() ==  null ? "" : (insertvalue[8]+"").trim() ;
		String bqmssr    = (insertvalue[9]+"").trim() ==  null ? "" : (insertvalue[9]+"").trim() ;
	
        if(grgh==null || "".equals(grgh)){
        	String sqlyf = "delete from T_COM_GRSD where ssyf='" + yf +"'";
    		Db.update(sqlyf);
    		return "no";
        }		   
			Record record = new Record();
			record.set("SDS_ID", "T_COM_GRSD_SEQ.nextval");
			record.set("GRGH", grgh);
			record.set("grname", grname);
			record.set("zzlx", zzlx);
			record.set("zzhm", zzhm);
			record.set("skssqq", skssqq);
			record.set("skssqz", skssqz);
			record.set("sdxm", sdxm);
			record.set("bqsr", Double.parseDouble(bqsr));
			record.set("bqfy", Double.parseDouble(bqfy));
			record.set("bqmssr", Double.parseDouble(bqmssr));
		    Db.save("T_COM_GRSD","SDS_ID", record);
		    return "ok";		
	}

导入xlsx后缀的excel文件需要使用XSSFWorkbook。

XSSFSheet st = null;
XSSFWorkbook wb = null;
String ghflag = "ok";
try {
  	wb = new XSSFWorkbook(bis);   //创建一个新的excel
  	st = wb.getSheetAt(0);   //创建一个新的sheet页
 } catch (IOException e) {
	// TODO Auto-generated catch block
	e.printStackTrace();
 }

总结：前台通过ajaxsubmit函数将文件传到后台，后台通过uploadFile接收。将文件对象转为BufferedInputStream，调用自己写好的importExcel方法来处理函数即可。