摘要：基于編程思想，在框架下，將邏輯復(fù)雜的代碼放在層下，層只負(fù)責(zé)調(diào)用。

基于MVC編程思想，在springMVC框架下，將邏輯復(fù)雜的代碼放在manager層下，controller層只負(fù)責(zé)調(diào)用manager。
(注：前端使用jQuery-file-upload插件)

controller層

@RequestMapping(value = "import_cust_user")
@ResponseBody
public String importCustUser(HttpSession session, @RequestParam("uploadFiles") MultipartFile excelFile,
    String brandCode) {    //uploadFiles：應(yīng)與前端jsp中
    LOG.info("開始導(dǎo)入用戶信息：brandCode={}", brandCode);
    JSONObject result = null;
    try {
            result = custUserManager.ImportCustUser(excelFile, brandCode, loginName);
        } catch (Exception e) {
            LOG.error("導(dǎo)入用戶失敗", e);
            result.put("result", "failed");
        }
        return result.toJSONString();
}

manager層

//電話號碼為空
private static final String IMPORT_NO_CONTENT = "電話號碼為空";
//電話號已存在
private static final String IMPORT_EXISTED_DATA = "電話號碼已存在";
//無效信息(手機(jī)號不滿足11位、手機(jī)號不為阿拉伯?dāng)?shù)字)
private static final String IMPORT_INVALID_DATA = "電話號碼必須為11位阿拉伯?dāng)?shù)字";

/**導(dǎo)入的定制用戶的列*/
private static final String[] importCustUserColumns = {"電話", "姓名", "備注"};
/**導(dǎo)出的定制用戶的列*/
private static final String[] exportCustUserColumns = {"電話", "姓名", "備注", "失敗原因"};

@Override
public JSONObject ImportCustUser(MultipartFile excelFile, String brandCode, String loginName) throws Exception {
    LOG.info("進(jìn)入導(dǎo)入用戶信息:brandCode={}", brandCode);
    // 獲取解析excel的wb
    XSSFWorkbook wb = new XSSFWorkbook(excelFile.getInputStream());
    // 返回json
    JSONObject result = new JSONObject();
    
    // 校驗文件頭部信息是否匹配
    if (checkExcelTitles(wb,importCustUserColumns,result)) {
        
        // 校驗文件內(nèi)容是否有錯誤內(nèi)容
        Map map = checkExcel(wb, brandCode, loginName);
        
        //分別獲取正確和錯誤的的用戶對象list
        @SuppressWarnings("unchecked")
        List importCustUserList = (List) map.get("importCustUserList");

        @SuppressWarnings("unchecked")
        List errorUserList = (List) map.get("errorUserList");

        // 不為空則批量導(dǎo)入
        if (!CollectionUtil.isEmpty(importCustUserList)) {
            custUserInfoService.importUserInfo(importCustUserList);
            result.put("successCount", importCustUserList.size());    //成功導(dǎo)入的條數(shù)
        } else {
            result.put("successCount", 0);
        }
        
        // 不為空則創(chuàng)建導(dǎo)入失敗的文件
        if (!CollectionUtil.isEmpty(errorUserList)) {
            //創(chuàng)建導(dǎo)入失敗的excel對象
            XSSFWorkbook errorWb = createExportContent(errorUserList);
            //創(chuàng)建字節(jié)流
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            //將excel對象寫入字節(jié)流
            errorWb.write(bos);
            
            //設(shè)置文件后綴，將文件上傳至服務(wù)器并返回鏈接（有待學(xué)習(xí)??！這里用的公司寫好的工具類）
            String suffix = ".xlsx";
            String errorUrl = fileUploadService.toUpFile(bos.toByteArray(), FileUpLoadEnum.file, suffix);
            
            result.put("errorCount", errorUserList.size());    //導(dǎo)入失敗的條數(shù)
            result.put("errorUrl", errorUrl);                  //下載鏈接

        } else {
            result.put("errorCount", 0);
        }
        result.put("result", "success");
    } else {
        result.put("result", "failed");
    }
    return result; 
}

//校驗文件頭部信息是否匹配
private boolean checkExcelTitles(XSSFWorkbook wb, String[] titles , JSONObject result) {
    
    if (null == wb.getSheetAt(0).getRow(0) || ExcelUtil.isExistTitles(wb, titles)) {
        result.put("failMsg", "請使用模版進(jìn)行導(dǎo)入！");
        return false;
    } else {
        XSSFRow row = wb.getSheetAt(0).getRow(1);
        //判斷第一行是否有數(shù)據(jù)
        if (row == null) { 
            result.put("failMsg", "導(dǎo)入表格為空，請?zhí)顚懲晷畔⒑笾匦聦?dǎo)入！");
            return false;
        }
        
        //有空格的空白行也視為表格為空
        boolean flag = false;
        for (int i = 0; i < importCustUserColumns.length; i++) {
            if (row.getCell(i) != null && StringUtils.isNotEmpty(ExcelUtil.getStringCellValue(row.getCell(i)))) {
                flag = true;
                break;
            }
        }
        if (!flag) {    // 判斷第一行是否有數(shù)據(jù)
            result.put("failMsg", "導(dǎo)入表格為空，請?zhí)顚懲晷畔⒑笾匦聦?dǎo)入！");
            return false;
        }
        
        //大于5000條不允許導(dǎo)入
        int rowSum = wb.getSheetAt(0).getLastRowNum();
        if (rowSum > 5000) {     // 判斷數(shù)據(jù)是否大于5000條
            result.put("failMsg", "電話信息條數(shù)超過5000條，請刪減后重新導(dǎo)入！");
            return false;
        }
        return true;
    }
}

//檢查文件內(nèi)容是否有誤
private Map checkExcel(XSSFWorkbook wb, String brandCode, String loginName) throws Exception {
        
    Map map = new HashMap();
    
    // 不可導(dǎo)入的List
    List errorUserList = new ArrayList();
    // 可成功導(dǎo)入的List
    List importCustUserList = new ArrayList();

    // 獲得第一張表單
    XSSFSheet sheet = wb.getSheetAt(0);
    // 獲得總行數(shù)
    int rowSum = sheet.getLastRowNum();
    // 獲得該品牌下所有用戶信息
    List custUserList = custUserInfoService.getCustUserByBrandCode(brandCode);
    // 手機(jī)號List(用于判斷本表單手機(jī)號是否重復(fù))
    List mobileList = new ArrayList();
    
    for (int i = 1; i <= rowSum; i++) {
        if (null == sheet.getRow(i)) {
            continue;
        }
        // 獲取電話
        String mobileNo = null;
        if (null != sheet.getRow(i).getCell((short) 0)) {
            mobileNo = ExcelUtil.getStringCellValue(sheet.getRow(i).getCell((short) 0));
            if (mobileNo.contains("E")) {    //當(dāng)cell不是文本格式時，手機(jī)號會被會被轉(zhuǎn)換成科學(xué)計數(shù)，需要用以下方法轉(zhuǎn)回為手機(jī)號
                DecimalFormat df = new DecimalFormat("0");
                mobileNo = df.format(sheet.getRow(i).getCell((short) 0).getNumericCellValue());
            }
        }
        //獲取姓名
        ...
        //獲取備注
        ...
        // 校驗數(shù)據(jù)
        String errorReason = "";
        if (StringUtils.isEmpty(mobileNo)) {    //判斷手機(jī)號是否為空
            errorReason = IMPORT_NO_CONTENT + ";";
        } else if (mobileNo.length() != 11 || !isNotNumeric(mobileNo)) {    //判斷手機(jī)號是否輸入合法
            errorReason += IMPORT_INVALID_DATA + ";";
        }
        if (mobileList.contains(mobileNo) || custUserList.contains(mobileNo)) {    //判斷是否與已存在的用戶電話重復(fù)
            errorReason += IMPORT_EXISTED_DATA + ";";
        } else {
            if (null != mobileNo){
                mobileList.add(mobileNo);
            }
        }
        
        // 創(chuàng)建可導(dǎo)入的list和不可導(dǎo)入的list
        if (!StringUtils.isEmpty(errorReason)) {
            errorUserList.add(buildErrorUser(userName, mobileNo, remark, errorReason));
        } else {
            importCustUserList.add(buildUser(brandCode, userName, mobileNo, remark, loginName));
        }
    }
    map.put("errorUserList", errorUserList);
    map.put("importCustUserList", importCustUserList);
    return map;
}

//創(chuàng)建導(dǎo)入失敗的excel文件
private XSSFWorkbook createExportContent(List errorUserList) {
    // 創(chuàng)建XSSFWorkbook
    XSSFWorkbook wb = new XSSFWorkbook();

    // 創(chuàng)建表單并設(shè)置cell寬度
    XSSFSheet currentSheet = wb.createSheet("Sheet1");
    currentSheet.setDefaultColumnWidth(20);

    // 創(chuàng)建表頭
    createTitle(currentSheet,exportCustUserColumns);
    
    // 創(chuàng)建cellStyle
    XSSFCellStyle style = wb.createCellStyle();
    style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);    //設(shè)置cell底色
    style.setFillForegroundColor(new XSSFColor(Color.red));

    // 插入表內(nèi)容
    int currentRow = 1;
    Row row = currentSheet.getRow(0);
    Cell cell = null;
    for (CustUserImportError errorUser : errorUserList) {
        int cellIndex = 0;
        // 行
        row = currentSheet.createRow(currentRow);
        // 列
        cell = row.createCell(cellIndex++);
        cell.setCellValue(errorUser.getMobileNo());

        cell = row.createCell(cellIndex++);
        cell.setCellValue(errorUser.getUserName());

        cell = row.createCell(cellIndex++);
        cell.setCellValue(errorUser.getRemark());

        cell = row.createCell(cellIndex++);
        cell.setCellValue(errorUser.getErrorReason());
        // 最后一個單元格設(shè)置樣式
        cell.setCellStyle(style);

        currentRow += 1;
    }
    return wb;
}

//創(chuàng)建導(dǎo)入失敗的表頭
private void createTitle(XSSFSheet sheet, String[] titles) {
    Row row = sheet.createRow(0);
    for (int i = 0; i < titles.length; i++) {
        Cell cell = row.createCell(i);
        cell.setCellValue(titles[i]);
    }
}

到這里基本完成了導(dǎo)入工作，導(dǎo)出失敗的excel文件如下：
因為在做導(dǎo)入工作時，已經(jīng)將失敗的文件上傳至服務(wù)器并返回了鏈接，所以這里只要寫如何將文件寫到本地
同樣式剛才的controller層

@RequestMapping(value = "download_excel")
public void downloadExcel(String downloadUrl, HttpServletRequest request, HttpServletResponse response)
        throws IOException {
    LOG.info("開始下載導(dǎo)入失敗的用戶信息：downloadUrl={}", downloadUrl);
    
    InputStream in = null;        //輸入流
    OutputStream out = null;      //輸出流

    try {
        //需要下載的文件鏈接
        URL url = new URL(downloadUrl);
        //建立連接
        URLConnection conn = url.openConnection();
        //根據(jù)鏈接獲得輸入流
        in = conn.getInputStream();
        //文件名
        String fileName = FAILED_FILE_NAME;
        //設(shè)置響應(yīng)
        response.setContentType("application/x-excel");
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename="
                    + new String(fileName.getBytes("GB2312"), "ISO-8859-1"));
        //獲得輸出流
        out = response.getOutputStream();
        byte[] data = new byte[1024];
        int len = 0;
        while ((len = in.read(data, 0, data.length)) != -1) {
            out.write(data, 0, len);
        }
    } catch (Exception e) {
        LOG.error("下載文件異常", e);
    } finally {
        if (in != null) {
            in.close();
        }
        if (out != null) {
            out.close();
        }
    }
}