JAVA导入Excel文件

“落叶他乡树,寒灯独夜人”
JSP:

<div id="excelUser" title="excel导入"
     style="display: none; width: 350px; height: 150px; padding: 15px; background: #E9F1FF;">
    <form id="excelUserForm" method="post" enctype="multipart/form-data"
          action="">
        <table cellspacing='0' cellpadding='1'>
            <tr>
                <td style="text-align: left; font-size: 12px;">选择excel文件:</td>
                <td><input style="width: 200px;" type="file"
                           class="input easyui-validatebox" id="excelfile" name="excelfile"
                           required="required" />
                </td>
            </tr>
            <tr>
                <td colspan="2" style="text-align: left; font-size: 12px;"><span
                        style="color: red">注：请选择后缀名为xlsx文件,否则无法导入。</span></td>
            </tr>
        </table>
    </form>
</div>

JS:

/**
 * 上传方法
 */
function doImport() {
    $('#excelUser').show().dialog({
        modal : true,
        toolbar : [ {
            text : '提交',
            iconCls : 'icon-ok',
            handler : function() {
                $('#excelUserForm').form('submit', {
                    url : 'controller/clientActivityHotInfoManage/import.json',
                    onSubmit : function() {
                        parent.$.messager.progress({
                            title : '提示',
                            text : '数据导入中，请稍等....'
                        });
                        var isValid = $('#excelUserForm').form('validate');
                        if (!isValid) {
                            parent.$.messager.progress('close');
                            return false;
                        }
                    },
                    success : function(response) {
                        $('#excelUser').dialog('close');
                        var response = eval('(' + response + ')');
                        parent.$.messager.progress('close');
                        if (response.success) {
                            $.messager.alert('成功', response.msg, 'info');
                            $('#subjectGrid').datagrid('reload');
                            // doAuto();

                        } else {

                            $.messager.alert('失败', response.msg, 'error');
                        }
                        $('#projectStageConfig').datagrid('load', {
                            id : $.trim($('#id').val())
                        });
                    },
                    failure : function(response) {
                        parent.$.messager.progress('close');
                        $('#excelUser').dialog('close');
                        $.messager.alert('失败', response.msg, 'error');
                    }
                });

            }

        }, {
            text : '关闭',
            iconCls : 'icon-cancel',
            handler : function() {
                $('#excelUser').dialog('close');
            }
        } ]

    });
}

Controller:

@CodeComments("导入EXCEL到表中")
    @RequestMapping({"/import.json"})
    @ResponseBody
    public void importExcel(MultipartHttpServletRequest request, HttpServletRequest request1, HttpServletResponse response, ClientActivityHotInfoManage eui, Integer egroupid)
    {
        Map messages = new HashMap();
        MultipartFile file = request.getFile("excelfile");
        String inputPath = file.getOriginalFilename();
        System.out.println("inputPath=========================" + inputPath);
        String originalFile = inputPath.substring(
                inputPath.lastIndexOf(".") + 1, inputPath.length())
                .toLowerCase();

        if ("xls,xlsx".indexOf(originalFile) < 0) {
            messages.put("success", Boolean.valueOf(false));
            messages.put("msg", "文件导入类型错误,只能导入后缀名是xlsx的EXCEL文件类型!");
            toJson(response, messages);
            return;
        }
        if ("xls".equals(originalFile)) {
            messages.put("success", Boolean.valueOf(false));
            messages.put("msg", "文件导入类型错误,只能导入后缀名是xlsx的EXCEL文件类型!");
            toJson(response, messages);
            return;
        }
        if (file.getSize() > 20048576L) {
            messages.put("success", Boolean.valueOf(false));
            messages.put("msg", "文件过大，只能导入20M内文件！");
            toJson(response, messages);
            return;
        }

        int i = 0;
        try
        {
            InputStream fis = file.getInputStream();
        //这个方法在下面展示
            List imielist = ActivityHotInfoExcelManage.importExcelInfo(fis, eui);

            ClientActivityHotInfoManage order = new ClientActivityHotInfoManage();


            for (i = 0; i < imielist.size(); i++)
            {
                ClientActivityHotInfoManage vo = (ClientActivityHotInfoManage)imielist.get(i);
                ClientActivityHotInfoManage lx = new ClientActivityHotInfoManage();

                lx.setAcId(vo.getAcId());
                lx.setAcUrl(vo.getAcUrl());
                lx.setStartDate(vo.getStartDate());
                lx.setEndDate(vo.getEndDate());
                lx.setRepeat(vo.getRepeat());
                lx.setStatus(vo.getStatus());
                lx.setPlotId(vo.getPlotId());
                lx.setAcName(vo.getAcName());


//这里就可以直接插入实体类了             this.clientActivityHotInfoManageService.insert(lx);

                messages.put("success", Boolean.valueOf(true));
                messages.put("msg", "数据导入成功!");
            }
            fis.close();
        }
        catch (Exception e) {
            messages.put("success", Boolean.valueOf(false));
            messages.put("msg", "导入异常，请在检查excel表的第" + (i + 2) + "行后，从第" + (
                    i + 2) + "行开始导入！");
            System.out.println("importExcel异常--------->" + e.getMessage());
        }
        toJson(response, messages);
    }

importExcelInfo方法

public static List<ClientActivityHotInfoManage> importExcelInfo(InputStream fis, ClientActivityHotInfoManage eui)
    {
        List<ClientActivityHotInfoManage> excelInfos = new ArrayList<ClientActivityHotInfoManage>();
        try
        {
            XSSFWorkbook hwb = new XSSFWorkbook(fis);

            XSSFSheet sheet = hwb.getSheetAt(0);
            XSSFRow row = null;

            System.out.println("*****总行数****" + sheet.getPhysicalNumberOfRows());

            for (int i = 0; i < hwb.getNumberOfSheets(); i++) {
                sheet = hwb.getSheetAt(i);

                for (int j = 1; j < sheet.getPhysicalNumberOfRows(); j++)
                {
                    row = sheet.getRow(j);
                    ClientActivityHotInfoManage excelInfo = new ClientActivityHotInfoManage();

                    if ((row.getCell(1) != null) &&
                            (getCellValue(row.getCell(1)) != "")) {

                        excelInfo.setAcId(getCellValue(row.getCell(1)).trim());
                        excelInfo.setAcUrl(getCellValue(row.getCell(2)).trim());
                        excelInfo.setStartDate(getCellValue(row.getCell(3)).trim());
                        excelInfo.setEndDate(getCellValue(row.getCell(4)).trim());
                        excelInfo.setRepeat(Long.parseLong(getCellValue(row.getCell(5)).trim().substring(0,1)));
                        excelInfo.setStatus(Long.parseLong(getCellValue(row.getCell(6)).trim().substring(0,1)));
                        excelInfo.setPlotId((getCellValue(row.getCell(7)).trim()));
                        excelInfo.setAcName((getCellValue(row.getCell(8)).trim()));
                    }

                    excelInfos.add(excelInfo);
                }

            }

            return excelInfos;
        }
        catch (Exception e) {
            System.out.println("导入EXCEL文件出错------------>" + e.getMessage());
        }
        return null;
    }