excel批量导入数据

来源：互联网发布：linux菜鸟私房菜 pdf 编辑：程序博客网时间：2024/05/17 23:40

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

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