JAVA导入Excel文件

心已赠人 2022-06-07 05:13 177阅读 0赞

**“落叶他乡树,寒灯独夜人”**  
**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;
        }