java读Excel

于 2019-10-22 15:54:59 发布
阅读量150 收藏
点赞数
分类专栏： java excel
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/weixin_44855207/article/details/102684351
版权

public static void main(String[] args) {

private transient Service service;


private RoamingCardStatus roamingCardStatus = new RoamingCardStatus();

private String message;


private int successNum;

private int errorNum;



private final int maxSize = 1048576;

private final int thousand = 1000;

private int total;

private String postfix;









/**
 * 批量导入
 * @return String 成功失败标识
 */
public String batchImportSave() {
    Set<RoamingCardStatus> importSet = null;
    Set<String> importMsisdnSet = new HashSet<String>();
    /* 读取Excel中数据 */
    try {
        importSet = this.getExcelData(roamingCardStatus.getFile());
        if (null != getMessage() && !"".equals(getMessage())) {
            return ERROR;
        }
    } catch (Exception e) {
        if (null == getMessage() || "".equals(getMessage())) {
            setMessage(getText("info.read.failed"));
        }
        log.error("Failed to read the data in the Excel, please change into！" + e, e);
        return ERROR;
    }
    int cardCount = 0;
    try {
        cardCount = service.getCount(RoamingCardStatus.class, roamingCardStatus);
    } catch (BusinessException e1) {
        e1.printStackTrace();
    }
    total = importSet.size();
    if(total > 0){
        List<RoamingCardStatus> roamingCardStatusList = new ArrayList<RoamingCardStatus>(importSet);
        successNum = 0;
        errorNum = 0;
        BufferedReader responseReader = null;
        // 批量设置
        for(int i=0; i<roamingCardStatusList.size(); i++){
            responseReader = null;
            RoamingCardStatus roamingCardStatus = roamingCardStatusList.get(i);
            SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
            String str = df.format(new Date());
            //cardStatusNew.setUpdate_time(str);
            roamingCardStatus.setTime_stamp(str);
            try {
                int ss = 0;
                if(cardCount>0) {
                    ss = service.update("RoamingCardStatus.updateDeviceName", roamingCardStatus);
                }else {
                    ss = service.update("RoamingCardStatus.updatePaugoDeviceName", roamingCardStatus);
                }                                                  
                if(ss==1){
                    successNum++;
                }else{
                    errorNum++;
                }
                
            } catch (BusinessException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }finally {
                try {
                    /* 关闭流 */
                    if (responseReader != null) {
                        responseReader.close();
                    }
                } catch (IOException e) {
                    log.info("关闭流时出错! Cause: " + e, e);
                    errorNum++;
                }
            }
        }
        
        String msg = "";
        if(errorNum == 0){
            msg = getText("cardStatus.batchImport.operate_all.a_0_2");
        }else if(successNum == 0){
            msg = getText("cardStatus.batchImport.operate_all.a_0_1");
        }else{
            msg = getText("cardStatus.batchImport.operate_all.0_0");
            msg += successNum + getText("cardStatus.batchImport.operate_all.1") + errorNum +  getText("cardStatus.batchImport.operate_all.2");
        }
        setMessage(msg);
    }
    ActionContext.getContext().getSession().put("importMsisdn", importMsisdnSet.toString());
    roamingCardStatus.setBatchImportFlag("0");

    return SUCCESS;
}

/**
 * 读取Excel中数据
 * @return Set<String> Excel中数据
 */
private Set<RoamingCardStatus> getExcelData(File file) throws Exception {
    if (maxSize < file.length()) {
        setMessage(getText("info.beyond.1mb.error"));
        log.error("The Excel is greater than 1M, please change into！");
        throw new Exception("導入的Excel大於1M，請修改后導入！");
    }
    Set<RoamingCardStatus> importSet = new HashSet<RoamingCardStatus>();
    InputStream in = new FileInputStream(file);
    if("xls".equals(postfix)){
        importSet = getXlsData(in);
    }else if("xlsx".equals(postfix)){  
        importSet = getXlsxData(in);
    } 
    in.close();
    return importSet;
}

private Set<RoamingCardStatus> getXlsData(InputStream in) throws Exception {
    int sheetnum = 1;//wb.getNumberOfSheets(); 只有第一个sheet页是待导数据，别的sheet页不管
    String val;
    Boolean isTrue = true;
    Set<RoamingCardStatus> importSet = new HashSet<RoamingCardStatus>();
    int totalLength = 0; // 数据行数
    HSSFWorkbook wb = new HSSFWorkbook(in);  
    HSSFSheet sheet;
    HSSFRow row;
    HSSFCell cell = null;
    for (int i = 0; i < sheetnum; i++) {
        
        sheet = wb.getSheetAt(i);
        int max = sheet.getLastRowNum(); // 行索引

        /* 解决POI的BUG：getLastRowNum统计了空行，需要去掉 */
        for (int j = 0; j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if (null != row) {
                if (null == row.getCell(0) || "".equals(row.getCell(0).toString().trim())) {
                    max--;
                }
            } else {
                //max--;
            }
        }

        totalLength = totalLength + max + 1;

        row = sheet.getRow(0);
        if (max == 0) { // 除表头外，此Sheet页中无数据。表头分有，无数据两种情况
            if (null == row) { // 表头无数据
                --totalLength;
                continue;
            } else { // 表头有数据，判断是否是字段描述，若不是提示错误信息
                int flag = checkTop(cell, row, wb, i);
                if (flag != 1) {
                    log.error("First field description can not be modified！");
                    setMessage(getText("info.top.check"));
                } else {
                    --totalLength;
                }
            }
        } else { // Sheet中除表头外另有存在的数据
            if (null != row) { // 表头不为空，取表头数据
                int flag = checkTop(cell, row, wb, i);
                if (flag != 1) {
                    log.error("First field description can not be modified！");
                    setMessage(getText("info.top.check"));
                    isTrue = false;
                } else {
                    --totalLength;
                }
            } else { // 表头为空
                --totalLength;
            }

            if (isTrue) { // 读取表头以后的数据
                labelA: for (int y = 1; y <= max; y++) {
                    row = sheet.getRow(y);
                    if (null != row) { // 此行非空
                        String msisdn = "";
                        /* 卡号 */
                        cell = row.getCell(0);
                        HSSFCell deviceName = row.getCell(1);
                        if (null != cell) {
                            val = getCellValue(cell, wb.getSheetName(i), 0, 1);
                        } else {
                            val = "";
                        }
                        if (checkMsisdn(val)) {
                            msisdn = val.trim();
                        } else {
                            log.error("No." + (y + 1) + "The line number is not correct！");
                            setMessage("<span class=icon-error></span><span class=tips-red>第" + (y + 1) + "行門號不正確！請修改后上傳！</span>");
                            importSet = new HashSet<RoamingCardStatus>();
                            break labelA;
                        }
                        
                        if(!"".equals(msisdn)){
                            RoamingCardStatus tem = new RoamingCardStatus();
                            tem.setMsisdn(msisdn);
                            tem.setDevice_name(deviceName.getStringCellValue() == null ? "" : deviceName.getStringCellValue().trim());
                            importSet.add(tem);
                        }
                    } else {
                        --totalLength;
                    }
                }
            }
        }
        
        /* 除表头和空行外的数据条数判断，大于1000条则提示用户重新整理。 */
        if (totalLength == 0) {
            setMessage(getText("info.no.data.error"));
            log.error("The Excel data is 0, please fill in the import data！");
            throw new Exception("Excel中数据為0條，請填寫數據后導入！");
        } else if (totalLength > thousand) {
            setMessage(getText("cardStatus.beyond.1000.error"));
            log.error("Import data of more than 1000, please change into！");
            throw new Exception("導入的數據多於1000條，請修改后導入！");
        }
        
        //total = totalLength;
    }
    return importSet;
}

private Set<RoamingCardStatus> getXlsxData(InputStream in) throws Exception {
    int sheetnum = 1;//wb.getNumberOfSheets(); 只有第一个sheet页是待导数据，别的sheet页不管
    String val;
    Boolean isTrue = true;
    Set<RoamingCardStatus> importSet = new HashSet<RoamingCardStatus>();
    int totalLength = 0; // 数据行数
    XSSFWorkbook wb = new XSSFWorkbook(in);  
    XSSFSheet sheet;
    XSSFRow row;
    XSSFCell cell = null;
    for (int i = 0; i < sheetnum; i++) {
        
        sheet = wb.getSheetAt(i);
        int max = sheet.getLastRowNum(); // 行索引

        /* 解决POI的BUG：getLastRowNum统计了空行，需要去掉 */
        for (int j = 0; j <= sheet.getLastRowNum(); j++) {
            row = sheet.getRow(j);
            if (null != row) {
                if (null == row.getCell(0) || "".equals(row.getCell(0).toString().trim())) {
                    max--;
                }
            } else {
                //max--;
            }
        }

        totalLength = totalLength + max + 1;

        row = sheet.getRow(0);
        if (max == 0) { // 除表头外，此Sheet页中无数据。表头分有，无数据两种情况
            if (null == row) { // 表头无数据
                --totalLength;
                continue;
            } else { // 表头有数据，判断是否是字段描述，若不是提示错误信息
                int flag = checkTop_xlsx(cell, row, wb, i);
                if (flag != 1) {
                    log.error("First field description can not be modified！");
                    setMessage(getText("info.top.check"));
                } else {
                    --totalLength;
                }
            }
        } else { // Sheet中除表头外另有存在的数据
            if (null != row) { // 表头不为空，取表头数据
                int flag = checkTop_xlsx(cell, row, wb, i);
                if (flag != 1) {
                    log.error("First field description can not be modified！");
                    setMessage(getText("info.top.check"));
                    isTrue = false;
                } else {
                    --totalLength;
                }
            } else { // 表头为空
                --totalLength;
            }

            if (isTrue) { // 读取表头以后的数据
                labelA: for (int y = 1; y <= max; y++) {
                    row = sheet.getRow(y);
                    if (null != row) { // 此行非空
                        String msisdn = "";
                        /* 卡号 */
                        cell = row.getCell(0);
                        if (null != cell) {
                            val = getCellValue_xlsx(cell, wb.getSheetName(i), 0, 1);
                        } else {
                            val = "";
                        }
                        if (checkMsisdn(val)) {
                            msisdn = val.trim();
                        } else {
                            log.error("NO." + (y + 1) + "The line number is not correct！");
                            setMessage("<span class=icon-error></span><span class=tips-red>第" + (y + 1) + "行門號不正確！請修改后上傳！</span>");
                            importSet = new HashSet<RoamingCardStatus>();
                            break labelA;
                        }
                        
                        if(!"".equals(msisdn)){
                            RoamingCardStatus tem = new RoamingCardStatus();
                            tem.setMsisdn(msisdn);
                            importSet.add(tem);
                        }
                    } else {
                        --totalLength;
                    }
                }
            }
        }
        
        /* 除表头和空行外的数据条数判断，大于1000条则提示用户重新整理。 */
        if (totalLength == 0) {
            setMessage(getText("info.no.data.error"));
            log.error("The Excel data is 0, please fill in the import data！");
            throw new Exception("Excel中數據為0條，請填寫數據后導入！");
        } else if (totalLength > thousand) {
            setMessage(getText("cardStatus.beyond.1000.error"));
            log.error("Import data of more than 1000, please change into！");
            throw new Exception("導入的數據多於1000條，請修改后導入！");
        }
        
        //total = totalLength;
    }
    return importSet;
}

/**
 * 第一行数据校验
 * @return int 表头是否正确
 */
private int checkTop(HSSFCell cell, HSSFRow row, HSSFWorkbook wb, int i) throws Exception {
    String val;
    int flag = 0; // 标识第一行数据是否是表头，不是则按有效数据处理
    /* 卡号 */
    cell = row.getCell(0);
    val = getCellValue(cell, wb.getSheetName(i), 0, 1);
    if (getText("cardStatus.msisdn.no.modify").equals(val)) {// 判断是否为字段名
        ++flag;
    }
    return flag;
}

/**
 * 取得Excel单元格内数据
 * @return String 单元格内数据
 */
private String getCellValue(HSSFCell cell, String sheetName, int row, int col) throws Exception {
    if (cell != null) {
        int cellType = cell.getCellType();
        switch (cellType) {
        case HSSFCell.CELL_TYPE_NUMERIC: // 单元格类型为数字
            double d = cell.getNumericCellValue();
            String s = String.valueOf(d);
            int pos = s.indexOf(".");
            return s.substring(0, pos).trim();
        case HSSFCell.CELL_TYPE_STRING: // 单元格类型为字符串
            return cell.getStringCellValue() == null ? "" : cell.getStringCellValue().trim();
        case HSSFCell.CELL_TYPE_BLANK:
            return "";
        default:
            throw new Exception(sheetName + "中單元格格式有問題，請確認后重新導入！");
        }
    } else {
        throw new Exception(sheetName + "中單元格格式有問題，請確認后重新導入！");
    }
}

/**
 * 判断卡号是否存在
 * @return boolean true存在，false不存在
 */
private Boolean checkMsisdn(String data) throws BusinessException{
    Boolean flag = false;
    if (data.length() > 0) {
        RoamingCardStatus cardStatusTemp = new RoamingCardStatus();
        User user = (User) ActionContext.getContext().getSession().get("adminProfile");
        cardStatusTemp.setMsisdn(data);
        if (user.getRoleType().equals("2") || user.getRoleType().equals("3")) {
            cardStatusTemp.setIid(user.getUserCode());
        }
        
        /* 判断数据库是否存在此卡号 ,并查询地市*/
        int count = service.getCount(RoamingCardStatus.class, cardStatusTemp);
        if(count > 0 ) {
            flag = true;
        }
    }
    return flag;
}


private int checkTop_xlsx(XSSFCell cell, XSSFRow row, XSSFWorkbook wb, int i) throws Exception {
    String val;
    int flag = 0; // 标识第一行数据是否是表头，不是则按有效数据处理
    /* 卡号 */
    cell = row.getCell(0);
    val = getCellValue_xlsx(cell, wb.getSheetName(i), 0, 1);
    if (getText("cardStatus.msisdn.no.modify").equals(val)) {// 判断是否为字段名
        ++flag;
    }
    return flag;
}


private String getCellValue_xlsx(XSSFCell cell, String sheetName, int row, int col) throws Exception {
    if (cell != null) {
        int cellType = cell.getCellType();
        switch (cellType) {
        case HSSFCell.CELL_TYPE_NUMERIC: // 单元格类型为数字
            double d = cell.getNumericCellValue();
            String s = String.valueOf(d);
            int pos = s.indexOf(".");
            return s.substring(0, pos).trim();
        case HSSFCell.CELL_TYPE_STRING: // 单元格类型为字符串
            return cell.getStringCellValue() == null ? "" : cell.getStringCellValue().trim();
        case HSSFCell.CELL_TYPE_BLANK:
            return "";
        default:
            throw new Exception(sheetName + "中單元格格式有問題，請確認后重新導入！");
        }
    } else {
        throw new Exception(sheetName + "中單元格格式有問題，請確認后重新導入！");
    }
}

}

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值