how to transfer excel data to mysql using poi

Rainie999

　　在我们日常的开发中经常需要把excel文件的数据导入数据库中，近期做项目需要实现这样的需求，现把实现过程分享给大家：
    到官方网站下载poi包最新版本。放入项目lib目录下。经研究发现poi的接口不知道为什么没有做的尽善尽美：
   1、在表格为空时会报错
   2、为日期型时获取到的日期不准确
   3、没有整型值的获取方法
   遂进行二次封装，以下是项目中部分源代码的摘录。
   封装类如下：
 

package com.toto.service;
 
import java.text.SimpleDateFormat;
import java.util.Date;
 
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
 
/**
* <p>封装poi常用操作</p>
* @author zxc
*
*/
public class ExcelService {
/**
* <p>单元格格式为日期时，获取指定形式的日期格式</p>
* @param cell
* @return The value
*/
public String getExcelDateTime(HSSFCell cell) {
if (isBlank(cell))
return null;
double d =cell.getNumericCellValue();
Datedate = HSSFDateUtil.getJavaDate(d);
SimpleDateFormatsFormat = new SimpleDateFormat("yyyy-MM-ddHH:mm:ss");
Strings_datetime = sFormat.format(date);
return s_datetime;
}
 
/**
* <p>空判断</p>
* @param cell
* @return The value
*/
public boolean isBlank(HSSFCellcell) {
if (null == cell)
return true;
int cellType =cell.getCellType();
if (HSSFCell.CELL_TYPE_BLANK == cellType) {
return true;
}
return false;
}
 
/**
* <p>取得整型格式</p>
* @param cell
* @return The value
*/
public String getExcelInt(HSSFCell cell) {
if (isBlank(cell))
return null;
return (int)cell.getNumericCellValue() + "";
}
/**
* <p>取得长整型格式</p>
* @param cell
* @return The value
*/
public String getExcelLong(HSSFCell cell) {
if (isBlank(cell))
return null;
return (long)cell.getNumericCellValue() + "";
}
/**
* <p>取得长整型格式</p>
* @param cell
* @return The value
*/
public String getExcelDouble(HSSFCell cell) {
if (isBlank(cell))
return null;
returncell.getNumericCellValue() + "";
}
/**
* <p>取得字符串形式
* @param cell
* @return The value
*/
public String getExcelString(HSSFCell cell) {
if (isBlank(cell))
return null;
returncell.getStringCellValue();
}
}
上传文件成功后立刻调用importGameBack 方法，Bo中的导入方法如下：

/**
* 导excel进mysql
* @param pathWithName：物理目录
* @return error info if fail
* @throws FileNotFoundException
* @throws IOException
*/
public String importGameBack(StringpathWithName) throws FileNotFoundException, IOException {
  DAO dao = factory.getDaoFactory().getDao();
  ExcelService es = new ExcelService();
  StringBuffer sb = new StringBuffer();
  HSSFWorkbook workbook = new HSSFWorkbook(newFileInputStream(pathWithName));
  HSSFSheet sheet = workbook.getSheetAt(0);
  Iterator iter = sheet.iterator();
if(iter.hasNext())
  iter.next();
for(;iter.hasNext();){
  GameForm gameForm = new GameForm();
  setDefaultVal4Import(gameForm);
  HSSFRow row = (HSSFRow) iter.next();
if(es.isBlank(row.getCell(0))){
break;
  }
  String result = setVal4Import(gameForm,row);
if(result.length() > 0){
  sb.append(result);
break;
  }
if( ! dao.insert("game.insert.only", gameForm)){
  sb.append("Error:excel row:").append(row.getRowNum()+ 1);
break;
  }
  }
return sb.toString();
}
 
/**
* <p>设置表记录默认值</p>
* @param gameForm
*/
public void setDefaultVal4Import(GameForm gameForm){
UserBOubo = factory.getBoFactory().getUserBO();
Stringnow = ubo.getNow();
gameForm.setAdd_time(now);
gameForm.setSort_time(now);
}
/**
* <p>根据excel设置表记录值</p>
* @param gameForm
* @param row
* @return
*/
public String setVal4Import(GameForm gameForm,HSSFRow row){
StringBuffersb = new StringBuffer();
ExcelServicees = new ExcelService();
try{
Stringis_event = es.getExcelInt(row.getCell(0));
if(null == is_event){
sb.append("is_event is null");
return sb.toString();
}
gameForm.setIs_event(is_event);
Stringrace_time = es.getExcelDateTime(row.getCell(1));
if(null == race_time){
sb.append("race_time is null");
return sb.toString();
}
gameForm.setRace_time(race_time);
Stringgame_type = es.getExcelInt(row.getCell(2));
if(null == game_type){
sb.append("game_type is null");
return sb.toString();
}
gameForm.setGame_type(game_type);
Stringleague_id = es.getExcelInt(row.getCell(3));
if(null == league_id){
sb.append("league_id is null");
return sb.toString();
}
gameForm.setLeague_id(league_id);
Stringmaster_team = es.getExcelString(row.getCell(4));
if(null == master_team){
sb.append("master_team is null");
return sb.toString();
}
gameForm.setMaster_team(master_team);
Stringguest_team = es.getExcelString(row.getCell(5));
if(null == guest_team){
sb.append("guest_team is null");
return sb.toString();
}
gameForm.setGuest_team(guest_team);
Stringmaster_val = es.getExcelDouble(row.getCell(7));
if(null == master_val){
sb.append("master_val is null");
return sb.toString();
}
gameForm.setMaster_val(master_val);
Stringguest_val = es.getExcelDouble(row.getCell(9));
if(null == guest_val){
sb.append("guest_val is null");
return sb.toString();
}
gameForm.setGuest_val(guest_val);
gameForm.setRef_val(es.getExcelDouble(row.getCell(6)));
gameForm.setPeace_val(es.getExcelDouble(row.getCell(8)));
}catch(Exception e){
sb.append("row:").append(row.getRowNum());
}
return "";
}