原理: 先将文件导入,再读取文件内容,提取数据,生成对象,保存到数据库。
jar包:POI中poi-3.9-20121203.jar,poi-ooxml-3.9-20121203.jar这两个个为必须。实验结果。
xbean.jar必须。
在导入的基础上操作:
public String uploadFile(){
String directory = "/upload";
String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);
//生成上传的文件对象
File target = new File(targetDirectory,uploadFileFileName);
//如果文件已经存在,则删除原有文件
if(target.exists()){
target.delete();
}
//复制file对象,实现上传
try {
FileUtils.copyFile(uploadFile, target);
} catch (IOException e) {
e.printStackTrace();
}
//从导入的文件中读取数据
loadUserInfo(uploadFileFileName);
return SUCCESS;
}
private void loadUserInfo(String uploadFileFileName) {
//读取刚才上传文件,确保路径相同
String directory = "/upload";
String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);
File target = new File(targetDirectory,uploadFileFileName);
// HSSFWorkbook只能用来读取2003前(含)的版本, .xls 读取Excel2007时发生如下异常:
org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML.
You are calling the part of POI that deals with OLE2 Office Documents.
You need to call a different part of POI to process this data (eg XSSF instead of HSSF)
//XSSFWorkbook 只能读取2007版本 .xlsx 读取Excel2003以前(包括2003)的版本时却发生了如下新异常:
org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file: '*.xls'
//XSSF和HSSF虽然在不同的包里,但却引用了同一接口Workbook,可以用下面判断
Workbook wb = null;
try{
FileInputStream fi = new FileInputStream(target);
if (uploadFileFileName.toLowerCase().endsWith("xls")) {
wb = new HSSFWorkbook(fi);
}else if(uploadFileFileName.toLowerCase().endsWith("xlsx")) {
wb = new XSSFWorkbook(fi);
}
Sheet sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum()+1;
userList = new ArrayList<User>();
//i 从1开始表示第一行为标题 不包含在数据中
for(int i=1;i<rowNum;i++){
user = new User();
Row row = sheet.getRow(i);
int cellNum = row.getLastCellNum();
for(int j=0;j<cellNum;j++){
Cell cell = row.getCell(j);
String cellValue = null;
switch(cell.getCellType()){ //判断excel单元格内容的格式,并对其进行转换,以便插入数据库
case 0 : cellValue = String.valueOf((int)cell.getNumericCellValue()); break;
case 1 : cellValue = cell.getStringCellValue(); break;
case 2 : cellValue = String.valueOf(cell.getDateCellValue()); break;
case 3 : cellValue = ""; break;
case 4 : cellValue = String.valueOf(cell.getBooleanCellValue()); break;
case 5 : cellValue = String.valueOf(cell.getErrorCellValue()); break;
}
switch(j){//通过列数来判断对应插如的字段
//数据中不应该保护ID这样的主键记录
//case 0 : user.setId(Integer.valueOf(cellValue));break;
case 0 : user.setUserName(cellValue);break;
case 1 : user.setPassword(cellValue);break;
case 2 : user.setFirstName(cellValue);break;
case 3 : user.setLastName(cellValue);break;
case 4 : user.setAddress(cellValue);break;
}
}
userList.add(user);
}
userIntoDB(userList);
}catch(IOException e){
e.printStackTrace();
}
}
private void userIntoDB(List<User> userList) {
// TODO Auto-generated method stub
int num = userList.size();
for(int i=0; i<num; i++){
userService.add(userList.get(i));
}
}