Example usage for org.apache.poi.xssf.usermodel XSSFRow getCell

List of usage examples for org.apache.poi.xssf.usermodel XSSFRow getCell

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.xssf.usermodel.*
  5. XSSFRow
  6. getCell

Introduction

In this page you can find the example usage for org.apache.poi.xssf.usermodel XSSFRow getCell.

Prototype

@Override
public XSSFCell getCell(int cellnum)

Source Link

Document

Returns the cell at the given (0 based) index, with the org.apache.poi.ss.usermodel.Row.MissingCellPolicy from the parent Workbook.

Usage

From source file:org.alfresco.bm.report.XLSXReporter.java

License:Open Source License

/**
 * Create a 'Summary' sheet containing the table of averages
 *//*from   ww w. j a v a2 s  .c  o  m*/
private void createSummarySheet(XSSFWorkbook workbook) throws IOException, NotFoundException {
    DBObject testRunObj = getTestService().getTestRunMetadata(test, run);

    // Create the sheet
    XSSFSheet sheet = workbook.createSheet("Summary");

    // Create the fonts we need
    Font fontBold = workbook.createFont();
    fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD);

    // Create the styles we need
    XSSFCellStyle summaryDataStyle = sheet.getWorkbook().createCellStyle();
    summaryDataStyle.setAlignment(HorizontalAlignment.RIGHT);
    XSSFCellStyle headerStyle = sheet.getWorkbook().createCellStyle();
    headerStyle.setAlignment(HorizontalAlignment.RIGHT);
    headerStyle.setFont(fontBold);

    XSSFRow row = null;
    int rowCount = 0;
    row = sheet.createRow(rowCount++);
    {
        row.getCell(0).setCellValue("Name:");
        row.getCell(0).setCellStyle(headerStyle);
        row.getCell(1).setCellValue(title);
        row.getCell(1).setCellStyle(summaryDataStyle);
    }
    row = sheet.createRow(rowCount++);
    {
        String description = (String) testRunObj.get(FIELD_DESCRIPTION);
        description = description == null ? "" : description;
        row.getCell(0).setCellValue("Description:");
        row.getCell(0).setCellStyle(headerStyle);
        row.getCell(1).setCellValue(description);
        row.getCell(1).setCellStyle(summaryDataStyle);
    }
    row = sheet.createRow(rowCount++);
    {
        row.getCell(0).setCellValue("Progress (%):");
        row.getCell(0).setCellStyle(headerStyle);
        Double progress = (Double) testRunObj.get(FIELD_PROGRESS);
        progress = progress == null ? 0.0 : progress;
        row.getCell(1).setCellValue(progress * 100);
        row.getCell(1).setCellType(XSSFCell.CELL_TYPE_NUMERIC);
        row.getCell(1).setCellStyle(summaryDataStyle);
    }
    row = sheet.createRow(rowCount++);
    {
        row.getCell(0).setCellValue("State:");
        row.getCell(0).setCellStyle(headerStyle);
        String state = (String) testRunObj.get(FIELD_STATE);
        if (state != null) {
            row.getCell(1).setCellValue(state);
            row.getCell(1).setCellStyle(summaryDataStyle);
        }
    }
    row = sheet.createRow(rowCount++);
    {
        row.getCell(0).setCellValue("Started:");
        row.getCell(0).setCellStyle(headerStyle);
        Long time = (Long) testRunObj.get(FIELD_STARTED);
        if (time > 0) {
            row.getCell(1).setCellValue(FastDateFormat
                    .getDateTimeInstance(FastDateFormat.MEDIUM, FastDateFormat.MEDIUM).format(time));
            row.getCell(1).setCellStyle(summaryDataStyle);
        }
    }
    row = sheet.createRow(rowCount++);
    {
        row.getCell(0).setCellValue("Finished:");
        row.getCell(0).setCellStyle(headerStyle);
        Long time = (Long) testRunObj.get(FIELD_COMPLETED);
        if (time > 0) {
            row.getCell(1).setCellValue(FastDateFormat
                    .getDateTimeInstance(FastDateFormat.MEDIUM, FastDateFormat.MEDIUM).format(time));
            row.getCell(1).setCellStyle(summaryDataStyle);
        }
    }
    row = sheet.createRow(rowCount++);
    {
        row.getCell(0).setCellValue("Duration:");
        row.getCell(0).setCellStyle(headerStyle);
        Long time = (Long) testRunObj.get(FIELD_DURATION);
        if (time > 0) {
            row.getCell(1).setCellValue(DurationFormatUtils.formatDurationHMS(time));
            row.getCell(1).setCellStyle(summaryDataStyle);
        }
    }

    rowCount++;
    rowCount++;
    // Create a header row
    row = sheet.createRow(rowCount++); // Header row
    String[] headers = new String[] { "Event Name", "Total Count", "Success Count", "Failure Count",
            "Success Rate (%)", "Min (ms)", "Max (ms)", "Arithmetic Mean (ms)", "Standard Deviation (ms)" };
    int columnCount = 0;
    for (String header : headers) {
        XSSFCell cell = row.getCell(columnCount++);
        cell.setCellStyle(headerStyle);
        cell.setCellValue(header);
    }
    // Grab results and output them
    columnCount = 0;
    TreeMap<String, ResultSummary> summaries = collateResults(true);
    for (Map.Entry<String, ResultSummary> entry : summaries.entrySet()) {
        // Reset column count
        columnCount = 0;

        row = sheet.createRow(rowCount++);
        String eventName = entry.getKey();
        ResultSummary summary = entry.getValue();
        SummaryStatistics statsSuccess = summary.getStats(true);
        SummaryStatistics statsFail = summary.getStats(false);
        // Event Name
        row.getCell(columnCount++).setCellValue(eventName);
        // Total Count
        row.getCell(columnCount++).setCellValue(summary.getTotalResults());
        // Success Count
        row.getCell(columnCount++).setCellValue(statsSuccess.getN());
        // Failure Count
        row.getCell(columnCount++).setCellValue(statsFail.getN());
        // Success Rate (%)
        row.getCell(columnCount++).setCellValue(summary.getSuccessPercentage());
        // Min (ms)
        row.getCell(columnCount++).setCellValue((long) statsSuccess.getMin());
        // Max (ms)
        row.getCell(columnCount++).setCellValue((long) statsSuccess.getMax());
        // Arithmetic Mean (ms)
        row.getCell(columnCount++).setCellValue((long) statsSuccess.getMean());
        // Standard Deviation (ms)
        row.getCell(columnCount++).setCellValue((long) statsSuccess.getStandardDeviation());
    }

    // Auto-size the columns
    for (int i = 0; i < 10; i++) {
        sheet.autoSizeColumn(i);
    }
    sheet.setColumnWidth(1, 5120);

    // Printing
    PrintSetup ps = sheet.getPrintSetup();
    sheet.setAutobreaks(true);
    ps.setFitWidth((short) 1);
    ps.setLandscape(true);

    // Header and footer
    sheet.getHeader().setCenter(title);
}

From source file:org.apache.commons.g.QueryStorePicUI.java

/**
 * ??//from  w ww . j ava  2 s . c  om
 * @param path
 * @throws Exception
 */
public void queryStorePic(String filePath, String outPath) throws Exception {

    final MyDialog dialog = new MyDialog(mContext, " ...");

    final List<String> noPics = new ArrayList<String>();
    final List<String> noStores = new ArrayList<String>();

    InputStream in = new FileInputStream(filePath);
    XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in);
    final XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
    int rows = xssfSheet.getLastRowNum();
    if (rows > 100) {
        rows = 100;
    }

    System.out.println();
    System.out.println("  " + rows + "...");
    dialog.setWorkText("  " + rows + "...");

    HashMap<String, String> headers = new HashMap<String, String>();
    headers.put("Content-Type", "application/json");
    headers.put("Cookie", Config.JSESSIONID);
    headers.put("Referer", "https://homesis.homecredit.cn/hsis/index.html");

    final XSSFCell cell0 = xssfSheet.getRow(0).createCell(0);
    cell0.setCellValue("");

    System.out.println("?? |  | ? |    ??    | ??");
    for (int i = 1; i <= rows; i++) {
        final XSSFRow xssfRow = xssfSheet.getRow(i);

        if (xssfRow == null)
            continue;

        // ??
        String visitName = XssfUtil.getCellValue(xssfRow.getCell(0));
        if (TextUtils.isEmpty(visitName))
            continue;

        // ?
        final String storeCode = XssfUtil.getCellValue(xssfRow.getCell(3));
        final String data = "{\"code\":\"" + storeCode
                + "\",\"name\":null,\"licenseNumber\":null,\"salesDistrictId\":null,\"offset\":0,\"limit\":15}";

        // ??
        final String storeName = XssfUtil.getCellValue(xssfRow.getCell(10));

        final XSSFCell cell = xssfRow.createCell(0);

        String space2 = "     ";
        // ??2
        if (storeName.length() < 20)
            space2 += Util.getFormateSpace(20 - storeName.length());
        ;

        String space = "     ";
        // ??2
        if (visitName.length() == 3)
            space = space.substring(2);
        // ?????
        if (i < 10) {
            System.out.print(
                    "    " + i + "      " + visitName + space + storeCode + "        " + storeName + space2);
        } else {
            System.out.print(
                    "    " + i + "     " + visitName + space + storeCode + "        " + storeName + space2);
        }
        dialog.setWorkText(" " + storeCode + "-" + storeName);
        Thread.sleep(1234);

        String store = null;
        boolean run = true;
        while (run) {
            try {
                // ?
                HttpPost seachStore = mHttpUtil
                        .doPost("https://homesis.homecredit.cn/hsis/api/salesroom/search", headers, data);
                store = mHttpUtil.executeForBody(seachStore);
                //System.out.println(store);
                run = false;
            } catch (Exception e) {
                System.out.print("/");
            }
        }

        if (store.contains("HTTP/1.1 500 Internal Server Error")) {
            System.out.println("?(?)");
            break;
        }
        JSONObject json = null;
        try {
            json = JSONObject.parseObject(store);
        } catch (Exception e) {
            System.out.println(e.getMessage());
            System.out.println(store);
            continue;
        }
        if (json == null) {
            System.out.println("?");
            continue;
        }

        JSONArray array = json.getJSONArray("data");
        if (array.size() == 0) {
            noStores.add(storeCode);
            cell.setCellValue("?");
            System.out.println("?");
            continue;
        }
        JSONObject obj = (JSONObject) array.get(0);

        //  ID
        int id = obj.getIntValue("id");
        //System.out.println(id);

        String storePic = null;
        run = true;
        while (run) {
            try {
                // 
                HttpGet seachPic = mHttpUtil
                        .doGet("https://homesis.homecredit.cn/hsis/api/document/salesroom/all/" + id, headers);
                storePic = mHttpUtil.executeForBody(seachPic);
                //                    System.out.println(storePic);
                run = false;
            } catch (Exception e) {
                System.out.print("/");
            }
        }

        json = JSONObject.parseObject(storePic);
        array = json.getJSONArray("documents");
        if (array.size() == 0) {
            noPics.add(storeCode);
            cell.setCellValue("");
            System.out.println("");
            continue;
        }

        boolean print = true;
        for (int j = 0; j < array.size(); j++) {
            obj = (JSONObject) array.get(j);
            //  ID
            int picid = obj.getIntValue("id");
            String fileName = obj.getString("fileName");
            if (fileName.contains("POSRepID"))
                continue;
            //                fileName = fileName.substring(0, fileName.indexOf("-"));//+fileName.substring(fileName.indexOf("."));
            run = true;
            while (run) {
                try {
                    // 
                    HttpGet down = mHttpUtil.doGet(
                            "https://homesis.homecredit.cn/hsis/api/document/download/" + picid, headers);
                    String result = mHttpUtil.downPic(down, Util
                            .mkdirsIfNeed(outPath + File.separator + visitName, storeCode + "-" + fileName));
                    if (print) {
                        print = false;
                        System.out.println(fileName.substring(0, fileName.indexOf("-")));
                    }
                    run = false;
                } catch (Exception e) {
                    System.out.println();
                    e.printStackTrace();
                    System.out.println();
                    System.out.println("?/");
                    System.out.println();
                }
            }
        }
    }
    xssfWorkbook.write(new FileOutputStream(filePath.replace(".xlsx", "_pic.xlsx")));
    xssfWorkbook.close();
    xssfWorkbook = null;

    in.close();
    in = null;

    if (noStores.size() > 0) {
        System.out.println();
        System.out.println();
        System.out.println("??");
        System.out.println(
                "");
        for (int k = 0; k < noStores.size(); k++) {
            System.out.println(noStores.get(k));
        }
    }

    if (noPics.size() > 0) {
        System.out.println();
        System.out.println();
        System.out.println("?");
        System.out.println(
                "");
        for (int k = 0; k < noPics.size(); k++) {
            System.out.println(noPics.get(k));
        }
    }

    dialog.dispose(2);
}

From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java

License:Apache License

private void copyRow(XSSFRow sourceRow, XSSFRow targetRow, XSSFCreationHelper factory, XSSFDrawing patriarch) {
    for (int j = 0; j < sourceRow.getPhysicalNumberOfCells(); j++) {
        XSSFCell cell = sourceRow.getCell(j);
        if (cell != null) {
            XSSFCell newCell = targetRow.createCell(j);
            int cellType = cell.getCellType();
            newCell.setCellType(cellType);
            switch (cellType) {
            case XSSFCell.CELL_TYPE_BOOLEAN:
                newCell.setCellValue(cell.getBooleanCellValue());
                break;
            case XSSFCell.CELL_TYPE_ERROR:
                newCell.setCellErrorValue(cell.getErrorCellValue());
                break;
            case XSSFCell.CELL_TYPE_FORMULA:
                newCell.setCellFormula(cell.getCellFormula());
                break;
            case XSSFCell.CELL_TYPE_NUMERIC:
                newCell.setCellValue(cell.getNumericCellValue());
                break;
            case XSSFCell.CELL_TYPE_STRING:
                newCell.setCellValue(cell.getRichStringCellValue());
                break;
            default:
                newCell.setCellValue(formatter.formatCellValue(cell));
            }/*  w w w .  j  a  va 2  s  . c  o m*/
            if (cell.getCellComment() != null) {
                XSSFClientAnchor anchor = factory.createClientAnchor();
                anchor.setDx1(100);
                anchor.setDx2(100);
                anchor.setDy1(100);
                anchor.setDy2(100);
                anchor.setCol1(newCell.getColumnIndex());
                anchor.setCol2(newCell.getColumnIndex() + 4);
                anchor.setRow1(newCell.getRowIndex());
                anchor.setRow2(newCell.getRowIndex() + 4);
                anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);

                XSSFComment comment = patriarch.createCellComment(anchor);
                comment.setString(cell.getCellComment().getString());
                newCell.setCellComment(comment);
            }
            newCell.setCellStyle(cell.getCellStyle());
            newCell.getSheet().setColumnWidth(newCell.getColumnIndex(),
                    cell.getSheet().getColumnWidth(cell.getColumnIndex()));
        }
    }
}

From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java

License:Apache License

/**
 * Get data by version definition.//from   www .ja  va2  s  .  c om
 * 
 * @param row
 * @param colNames 
 * @param size 
 * @return
 */
public List<Object> getCellContents(XSSFRow row, List<Object[]> colNames, int size) {
    List<Object> results = new ArrayList<Object>();
    boolean foundError = false;
    if (isEmptyRow(row, size, true)) {
        return null;
    }
    for (int i = 0; i < size; i++) {
        XSSFCell cell = null;
        if (row.getPhysicalNumberOfCells() > i) {
            cell = row.getCell(i);
        }
        if (cell == null) {
            if (((Boolean) colNames.get(i)[2]).booleanValue()) {
                report.print(UtilProperties.getMessage(resource, "ErrorColCannotEmpty",
                        new Object[] { colNames.get(i)[0] }, locale), InterfaceReport.FORMAT_WARNING);
                errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource,
                        "ErrorColCannotEmpty", new Object[] { colNames.get(i)[0] }, locale));
                foundError = true;
                continue;
            } else {
                cell = row.createCell(i);
            }
        }
        int cellType = cell.getCellType();
        String cellValue = formatter.formatCellValue(cell);
        if (UtilValidate.isNotEmpty(cellValue)) {
            if (cellType == XSSFCell.CELL_TYPE_FORMULA) {
                cellValue = BigDecimal.valueOf(cell.getNumericCellValue())
                        .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding).toString();
                report.print(((i == 0) ? "" : ", ") + cellValue, InterfaceReport.FORMAT_NOTE);
            } else {
                report.print(((i == 0) ? "" : ", ") + cellValue, InterfaceReport.FORMAT_NOTE);
            }
        } else {
            report.print(((i == 0) ? "" : ","), InterfaceReport.FORMAT_NOTE);
        }
        if (((Boolean) colNames.get(i)[2]).booleanValue() && UtilValidate.isEmpty(cellValue)) {
            report.print(UtilProperties.getMessage(resource, "ErrorColCannotEmpty",
                    new Object[] { colNames.get(i)[0] }, locale), InterfaceReport.FORMAT_WARNING);
            errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource,
                    "ErrorColCannotEmpty", new Object[] { colNames.get(i)[0] }, locale));
            foundError = true;
            results.add(null);
            continue;
        }
        if (((Boolean) colNames.get(i)[2]).booleanValue() && cellType != (int) colNames.get(i)[1]) {
            // String warningMessage = "";
            if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_STRING) {
                results.add(cellValue);
            } else if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_NUMERIC) {
                if (cell.getCellType() != XSSFCell.CELL_TYPE_STRING) {
                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                }
                try {
                    results.add(BigDecimal.valueOf(Double.parseDouble(cell.getStringCellValue()))
                            .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding));
                } catch (NumberFormatException e) {
                    results.add(null);
                    errorMessages.put(new CellReference(cell),
                            UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale));
                }
            }
        } else {
            if (UtilValidate.isEmpty(cellValue)) {
                results.add(null);
                continue;
            }
            if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_STRING) {
                if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                    results.add(cell.getStringCellValue());
                } else {
                    results.add(cellValue);
                }
            } else if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_NUMERIC) {
                if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                    try {
                        results.add(BigDecimal.valueOf(Double.valueOf(cell.getStringCellValue())));
                    } catch (NumberFormatException e) {
                        results.add(null);
                        errorMessages.put(new CellReference(cell),
                                UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale));
                    }
                } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                    try {
                        results.add(BigDecimal.valueOf(cell.getNumericCellValue())
                                .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding));
                    } catch (NumberFormatException e) {
                        results.add(null);
                        errorMessages.put(new CellReference(cell),
                                UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale));
                    }
                } else {
                    try {
                        results.add(BigDecimal.valueOf(Double.valueOf(cellValue))
                                .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding));
                    } catch (NumberFormatException e) {
                        results.add(null);
                        errorMessages.put(new CellReference(cell),
                                UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale));
                    }
                }
            }
        }
    }
    if (foundError) {
        return null;
    }
    return results;
}

From source file:org.apache.ofbiz.pricat.AbstractPricatParser.java

License:Apache License

protected boolean isEmptyRow(XSSFRow row, int size, boolean display) {
    // check whether this row is empty
    if (UtilValidate.isEmpty(row)) {
        report.print(UtilProperties.getMessage(resource, "ExcelEmptyRow", locale), InterfaceReport.FORMAT_NOTE);
        return true;
    }/*  w w  w . j  a v  a  2s  . co m*/
    boolean isEmptyRow = true;
    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
    int i = 0;
    for (; i < size; i++) {
        XSSFCell cell = null;
        if (physicalNumberOfCells > i) {
            cell = row.getCell(i);
        }
        if (cell != null && UtilValidate.isNotEmpty(formatter.formatCellValue(cell))
                && UtilValidate.isNotEmpty(formatter.formatCellValue(cell).trim())) {
            isEmptyRow = false;
            break;
        }
    }
    if (isEmptyRow) {
        if (display) {
            report.print(UtilProperties.getMessage(resource, "ExcelEmptyRow", locale),
                    InterfaceReport.FORMAT_NOTE);
        }
        return true;
    } else if (!isEmptyRow && i > size) {
        if (display) {
            report.print(UtilProperties.getMessage(resource, "IgnoreDataOutOfRange", locale),
                    InterfaceReport.FORMAT_NOTE);
        }
        return true;
    }
    return isEmptyRow;
}

From source file:org.apache.ofbiz.pricat.sample.SamplePricatParser.java

License:Apache License

private boolean parseCellContentsAndStoreV1_X(XSSFRow row, List<Object> cellContents)
        throws GenericTransactionException {
    if (UtilValidate.isEmpty(cellContents)) {
        return false;
    }//  ww  w  .  ja v a  2  s  .c o m
    // 1. check if facilityId is in the facilities belong to the user, or if the name is correct for the id
    String facilityName = (String) getCellContent(cellContents, "Facility Name");
    String facilityId = (String) getCellContent(cellContents, "FacilityId");
    if (!isFacilityOk(row, facilityName, facilityId))
        return false;

    // 2. get productCategoryId
    String ownerPartyId = facilities.get(facilityId)[1];
    String productCategoryId = getProductCategoryId(cellContents, ownerPartyId);

    // 3. get productFeatureId of brand
    String brandName = (String) getCellContent(cellContents, "Brand");
    String brandId = getBrandId(brandName, ownerPartyId);
    if (UtilValidate.isEmpty(brandId)) {
        return false;
    }

    // 4. get productId from brandId, model name
    String modelName = (String) getCellContent(cellContents, "Style No");
    String productName = (String) getCellContent(cellContents, "Product Name");
    BigDecimal listPrice = (BigDecimal) getCellContent(cellContents, "List Price");
    String productId = getProductId(row, brandId, modelName, productName, productCategoryId, ownerPartyId,
            listPrice);
    if (UtilValidate.isEmpty(productId) || UtilValidate.isEmpty(listPrice)) {
        return false;
    }

    // 5. update color and size if necessary
    String color = (String) getCellContent(cellContents, "Color");
    if (UtilValidate.isEmpty(color) || UtilValidate.isEmpty(color.trim())) {
        color = defaultColorName;
    }
    String dimension = (String) getCellContent(cellContents, "Size");
    if (UtilValidate.isEmpty(dimension) || UtilValidate.isEmpty(dimension.trim())) {
        dimension = defaultDimensionName;
    }
    Map<String, Object> features = updateColorAndDimension(productId, ownerPartyId, color, dimension);
    if (ServiceUtil.isError(features)) {
        if (features.containsKey("index") && String.valueOf(features.get("index")).contains("0")) {
            int cell = headerColNames.indexOf("Color");
            XSSFCell colorCell = row.getCell(cell);
            errorMessages.put(new CellReference(colorCell),
                    UtilProperties.getMessage(resource, "PricatColorError", locale));
        }
        if (features.containsKey("index") && String.valueOf(features.get("index")).contains("1")) {
            int cell = headerColNames.indexOf("Size");
            XSSFCell colorCell = row.getCell(cell);
            errorMessages.put(new CellReference(colorCell),
                    UtilProperties.getMessage(resource, "PricatDimensionError", locale));
        }
        return false;
    }
    String colorId = (String) features.get("colorId");
    String dimensionId = (String) features.get("dimensionId");

    // 6. update skuIds by productId
    String barcode = (String) getCellContent(cellContents, "Barcode");
    BigDecimal inventory = (BigDecimal) getCellContent(cellContents, "Stock Qty");
    BigDecimal averageCost = (BigDecimal) getCellContent(cellContents, "Average Cost");
    String skuId = updateSku(row, productId, ownerPartyId, facilityId, barcode, inventory, colorId, color,
            dimensionId, dimension, listPrice, averageCost);
    if (UtilValidate.isEmpty(skuId)) {
        return false;
    }

    // 7. store prices
    BigDecimal memberPrice = (BigDecimal) getCellContent(cellContents, "Member Price");
    Map<String, Object> results = updateSkuPrice(skuId, ownerPartyId, memberPrice);
    if (ServiceUtil.isError(results)) {
        return false;
    }

    return true;
}

From source file:org.apache.ofbiz.pricat.sample.SamplePricatParser.java

License:Apache License

public boolean isFacilityOk(XSSFRow row, String facilityName, String facilityId) {
    if (!facilities.containsKey(facilityId)) {
        if (UtilValidate.isEmpty(facilityId) && facilities.keySet().size() == 1) {
            if (UtilValidate.isEmpty(facilityName)) {
                return true;
            } else {
                String theFacilityId = (String) facilities.keySet().toArray()[0];
                String name = facilities.get(theFacilityId)[0];
                if (!name.equals(facilityName)) {
                    String errorMessage = UtilProperties.getMessage(resource, "FacilityNameNotMatchId",
                            new Object[] { theFacilityId, name, facilityName }, locale);
                    report.println();//from  ww  w  .ja  v  a 2  s .  com
                    report.print(errorMessage, InterfaceReport.FORMAT_ERROR);
                    XSSFCell cell = row.getCell(0);
                    errorMessages.put(new CellReference(cell), errorMessage);
                    return false;
                }
            }
        } else {
            String errorMessage = UtilProperties.getMessage(resource, "FacilityNotBelongToYou",
                    new Object[] { facilityName, facilityId }, locale);
            report.println();
            report.print(errorMessage, InterfaceReport.FORMAT_ERROR);
            XSSFCell cell = row.getCell(1);
            errorMessages.put(new CellReference(cell), errorMessage);
            return false;
        }
    } else {
        String name = facilities.get(facilityId)[0];
        if (!name.equals(facilityName)) {
            String errorMessage = UtilProperties.getMessage(resource, "FacilityNameNotMatchId",
                    new Object[] { facilityId, name, facilityName }, locale);
            report.println();
            report.print(errorMessage, InterfaceReport.FORMAT_ERROR);
            XSSFCell cell = row.getCell(0);
            errorMessages.put(new CellReference(cell), errorMessage);
            return false;
        }
    }
    return true;
}

From source file:org.apache.ofbiz.pricat.sample.SamplePricatParser.java

License:Apache License

/**
 * Get data by version definition.//  w ww.j av  a  2  s. c  o m
 * 
 * @param row
 * @param colNames 
 * @param size 
 * @return
 */
public List<Object> getCellContents(XSSFRow row, List<Object[]> colNames, int size) {
    List<Object> results = new ArrayList<Object>();
    boolean foundError = false;
    if (isEmptyRow(row, size, true)) {
        return null;
    }

    // check and get data
    for (int i = 0; i < size; i++) {
        XSSFCell cell = null;
        if (row.getPhysicalNumberOfCells() > i) {
            cell = row.getCell(i);
        }
        if (cell == null) {
            if (((Boolean) colNames.get(i)[2]).booleanValue()
                    && (facilities.keySet().size() > 1 || (facilities.keySet().size() == 1 && i >= 2))) {
                report.print(UtilProperties.getMessage(resource, "ErrorColCannotEmpty",
                        new Object[] { colNames.get(i)[0] }, locale), InterfaceReport.FORMAT_WARNING);
                cell = row.createCell(i);
                errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource,
                        "ErrorColCannotEmpty", new Object[] { colNames.get(i)[0] }, locale));
                foundError = true;
                results.add(null);
                continue;
            } else {
                cell = row.createCell(i);
            }
        }
        int cellType = cell.getCellType();
        String cellValue = formatter.formatCellValue(cell);
        if (UtilValidate.isNotEmpty(cellValue) && UtilValidate.isNotEmpty(cellValue.trim())) {
            if (cellType == XSSFCell.CELL_TYPE_FORMULA) {
                try {
                    cellValue = BigDecimal.valueOf(cell.getNumericCellValue())
                            .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding).toString();
                } catch (IllegalStateException e) {
                    try {
                        cellValue = cell.getStringCellValue();
                    } catch (IllegalStateException e1) {
                        // do nothing
                    }
                }
                report.print(((i == 0) ? "" : ", ") + cellValue, InterfaceReport.FORMAT_NOTE);
            } else {
                report.print(((i == 0) ? "" : ", ") + cellValue, InterfaceReport.FORMAT_NOTE);
            }
        } else {
            report.print(((i == 0) ? "" : ","), InterfaceReport.FORMAT_NOTE);
        }
        if (((Boolean) colNames.get(i)[2]).booleanValue() && UtilValidate.isEmpty(cellValue)
                && (facilities.keySet().size() > 1 || (facilities.keySet().size() == 1 && i >= 2))) {
            report.print(UtilProperties.getMessage(resource, "ErrorColCannotEmpty",
                    new Object[] { colNames.get(i)[0] }, locale), InterfaceReport.FORMAT_WARNING);
            errorMessages.put(new CellReference(cell), UtilProperties.getMessage(resource,
                    "ErrorColCannotEmpty", new Object[] { colNames.get(i)[0] }, locale));
            foundError = true;
            results.add(null);
            continue;
        }
        if (((Boolean) colNames.get(i)[2]).booleanValue() && cellType != (int) colNames.get(i)[1]) {
            // String warningMessage = "";
            if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_STRING) {
                if (UtilValidate.isNotEmpty(cellValue) && UtilValidate.isNotEmpty(cellValue.trim())) {
                    results.add(cellValue);
                } else {
                    results.add(null);
                }
            } else if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_NUMERIC) {
                if (cell.getCellType() != XSSFCell.CELL_TYPE_STRING) {
                    cell.setCellType(XSSFCell.CELL_TYPE_STRING);
                }
                try {
                    results.add(BigDecimal.valueOf(Double.parseDouble(cell.getStringCellValue()))
                            .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding));
                } catch (NumberFormatException e) {
                    results.add(null);
                    errorMessages.put(new CellReference(cell),
                            UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale));
                }
            }
        } else {
            if (UtilValidate.isEmpty(cellValue) || UtilValidate.isEmpty(cellValue.trim())) {
                results.add(null);
                continue;
            }
            if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_STRING) {
                if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                    cellValue = cell.getStringCellValue().trim();
                    results.add(cellValue);
                } else {
                    results.add(cellValue.trim());
                }
            } else if ((int) colNames.get(i)[1] == XSSFCell.CELL_TYPE_NUMERIC) {
                if (cell.getCellType() == XSSFCell.CELL_TYPE_STRING) {
                    try {
                        results.add(BigDecimal.valueOf(Double.valueOf(cell.getStringCellValue())));
                    } catch (NumberFormatException e) {
                        results.add(null);
                        errorMessages.put(new CellReference(cell),
                                UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale));
                    }
                } else if (cell.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
                    try {
                        results.add(BigDecimal.valueOf(cell.getNumericCellValue())
                                .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding));
                    } catch (NumberFormatException e) {
                        results.add(null);
                        errorMessages.put(new CellReference(cell),
                                UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale));
                    }
                } else {
                    try {
                        results.add(BigDecimal.valueOf(Double.valueOf(cellValue))
                                .setScale(FinAccountHelper.decimals, FinAccountHelper.rounding));
                    } catch (NumberFormatException e) {
                        results.add(null);
                        errorMessages.put(new CellReference(cell),
                                UtilProperties.getMessage(resource, "ErrorParseValueToNumeric", locale));
                    }
                }
            }
        }
    }
    if (foundError) {
        return null;
    }
    return results;
}

From source file:org.azkfw.datasource.excel.ExcelDatasourceBuilder.java

License:Apache License

/**
 * ?//from w  ww . j  a v a 2s . c  om
 * 
 * @return 
 * @throws FileNotFoundException
 * @throws ParseException
 * @throws IOException
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public Datasource build() throws FileNotFoundException, ParseException, IOException {
    ExcelDatasource datasource = new ExcelDatasource();
    datasource.name = datasourceName;

    InputStream stream = null;
    try {
        List<Table> tables = new ArrayList<>();

        for (File file : excelFiles) {

            stream = new FileInputStream(file);
            XSSFWorkbook workbook = new XSSFWorkbook(stream);
            int cntSheet = workbook.getNumberOfSheets();
            for (int i = 0; i < cntSheet; i++) {
                String sheetName = workbook.getSheetName(i); // sheet name -> table name

                ExcelTable table = new ExcelTable();

                Matcher matcher = PTN_TABLE_NAME.matcher(sheetName);
                if (matcher.find()) {
                    table.label = matcher.group(3);
                    table.name = matcher.group(1);
                } else {
                    table.label = sheetName;
                    table.name = sheetName;
                }

                XSSFSheet sheet = workbook.getSheetAt(i);
                // Check row size
                int cntRow = sheet.getLastRowNum() + 1;
                if (3 > cntRow) {
                    System.out.println("Skip sheet[" + sheetName + "]. row size < 3");
                    continue;
                }

                // Read Field
                List<ExcelField> fields = new ArrayList<ExcelField>();
                XSSFRow rowLabel = sheet.getRow(0);
                XSSFRow rowName = sheet.getRow(1);
                XSSFRow rowType = sheet.getRow(2);
                for (int col = 0; col < rowLabel.getLastCellNum(); col++) {
                    ExcelField field = readField(col, rowLabel.getCell(col), rowName.getCell(col),
                            rowType.getCell(col));
                    fields.add(field);
                }

                // Read Data
                List<ExcelRecord> records = new ArrayList<ExcelRecord>();
                for (int row = 3; row < cntRow; row++) {
                    XSSFRow xssfrow = sheet.getRow(row);
                    if (!isEmptyRow(xssfrow)) {
                        ExcelRecord record = readData(row, xssfrow, fields);
                        records.add(record);
                    } else {
                        System.out
                                .println("Skip empty row.[table: " + table.getName() + "; row: " + row + ";]");
                    }
                }

                table.fields = (List) fields;
                table.records = (List) records;

                tables.add(table);
            }
        }

        datasource.tables = tables;

    } catch (FileNotFoundException ex) {
        throw ex;
    } catch (ParseException ex) {
        throw ex;
    } catch (IOException ex) {
        throw ex;
    } finally {
        if (null != stream) {
            try {
                stream.close();
            } catch (IOException ex) {
            } finally {
                stream = null;
            }
        }
    }

    return datasource;
}

From source file:org.azkfw.datasource.excel.ExcelDatasourceBuilder.java

License:Apache License

private ExcelRecord readData(final int aRowNum, final XSSFRow aRow, final List<ExcelField> aFields)
        throws ParseException {
    Map<String, Object> data = new HashMap<String, Object>();
    for (int i = 0; i < aFields.size(); i++) {
        ExcelField field = aFields.get(i);

        int col = field.col;
        XSSFCell cell = aRow.getCell(col);

        String value = toStringFromCell(cell);

        if (nullString.equals(value)) {
            data.put(field.name, null);//from w ww . java  2  s. c o m
        } else {
            if (FieldType.String == field.type) {
                String obj = value;
                data.put(field.name, obj);
            } else if (FieldType.Boolean == field.type) {
                Boolean obj = Boolean.parseBoolean(value);
                data.put(field.name, obj);
            } else if (FieldType.Integer == field.type) {
                Double obj = Double.parseDouble(value);
                data.put(field.name, Integer.valueOf(obj.intValue()));
            } else if (FieldType.Long == field.type) {
                Double obj = Double.parseDouble(value);
                data.put(field.name, Long.valueOf(obj.longValue()));
            } else if (FieldType.Float == field.type) {
                Float obj = Float.parseFloat(value);
                data.put(field.name, obj);
            } else if (FieldType.Double == field.type) {
                Double obj = Double.parseDouble(value);
                data.put(field.name, obj);
            } else if (FieldType.Timestamp == field.type) {
                Timestamp obj = null;
                if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                    obj = new Timestamp(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").parse(value).getTime());
                } else {
                    obj = new Timestamp(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").parse(value).getTime());
                }
                data.put(field.name, obj);
            } else if (FieldType.Date == field.type) {
                Timestamp ts = null;
                if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                    ts = new Timestamp(new SimpleDateFormat("yyyy/MM/dd").parse(value).getTime());
                } else {
                    ts = new Timestamp(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").parse(value).getTime());
                }
                Date obj = new Date(ts.getTime());
                data.put(field.name, obj);
            } else if (FieldType.Time == field.type) {
                Timestamp ts = null;
                if (Cell.CELL_TYPE_STRING == cell.getCellType()) {
                    ts = new Timestamp(new SimpleDateFormat("HH:mm:ss").parse(value).getTime());
                } else {
                    ts = new Timestamp(new SimpleDateFormat("yyyy/MM/dd HH:mm:ss").parse(value).getTime());
                }
                Time obj = new Time(ts.getTime());
                data.put(field.name, obj);
            } else {
                throw new ParseException("Undefined type.[" + field.getType() + "]", aRowNum);
            }
        }
    }

    ExcelRecord record = new ExcelRecord();
    record.data = data;
    return record;
}