在我们日常的开发中经常需要把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
;
return
cell.getNumericCellValue() +
""
;
}
/**
*
<p>
取得字符串形式
*
@param
cell
*
@return
The
value
*/
public
String getExcelString(HSSFCell cell) {
if
(isBlank(cell))
return
null
;
return
cell.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(
new
FileInputStream(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
""
;
}