Example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum

List of usage examples for org.apache.poi.ss.usermodel Sheet getLastRowNum

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. Sheet
  6. getLastRowNum

Introduction

In this page you can find the example usage for org.apache.poi.ss.usermodel Sheet getLastRowNum.

Prototype

int getLastRowNum();

Source Link

Document

Gets the last row on the sheet Note: rows which had content before and were set to empty later might still be counted as rows by Excel and Apache POI, so the result of this method will include such rows and thus the returned value might be higher than expected!

Usage

From source file:com.hp.idc.resm.util.ExcelUtil.java

License:Open Source License

/**
 * ,//from   www .j  a v  a 2  s  .  co  m
 * 
 * @param fileName
 *            excel, getModelExcel
 * @return 
 * @throws FileNotFoundException 
 */
public Map<String, String> readModelExcel(File file, String modelId) {
    if (modelId == null)
        return null;
    Map<String, String> m = new HashMap<String, String>();
    try {
        InputStream in = new FileInputStream(file);
        Workbook wb;
        try {
            wb = new HSSFWorkbook(in);
        } catch (IllegalArgumentException e) {
            wb = new XSSFWorkbook(in);
        }
        Sheet sheet = wb.getSheetAt(0);
        int total = sheet.getLastRowNum();
        Row row0 = sheet.getRow(0);
        String[] head = new String[row0.getLastCellNum()];
        for (int j = 0; j < row0.getLastCellNum(); j++) {
            String[] str = row0.getCell(j).getStringCellValue().split("/");
            if (str.length == 2) {
                head[j] = str[1];
            } else {
                head[j] = "";
            }
            System.out.println(head[j]);
        }
        Row row = null;
        Cell cell = null;
        for (int i = 1; i < total; i++) {
            m.clear();
            row = sheet.getRow(i);
            for (int j = 0; j < row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                m.put(head[j], cell.getStringCellValue());
                System.out.println(head[j] + "--" + cell.getStringCellValue());
            }
            // ServiceManager.getResourceUpdateService().addResource(modelId,
            // m, 1);
        }
    } catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        file.delete();
    }
    return m;
}

From source file:com.javabean.excel.ExcelDocumentReader.java

public List<List<String>> GetDataRowList(int sheetIndex) {
    List<List<String>> dataRowList;
    Sheet sheet;
    Row row;/*from  w ww  .j av a2s . c om*/
    int rowCount;
    int columnCount;
    sheet = GetSheet(sheetIndex);
    if (sheet == null) {
        return null;
    }
    rowCount = sheet.getLastRowNum();
    row = sheet.getRow(0);
    columnCount = row.getLastCellNum();
    dataRowList = new ArrayList<>();
    for (int i = 0; i < rowCount + 1; i++) {
        List<String> dataRow = GetOneDataRow(sheet, i, columnCount);
        if (dataRow != null) {
            dataRowList.add(dataRow);
        }
    }
    return dataRowList;
}

From source file:com.jeans.iservlet.action.admin.DataImportAction.java

/**
 * ??//from w ww.j a  v  a 2  s  .c  o  m
 * 
 * @return
 * @throws Exception
 */
@Action(value = "hr-import", results = { @Result(name = SUCCESS, type = "json", params = { "root", "results",
        "contentType", "text/plain", "encoding", "UTF-8" }) })
public String uploadHRData() throws Exception {
    if (!checkDataFile()) {
        return SUCCESS;
    }
    int deptCount = 0, emplCount = 0;
    try (Workbook workBook = WorkbookFactory.create(data)) {
        Sheet deptSheet = workBook.getSheet("");
        Sheet emplSheet = workBook.getSheet("");
        if (null == deptSheet || null == emplSheet) {
            results.put("code", 4);
            results.put("tip", "????Sheet");
            return SUCCESS;
        }
        Company comp = getCurrentCompany();
        // deptSheet: 1?04?5??????
        int last = deptSheet.getLastRowNum();
        for (int rn = 1; rn <= last; rn++) {
            Row r = deptSheet.getRow(rn);
            // ??""???
            String flag = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(4, Row.RETURN_BLANK_AS_NULL)));
            if (!"".equals(flag))
                continue;
            // ?name?
            String name = ExcelUtils.getCellValueAsString(r.getCell(0, Row.RETURN_BLANK_AS_NULL));
            if (StringUtils.isBlank(name))
                continue;
            else
                name = StringUtils.trim(name);
            // ?alias?
            String alias = ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL));
            if (StringUtils.isBlank(alias))
                alias = name.substring(0, 15);
            else
                alias = StringUtils.trim(alias);
            // ?ID(superiorId)????????
            String superior = ExcelUtils.getCellValueAsString(r.getCell(2, Row.RETURN_BLANK_AS_NULL));
            long superiorId = 0;
            if (StringUtils.isBlank(superior)) {
                superiorId = comp.getId();
            } else {
                HRUnitNode suprDept = hrService.getDepartmentByName(comp, superior);
                if (null == suprDept)
                    continue;
                else
                    superiorId = suprDept.getId();
            }
            // ???listOrder??short999??1999
            short listOrder = 999;
            try {
                double order = (double) ExcelUtils.getCellValue(r.getCell(3, Row.RETURN_BLANK_AS_NULL));
                if (order < 1)
                    listOrder = 1;
                else if (order > 999)
                    listOrder = 999;
                else
                    listOrder = (short) Math.round(order);
            } catch (ClassCastException e) {
                log(e);
                listOrder = 999;
            }
            hrService.appendDept(name, alias, superiorId, listOrder);
            deptCount++;
        }
        // emplSheet: 1?08?9??????????????admin?
        last = emplSheet.getLastRowNum();
        for (int rn = 1; rn <= last; rn++) {
            Row r = emplSheet.getRow(rn);
            // ??""???
            String flag = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(8, Row.RETURN_BLANK_AS_NULL)));
            if (!"".equals(flag))
                continue;
            // ???name?
            String name = ExcelUtils.getCellValueAsString(r.getCell(0, Row.RETURN_BLANK_AS_NULL));
            if (StringUtils.isBlank(name))
                continue;
            else
                name = StringUtils.trim(name);
            // ?ID(deptId)???
            String deptName = ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL));
            long deptId = 0;
            if (StringUtils.isBlank(deptName)) {
                continue;
            } else {
                HRUnitNode dept = hrService.getDepartmentByName(comp, deptName);
                if (null == dept)
                    continue;
                else
                    deptId = dept.getId();
            }
            // ???listOrder??short999??1999
            short listOrder = 999;
            try {
                double order = (double) ExcelUtils.getCellValue(r.getCell(2, Row.RETURN_BLANK_AS_NULL));
                if (order < 1)
                    listOrder = 1;
                else if (order > 999)
                    listOrder = 999;
                else
                    listOrder = (short) Math.round(order);
            } catch (ClassCastException e) {
                log(e);
                listOrder = 999;
            }
            // ???????admin?
            boolean leader = "".equals(
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(3, Row.RETURN_BLANK_AS_NULL))));
            boolean supervisor = "".equals(
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(4, Row.RETURN_BLANK_AS_NULL))));
            boolean auditor = "".equals(
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(5, Row.RETURN_BLANK_AS_NULL))));
            boolean iter = "".equals(
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(6, Row.RETURN_BLANK_AS_NULL))));
            boolean admin = "".equals(
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(7, Row.RETURN_BLANK_AS_NULL))));
            hrService.appendEmpl(name, deptId, listOrder, leader, supervisor, auditor, iter, admin);
            emplCount++;
        }
        results.put("code", 0);
        results.put("tip", "???" + deptCount + "" + emplCount
                + "??");
        log("?HR??" + deptCount + "" + emplCount + "??");
        return SUCCESS;
    } catch (Exception e) {
        log(e);
        results.put("code", 4);
        results.put("tip", "???" + deptCount
                + "" + emplCount + "??");
        log("?HR??" + deptCount + "" + emplCount
                + "??");
        return SUCCESS;
    }
}

From source file:com.jeans.iservlet.action.admin.DataImportAction.java

/**
 * ?/*w  ww  . ja v  a 2s .  com*/
 * 
 * @return
 * @throws Exception
 */
@Action(value = "ci-import", results = { @Result(name = SUCCESS, type = "json", params = { "root", "results",
        "contentType", "text/plain", "encoding", "UTF-8" }) })
public String uploadCIData() throws Exception {
    if (!checkDataFile()) {
        return SUCCESS;
    }
    int hardCount = 0, softCount = 0;
    try (Workbook workBook = WorkbookFactory.create(data)) {
        Sheet hardSheet = workBook.getSheet("");
        Sheet softSheet = workBook.getSheet("");
        if (null == hardSheet || null == softSheet) {
            results.put("code", 4);
            results.put("tip", "????Sheet");
            return SUCCESS;
        }
        Company comp = getCurrentCompany();
        ExcelUtils.setNumberFormat("#");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyymm");
        // hardSheet: 1?015?160?
        int last = hardSheet.getLastRowNum();
        for (int rn = 1; rn <= last; rn++) {
            Row r = hardSheet.getRow(rn);
            // ??""???
            String flag = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(0, Row.RETURN_BLANK_AS_NULL)));
            // ???name?
            String name = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(3, Row.RETURN_BLANK_AS_NULL)));
            if (!"".equals(flag) || StringUtils.isBlank(name)) {
                continue;
            }
            Map<String, Object> hardware = new HashMap<String, Object>();
            hardware.put("company", comp);
            hardware.put("type", AssetConstants.HARDWARE_ASSET);
            hardware.put("name", name);
            hardware.put("code",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("catalog",
                    parseAssetCatalog(
                            StringUtils.trim(
                                    ExcelUtils.getCellValueAsString(r.getCell(2, Row.RETURN_BLANK_AS_NULL))),
                            AssetConstants.HARDWARE_ASSET));
            hardware.put("vendor",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(4, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("modelOrVersion",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(5, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("assetUsage",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(6, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("purchaseTime",
                    ExcelUtils.getCellValueAsDate(r.getCell(7, Row.RETURN_BLANK_AS_NULL), sdf));
            try {
                double q = (double) ExcelUtils.getCellValue(r.getCell(8, Row.RETURN_BLANK_AS_NULL));
                if (q < 1) {
                    hardware.put("quantity", 1);
                } else {
                    hardware.put("quantity", (int) Math.round(q));
                }
            } catch (Exception e) {
                hardware.put("quantity", 1);
            }
            try {
                hardware.put("cost", BigDecimal
                        .valueOf((double) ExcelUtils.getCellValue(r.getCell(9, Row.RETURN_BLANK_AS_NULL))));
            } catch (Exception e) {
                hardware.put("cost", new BigDecimal(0));
            }
            hardware.put("state", AssetConstants.IDLE);
            hardware.put("sn",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(10, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("configuration",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(11, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("warranty", AssetConstants.IMPLIED_WARRANTY);
            hardware.put("location",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(12, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("ip",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(13, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("importance", AssetConstants.GENERAL_DEGREE);
            hardware.put("owner", null);
            hardware.put("comment",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(14, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("financialCode",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(15, Row.RETURN_BLANK_AS_NULL))));

            assetService.newAsset(hardware);
            hardCount++;
        }
        // softSheet: 1?012?130?
        last = softSheet.getLastRowNum();
        for (int rn = 1; rn <= last; rn++) {
            Row r = softSheet.getRow(rn);
            // ??""???
            String flag = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(0, Row.RETURN_BLANK_AS_NULL)));
            // ???name?
            String name = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(2, Row.RETURN_BLANK_AS_NULL)));
            if (!"".equals(flag) || StringUtils.isBlank(name)) {
                continue;
            }
            if (StringUtils.isBlank(name))
                continue;
            Map<String, Object> software = new HashMap<String, Object>();
            software.put("company", comp);
            software.put("type", AssetConstants.SOFTWARE_ASSET);
            software.put("name", name);
            software.put("catalog",
                    parseAssetCatalog(
                            StringUtils.trim(
                                    ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL))),
                            AssetConstants.SOFTWARE_ASSET));
            software.put("vendor",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(3, Row.RETURN_BLANK_AS_NULL))));
            software.put("modelOrVersion",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(4, Row.RETURN_BLANK_AS_NULL))));
            try {
                double q = (double) ExcelUtils.getCellValue(r.getCell(5, Row.RETURN_BLANK_AS_NULL));
                if (q < 1) {
                    software.put("quantity", 1);
                } else {
                    software.put("quantity", (int) Math.round(q));
                }
            } catch (Exception e) {
                software.put("quantity", 1);
            }
            try {
                software.put("cost", BigDecimal
                        .valueOf((double) ExcelUtils.getCellValue(r.getCell(6, Row.RETURN_BLANK_AS_NULL))));
            } catch (Exception e) {
                software.put("cost", new BigDecimal(0));
            }
            software.put("purchaseTime",
                    ExcelUtils.getCellValueAsDate(r.getCell(7, Row.RETURN_BLANK_AS_NULL), sdf));
            software.put("assetUsage",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(8, Row.RETURN_BLANK_AS_NULL))));
            software.put("state", AssetConstants.IN_USE);
            software.put("softwareType", parseSoftwareType(
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(9, Row.RETURN_BLANK_AS_NULL)))));
            software.put("license",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(10, Row.RETURN_BLANK_AS_NULL))));
            software.put("expiredTime",
                    ExcelUtils.getCellValueAsDate(r.getCell(11, Row.RETURN_BLANK_AS_NULL), sdf));
            software.put("comment",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(12, Row.RETURN_BLANK_AS_NULL))));

            assetService.newAsset(software);
            softCount++;
        }
        results.put("code", 0);
        results.put("tip", "???" + hardCount + ""
                + softCount + "");
        log("?CI??" + hardCount + "" + softCount
                + "");
        return SUCCESS;
    } catch (Exception e) {
        log(e);
        results.put("code", 4);
        results.put("tip", "???" + hardCount
                + "" + softCount + "");
        log("?CI??" + hardCount + "" + softCount
                + "");
        return SUCCESS;
    }
}

From source file:com.jeans.iservlet.controller.impl.ImportController.java

@RequestMapping(method = RequestMethod.POST, value = "/hr")
@ResponseBody/*from w w w . ja  v  a 2 s.co  m*/
public void importHR(@RequestParam("data") CommonsMultipartFile data, HttpServletResponse response)
        throws IOException {
    PrintWriter out = response.getWriter();
    if (null == data) {
        showError(out, "??");
        out.close();
        return;
    }
    int deptCount = 0, emplCount = 0;
    String info = null;
    try (Workbook workBook = WorkbookFactory.create(data.getInputStream())) {
        Sheet deptSheet = workBook.getSheet("");
        Sheet emplSheet = workBook.getSheet("");
        if (null == deptSheet || null == emplSheet) {
            data.getInputStream().close();
            showError(out, "????Sheet");
            out.close();
            return;
        }
        Company comp = getCurrentCompany();
        // deptSheet: 1?04?5??????
        int total = deptSheet.getLastRowNum() + emplSheet.getLastRowNum();
        double progress = 0.0;
        double step = 100.0 / (double) total;
        showProgressDialog(out, getCurrentCompany().getAlias() + "");
        int last = deptSheet.getLastRowNum();
        for (int rn = 1; rn <= last; rn++) {
            Row r = deptSheet.getRow(rn);
            // ??""???
            String flag = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(4, Row.RETURN_BLANK_AS_NULL)));
            progress += step;
            if (!"".equals(flag)) {
                continue;
            }
            // ?name?
            String name = ExcelUtils.getCellValueAsString(r.getCell(0, Row.RETURN_BLANK_AS_NULL));
            if (StringUtils.isBlank(name)) {
                continue;
            } else {
                name = StringUtils.trim(name);
            }
            showProgress(out, "" + name, progress);// 
            // ?alias?
            String alias = ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL));
            if (StringUtils.isBlank(alias)) {
                alias = name.substring(0, 15);
            } else {
                alias = StringUtils.trim(alias);
            }
            // ?ID(superiorId)????????
            String superior = ExcelUtils.getCellValueAsString(r.getCell(2, Row.RETURN_BLANK_AS_NULL));
            long superiorId = 0;
            if (StringUtils.isBlank(superior)) {
                superiorId = comp.getId();
            } else {
                Department suprDept = hrService.findDepartmentByName(comp, superior);
                if (null == suprDept) {
                    continue;
                } else {
                    superiorId = suprDept.getId();
                }
            }
            // ???listOrder??short999??1999
            short listOrder = 999;
            try {
                double order = (double) ExcelUtils.getCellValue(r.getCell(3, Row.RETURN_BLANK_AS_NULL));
                if (order < 1) {
                    listOrder = 1;
                } else if (order > 999) {
                    listOrder = 999;
                } else {
                    listOrder = (short) Math.round(order);
                }
            } catch (ClassCastException e) {
                log(e);
                listOrder = 999;
            }
            if (null != hrService.createDept(name, alias, superiorId, listOrder)) {
                deptCount++;
            }
        }
        // emplSheet: 1?08?9??????????????admin?
        last = emplSheet.getLastRowNum();
        for (int rn = 1; rn <= last; rn++) {
            Row r = emplSheet.getRow(rn);
            // ??""???
            String flag = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(8, Row.RETURN_BLANK_AS_NULL)));
            progress += step;
            if (!"".equals(flag)) {
                continue;
            }
            // ???name?
            String name = ExcelUtils.getCellValueAsString(r.getCell(0, Row.RETURN_BLANK_AS_NULL));
            if (StringUtils.isBlank(name)) {
                continue;
            } else {
                name = StringUtils.trim(name);
            }
            showProgress(out, "" + name, progress); // 
            // ?ID(deptId)???
            String deptName = ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL));
            long deptId = 0;
            if (StringUtils.isBlank(deptName)) {
                continue;
            } else {
                Department dept = hrService.findDepartmentByName(comp, deptName);
                if (null == dept) {
                    continue;
                } else {
                    deptId = dept.getId();
                }
            }
            // ???listOrder??short999??1999
            short listOrder = 999;
            try {
                double order = (double) ExcelUtils.getCellValue(r.getCell(2, Row.RETURN_BLANK_AS_NULL));
                if (order < 1) {
                    listOrder = 1;
                } else if (order > 999) {
                    listOrder = 999;
                } else {
                    listOrder = (short) Math.round(order);
                }
            } catch (ClassCastException e) {
                log(e);
                listOrder = 999;
            }
            // ???????admin?
            boolean leader = "".equals(
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(3, Row.RETURN_BLANK_AS_NULL))));
            boolean supervisor = "".equals(
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(4, Row.RETURN_BLANK_AS_NULL))));
            boolean auditor = "".equals(
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(5, Row.RETURN_BLANK_AS_NULL))));
            boolean iter = "".equals(
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(6, Row.RETURN_BLANK_AS_NULL))));
            boolean admin = "".equals(
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(7, Row.RETURN_BLANK_AS_NULL))));
            if (null != hrService.createEmpl(name, deptId, listOrder, leader, supervisor, auditor, iter,
                    admin)) {
                emplCount++;
            }
        }
        info = "?" + deptCount + "" + emplCount
                + "??";
    } catch (Exception e) {
        log(e);
        info = "????"
                + deptCount + "" + emplCount + "??";
    } finally {
        data.getInputStream().close();
        closeProgressDialog(out);
        showInfo(out, info);
        out.close();
        log(info);
    }
}

From source file:com.jeans.iservlet.controller.impl.ImportController.java

@RequestMapping(method = RequestMethod.POST, value = "/ci")
@ResponseBody//from   www  .  j a  v a2s  . c o  m
public void importCI(@RequestParam("data") CommonsMultipartFile data, HttpServletResponse response)
        throws IOException {
    PrintWriter out = response.getWriter();
    if (null == data) {
        showError(out, "??");
        out.close();
        return;
    }
    int hardCount = 0, softCount = 0;
    String info = null;
    try (Workbook workBook = WorkbookFactory.create(data.getInputStream())) {
        Sheet hardSheet = workBook.getSheet("");
        Sheet softSheet = workBook.getSheet("");
        if (null == hardSheet || null == softSheet) {
            data.getInputStream().close();
            showError(out, "????Sheet");
            out.close();
            return;
        }
        Company comp = getCurrentCompany();
        ExcelUtils.setNumberFormat("#");
        SimpleDateFormat sdf = new SimpleDateFormat("yyyymm");
        int total = hardSheet.getLastRowNum() + softSheet.getLastRowNum();
        double progress = 0.0;
        double step = 100.0 / (double) total;
        showProgressDialog(out, getCurrentCompany().getAlias() + "?");
        // hardSheet: 1?019?200?
        int last = hardSheet.getLastRowNum();
        for (int rn = 1; rn <= last; rn++) {
            Row r = hardSheet.getRow(rn);
            // ??""???
            String flag = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(0, Row.RETURN_BLANK_AS_NULL)));
            // ???name?
            String name = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(4, Row.RETURN_BLANK_AS_NULL)));
            progress += step;
            if (!"".equals(flag) || StringUtils.isBlank(name)) {
                continue;
            }
            showProgress(out, "" + name, progress); // 
            Map<String, Object> hardware = new HashMap<String, Object>();
            hardware.put("company", comp);
            hardware.put("type", AssetConstants.HARDWARE_ASSET);
            hardware.put("name", name);
            hardware.put("code",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("financialCode",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(2, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("catalog",
                    parseAssetCatalog(
                            StringUtils.trim(
                                    ExcelUtils.getCellValueAsString(r.getCell(3, Row.RETURN_BLANK_AS_NULL))),
                            AssetConstants.HARDWARE_ASSET));
            hardware.put("vendor",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(5, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("modelOrVersion",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(6, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("assetUsage",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(7, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("sn",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(8, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("configuration",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(9, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("purchaseTime",
                    ExcelUtils.getCellValueAsDate(r.getCell(10, Row.RETURN_BLANK_AS_NULL), sdf));
            try {
                double q = (double) ExcelUtils.getCellValue(r.getCell(11, Row.RETURN_BLANK_AS_NULL));
                if (q < 1) {
                    hardware.put("quantity", 1);
                } else {
                    hardware.put("quantity", (int) Math.round(q));
                }
            } catch (Exception e) {
                hardware.put("quantity", 1);
            }
            try {
                hardware.put("cost", BigDecimal
                        .valueOf((double) ExcelUtils.getCellValue(r.getCell(12, Row.RETURN_BLANK_AS_NULL))));
            } catch (Exception e) {
                hardware.put("cost", new BigDecimal(0));
            }
            hardware.put("state",
                    parseAssetState(
                            StringUtils.trim(
                                    ExcelUtils.getCellValueAsString(r.getCell(13, Row.RETURN_BLANK_AS_NULL))),
                            AssetConstants.HARDWARE_ASSET));
            hardware.put("warranty", parseHardwareWarranty(StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(14, Row.RETURN_BLANK_AS_NULL)))));
            hardware.put("location",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(15, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("ip",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(16, Row.RETURN_BLANK_AS_NULL))));
            hardware.put("importance", parseHardwareImportance(StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(17, Row.RETURN_BLANK_AS_NULL)))));
            hardware.put("owner", hrService.findEmployeeByName(getCurrentCompany(), StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(18, Row.RETURN_BLANK_AS_NULL)))));
            hardware.put("comment",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(19, Row.RETURN_BLANK_AS_NULL))));

            if (null != assetService.create(hardware)) {
                hardCount++;
            }
        }
        // softSheet: 1?013?140?
        last = softSheet.getLastRowNum();
        for (int rn = 1; rn <= last; rn++) {
            Row r = softSheet.getRow(rn);
            // ??""???
            String flag = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(0, Row.RETURN_BLANK_AS_NULL)));
            // ???name?
            String name = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(2, Row.RETURN_BLANK_AS_NULL)));
            progress += step;
            if (!"".equals(flag) || StringUtils.isBlank(name)) {
                continue;
            }
            showProgress(out, "" + name, progress); // 
            Map<String, Object> software = new HashMap<String, Object>();
            software.put("company", comp);
            software.put("type", AssetConstants.SOFTWARE_ASSET);
            software.put("name", name);
            software.put("catalog",
                    parseAssetCatalog(
                            StringUtils.trim(
                                    ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL))),
                            AssetConstants.SOFTWARE_ASSET));
            software.put("vendor",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(3, Row.RETURN_BLANK_AS_NULL))));
            software.put("modelOrVersion",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(4, Row.RETURN_BLANK_AS_NULL))));
            software.put("assetUsage",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(5, Row.RETURN_BLANK_AS_NULL))));
            software.put("purchaseTime",
                    ExcelUtils.getCellValueAsDate(r.getCell(6, Row.RETURN_BLANK_AS_NULL), sdf));
            try {
                double q = (double) ExcelUtils.getCellValue(r.getCell(7, Row.RETURN_BLANK_AS_NULL));
                if (q < 1) {
                    software.put("quantity", 1);
                } else {
                    software.put("quantity", (int) Math.round(q));
                }
            } catch (Exception e) {
                software.put("quantity", 1);
            }
            try {
                software.put("cost", BigDecimal
                        .valueOf((double) ExcelUtils.getCellValue(r.getCell(8, Row.RETURN_BLANK_AS_NULL))));
            } catch (Exception e) {
                software.put("cost", new BigDecimal(0));
            }
            software.put("state",
                    parseAssetState(
                            StringUtils.trim(
                                    ExcelUtils.getCellValueAsString(r.getCell(9, Row.RETURN_BLANK_AS_NULL))),
                            AssetConstants.SOFTWARE_ASSET));
            software.put("softwareType", parseSoftwareType(StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(10, Row.RETURN_BLANK_AS_NULL)))));
            software.put("license",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(11, Row.RETURN_BLANK_AS_NULL))));
            software.put("expiredTime",
                    ExcelUtils.getCellValueAsDate(r.getCell(12, Row.RETURN_BLANK_AS_NULL), sdf));
            software.put("comment",
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(13, Row.RETURN_BLANK_AS_NULL))));

            if (null != assetService.create(software)) {
                softCount++;
            }
        }
        info = "??" + hardCount + "" + softCount
                + "";
    } catch (Exception e) {
        log(e);
        info = "?????"
                + hardCount + "" + softCount + "";
    } finally {
        data.getInputStream().close();
        closeProgressDialog(out);
        showInfo(out, info);
        out.close();
        log(info);
    }
}

From source file:com.jeans.iservlet.controller.impl.ImportController.java

@RequestMapping(method = RequestMethod.POST, value = "/ac")
@ResponseBody//from   w  w  w .  j av  a  2  s .  c o  m
public void importAC(@RequestParam("data") CommonsMultipartFile data, HttpServletResponse response)
        throws IOException {
    PrintWriter out = response.getWriter();
    if (null == data) {
        showError(out, "??");
        out.close();
        return;
    }
    int count = 0;
    String info = null;
    try (Workbook workBook = WorkbookFactory.create(data.getInputStream())) {
        Sheet acsSheet = workBook.getSheet("");
        if (null == acsSheet) {
            data.getInputStream().close();
            showError(out, "????Sheet");
            out.close();
            return;
        }
        Company comp = getCurrentCompany();
        int total = acsSheet.getLastRowNum();
        double progress = 0.0;
        double step = 100.0 / (double) total;
        showProgressDialog(out, getCurrentCompany().getAlias() + "???");
        // acsSheet: 1?06?70?
        int last = acsSheet.getLastRowNum();
        for (int rn = 1; rn <= last; rn++) {
            Row r = acsSheet.getRow(rn);
            // ??""???
            String flag = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(0, Row.RETURN_BLANK_AS_NULL)));
            // ???name?
            String name = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(2, Row.RETURN_BLANK_AS_NULL)));
            progress += step;
            if (!"".equals(flag) || StringUtils.isBlank(name)) {
                continue;
            }
            showProgress(out, "???" + name, progress); // 
            AccessoryType type = parseAccessoryType(
                    StringUtils.trim(ExcelUtils.getCellValueAsString(r.getCell(1, Row.RETURN_BLANK_AS_NULL))));
            String brand = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(3, Row.RETURN_BLANK_AS_NULL)));
            String model = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(4, Row.RETURN_BLANK_AS_NULL)));
            String description = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(5, Row.RETURN_BLANK_AS_NULL)));
            String unit = StringUtils
                    .trim(ExcelUtils.getCellValueAsString(r.getCell(6, Row.RETURN_BLANK_AS_NULL)));

            if (null != acsService.create(comp, type, name, brand, model, unit, description)) {
                count++;
            }
        }
        info = "????" + count + "?";
    } catch (Exception e) {
        log(e);
        info = "???????"
                + count + "?";
    } finally {
        data.getInputStream().close();
        closeProgressDialog(out);
        showInfo(out, info);
        out.close();
        log(info);
    }
}

From source file:com.jwm123.loggly.reporter.ReportGenerator.java

License:Apache License

public byte[] build(List<Map<String, String>> row) throws IOException {
    List<String> colLabels = new ArrayList<String>();
    Sheet reportSheet = workbook.getSheet("Report");
    if (reportSheet == null) {
        reportSheet = workbook.createSheet("Report");
    }//  w  w w .j a  va 2s . c om
    Row firstRow = reportSheet.getRow(0);
    if (firstRow == null) {
        firstRow = reportSheet.createRow(0);
        int cellNum = 0;
        for (Map<String, String> col : row) {
            for (String key : col.keySet()) {
                Cell cell = firstRow.createCell(cellNum++);
                setCellValue(cell, key);
            }
        }
    }
    for (int i = 0; i < firstRow.getLastCellNum(); i++) {
        Cell cell = firstRow.getCell(i);
        if (cell != null) {
            colLabels.add(cell.getStringCellValue());
        }
    }
    Row newRow = reportSheet.createRow(reportSheet.getLastRowNum() + 1);
    int lastIndex = -1;
    for (Map<String, String> col : row) {
        for (String key : col.keySet()) {
            int colNum = -1;
            Cell cell = null;
            if (colLabels.contains(key)) {
                colNum = colLabels.indexOf(key);
                lastIndex = colNum;
            }
            if (colNum == -1) {
                lastIndex++;
                colNum = lastIndex;
                shiftColumns(reportSheet, colNum, key);
                colLabels.add(colNum, key);
            }
            cell = newRow.getCell(colNum);
            if (cell == null) {
                cell = newRow.createCell(colNum);
            }
            setCellValue(cell, col.get(key));
        }
    }
    for (int i = 0; i < firstRow.getLastCellNum(); i++) {
        reportSheet.autoSizeColumn(i);
    }
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    workbook.write(baos);
    return baos.toByteArray();
}

From source file:com.kongwu.insweb.utils.ReadExcel.java

License:Apache License

/**
 * @param filepath excel/*from   w ww.jav a  2s  .co  m*/
 *        
 *        
 *        
 * @return ??,:list<usrid,query,biz,semantic>
 *                        :
 *                           :
 * @throws IOException
 */
public static List<List<String>> readTestset(String filepath) throws IOException {
    FileInputStream fileIn = null;
    List<List<String>> testsetList = new ArrayList<List<String>>();
    /**?excel**/
    try {
        fileIn = new FileInputStream(filepath);
        /**excel 2007**/
        if (filepath.endsWith(".xlsx")) {
            Workbook wb = new XSSFWorkbook(fileIn);
            Sheet sheet = wb.getSheetAt(0);
            Row row = null;
            Cell cell = null;
            int rowSize = sheet.getLastRowNum() + 1;// getLastRowNum()1
            logger.info("?" + rowSize);
            if (rowSize < 2)
                return null;
            // ?,
            for (int i = 1; i < rowSize; i++) {
                row = sheet.getRow(i);
                if (row == null)
                    continue;
                int cellSize = row.getLastCellNum();
                logger.info("?" + cellSize);
                /**
                 * ?cell
                 */
                List<String> list = new ArrayList<String>();
                for (int j = 0; j < cellSize; j++) {
                    cell = row.getCell(i);
                    if (cell != null) {
                        list.add(cell.toString());
                    }
                }

                testsetList.add(list);
            }
        } else {
            POIFSFileSystem fs = new POIFSFileSystem(fileIn);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            HSSFRow row = null;
            HSSFCell cell = null;
            int rowSize = sheet.getLastRowNum() + 1;// getLastRowNum()1
            logger.info("?" + rowSize);
            if (rowSize < 2)// ???
                return null;
            // ?,
            for (int i = 1; i < rowSize; i++) {
                row = sheet.getRow(i);
                if (row == null)
                    continue;
                int cellSize = row.getLastCellNum();
                logger.info("?" + cellSize);
                List<String> list = new ArrayList<String>();
                for (int j = 0; j < cellSize; j++) {
                    cell = row.getCell(j);
                    if (cell != null) {
                        list.add(cell.toString());
                    }
                }
                testsetList.add(list);
            }
        }
    } finally {
        if (fileIn != null)
            fileIn.close();
    }
    return testsetList;
}

From source file:com.kysoft.cpsi.audit.service.SelfCheckServiceImpl.java

private void gudongchuzi(Hcrw hcrw, Sheet sheetGDCZ, Integer nd) {
    try {/*from ww w  .  ja v  a  2s .  co  m*/
        stockholderContributionMapper.deleteByTaskIdNd(hcrw.getId(), nd);
        // 
        int rowNum = sheetGDCZ.getLastRowNum();
        for (int i = 6; i < rowNum; i++) {
            if (null != POIUtils.getStringCellValue(sheetGDCZ.getRow(i).getCell(2)).trim()
                    && !POIUtils.getStringCellValue(sheetGDCZ.getRow(i).getCell(2)).trim().equals("")) {
                StockholderContribution stockholderContribution = new StockholderContribution();
                stockholderContribution.setId(UUID.randomUUID().toString().replace("-", ""));
                stockholderContribution.setNd(nd);
                stockholderContribution.setXydm(hcrw.getHcdwXydm());
                stockholderContribution.setGd(POIUtils.getStringCellValue(sheetGDCZ.getRow(i).getCell(2)));
                stockholderContribution.setRjcze(null);
                stockholderContribution.setRjczdqsj(null);
                stockholderContribution.setRjczfs(null);
                stockholderContribution
                        .setSjcze(parseFloat(POIUtils.getStringCellValue(sheetGDCZ.getRow(i).getCell(3))));
                stockholderContribution.setSjczsj(POIUtils.getStringCellValue(sheetGDCZ.getRow(i).getCell(4)));
                stockholderContribution.setSjczfs(POIUtils.getStringCellValue(sheetGDCZ.getRow(i).getCell(5)));
                stockholderContributionMapper.insert2(stockholderContribution);
            }
        }
    } catch (Exception e) {
        e.printStackTrace();
        MongoLogger.warn("?? ?? ",
                ExceptionUtils.getStackTrace(e), hcrw.getId());
        throw new RuntimeException("[??]??;");
    }
}