jsp页面导入excel文件的步骤及配置

上传使用flash插件

需要jquery.uploadify.min.js，uploadify.css，poi-ooxml-3.8-20120326.jar等

jsp页面：

<%@include file="/uploadDeclare.jsp"%>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style type="text/css">
.uploadify {
    float: left;
    margin: 0 10px;
}
</style>
<script type="text/javascript">
//导入功能
$(function() {
    $("#uploadFile").uploadify({
        buttonClass : '',
        fileSizeLimit : '51200KB',
        uploader : '${basePath}core/upload!uploadFile.action', // 服务器端处理地址  
        swf : '${basePath}js/uploadify/uploadify.swf', // 上传使用的 Flash  
        buttonText : "导入",
        buttonCursor : 'hand',
        fileObjName : 'uploadify',// 上传参数名称 后台action里面的属性uploadify  
        fileTypeExts : "*.xls;*.xlsx", // 扩展名  
        fileTypeDesc : "请选择 文件格式",
        removeTimeout : 1, // 文件说明  
        auto : true, // 选择之后，自动开始上传  
        multi : false, // 是否支持同时上传多个文件  
        queueSizeLimit : 1, // 允许多文件上传的时候，同时上传文件的个数
        queueID : 'queueID',

        onUploadSuccess : function(file, data, response) {
            var data = jQuery.parseJSON(data);
            if (data.error == 1) {
                ldDialog.alert(data.message);
            } else {
                $.ajax({
                    url : "${basePath}uploadDocumentAction/uploadDocument!importManyOverallPlan.action",
                    type : "post",
                    dataType : "json",
                    data : {
                        filePath : data.url,
                        fileName : data.newFileName,
                        proId : "${proId}",
                    },
                    async : false,
                    success : function(r) {
                        if (r.code == 'success') {
                            ldDialog.tips("导入成功！");
                            document.pagerForm.submit();
                        } else {
                            ldDialog.tips("导入失败！");
                        }
                    }
                });
            }
        }
    });
});
</script>
</head>

<td> 
     <input type="button" id="uploadFile" class="ldBtnGray" value="导入" />&nbsp;&nbsp;
     <a href="${basePath}/upload/template/template13.xlsx" download="template13.xlsx">
         <span style='float: right; margin-right: 20px; margin-top: 10px; font-size: 18px; color: #2891d2;'>模板下载</span>
     </a>
</td>

uploadDeclare.jsp

<%@page language="java" contentType="text/html; charset=utf-8"%>
<base
    href="${pageContext.request.scheme}://${pageContext.request.serverName}:${pageContext.request.serverPort}${pageContext.request.contextPath}/" />
<LINK href="${basePath}plugins/uploadify/uploadify.css" type="text/css" rel="stylesheet" />
<script language="javascript" type="text/javascript">
    //防止客户端缓存文件，造成uploadify.js不更新，而引起的“喔唷，崩溃啦”  
    document.write ("<script type='text/javascript' " + "src='${basePath}plugins/uploadify/jquery.uploadify.min.js?" + new Date () + "'></script>");
</script>
<style>
.uploadify-box {
     130px;
    margin: 0px;
    margin-top: 10px;
}
</style>

效果页面：

excel文件放在eclipse-workspace eportWebRootupload emplate emplate13.xlsx目录下，都有的字段：

 jsp页面 url : "${basePath}uploadDocumentAction/uploadDocument!importManyOverallPlan.action"对应的类，uploadDocumentAction

其中base类DesigndrawOverallPlanPo中书协的属性和属性的get(),set()方法，可自行编写

    /**
     * 批量导入
     * @param modelMap
     * @param request
     * @return
     * @throws ParseException 
     */
    @ResponseBody
    @RequestMapping("uploadDocument!importManyOverallPlan.action")
    public JSONObject importManyOverallPlan(ModelMap modelMap, HttpServletRequest request) throws ParseException {
        String filePath = this.getStringParameter("filePath");
        String fileName = this.getStringParameter("fileName");
        String proId = this.getStringParameter("proId");
        final JSONObject result = new JSONObject();
        final UploadDocument uploadDocument = this.parameterToPoPrefix(UploadDocument.class);
        ProApprovalPo proApproval = this.proApprovalBaseService.queryProApprovalById(proId);
        User user = this.getSessionUser();
        String proName = "";
        boolean success = false;
        String code = "";
        
        if(user != null && proApproval != null) {
            String abPath = request.getSession().getServletContext().getRealPath("/");
            filePath = abPath + filePath;
            List<DesigndrawOverallPlanPo> designdrawOverallPlans = new ReadInfoTableExcel().getExcelToOverallPlan(filePath);
            proName = proApproval.getProName();
            for(int i=0;i<designdrawOverallPlans.size();i++) {
                DesigndrawOverallPlanPo designdrawOverallPlan = designdrawOverallPlans.get(i);
                //首先校验excel文件中的项目id和项目名称是否正确
                //还有计划开始时间要小于等于计划结束时间
                //且时间格式2018-01-02要正确
                //计划开始时间和计划结束时间是date类型与导入时类型（java.long.String）不匹配，
                //所以现在导入时，计划开始时间值:reserved1，计划结束时间：reserved2
                //.trim():取消空格
                if(StringUtils.isEmpty(designdrawOverallPlan.getProId()) || !proId.equals(designdrawOverallPlan.getProId().trim())) {
                    continue;
                }else if(StringUtils.isEmpty(designdrawOverallPlan.getProName()) || !proName.equals(designdrawOverallPlan.getProName().trim())) {
                    continue;
                }else if(StringUtils.isNotEmpty(designdrawOverallPlan.getReserved1()) && StringUtils.isNotEmpty(designdrawOverallPlan.getReserved2())) {
                    java.text.SimpleDateFormat format = new java.text.SimpleDateFormat("yyyy-MM-dd");
                    String startAcTime = designdrawOverallPlan.getReserved1().trim();
                    String endAcTime = designdrawOverallPlan.getReserved2().trim();
                    //计算持续时长(天):计划结束时间-计划开始时间
                    if(validationTimeFormat(startAcTime) && validationTimeFormat(endAcTime)) {
                        int day = (int)((format.parse(endAcTime).getTime() - format.parse(startAcTime).getTime())/(24 * 60 * 60 * 1000)) + 1;
                        if(day<1) {
                            continue;
                        }else {
                            designdrawOverallPlan.setStartAcTime(format.parse(startAcTime));
                            designdrawOverallPlan.setEndAcTime(format.parse(endAcTime));
                            designdrawOverallPlan.setContinueDate(day);
                        }
                    }
                }
                designdrawOverallPlan.setReserved1(null);
                designdrawOverallPlan.setReserved2(null);
                designdrawOverallPlan.setProId(designdrawOverallPlan.getProId().trim());
                designdrawOverallPlan.setProName(designdrawOverallPlan.getProName().trim());
                designdrawOverallPlan.setBaseInfo(user);
                designdrawOverallPlanBaseService.insertDesigndrawOverallPlan(designdrawOverallPlan);
                success = true;
            }
        }
        result.put("code", code);
        return result;
    }
    /**
     * 校验时间格式为2018-01-02
     * @param time
     * @return
     */
    public boolean validationTimeFormat(String time) {
        boolean bool = false;
        Pattern pattern = Pattern.compile("[0-9]*");
        if(time != null && time.length() == 10) {
            //校验前四位是否为数字
            if(pattern.matcher(time.substring(0, 4)).matches()) {
                //校验第五位是-
                if("-".indexOf(time.substring(4, 5))!=-1) {
                    //校验第6,7位是否为数字
                    if(pattern.matcher(time.substring(5,7)).matches()) {
                        //校验第8位是否为-
                        if("-".indexOf(time.substring(7, 8))!=-1) {
                            //校验第9,10位是否为数字
                            if(pattern.matcher(time.substring(8, 10)).matches()) {
                                bool = true;
                            }
                        }
                    }
                }
            }
            
        }
        return bool;
    }

方法：ReadInfoTableExcel().getExcelToOverallPlan(filePath);

 /**
     *     获取excel里的信息
     * @param filepath
     * @param filetype
     * @param uuid
     * @return
     */
    public List<DesigndrawOverallPlanPo> getExcelToOverallPlan(String filePath) {
        //这儿导入使用两个方法原因：
        //excel版本有03版本和07版本的区别，文件后缀名分别为.xls和.xlsx。它们对应的POI中的Workbook也是不同的，
        //分别是HSSFWorkbook和XSSFWorkbook；对于不同版本的EXCEL文档要使用不同的工具类
        try{
            return addExcelToOverallPlanXSSF1(filePath);
        }catch(Exception e){
            try{
                return addExcelToOverallPlanXSSF2(filePath);
            }catch (Exception e1){
                return null;
            }
        }
    }

方法：addExcelToOverallPlanXSSF1 和 addExcelToOverallPlanXSSF2，这儿利用到了反射的方法使属性和字段值一一对应起来。

  /**
     *  批量导入 方法1
     * @param filePath
     * @return
     * @throws IOException
     */
    public List<DesigndrawOverallPlanPo> addExcelToOverallPlanXSSF1(String filePath) throws IOException{
        List<ZuobiaoInfo> zuobiaoInfos = new ArrayList<>();
        List<DesigndrawOverallPlanPo> designdrawOverallPlans = new ArrayList<DesigndrawOverallPlanPo>();
        XSSFWorkbook swb = new XSSFWorkbook(filePath);
        XSSFSheet sheet = swb.getSheetAt(0);
        zuobiaoInfos = new TableinfoTest().getOverallPlan();
        //获取excel文件中的行数
        int rowsNumber = this.getnumber(filePath);
        for(int i=0;i<rowsNumber;i++) {
            boolean flag = true;
            DesigndrawOverallPlanPo designdrawOverallPlan = new DesigndrawOverallPlanPo();
            for(int j=0;j<zuobiaoInfos.size();j++) {
                Class c = designdrawOverallPlan.getClass();
                Class[] cargs = new Class[1];
                int cellnumber = zuobiaoInfos.get(j).getZongzuobiao();
                try {
                    Object realArgs = this.getXSSFSheetExcelvalue(i+1, cellnumber, sheet); 
                    if(realArgs!=null) {
                        cargs[0] = realArgs.getClass();
                        String method = zuobiaoInfos.get(j).getMethod();
                        Method m = c.getMethod(method, cargs);
                        Object[] inArgs = new Object[1];
                        inArgs[0] = realArgs;
                        m.invoke(designdrawOverallPlan, inArgs);
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    flag = false;
                    break;
                }
            }
            if(flag) {
                designdrawOverallPlans.add(designdrawOverallPlan);
            }
        }
        return designdrawOverallPlans;
    }
    /**
     *  批量导入 方法2
     * @param filePath
     * @return
     * @throws IOException
     */
    public List<DesigndrawOverallPlanPo> addExcelToOverallPlanXSSF2(String filePath) throws IOException{
        List<ZuobiaoInfo> zuobiaoInfos = new ArrayList<>();
        List<DesigndrawOverallPlanPo> designdrawOverallPlans = new ArrayList<DesigndrawOverallPlanPo>();
        HSSFWorkbook xwb = new HSSFWorkbook(new FileInputStream(filePath));  
        HSSFSheet sheet= xwb.getSheetAt(0);
        zuobiaoInfos = new TableinfoTest().getOverallPlan();
        //获取excel文件中的行数
        int rowsNumber = this.getnumber(filePath);
        for(int i=0;i<rowsNumber;i++) {
            boolean flag = true;
            DesigndrawOverallPlanPo designdrawOverallPlan = new DesigndrawOverallPlanPo();
            for(int j=0;j<zuobiaoInfos.size();j++) {
                Class c = designdrawOverallPlan.getClass();
                Class[] cargs = new Class[1];
                int cellnumber = zuobiaoInfos.get(j).getZongzuobiao();
                try {
                    Object realArgs=this.getHSSFSheetExcelvalue(i+1, cellnumber, sheet);
                    if(realArgs!=null) {
                        cargs[0] = realArgs.getClass();
                        String method = zuobiaoInfos.get(j).getMethod();
                         if(!realArgs.toString().equals("")){
                                Method m = c.getMethod(method, cargs);
                                Object[] inArgs = new Object[1];
                                inArgs[0] = realArgs;
                                m.invoke(designdrawOverallPlan, inArgs);
                            }else{
                                flag = false;
                                break;
                            }
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                    flag = false;
                    break;
                }
            }
            if(flag) {
                designdrawOverallPlans.add(designdrawOverallPlan);
            }
        }
        return designdrawOverallPlans;
    }

方法：this.getnumber(filePath);

 public int getnumber(String filepath)  {
        
        try{
            XSSFWorkbook xwb = new XSSFWorkbook(filepath);  
            XSSFSheet sheet = xwb.getSheetAt(0);
            int allsheetnumber=sheet.getLastRowNum();
            System.out.println(allsheetnumber);
            return allsheetnumber;
        }catch (Exception e){
            e.printStackTrace();
            try {

                HSSFWorkbook xwb1 = new HSSFWorkbook(new FileInputStream(filepath));  
                HSSFSheet sheet1= xwb1.getSheetAt(0);
                int allsheetnumber1=sheet1.getLastRowNum();
                return allsheetnumber1;
            }catch (Exception e1){
                e1.printStackTrace();
                return 0;
            }
        }
        
     
    }

方法：this.getHSSFSheetExcelvalue(i+1, cellnumber, sheet);

 public String getHSSFSheetExcelvalue(int rownumber,int cellnumber,HSSFSheet sheet){
        String str=null;
        if(sheet.getRow(rownumber)!=null){
            if(sheet.getRow(rownumber).getCell(cellnumber)!=null){
                str=sheet.getRow(rownumber).getCell(cellnumber).toString();
            }
        }
        return str;
     }

方法：TableinfoTest().getOverallPlan()，其中setProId等是base类DesigndrawOverallPlanPo中属性proId的set()方法。

public class TableinfoTest {
 List<ZuobiaoInfo> overallPlan = new ArrayList<ZuobiaoInfo>
    (Arrays.asList(
        new ZuobiaoInfo("setProId", "项目编号", 0),
        new ZuobiaoInfo("setProName", "项目名称", 1),
        new ZuobiaoInfo("setWorkContent", "工作内容", 2),
        new ZuobiaoInfo("setReserved1", "计划开始时间", 3),
        new ZuobiaoInfo("setReserved2", "计划结束时间", 4),
        new ZuobiaoInfo("setRemarks", "备注", 5)
    ));
    public List<ZuobiaoInfo> getOverallPlan() {
        return overallPlan;
    }

    public void setOverallPlan(List<ZuobiaoInfo> overallPlan) {
        this.overallPlan = overallPlan;
    }
     
}

类：ZuobiaoInfo

package com.landicorp.pilot.action.readexcel;

public class ZuobiaoInfo {
    
    public String Method;//方法名
    public String name;
    public int zongzuobiao;//纵坐标

    
     public ZuobiaoInfo(){
        
    }
     public ZuobiaoInfo(String Method, String name ,int zongzuobiao){
         this.Method=Method;
         this .zongzuobiao=zongzuobiao;
         this .name=name;
     }
    public int getZongzuobiao() {
        return zongzuobiao;
    }
    public void setZongzuobiao(int zongzuobiao) {
        this.zongzuobiao = zongzuobiao;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    
    public String getMethod() {
        return Method;
    }
    
    public void setMethod(String method) {
        Method = method;
    }
     
     
     
}
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/lxnlxn/p/10253727.html