java实现excel导入，并将错误的以链接形式返回，可供下载

摘要：基于编程思想，在框架下，将逻辑复杂的代码放在层下，层只负责调用。

基于MVC编程思想，在springMVC框架下，将逻辑复杂的代码放在manager层下，controller层只负责调用manager。
(注：前端使用jQuery-file-upload插件)

controller层

@RequestMapping(value = "import_cust_user")
@ResponseBody
public String importCustUser(HttpSession session, @RequestParam("uploadFiles") MultipartFile excelFile,
    String brandCode) {    //uploadFiles：应与前端jsp中
    LOG.info("开始导入用户信息：brandCode={}", brandCode);
    JSONObject result = null;
    try {
            result = custUserManager.ImportCustUser(excelFile, brandCode, loginName);
        } catch (Exception e) {
            LOG.error("导入用户失败", e);
            result.put("result", "failed");
        }
        return result.toJSONString();
}

manager层

//电话号码为空
private static final String IMPORT_NO_CONTENT = "电话号码为空";
//电话号已存在
private static final String IMPORT_EXISTED_DATA = "电话号码已存在";
//无效信息(手机号不满足11位、手机号不为阿拉伯数字)
private static final String IMPORT_INVALID_DATA = "电话号码必须为11位阿拉伯数字";

/**导入的定制用户的列*/
private static final String[] importCustUserColumns = {"电话", "姓名", "备注"};
/**导出的定制用户的列*/
private static final String[] exportCustUserColumns = {"电话", "姓名", "备注", "失败原因"};

@Override
public JSONObject ImportCustUser(MultipartFile excelFile, String brandCode, String loginName) throws Exception {
    LOG.info("进入导入用户信息:brandCode={}", brandCode);
    // 获取解析excel的wb
    XSSFWorkbook wb = new XSSFWorkbook(excelFile.getInputStream());
    // 返回json
    JSONObject result = new JSONObject();
    
    // 校验文件头部信息是否匹配
    if (checkExcelTitles(wb,importCustUserColumns,result)) {
        
        // 校验文件内容是否有错误内容
        Map map = checkExcel(wb, brandCode, loginName);
        
        //分别获取正确和错误的的用户对象list
        @SuppressWarnings("unchecked")
        List importCustUserList = (List) map.get("importCustUserList");

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

        // 不为空则批量导入
        if (!CollectionUtil.isEmpty(importCustUserList)) {
            custUserInfoService.importUserInfo(importCustUserList);
            result.put("successCount", importCustUserList.size());    //成功导入的条数
        } else {
            result.put("successCount", 0);
        }
        
        // 不为空则创建导入失败的文件
        if (!CollectionUtil.isEmpty(errorUserList)) {
            //创建导入失败的excel对象
            XSSFWorkbook errorWb = createExportContent(errorUserList);
            //创建字节流
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            //将excel对象写入字节流
            errorWb.write(bos);
            
            //设置文件后缀，将文件上传至服务器并返回链接（有待学习！！这里用的公司写好的工具类）
            String suffix = ".xlsx";
            String errorUrl = fileUploadService.toUpFile(bos.toByteArray(), FileUpLoadEnum.file, suffix);
            
            result.put("errorCount", errorUserList.size());    //导入失败的条数
            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", "请使用模版进行导入！");
        return false;
    } else {
        XSSFRow row = wb.getSheetAt(0).getRow(1);
        //判断第一行是否有数据
        if (row == null) { 
            result.put("failMsg", "导入表格为空，请填写完信息后重新导入！");
            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) {    // 判断第一行是否有数据
            result.put("failMsg", "导入表格为空，请填写完信息后重新导入！");
            return false;
        }
        
        //大于5000条不允许导入
        int rowSum = wb.getSheetAt(0).getLastRowNum();
        if (rowSum > 5000) {     // 判断数据是否大于5000条
            result.put("failMsg", "电话信息条数超过5000条，请删减后重新导入！");
            return false;
        }
        return true;
    }
}

//检查文件内容是否有误
private Map checkExcel(XSSFWorkbook wb, String brandCode, String loginName) throws Exception {
        
    Map map = new HashMap();
    
    // 不可导入的List
    List errorUserList = new ArrayList();
    // 可成功导入的List
    List importCustUserList = new ArrayList();

    // 获得第一张表单
    XSSFSheet sheet = wb.getSheetAt(0);
    // 获得总行数
    int rowSum = sheet.getLastRowNum();
    // 获得该品牌下所有用户信息
    List custUserList = custUserInfoService.getCustUserByBrandCode(brandCode);
    // 手机号List(用于判断本表单手机号是否重复)
    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")) {    //当cell不是文本格式时，手机号会被会被转换成科学计数，需要用以下方法转回为手机号
                DecimalFormat df = new DecimalFormat("0");
                mobileNo = df.format(sheet.getRow(i).getCell((short) 0).getNumericCellValue());
            }
        }
        //获取姓名
        ...
        //获取备注
        ...
        // 校验数据
        String errorReason = "";
        if (StringUtils.isEmpty(mobileNo)) {    //判断手机号是否为空
            errorReason = IMPORT_NO_CONTENT + ";";
        } else if (mobileNo.length() != 11 || !isNotNumeric(mobileNo)) {    //判断手机号是否输入合法
            errorReason += IMPORT_INVALID_DATA + ";";
        }
        if (mobileList.contains(mobileNo) || custUserList.contains(mobileNo)) {    //判断是否与已存在的用户电话重复
            errorReason += IMPORT_EXISTED_DATA + ";";
        } else {
            if (null != mobileNo){
                mobileList.add(mobileNo);
            }
        }
        
        // 创建可导入的list和不可导入的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;
}

//创建导入失败的excel文件
private XSSFWorkbook createExportContent(List errorUserList) {
    // 创建XSSFWorkbook
    XSSFWorkbook wb = new XSSFWorkbook();

    // 创建表单并设置cell宽度
    XSSFSheet currentSheet = wb.createSheet("Sheet1");
    currentSheet.setDefaultColumnWidth(20);

    // 创建表头
    createTitle(currentSheet,exportCustUserColumns);
    
    // 创建cellStyle
    XSSFCellStyle style = wb.createCellStyle();
    style.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND);    //设置cell底色
    style.setFillForegroundColor(new XSSFColor(Color.red));

    // 插入表内容
    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());
        // 最后一个单元格设置样式
        cell.setCellStyle(style);

        currentRow += 1;
    }
    return wb;
}

//创建导入失败的表头
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]);
    }
}

到这里基本完成了导入工作，导出失败的excel文件如下：
因为在做导入工作时，已经将失败的文件上传至服务器并返回了链接，所以这里只要写如何将文件写到本地
同样式刚才的controller层

@RequestMapping(value = "download_excel")
public void downloadExcel(String downloadUrl, HttpServletRequest request, HttpServletResponse response)
        throws IOException {
    LOG.info("开始下载导入失败的用户信息：downloadUrl={}", downloadUrl);
    
    InputStream in = null;        //输入流
    OutputStream out = null;      //输出流

    try {
        //需要下载的文件链接
        URL url = new URL(downloadUrl);
        //建立连接
        URLConnection conn = url.openConnection();
        //根据链接获得输入流
        in = conn.getInputStream();
        //文件名
        String fileName = FAILED_FILE_NAME;
        //设置响应
        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();
        }
    }
}