1.把jxl.jar导入项目bin中(项目是ssh框架)；
2.jsp页面index.jsp:
<script type="text/javascript">
<!-- 弹出信息框 -->
function load(){
var info=document.f1.info.value;
if(info.length > 0){
alert(info);
}
}
</script>
<body onload="load()">
<form action="userinfo.do" method="post" enctype="MULTIPART/FORM-DATA" name="f1">
<input type="hidden" name="user" value="importExcel"/>
<input type="file" name="file" value="浏
<input type="submit" value="导入" />
<input type="hidden" name="info" value="${info }" />
<form>
<body>
3.action的写法：
public ActionForward importExcel(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response)
throws Exception {
Userinfo ui= (Userinfo)form;//要操作的对象
FormFile file=ui.getFile();
StringBuffer str= new StringBuffer();//存入操作信息（如：错误信息）到jsp信息框
String filename=file.getFileName();
//判断导入的文件是不是excel
if(filename.indexOf(".xls")!=-1){
String pp=request.getSession().getServletContext().getRealPath("\\")+"user";
String path=pp+"\\"+filename;
File director=new File(path);
File ff=new File(pp);
if(!ff.exists()){
ff.mkdir();
}
FileOutputStream fos=new FileOutputStream(director);
fos.write(file.getFileData());
fos.flush();
fos.close();
str=iu.importExcel(str,path);
}else{
str.append("导入的文件必须为excel文件！");
}
request.setAttribute("info", str.toString());
return mapping.findForward("index");//返回刚才的点击导入页面index.jsp
}
4.dao类：
public StringBuffer importExcel(StringBuffer sb,String path) {
// TODO Auto-generated method stub
try {
WorkbookSettings setting = new WorkbookSettings();
java.util.Locale locale = new java.util.Locale("zh","CN");
setting.setLocale(locale);
setting.setEncoding("ISO-8859-1");
//获得excel
Workbook workbook=Workbook.getWorkbook(new File(path),setting);
//获得excel第一个工作表
Sheet sheet=workbook.getSheet(0);
//获得excel的行数和列数
int rows=sheet.getRows();
int columns=sheet.getColumns();
if(rows<=0){
sb.append("导入的excel为空！");
return sb;
}
//中间数组存放每行的数据
String [] content=new String [columns+1];
//中间集合存放要保存的对象
ArrayList<Userinfo> al=new ArrayList<Userinfo>();
//获得excel单元格的内容
for(int i=1;i<rows;i++) {//循环行
//循环列
for(int j=0;j<columns;j++) {
Cell cell=sheet.getCell(j,i);
content[j+1]=cell.getContents().trim();
}
//给对象赋值
Userinfo ui=new Userinfo();
ui.setUsername(content[1]);
ui.setPwd(content[2]);
ui.setEmail(content[3]);
//对特别数据类型进行正则判断(如数字)
Pattern p = Pattern.compile("[0-9]+");
Matcher m= p.matcher(content[4]);
if(m.matches()){
ui.setAge(Integer.parseInt(content[4]));
}else{
sb.append("数据类型错误："+"第"+(i+1)+"条数据的\'"+content[4]+"\' 不是数值型; \n");
}
//去除excel中的重复数据
for(int k=0;k<al.size();k++){
Userinfo ii= (Userinfo)al.get(k);
if(ii.getUsername().equals(ui.getUsername())&&ii.getPwd().equals(ui.getPwd())){
al.remove(ii);
sb.append("数据重复："+"第"+(i+1)+"条数据"+(k+2)+"条重复; \n");
break;
}
}
al.add(ui);
}
//执行插入
if(sb.length()==0){
this.getHibernateTemplate().saveOrUpdateAll(al);
sb.append("导入成功！");
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return sb;
}
