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