excel批量导入数据

excel批量导入数据

这个功能也是我以前项目中经常用到的，感觉很实用，必须拿来分享下：

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;
    }
}