表格导入简单教程

1、VM页面导入按钮

<input value=" 导入员工信息 " type="button" class="small-width-btn" onclick="Employee.excelImport()" />

2、Employee.excelImport()实现（js）。

excelImport: function () {
        try {
            //这里的url是控制页面跳转的url,后端中的方法中无需有代码,仅仅在配置文件中配置页面跳转,action跳转页面为employeeUpload.vm
            $.jBox("iframe:" + contextPath + "/employee/employee_goImportExcel" + webtype, {
                title: "导入员工信息列表",
                width: 400,
                height: 300,
                id: "excelImport",
                buttons : {
                    "关闭":-1
                },
                submit: function (v, o, f) {
                    if (v == -1) {
                        window.location.reload();
                    }
                }
            });
        } catch (e) {
            $.jBox.info(e.description, "温馨提示");
        }
    },

3、《iframe:" + contextPath + "/employee/employee_goImportExcel》实现类（java）

/**
     * 跳转到导入员工信息界面：employeeUpload.vm
     * 主要用于更改员工标签：先导出员工信息，然后更改标签的属性，再导入进去。
     */
    public void goImportExcel() {
    }

4、employeeUpload.vm导入页面代码。

<script type="text/javascript" lang="javascript"
        src="${contextPath}/js/employee/employee.js?versionString=$!versionString">
</script>
<!--中右-->
<div class="middle-right">
    <div class="content">
        <div class="box">
            <table width="100%" border="0" cellspacing="0" cellpadding="0" class="data_table">
                <tbody>
                <tr>
                    <td>
                        <form id="importForm" enctype="multipart/form-data" type="post"
                              onsubmit="return Employee.importList0()">
                            <input type="file" name="file" id="file">
                            <input type="submit" style="width: 64px;" value="上传文件" id="uploadSubmit"/>
                        </form>
                    </td>
                </tr>
                </tbody>
            </table>
            <div class="clear"></div>
            <!--右侧内容-->
        </div>
    </div>
</div>

5、Employee.importList0()实现（js）。

importList0:function() {
        var file = new FormData($('#importForm')[0]);
        console.log(file)
        if ($("#file").val() == "") {
            showTips("请选择文件", "1");
            return false;
        }
        Employee.submitImport();
        jQuery.jBox.closeTip();
        jQuery.jBox.tip("正在导入中，请稍候", "loading");
        return false;
    },
    submitImport:function() {
        var url = "";
        url = contextPath + "/employee/employee_importExcel" + webtype
        $.ajax({
            url: url,
            type: 'POST',
            cache: false,
            data: new FormData($('#importForm')[0]),
            processData: false,
            contentType: false,
            success: function (data) {
                data = JSON.parse(data);
                console.log(data);
                if (data.result == 0) {
                    $.jBox.closeTip();
                    $.jBox.prompt("导入成功", "系统温馨提示", "info", {
                        closed: function () {
                            parent.location.href = contextPath + "/employee/employee_employeeList" + webtype
                        }
                    });
                } else {
                    $.jBox.closeTip();
                    $.jBox.prompt(data.msg, "系统温馨提示", "info", {
                        closed: function () {
                            parent.location.href = contextPath + "/employee/employee_employeeList" + webtype
                        }
                    });
                }

            }
        });
    }

6、《contextPath + “/employee/employee_importExcel” + webtype》实现类（java）

public void importExcel() throws Exception {
        BaseExcelImportUtil<EmployeeInfo, EmployeeInfoService> baseExcelImportUtil = new BaseExcelImportUtil<EmployeeInfo, EmployeeInfoService>() {
            @Override
            protected void initService() {
                this.service = employeeInfoService;
            }

            @Override
            protected void processToDataBase(List<EmployeeInfo> employeeInfos) throws Exception {
                    this.service.batchInsert(employeeInfos);
            }
        };
        //以下属性中，是可以通过导出的表格修改后导入去修改员工信息的：员工ID---至--->开户银行行号是普通字符串文本类型，
        //员工标签添加的是员工标签id，通过员工标签管理查看，用"，"分割。
        ExcelHeaderRelationship excelHeaderRelationship = new ExcelHeaderRelationship();
        excelHeaderRelationship
                .setRelation("员工ID", "employeeId")
                .setRelation("姓名", "employeeName")
                .setRelation("身份证号码", "cardid")
                .setRelation("户口", "hukou")
                .setRelation("毕业学校", "gradutedSchool")
                .setRelation("所学专业", "major")
                .setRelation("联系方式", "mobile")
                .setRelation("户籍地址", "householdAddress")
                .setRelation("联系地址", "employeeAdress")
                .setRelation("银行卡号", "cardNumber")
                .setRelation("开户银行", "openingBank")
                .setRelation("开户银行行号", "openingBankNumber")
                .setRelation("员工专业结构", "professionalStructureId")
                .setRelation("员工标签", "employeeLabelId")
                .setRelation("明日之星推荐人", "referrerId");
        try {
            baseExcelImportUtil.importExcel(getServletRequest(), EmployeeInfo.class, excelHeaderRelationship);
        } catch (Exception e) {
        e.printStackTrace();
        writeToAjaxE("导入失败", this);   
        }
        writeToAjaxS("导入成功", this);
    }

7、this.service.batchInsert(employeeInfos);实现类（java）。

@Override
    @Transactional(rollbackFor = Exception.class)
    public void batchInsert(List<EmployeeInfo> employeeInfos) throws TimeCardServiceException {
        List<EmployeeInfo> updateList = new LinkedList<>();
        List<EmployeeInfo> insertList = new LinkedList<>();
        for (EmployeeInfo emp : employeeInfos) {
             EmployeeInfo employeeInfo = employeeInfoDAO.selectByPrimaryKey(emp.getEmployeeId());
            if (employeeInfo != null) {
                updateList.add(emp);
            } else {
                insertList.add(emp);
            }
        }
        this.batchInserts(insertList);
        this.batchUpdates(updateList);
    }

 void batchInserts(List<EmployeeInfo> employeeInfos) {
        allFieldIsNull(employeeInfos);
        employeeInfos.forEach(employeeInfo -> employeeInfoDAO.insertSelective(employeeInfo));
    }

 void batchUpdates(List<EmployeeInfo> employeeInfos) {
 	  allFieldIsNull(employeeInfos);
	  employeeInfos.forEach(employeeInfo -> employeeInfoDAO.updateByPrimaryKeySelective2(employeeInfo));
   }

//判断成员变量中是否有空替换为""
    public static boolean allFieldIsNull(List<EmployeeInfo> employeeInfos) {
        for (EmployeeInfo employeeInfo : employeeInfos) {
            try {
                for (Field field : employeeInfo.getClass().getDeclaredFields()) {
                    field.setAccessible(true);
                    if (field.get(employeeInfo) == null) {
                        field.set(employeeInfo, "");
                    }
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

8、BaseExcelImportUtil工具类。

public abstract class BaseExcelImportUtil<T, K> extends BaseAction {
    protected static final String OFFICE_EXCEL_2003_POSTFIX = "xls";
    protected static final String OFFICE_EXCEL_2010_POSTFIX = "xlsx";
    protected Logger logger = LoggerFactory.getLogger(BaseExcelImportUtil.class);
    protected TreeMap<Integer, String> headerSort;
    protected ExcelHeaderRelationship headerRelationship;
    protected Class tClass;
    protected K service;

    protected abstract void initService() throws ParseException;

    protected abstract void processToDataBase(List<T> tList) throws Exception;

    public List<T> importExcel(HttpServletRequest servletRequest, Class<T> clazz, ExcelHeaderRelationship excelHeaderRelationship) throws Exception {
        headerRelationship = excelHeaderRelationship;
        this.tClass = clazz;
        List<T> ts = new ArrayList<>();
        if (servletRequest instanceof StrutsRequestWrapper) {
            MultiPartRequestWrapper multiPartRequestWrapper = (MultiPartRequestWrapper) servletRequest;
            Enumeration<String> fileParameterNames = multiPartRequestWrapper.getFileParameterNames();
            while (fileParameterNames.hasMoreElements()) {
                String s = fileParameterNames.nextElement();
                File[] files = multiPartRequestWrapper.getFiles(s);
                String[] fileNames = multiPartRequestWrapper.getFileNames(s);
                if (files != null) {
                    for (int i = 0; i < files.length; i++) {
                        File file = files[i];
                        FileInputStream fileInputStream = null;
                        try {
                            fileInputStream = new FileInputStream(file);
                        } catch (FileNotFoundException e) {
                            e.printStackTrace();
                        }
                        process(fileInputStream, fileNames[i], ts);
                        logger.info("解析Excel文件完成，文件共包含{}条有效数据。", ts.size());
                        /**
                         * 保存到数据库，需自定义实现
                         */
                        initService();
                        processToDataBase(ts);
                    }
                }
            }
        } else {
            MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) servletRequest;
            Iterator<String> iter = multiRequest.getFileNames();
            while (iter.hasNext()) {
                MultipartFile multipartFile = multiRequest.getFile(iter.next());
                String fileName = multipartFile.getOriginalFilename();
                if (fileName == null || fileName.trim().equals("")) {
                    continue;
                }
                Integer index = fileName.lastIndexOf("\\");
                String newStr = "";
                if (index > -1) {
                    newStr = fileName.substring(index + 1);
                } else {
                    newStr = fileName;
                }
                if (!newStr.equals("")) {
                    fileName = newStr;
                }
                InputStream inputStream = multipartFile.getInputStream();
                /**
                 * 解析Excel
                 */
                process(inputStream, fileName, ts);
                logger.info("解析Excel文件完成，文件共包含{}条有效数据。", ts.size());
                /**
                 * 保存到数据库，需自定义实现
                 */
                initService();
                processToDataBase(ts);
            }
        }
        return new ArrayList<>();
    }

    protected void process(InputStream inputStream, String fileName, List<T> ts) throws IOException {
        if (fileName.contains(OFFICE_EXCEL_2010_POSTFIX)) {
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
            processReal(ts, xssfWorkbook);
        } else if (fileName.contains(OFFICE_EXCEL_2003_POSTFIX)) {
            HSSFWorkbook hssfWorkbook = new HSSFWorkbook(inputStream);
            processReal(ts, hssfWorkbook);
        } else {
            return;
        }
    }

    private void processReal(List<T> ts, Workbook workbook) {
        int sheetsNum = workbook.getNumberOfSheets();
        for (int currentNum = 0; currentNum < sheetsNum; currentNum++) {
            Optional.ofNullable(workbook.getSheetAt(currentNum)).ifPresent(
                    sheet -> {
                        initHeaderSort(sheet.getRow(sheet.getFirstRowNum()));
                        for (int rowNum = sheet.getFirstRowNum() + 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
                            Optional.ofNullable(sheet.getRow(rowNum)).ifPresent(
                                    row -> {
                                        T t = newInstance();
                                        Map<String, String> valueMap = new HashMap<>();
                                        for (int currentCellNum = (int) row.getFirstCellNum(); currentCellNum <= (int) row.getLastCellNum(); currentCellNum++) {
                                            Optional.ofNullable(row.getCell(currentCellNum)).ifPresent(
                                                    xssfCell -> {
                                                        /**
                                                         * 通过cell的index获取表头名字
                                                         */
                                                        String headerName = this.headerSort.get(xssfCell.getColumnIndex());
                                                        /**
                                                         * 获取表头名字对应的对象属性名
                                                         */
                                                        if(StringUtils.isNotBlank(headerName)){
                                                            String headerBeanName = headerRelationship.get(headerName);
                                                            /**
                                                             * 获取cell值
                                                             */
                                                            String cellValue = getCellValue(xssfCell);
                                                            valueMap.put(headerBeanName, cellValue);
                                                        }

                                                    }
                                            );
                                        }
                                        try {
                                            setValue(t, valueMap);
                                        } catch (Exception e) {
                                            e.printStackTrace();
                                        }
                                        ts.add(t);
                                    }
                            );
                        }
                    }
            );
        }
    }

    protected void initHeaderSort(Row row) {
        this.headerSort = new TreeMap<>();
        for (int currentNum = (int) row.getFirstCellNum(); currentNum <= (int) row.getLastCellNum(); currentNum++) {
            Optional.ofNullable(row.getCell(currentNum)).ifPresent(
                    xssfCell -> {
                        this.headerSort.put(xssfCell.getColumnIndex(), getCellValue(xssfCell));
                    }
            );
        }
    }

    protected String getCellValue(Cell cell) {
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                Date date = cell.getDateCellValue();
                return String.valueOf(date.getTime());
            } else if (cell.getCellStyle().getDataFormat() == 58) {
                Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue());
                return String.valueOf(date.getTime());
            }
        }
        cell.setCellType(cell.CELL_TYPE_STRING);
        return String.valueOf(cell.getStringCellValue());
    }

    protected T newInstance() {
        try {
            return (T) this.tClass.newInstance();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    protected T setValue(T t, Map<String, String> valueMap) throws Exception {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
        Class clazz = t.getClass();
        Arrays.stream(clazz.getDeclaredFields()).forEach(
                field -> {
                    field.setAccessible(true);
                    String name = field.getName();
                    String value = valueMap.get(name);
                    String fieldClassName = field.getType().getSimpleName();
                    try {
                        if (fieldClassName.equalsIgnoreCase("String")) {
                            field.set(t, StringUtils.isNotBlank(value) ? value : "");
                        } else if (fieldClassName.equalsIgnoreCase("boolean")) {
                            field.set(t, true);
                        } else if (fieldClassName.equalsIgnoreCase("int") || fieldClassName.equals("Integer")) {
                            field.set(t, StringUtils.isNotBlank(value) ? Integer.parseInt(value) : null);
                        } else if (fieldClassName.equalsIgnoreCase("double")) {
                            field.set(t, StringUtils.isNotBlank(value) ? Double.parseDouble(value) : null);
                        } else if (fieldClassName.equalsIgnoreCase("long")) {
                            field.set(t, StringUtils.isNotBlank(value) ? Long.parseLong(value) : null);
                        } else if (fieldClassName.equalsIgnoreCase("BigDecimal")) {
                            field.set(t, StringUtils.isNotBlank(value) ? BigDecimal.valueOf(Long.parseLong(value)) : null);
                        } else if (fieldClassName.equalsIgnoreCase("float")) {
                            field.set(t, StringUtils.isNotBlank(value) ? Float.parseFloat(value) : null);
                        } else if (fieldClassName.equalsIgnoreCase("date")) {
//                            field.set(t, StringUtils.isNotBlank(value) ?  new Date(Long.parseLong(value)): null);
                            field.set(t, StringUtils.isNotBlank(value) ? dateFormat.parse(value) : null);
                        } else {
                            logger.info("不支持的数据类型：{}:{}", name, fieldClassName);
                        }
                    } catch (IllegalAccessException | ParseException e) {
                        e.printStackTrace();
                    }
                }
        );
        return t;
    }
}