Example usage for org.apache.poi.ss.usermodel WorkbookFactory create

List of usage examples for org.apache.poi.ss.usermodel WorkbookFactory create

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.ss.usermodel.*
  5. WorkbookFactory
  6. create

Introduction

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

Prototype

public static Workbook create(File file) throws IOException, EncryptedDocumentException

Source Link

Document

Creates the appropriate HSSFWorkbook / XSSFWorkbook from the given File, which must exist and be readable.

Usage

From source file:com.ihhira.projects.badgemaker.MainWindow.java

private void loadData() throws IOException, EncryptedDocumentException, InvalidFormatException {

    JFileChooser fileChooser = new JFileChooser(curDir);
    int ok = fileChooser.showOpenDialog(this);
    if (ok != JFileChooser.APPROVE_OPTION) {
        return;/*from   w w  w. j  a  va 2  s  .com*/
    }
    File selectedFile = fileChooser.getSelectedFile();
    curDir = selectedFile.getParentFile();

    //        File selectedFile = new File("jugbd-meetup5-attendee-list.xlsx");

    Workbook wb = WorkbookFactory.create(selectedFile);
    Sheet sheet = wb.getSheetAt(0);
    Iterator<Row> rowIterator = sheet.iterator();
    ArrayList<Row> rows = new ArrayList<>();
    while (rowIterator.hasNext()) {
        rows.add(rowIterator.next());
    }
    this.rows = rows;
    if (rows.size() > 0) {
        showData(0);
    }
}

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

/**
 * ??//from ww w.java2s . 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

/**
 * ?//from w  ww  . ja  v a2s  .c  o  m
 * 
 * @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 a2  s. c  om
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 .jav  a  2s.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/*  w  ww.  ja v  a  2 s.co 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.jkoolcloud.tnt4j.streams.inputs.AbstractExcelStream.java

License:Apache License

@Override
protected void initialize() throws Exception {
    super.initialize();

    if (StringUtils.isEmpty(fileName)) {
        throw new IllegalStateException(
                StreamsResources.getStringFormatted(StreamsResources.RESOURCE_BUNDLE_NAME,
                        "TNTInputStream.property.undefined", StreamProperties.PROP_FILENAME));
    }//from ww w.  j  a  v  a2s  . co  m

    File wbFile = new File(fileName);
    if (!wbFile.exists()) {
        throw new IllegalArgumentException(StreamsResources.getStringFormatted(
                MsOfficeStreamConstants.RESOURCE_BUNDLE_NAME, "AbstractExcelStream.file.not.exist", fileName));
    }

    workbook = WorkbookFactory.create(wbFile);
    sheetIterator = workbook.sheetIterator();
}

From source file:com.jmc.jfxxlsdiff.task.GetWorkSheetContent.java

public GetWorkSheetContent(File xlsFile, String sheetName) throws IOException, InvalidFormatException {
    this(WorkbookFactory.create(xlsFile), sheetName);
}

From source file:com.jmc.jfxxlsdiff.task.GetWorkSheetNames.java

@Override
protected ObservableList<String> call() throws Exception {
    logger.log(Level.INFO, "call()");
    updateProgress(-1L, 1L);//w w w . j a v  a  2s . com

    ObservableList<String> ol = null;
    Workbook wb = null;

    try {
        logger.log(Level.INFO, "creating workbook");
        wb = WorkbookFactory.create(xlsFile);
    } catch (IOException | InvalidFormatException ex) {
        logger.log(Level.SEVERE, null, ex);
        updateProgress(0L, 1L);
        throw ex;
    }

    if (wb != null) {
        logger.log(Level.INFO, "get sheet names");

        ol = FXCollections.observableArrayList();
        for (int i = 0; i < wb.getNumberOfSheets(); i++) {
            ol.add(wb.getSheetAt(i).getSheetName());
        }

        logger.log(Level.INFO, "got {0} sheet names", ol.size());
        updateProgress(1L, 1L);
    } else {
        logger.log(Level.WARNING, "workbook *is* null");
        updateProgress(0L, 1L);
    }

    return ol;
}

From source file:com.jogo.dao.RepositorioDao.java

@Override
public List importar() {

    //arry de usuario para armazenar os meus usuarios que pega do excel na folha 1
    List<Usuario> usuarios = new ArrayList<>();

    //CAPTURA OS DADOS DO USUARIO NO EXCEL
    try {//from  w w w .j  a  v a 2  s. c  om
        //capturando o excel para meu wb
        wb = WorkbookFactory.create(new FileInputStream(patch));

        //CAPTURAR A PRIMEIRA FOLHA DO EXCEL 
        Sheet folha = wb.getSheetAt(0);

        //criO um iterator para interagir com as linhas
        Iterator filaIterator = folha.rowIterator();

        //ENQUANTO HOUVER LINHAS O ITERATOR ME TRAZ.
        while (filaIterator.hasNext()) {

            //CAPTURO A LINHA DO EXCEL
            Row linha = (Row) filaIterator.next();

            //CRIO UM INTERATOR PARA INTERAGIR COM AS COLUNAS
            Iterator colunaIterator = linha.cellIterator();

            //CRIOU A CLASSE DE USUARIO E ADD DENTRO DO MEU ARRAY
            Usuario user = new Usuario();
            usuarios.add(user);

            //ENAUQNTO HOUVER COLUNAS O INTERATOR ME TRAZ.
            while (colunaIterator.hasNext()) {

                //COM A LINHA E A COLUNA JA POSSO CRIAR UMA CELULA.
                Cell celula = (Cell) colunaIterator.next();

                //APOS CAPTURAR O VALOR NA CELULA, SETO PARA MINHA CLASSE USUARIO QUE CRIEI LOGO ACIMA.
                if (celula != null) {
                    //CAPTURO O TIPO DA CELULA, NESSE CASO E STRING E NUMERICO(INT)
                    switch (celula.getCellType()) {
                    case Cell.CELL_TYPE_NUMERIC:
                        //CONVERTENDO O VALOR PARA INTEIRO.
                        user.setPontuacao((int) Math.round(celula.getNumericCellValue()));
                        break;
                    case Cell.CELL_TYPE_STRING:
                        user.setNome(celula.getStringCellValue());
                        break;

                    }

                }

            }

        }

    } catch (IOException | InvalidFormatException | EncryptedDocumentException e) {
    }
    return usuarios;
}