excel表格导入功能

1、表格导入方法

（1）首先jsp文件中加入对应的HTML样式，并调用对应js中的方法

 <em class="separ"></em>
            <a class="txt-all" onclick="pageObj.import()"><i class="iconfont icon-up"></i>导入</a>
            <em class="separ"></em>
            <a class="txt-all" onclick="ExcelUtil.export('${pageTitle}.xls', pageObj.getDataGrid())">
                <i class="iconfont icon-down"></i>导出</a>
            <em class="separ"></em>


//这是设置点击导入按钮弹出的弹框的表单和对应方法
    <div class="invisible">
        <iframe id="importIframe" name="importIframe"></iframe>
        <form id="importForm" target="importIframe" method="post" enctype="multipart/form-data"
              action="${staticContextPath}/import/importExcel.json">
            <input type="file" name="importFile" id="importFile"
                   onchange="window.pageObj.importForm()" accept=".xls,.xlsx"/>
            <input type="hidden" name="type" value="publishLicense"/>
            <input type="hidden" name="pageObj" value="pageObj"/>
            <input type="hidden" name="dataGridMethod" value="getDataGrid"/>
        </form>
    </div>

（2）js文件中代码段

getDataGrid: function () {
            var $grid = $("#grid");
            if (!$grid[0].grid) {
                $grid.jqGrid({
                    url: App["contextPath"] + "/bs/publishLicense/getPublishLicensePage.json",
                    ajaxGridOptions: {
                        contentType: "application/json"
                    },
                    autoWidthPadding: 25,
                    autoHeightPadding: 90,
                    shrinkToFit: true,//自适应都挤在一块
                    multiselect: false,
                    multiboxonly: false,
                    enabledLoad: false,
                    pager: "#pager",
                    colModel: [
                        {name: "id",label: "主键",hidden: true},
                        {
                            name: "operate", label: "操作", width: 80, align: 'center',
                            formatter: function (cellContent, options, rowData) {
                                var html = '';
                                html += "<a class='mr5' οnclick='pageObj.edit(\"" + rowData.id + "\")'>编辑</a>";
                                html += "<a class='mr5' οnclick='pageObj.delete(this, \"" + rowData.id + "\")'>删除</a>";
                                return html;
                            }
                        },
                        {name:'name', label:'出版公司名称',align:'center'},
                        {name: 'publishLicense', label:'经营许可证',align:'center'},
                        {name:'phone', label:'手机号码', align:'center'}
                    ]
                });
            }
            return $grid;
        },
        import: function () {
            var fileObj = document.getElementById('importFile');
            fileObj.value = ''; // 每次先将file值设置为空值
            fileObj.click();
        },
        importForm: function () {
            document.getElementById('importForm').submit();
            $('body').block({
                message: '<h1>正在导入，请稍候...</h1>',
                css: {
                    width: '',
                    left: '40%',
                    border: 'none',
                    padding: '15px',
                    backgroundColor: '#000',
                    '-webkit-border-radius': '10px',
                    '-moz-border-radius': '10px',
                    opacity: .5,
                    color: '#fff'
                },
                overlayCSS: {backgroundColor: '#000', opacity: 0.4}
            });
        },
    });

第二步注意的几点：1、getDataGrid方法中

var $grid = $("#grid");
 pager: "#pager",

两个属性的绿色的值要与jsp中的值对应

2、import和importForm 方法中绿色的值和jsp中相应字段值对应

import: function () {
    var fileObj = document.getElementById('importFile');
    
importForm: function () {
    document.getElementById('importForm').submit();

这次用AbstractImportHandler抽象类先封装导入方法和获取excel表格中行列值的方法

public abstract class AbstractImportHandler {


    protected Logger logger = LoggerFactory.getLogger(getClass());

    /**
     * 导入
     *
     * @param sheet 工作簿
     * @param user  user
     * @return 错误消息
     */
    public abstract String doHandler(Sheet sheet, UserVOExt user);

    protected String getCell(Row row, int colIdx, String errMsg) {
        return Optional.ofNullable(getCellValue(row.getCell(colIdx))).orElseThrow(() -> new BusinessException(errMsg));
    }

    protected String getCellValue(Cell cell) {
        String cellValue = null;

        if (cell != null){
            if (CellType.STRING == cell.getCellTypeEnum()) {
                cellValue = cell.getStringCellValue();
            }
//            else (CellType.NUMERIC == cell.getCellTypeEnum()){
//                cellValue = cell.getNumericCellValue();
//            }
        }
        return cellValue;
    }

}

在具体实现类中重写doHandler方法

@Component
@ImportHandler(type = "publishLicense")
public class PublishLicenseImportHandler extends AbstractImportHandler {

    /**
     * 书店索引值
     */
    private final static Integer  STORE_NAME = 0;
    /**
     * 出版物经营许可证索引值
     */
    private final static Integer  PLNUM_INDEX = 1;
    /**
     * 手机号索引值
     */
    private final static Integer  PHONE_INDEX = 2;

    @Autowired
    private IPublishLicenseService publishLicenseService;

    @Override
    public String doHandler(Sheet sheet, UserVOExt user) {
        // 校验数据，存储数据，报错信息
        //Set<String> corpSet = new HashSet<>(); Set<String> cdrSet = new HashSet<>();
        List<String> plNumList = new ArrayList<>();
        List<String> phoneList = new ArrayList<>();
        List<PublishLicenseVOExt> records = new ArrayList<>();
        String errorMessage = "";
        //0行是标题 从第一行开始
        for (int i = 1; i <= sheet.getLastRowNum(); i++) {
            try{
                Row row = sheet.getRow(i);
                String storeName = getCell(row, STORE_NAME, "书店名称不能为空");
                String publishLicense = getCell(row, PLNUM_INDEX, "出版物经营许可证不能为空");
                String phone = convertCellValueToString(row.getCell(PHONE_INDEX),row);
                PublishLicenseVOExt voExt = new PublishLicenseVOExt();
                voExt.setName(storeName);
                voExt.setPublishLicense(publishLicense);
                voExt.setPhone(phone);
                publishLicenseService.checkPublishLicense(publishLicense, phone);
                records.add(voExt);
            }catch (BusinessException e){
                e.printStackTrace();
                errorMessage = String.format("导入失败！第%d行: %s", i+1, e.getMessage());
                break;
            }
        }
        publishLicenseService.saveImport(records);
        return errorMessage;
    }
    private String convertCellValueToString(Cell cell, Row row) {
        return new DecimalFormat("#").format(cell.getNumericCellValue());
    }

}

特别说明该类 @ImportHandler(type = “publishLicense”）注解中type的值要与jsp文件中相应属性的值一定要一样，因为这里属于映射关系

（3）编写导入功能的接口

@Controller
@RequestMapping("/import")
public class ImportController extends BaseController {

    private final static String IMPORTS_PACKAGE_PATH = "com.jiujie.book.application.web.imports";

    /**
     * Excel载入公共入口
     *
     * @param file     导入文件
     * @param request  请求参数
     * @param response 响应参数
     * @throws IOException 异常
     */
    @RequestMapping(value = "/importExcel.json", method = RequestMethod.POST)
    public void importExcel(@RequestParam("importFile") MultipartFile file, HttpServletRequest request,
                            HttpServletResponse response) throws IOException {
        String type = request.getParameter("type");
        String pageObj = request.getParameter("pageObj");
        String dataGridMethod = request.getParameter("dataGridMethod");
        PrintWriter out = response.getWriter();
        if (!file.isEmpty()) {
            try {
                POIFSFileSystem fs = new POIFSFileSystem(file.getInputStream());
                HSSFWorkbook wb = new HSSFWorkbook(fs);
                Sheet sheet = wb.getSheetAt(0);
                String errorMessage = importRoute(type, sheet);

                if ("".equals(errorMessage)) {
                    out.println("<script>parent.TipsUtil.info('载入成功！');parent." + pageObj + "." + dataGridMethod + "().jqGrid('reloadGrid');parent.$('body').unblock();</script>");
                } else {
                    out.println("<script>parent.TipsUtil.error('" + errorMessage + "');parent.$('body').unblock();</script>");
                }
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
                if (StringUtils.isNotEmpty(e.getMessage()) && e.getMessage().contains("Your file appears not to be a valid OLE2 document")) {
                    out.println("<script>parent.TipsUtil.error('请使用标准导入模板来导入数据！');</script>");
                } else if (StringUtils.isNotEmpty(e.getMessage()) && e.getMessage().contains("The supplied data appears to be in the Office 2007+ XML")) {
                    out.println("<script>parent.TipsUtil.error('请使用下载的模版添加数据进行导入！');</script>");
                } else {
                    out.println("<script>parent.TipsUtil.error('导入的模板有误，请校验是否满足以下要求：<br/>" +
                            "'1、填写的单元格中不得包含计算公式；<br/>'+\n" +
                            "'2、导入的模板不能删除或添加列；<br/>'+\n" +
                            "'3、需要下拉选择的信息，只能是模板中的选择项。');</script>");
                }
            } finally {
                out.close();
            }
        }
    }

    /**
     * 导入处理类路由，通过模板类型映射到对应的处理类
     *
     * @param type  模板类型
     * @param sheet 导入文件Excel
     * @return 错误消息
     * @throws ClassNotFoundException 类未找到
     */
    private String importRoute(String type, Sheet sheet) throws ClassNotFoundException {
        AbstractImportHandler bean = (AbstractImportHandler) SpringContextUtil.getBean(getImportHandlerClass(type));
        return bean.doHandler(sheet, getCurrentUser());
    }

    /**
     * 遍历imports目录下，先匹配注解ImportHandler type的值
     * 如果没有再匹配类名，如：ImportClazzHandler
     *
     * @param type 导入文件类型
     * @return 类
     * @throws ClassNotFoundException 类未找到
     */
    private Class<?> getImportHandlerClass(String type) throws ClassNotFoundException {
        ConfigurationBuilder builder = new ConfigurationBuilder();
        builder.setUrls(ClasspathHelper.forPackage(IMPORTS_PACKAGE_PATH));
        builder.filterInputsBy(new FilterBuilder().include(IMPORTS_PACKAGE_PATH + ".*.class"));
        Set<Class<?>> classList = new Reflections(builder).getTypesAnnotatedWith(ImportHandler.class);

        for (Class<?> clazz : classList) {
            ImportHandler importHandler = clazz.getAnnotation(ImportHandler.class);
            if (importHandler.type().equals(type)) {
                return clazz;
            }
        }

        String className = "Import" + StringUtils.capitalize(type) + "Handler";
        return Class.forName(IMPORTS_PACKAGE_PATH + "." + className);
    }

}

通过流来读取文件，在调用importRoute方法，遍历文件的行列数据，第三个方法是用来确定上传文件和对应handler映射关系