第一步、需要一个这个jar,好像是只支持07及以上版本的excel。
第二步、你需要建一个表,属性要和excel里面的一致,然后用hibernate的逆向工程建到model里
第三步、 /**导入excel
* @throws Exception */
public String excel() throws Exception{
String directory = "/file";
String targetDirectory = ServletActionContext.getServletContext().getRealPath(directory);
File target = UploadFile.Upload(uploadFile, uploadFileFileName,targetDirectory);
List<Excel> sList = new ArrayList<Excel>();
excelFile = new FileInputStream(target);
Workbook wb = new HSSFWorkbook(excelFile);
Sheet sheet = wb.getSheetAt(0);
int rowNum = sheet.getLastRowNum()+1;
for (int i = 1; i < rowNum; i++) {
Excel excel = new Excel();
Row row = sheet.getRow(i);
int cellNum = row.getLastCellNum();
System.out.println("现在是第"+cellNum);
for (int j = 0; j < cellNum; j++) {
Cell cell = row.getCell(j);
String cellValue = null;
switch (cell.getCellType()) { // 判断excel单元格内容的格式,并对其进行转换,以便插入数据库
case 0:
if(HSSFDateUtil.isCellDateFormatted(cell)){
Date a =cell.getDateCellValue();
DateFormat formater =new SimpleDateFormat("yyyy-MM-dd");
cellValue=formater.format(a);
break;
}else {
cellValue = String
.valueOf((int) cell.getNumericCellValue());
break;
}
case 1:
cellValue = cell.getStringCellValue();
break;
case 2:
cellValue = cell.getStringCellValue();
break;
case 3:
cellValue = cell.getStringCellValue();
break;
case 4:
cellValue = cell.getStringCellValue();
break;
case 5:
cellValue = cell.getStringCellValue();
break;
case 6:
cellValue = cell.getStringCellValue();
break;
case 7:
cellValue = cell.getStringCellValue();
break;
case 8:
if(HSSFDateUtil.isCellDateFormatted(cell)){
Date a =cell.getDateCellValue();
DateFormat formater =new SimpleDateFormat("yyyy-MM-dd");
cellValue=formater.format(a);
break;
}else {
cellValue = String
.valueOf((int) cell.getNumericCellValue());
break;
}
}
switch (j) {// 通过列数来判断对应插如的字段
case 1:
excel.setName(cellValue);
break;
case 2:
excel.setCardid(cellValue);
break;
case 3:
excel.setEdu(cellValue);
break;
case 4:
excel.setMajor(cellValue);
break;
case 5:
excel.setTel(cellValue);
break;
case 6:
excel.setQqnum(cellValue);
break;
case 7:
excel.setCity(cellValue);
break;
case 8:
excel.setDate(cellValue);
break;
}
}
sList.add(excel);
}
accountService.excel(sList);
return "succ";
}
acction里面的导入方法,其中excel的日期格式需要注意,excel直接拿值的话是数字,我们需要判断在进行格式化操作就可以了。
第四步、页面的设置是这样的
<body>
<form action="accountAction_excel.action" enctype="multipart/form-data" method="post">
<table width="100%" border="0" align="center">
<tr>
<td colspan="99" id="more">
<input type="file" name="uploadFile" id="uploadFile"/>
<input type="submit" value="上传"/>
<input type="reset" value="重置"/>
</td>
</tr>
</table>
</form>
</body>
之后就是你写语句将拿到的list循环添加进数据库就ok 了