这个功能也是我以前项目中经常用到的,感觉很实用,必须拿来分享下:
excel进行批量导入数据,结合struts2+ajax
导入的视图:batchAdd.ftl(视图无关紧要的,可以换成其他任何视图,就是普通的form表单的提交,就是这里是调用ajax)
<div class="controltitle">当前操作:excel批量导入学生信息</div>
<div>
<form action="excelUploadAction.action" method="post" enctype="multipart/form-data" id="uploadInfor"
onSubmit="return validateuploadInforFile(this);">
<input type="file" name="uploadExcel">
<input type="submit" value="提交"/><a href = "download/stuExcel.xls">[点击下载excel样例]</a>
</form>
</div>
<div>
<div>下面是导入的excel的样式<font color = "red">[上传的excel中的内容必须按照这个顺序就行排版,否则存储内容的位置会错乱的]</font></div>
<table class="tablefirst" id="radioSubStyle">
<tr>
<th>姓名</th><th>学号</th><th>性别</th><th>籍贯</th><th>专业</th><th>学制</th><th>入学年份</th>
<th>毕业年份</th><th>工作省市</th><th>工作单位</th><th>工作岗位</th><th>职务职称</th>
<th>电话</th><th>手机</th><th>qq</th><th>邮箱</th><th>通信地址</th><th>家庭地址</th>
</tr>
<tr>
<td>沈浪</td><td>1006010054</td><td>男</td><td>汉</td><td>眼7</td><td>7</td><td>2009-11-11</td>
<td>2011-11-11</td><td>浙江</td><td>医院</td><td>临床</td><td>主任</td>
<td>642</td><td>159</td><td>5449</td><td>544@qq.com</td><td>杭州</td><td>杭州</td>
</tr>
<tr>
<td>沈浪</td><td>1006010024</td><td>男</td><td>汉</td><td>眼7</td><td>7</td><td>2009-11-11</td>
<td>2011-11-11</td><td>浙江</td><td>医院</td><td>临床</td><td>主任</td>
<td>642</td><td>159</td><td>5449</td><td>544@qq.com</td><td>杭州</td><td>杭州</td>
</tr>
<tr>
<td>沈浪</td><td>1006010034</td><td>男</td><td>汉</td><td>眼7</td><td>7</td><td>2009-11-11</td>
<td>2011-11-11</td><td>浙江</td><td>医院</td><td>临床</td><td>主任</td>
<td>642</td><td>159</td><td>5449</td><td>544@qq.com</td><td>杭州</td><td>杭州</td>
</tr>
</table>
<div><font color="red">注:①excel各列的顺序必须按样式的顺序,而内容可依实际内容而定;这里不支持照片的导入,照片可在修改中进行上传②性别只能为‘男’与‘女’;③专业只能填:眼视光七年制;眼视光本科;眼视光专科;否则会影响后面的查询</font></div>
</div>
<div id="excelUploadMsg"></div>
传入的js(ajax+文件的验证):这里涉及到的ajax可以去阅读:http://www.cnblogs.com/shenliang123/archive/2012/04/16/2452670.html
/**
* 以下是上传excel的一系列方法
* */
function validateuploadInforFile(form){
if(!validateExcelUpLoadFile(form)) return false;
var options = {
dataType: 'json',
success: showResponse
};
$("#uploadInfor").ajaxSubmit(options);
return false;
}
function showResponse(responseText){
$("#excelUploadMsg").empty();
$("#excelUploadMsg").append(responseText.msg);
}
//导入考试时进行文件格式校验
function validateExcelUpLoadFile(form) {
var fileName = form.uploadExcel.value;
if (fileName != "" ) {
var fileType = (fileName.substring(fileName.lastIndexOf(".") + 1, fileName.length)).toLowerCase();
var suppotFile = ["xls", "XLS", "xlsx", "XLSX"];
for (var i = 0; i < suppotFile.length; i++) {
if (suppotFile[i] == fileType) {
return true;
} else {
continue;
}
}
alert("文件格式不正确!");
return false;
} else {
alert("请选择你需要的导入 的文件");
return false;
}
}
struts.xml:
<!-- excel导入 -->
<action name="excelUploadAction" class="xidian.sl.action.StuInforAction" method = "excelUpload">
<interceptor-ref name="fileUpload"/>
<interceptor-ref name="defaultStack" />
<result name="success">/WEB-INF/responseMsg.jsp</result>
</action>
action中的方法处理:再次进行文件的验证和调用服务层进行excel的处理和数据持久化
//以下为单文件上传,即excel
private File uploadExcel; //文件
private String uploadExcelFileName; //文件名
private static String[] allowFileType = { "xls", "XLS", "xlsx", "XLSX" }; //控制文件类型
/**
* excel批量导入
* */
public String excelUpload(){
try{
if ((uploadExcelFileName == null) || (uploadExcelFileName.equals(""))) {
response = "{success:false,msg:'文件名不能为空!'}";
}
if (!(FileUtil.validateFileType(uploadExcelFileName, allowFileType))) { //validateFileType方法在FileUtil中,返回的是boolean值
response = "{success:false,msg:'文件类型不正确!'}";
}
excelBatchInput.uploadStu(uploadExcel); //只传入一个excel文件
response = "{success:true,msg:'"+excelBatchInput.getFinalMsg()+"'}"; //得到结果的回复
}catch (Exception e) {
e.printStackTrace();
response = "{success:true,msg:'sorry: 导入失败'}";
}
return SUCCESS;
}
serviceInterface:
package xidian.sl.service.admin;
import java.io.File;
public interface ExcelBatchInput {
/**
* excel导入的主要逻辑
* */
public String uploadStu(File upload);
/**
* 导入后的消息
* */
public String getFinalMsg();
}
ServiceImpl:(这里对excel的内容没有做过多的验证,只是验证了学号为不重复,大家可以自行对其进行添加)
package xidian.sl.service.impl.admin;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.List;
import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.Sheet;
import jxl.Workbook;
import xidian.sl.dao.admin.StuInforDAO;
import xidian.sl.entity.StuInfor;
import xidian.sl.service.admin.ExcelBatchInput;
public class ExcelBatchInputImpl implements ExcelBatchInput {
private Sheet sheet;
private String[][] excelValue;
private int successRow;
private int failRow;
private StringBuilder msg=new StringBuilder();
private String finalMsg;
private StuInforDAO stuInforDAO;
/**
* excel导入的总方法
*/
public String uploadStu(File upload) {
initExcel(upload); //初始化
readExcel(); //读取
insertIntoDB(); //插入
return msg.toString();
}
/**
* 读取excel文件中数据,保存到sheet对象中
* @param upload
*/
private void initExcel(File upload) {
Workbook rwb = null;
try {
InputStream is = new FileInputStream(upload);
rwb = Workbook.getWorkbook(is);
sheet = rwb.getSheet(0);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* 读取excel中数据进入excelValue数组中
*/
private void readExcel(){
excelValue = new String[sheet.getRows()][sheet.getColumns()];
for (int i = 0; i < sheet.getRows(); i++)
for (int j = 0; j < sheet.getColumns(); j++) {
Cell cell = sheet.getCell(j, i);
if ("".equals(cell.getContents().toString().trim())){
excelValue[i][j] = "";
}
if (cell.getType() == CellType.LABEL) {
LabelCell labelcell = (LabelCell)cell;
excelValue[i][j] = labelcell.getString().trim();
} else if (cell.getType() == CellType.NUMBER){
excelValue[i][j] = cell.getContents();
} else if (cell.getType() == CellType.DATE) {
DateCell datcell = (DateCell)cell;
excelValue[i][j] = datcell.getDate().toString();
} else {
excelValue[i][j] = cell.getContents().toString().trim();
}
}
}
/**
* 3.保存进入数据库
* @param course
*/
private void insertIntoDB() {
int excelRows = excelValue.length;
//将消息清空
msg.delete(0, msg.length());
finalMsg = "";
successRow = 0;
failRow = 0;
if (excelValue.length > 1) {
for (int i = 1; i < excelRows; i++) { //从第二排开始,第一排为文字说明
String[] DBValue = excelValue[i]; //取一行数据
if (validateInfor(i,DBValue)){
successRow += 1;
finalInsert(DBValue);
} else {
failRow += 1;
}
}
finalMsg = "录入成功结束:"+"</br>"+"目标导入学生:"+(successRow+failRow)+"</br>"
+"成功录入数:"+(successRow)+"</br>"
+"失败录入数:"+(failRow)+"</br>"
+msg.toString();
System.out.println(finalMsg);
} else {
finalMsg = "excel中无任何数据!";
System.out.println("excel中没有任何数据");
}
}
//最终返回检验结果
private boolean validateInfor(int i,String[] DBValue){
Boolean bol=true;
if(!(validateNumRepeat(i,DBValue))){
bol=false;
}
return bol;
}
//验证岗位是否重复
private boolean validateNumRepeat(int i,String[] DBValue){
boolean bolValidate=true;
/**
* 主要检查学号是否有重复
* 这里需要改进,因为这里会每插入一条就会进行一次数据库的搜索
* */
String hql = "from StuInfor";
List<StuInfor> stuInfors = stuInforDAO.getStuInforListByHQL(hql);
for(StuInfor stuInfor: stuInfors){
if(stuInfor.getStuNum().equals(DBValue[1]) && DBValue[1]!=""){
bolValidate=false;
msg.append("错误信息:第"+i+"学生的学号与数据库已存储的重复,该学号是["+DBValue[1]+"],导入的学生名字["+DBValue[0]+"],请检查!</br>");
return bolValidate;
}
}
return bolValidate;
}
//最终插入数据,这里需要修改
private void finalInsert(String[] DBValue){
StuInfor stuInfor = new StuInfor();
stuInfor.setStuName(DBValue[0]);
stuInfor.setStuNum(DBValue[1]);
stuInfor.setStuSex(DBValue[2]);
stuInfor.setStuJg(DBValue[3]);
stuInfor.setStuZy(DBValue[4]);
stuInfor.setStuXz(DBValue[5]);
stuInfor.setStuStartTime(DBValue[6]);
stuInfor.setStuEndTime(DBValue[7]);
stuInfor.setStuWorkAddress(DBValue[8]);
stuInfor.setStuWorkPlace(DBValue[9]);
stuInfor.setStuWorkPost(DBValue[10]);
stuInfor.setStuWorkZc(DBValue[11]);
stuInfor.setStuPhone(DBValue[12]);
stuInfor.setStuTelephone(DBValue[13]);
stuInfor.setStuQq(DBValue[14]);
stuInfor.setStuEmail(DBValue[15]);
stuInfor.setStuCommAddress(DBValue[16]);
stuInfor.setStuAddress(DBValue[17]);
stuInfor.setDeleteSign("1");
stuInforDAO.makePersistence(stuInfor);
}
public Sheet getSheet() {
return sheet;
}
public void setSheet(Sheet sheet) {
this.sheet = sheet;
}
public String[][] getExcelValue() {
return excelValue;
}
public void setExcelValue(String[][] excelValue) {
this.excelValue = excelValue;
}
public int getSuccessRow() {
return successRow;
}
public void setSuccessRow(int successRow) {
this.successRow = successRow;
}
public int getFailRow() {
return failRow;
}
public void setFailRow(int failRow) {
this.failRow = failRow;
}
public StringBuilder getMsg() {
return msg;
}
public void setMsg(StringBuilder msg) {
this.msg = msg;
}
public String getFinalMsg() {
return finalMsg;
}
public void setFinalMsg(String finalMsg) {
this.finalMsg = finalMsg;
}
public StuInforDAO getStuInforDAO() {
return stuInforDAO;
}
public void setStuInforDAO(StuInforDAO stuInforDAO) {
this.stuInforDAO = stuInforDAO;
}
}
里面涉及到的持久层的操作我这里就省略不写了