Java tutorial
/******************************************************************************* * Licensed to the Apache Software Foundation (ASF) under one * or more contributor license agreements. See the NOTICE file * distributed with this work for additional information * regarding copyright ownership. The ASF licenses this file * to you under the Apache License, Version 2.0 (the * "License"); you may not use this file except in compliance * with the License. You may obtain a copy of the License at * * http://www.apache.org/licenses/LICENSE-2.0 * * Unless required by applicable law or agreed to in writing, * software distributed under the License is distributed on an * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY * KIND, either express or implied. See the License for the * specific language governing permissions and limitations * under the License. *******************************************************************************/ package org.apache.ofbiz.pricat.sample; import java.io.File; import java.io.FileInputStream; import java.io.IOException; import java.math.BigDecimal; import java.util.ArrayList; import java.util.List; import java.util.Locale; import java.util.Map; import org.apache.poi.POIXMLException; import org.apache.poi.hssf.usermodel.HSSFDataFormatter; import org.apache.poi.ss.util.CellReference; import org.apache.poi.xssf.usermodel.XSSFCell; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; import org.apache.commons.fileupload.FileItem; import org.apache.ofbiz.pricat.AbstractPricatParser; import org.apache.ofbiz.htmlreport.InterfaceReport; import org.apache.ofbiz.order.finaccount.FinAccountHelper; import org.apache.ofbiz.base.util.Debug; import org.apache.ofbiz.base.util.UtilMisc; import org.apache.ofbiz.base.util.UtilProperties; import org.apache.ofbiz.base.util.UtilValidate; import org.apache.ofbiz.entity.Delegator; import org.apache.ofbiz.entity.GenericEntityException; import org.apache.ofbiz.entity.GenericValue; import org.apache.ofbiz.entity.transaction.GenericTransactionException; import org.apache.ofbiz.service.LocalDispatcher; import org.apache.ofbiz.service.ServiceUtil; /** * Sample pricat excel parser. * */ public class SamplePricatParser extends AbstractPricatParser { public static final String module = SamplePricatParser.class.getName(); public static final Map<String, List<Object[]>> ColNamesList = UtilMisc.toMap("V1.1", genExcelHeaderNames("V1.1")); public static final int headerRowNo = 4; private List<String> headerColNames = new ArrayList<String>(); public SamplePricatParser(LocalDispatcher dispatcher, Delegator delegator, Locale locale, InterfaceReport report, Map<String, String[]> facilities, File pricatFile, GenericValue userLogin) { super(dispatcher, delegator, locale, report, facilities, pricatFile, userLogin); } /** * Parse pricat excel file in xlsx format. * */ public void parsePricatExcel(boolean writeFile) { XSSFWorkbook workbook = null; try { // 1. read the pricat excel file FileInputStream is = new FileInputStream(pricatFile); // 2. use POI to load this bytes report.print(UtilProperties.getMessage(resource, "ParsePricatFileStatement", new Object[] { pricatFile.getName() }, locale), InterfaceReport.FORMAT_DEFAULT); try { workbook = new XSSFWorkbook(is); report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); } catch (IOException e) { report.println(e); report.println(UtilProperties.getMessage(resource, "PricatSuggestion", locale), InterfaceReport.FORMAT_ERROR); return; } catch (POIXMLException e) { report.println(e); report.println(UtilProperties.getMessage(resource, "PricatSuggestion", locale), InterfaceReport.FORMAT_ERROR); return; } // 3. only first sheet will be parsed // 3.1 verify the file has a sheet at least formatter = new HSSFDataFormatter(locale); isNumOfSheetsOK(workbook); // 3.2 verify the version is supported XSSFSheet sheet = workbook.getSheetAt(0); if (!isVersionSupported(sheet)) { return; } // 3.3 get currencyId existsCurrencyId(sheet); // 3.4 verify the table header row is just the same as column names, if not, print error and return if (!isTableHeaderMatched(sheet)) { return; } // 3.5 verify the first table has 6 rows at least containsDataRows(sheet); if (UtilValidate.isNotEmpty(errorMessages)) { report.println(UtilProperties.getMessage(resource, "HeaderContainsError", locale), InterfaceReport.FORMAT_ERROR); return; } // 4. parse data // 4.1 parse row by row and store the contents into database parseRowByRow(sheet); if (UtilValidate.isNotEmpty(errorMessages)) { report.println(UtilProperties.getMessage(resource, "DataContainsError", locale), InterfaceReport.FORMAT_ERROR); if (writeFile) { sequenceNum = report.getSequenceNum(); writeCommentsToFile(workbook, sheet); } } // 5. clean up the log files and commented Excel files cleanupLogAndCommentedExcel(); } catch (IOException e) { report.println(e); Debug.logError(e, module); } finally { if (UtilValidate.isNotEmpty(fileItems)) { // remove tmp files FileItem fi = null; for (int i = 0; i < fileItems.size(); i++) { fi = fileItems.get(i); fi.delete(); } } if (workbook != null) { try { workbook.close(); } catch (IOException e) { Debug.logError(e, module); } } } } public boolean existsCurrencyId(XSSFSheet sheet) { report.print(UtilProperties.getMessage(resource, "StartCheckCurrencyId", locale), InterfaceReport.FORMAT_NOTE); XSSFCell currencyIdCell = sheet.getRow(2).getCell(1); currencyId = currencyIdCell.getStringCellValue().trim().toUpperCase(); if (UtilValidate.isEmpty(currencyId)) { String errorMessage = UtilProperties.getMessage(resource, "CurrencyIdRequired", locale); report.println(errorMessage, InterfaceReport.FORMAT_ERROR); errorMessages.put(new CellReference(currencyIdCell), errorMessage); return false; } else { try { GenericValue currencyUom = delegator.findOne("Uom", UtilMisc.toMap("uomId", currencyId), false); if (!"CURRENCY_MEASURE".equals(currencyUom.getString("uomTypeId"))) { String errorMessage = UtilProperties.getMessage(resource, "CurrencyIdNotCurrency", new Object[] { currencyId }, locale); report.println(errorMessage, InterfaceReport.FORMAT_ERROR); errorMessages.put(new CellReference(currencyIdCell), errorMessage); return false; } } catch (GenericEntityException e) { String errorMessage = UtilProperties.getMessage(resource, "CurrencyIdNotFound", new Object[] { currencyId }, locale); report.println(errorMessage, InterfaceReport.FORMAT_ERROR); errorMessages.put(new CellReference(currencyIdCell), errorMessage); return false; } report.print(UtilProperties.getMessage(resource, "CurrencyIdIs", new Object[] { currencyId }, locale), InterfaceReport.FORMAT_NOTE); report.println(" ... " + UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); } return true; } public void parseRowByRow(XSSFSheet sheet) { int rows = sheet.getPhysicalNumberOfRows(); List<Object[]> colNames = ColNamesList.get(pricatFileVersion); int colNumber = colNames.size(); int emptyRowStart = -1; int emptyRowEnd = -1; for (int i = headerRowNo + 1; i < rows; i++) { XSSFRow row = sheet.getRow(i); if (UtilValidate.isEmpty(row) || isEmptyRow(row, colNumber, false)) { if (emptyRowStart == -1) { report.print("(" + (i + 1) + ") ", InterfaceReport.FORMAT_NOTE); emptyRowStart = i; } else { emptyRowEnd = i; } continue; } else { if (emptyRowStart != -1) { if (emptyRowEnd != -1) { report.print(" - (" + (emptyRowEnd + 1) + ") ", InterfaceReport.FORMAT_NOTE); } report.print(UtilProperties.getMessage(resource, "ExcelEmptyRow", locale), InterfaceReport.FORMAT_NOTE); report.println(" ... " + UtilProperties.getMessage(resource, "skipped", locale), InterfaceReport.FORMAT_NOTE); emptyRowStart = -1; emptyRowEnd = -1; } } report.print("(" + (i + 1) + ") ", InterfaceReport.FORMAT_NOTE); List<Object> cellContents = getCellContents(row, colNames, colNumber); try { if (parseCellContentsAndStore(row, cellContents)) { report.println(" ... " + UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); } else { report.println(" ... " + UtilProperties.getMessage(resource, "skipped", locale), InterfaceReport.FORMAT_NOTE); } } catch (GenericTransactionException e) { report.println(e); } } if (emptyRowEnd != -1) { report.print(" - (" + (emptyRowEnd + 1) + ") ", InterfaceReport.FORMAT_NOTE); report.print(UtilProperties.getMessage(resource, "ExcelEmptyRow", locale), InterfaceReport.FORMAT_NOTE); report.println(" ... " + UtilProperties.getMessage(resource, "skipped", locale), InterfaceReport.FORMAT_NOTE); } } /** * Check data according to business logic. If data is ok, store it. * * @param row * @param cellContents * @param document * @return * @throws GenericTransactionException */ public boolean parseCellContentsAndStore(XSSFRow row, List<Object> cellContents) throws GenericTransactionException { if (UtilValidate.isEmpty(cellContents)) { return false; } switch (pricatFileVersion) { case "V1.1": default: return parseCellContentsAndStoreV1_X(row, cellContents); } } private boolean parseCellContentsAndStoreV1_X(XSSFRow row, List<Object> cellContents) throws GenericTransactionException { if (UtilValidate.isEmpty(cellContents)) { return false; } // 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; } public String updateSku(XSSFRow row, String productId, String ownerPartyId, String facilityId, String barcode, BigDecimal inventory, String colorId, String color, String dimensionId, String dimension, BigDecimal listPrice, BigDecimal averageCost) { return "sampleSkuId"; } public String getProductId(XSSFRow row, String brandId, String modelName, String productName, String productCategoryId, String ownerPartyId, BigDecimal listPrice) { return "sampleProductId"; } public Object getCellContent(List<Object> cellContents, String colName) { if (UtilValidate.isNotEmpty(headerColNames) && headerColNames.contains(colName)) { return cellContents.get(headerColNames.indexOf(colName)); } return null; } public String getProductCategoryId(List<Object> cellContents, String ownerPartyId) { return "sampleProductCategoryId"; } 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(); 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; } public boolean isTableHeaderMatched(XSSFSheet sheet) { List<Object[]> columnNames = ColNamesList.get(pricatFileVersion); short cols = sheet.getRow(headerRowNo).getLastCellNum(); report.print(UtilProperties.getMessage(resource, "StartCheckHeaderColNum", new Object[] { pricatFileVersion }, locale), InterfaceReport.FORMAT_NOTE); if (cols != columnNames.size()) { report.print( UtilProperties.getMessage(resource, "HeaderColNumNotMatch", new Object[] { String.valueOf(cols), String.valueOf(columnNames.size()) }, locale), InterfaceReport.FORMAT_WARNING); if (cols < columnNames.size()) { report.println( UtilProperties.getMessage(resource, "HeaderColNumShortThanRequired", new Object[] { String.valueOf(columnNames.size()) }, locale), InterfaceReport.FORMAT_ERROR); return false; } else { report.println( UtilProperties.getMessage(resource, "UseHeaderColNum", new Object[] { String.valueOf(columnNames.size()) }, locale), InterfaceReport.FORMAT_WARNING); cols = (short) columnNames.size(); } } else { report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); } report.print(UtilProperties.getMessage(resource, "StartCheckHeaderColLabel", new Object[] { pricatFileVersion }, locale), InterfaceReport.FORMAT_NOTE); boolean foundLabelNotMatch = false; for (int i = 0; i < cols; i++) { String coltext = sheet.getRow(headerRowNo).getCell(i).getStringCellValue().trim(); headerColNames.add(coltext); Object[] versionColumn = columnNames.get(i); if (!coltext.equals(versionColumn[0])) { report.println(UtilProperties.getMessage(resource, "HeaderColLabelNotMatch", new Object[] { String.valueOf(headerRowNo + 1), String.valueOf(i + 1), coltext, versionColumn[0] }, locale), InterfaceReport.FORMAT_ERROR); foundLabelNotMatch = true; } else { report.print(" " + coltext, InterfaceReport.FORMAT_NOTE); if (i < cols - 1) { report.print(",", InterfaceReport.FORMAT_NOTE); } } } if (foundLabelNotMatch) { report.println(); return false; } report.println(" ... " + UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); return true; } public boolean isVersionSupported(XSSFSheet sheet) { report.print(UtilProperties.getMessage(resource, "StartCheckPricatVersion", locale), InterfaceReport.FORMAT_NOTE); pricatFileVersion = sheet.getRow(2).getCell(0).getStringCellValue().trim(); if (ColNamesList.containsKey(pricatFileVersion)) { report.print(" " + pricatFileVersion + " ... ", InterfaceReport.FORMAT_NOTE); report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); } else { report.println(UtilProperties.getMessage(resource, "error", locale), InterfaceReport.FORMAT_ERROR); report.println(UtilProperties.getMessage(resource, "PricatVersionNotSupport", new Object[] { pricatFileVersion }, locale), InterfaceReport.FORMAT_ERROR); return false; } return true; } public boolean containsDataRows(XSSFSheet sheet) { int rows = sheet.getPhysicalNumberOfRows(); if (rows > headerRowNo + 1) { report.println( UtilProperties .getMessage(resource, "PricatTableRows", new Object[] { String.valueOf(headerRowNo + 1), String.valueOf(rows - headerRowNo - 1), sheet.getSheetName() }, locale), InterfaceReport.FORMAT_NOTE); } else { report.println(UtilProperties.getMessage(resource, "PricatNoDataRows", new Object[] { sheet.getSheetName() }, locale), InterfaceReport.FORMAT_ERROR); return false; } return true; } /** * The Object[] have 4 elements, they are: * 1. Header Label Name. * 2. Cell data type to return. * 3. Boolean value to indicate whether the column is required. * 4. Boolean value to indicate whether the column is a price when cell data type is BigDecimal, this element is optional. * * @param version * @return List of Object[] */ private static List<Object[]> genExcelHeaderNames(String version) { switch (version) { case "V1.1": default: return genExcelHeaderNamesV1_1(); } } /** * Get V1.1 pricat excel header names and attributes. * * @return list of Object[] */ private static List<Object[]> genExcelHeaderNamesV1_1() { List<Object[]> listHeaderName = new ArrayList<Object[]>(); listHeaderName.add(new Object[] { "Facility Name", XSSFCell.CELL_TYPE_STRING, Boolean.TRUE }); listHeaderName.add(new Object[] { "FacilityId", XSSFCell.CELL_TYPE_STRING, Boolean.TRUE }); listHeaderName.add(new Object[] { "Category L1", XSSFCell.CELL_TYPE_STRING, Boolean.FALSE }); listHeaderName.add(new Object[] { "Category L2", XSSFCell.CELL_TYPE_STRING, Boolean.FALSE }); listHeaderName.add(new Object[] { "Category L3", XSSFCell.CELL_TYPE_STRING, Boolean.FALSE }); listHeaderName.add(new Object[] { "Category L4", XSSFCell.CELL_TYPE_STRING, Boolean.FALSE }); listHeaderName.add(new Object[] { "Brand", XSSFCell.CELL_TYPE_STRING, Boolean.TRUE }); listHeaderName.add(new Object[] { "Style No", XSSFCell.CELL_TYPE_STRING, Boolean.TRUE }); listHeaderName.add(new Object[] { "Product Name", XSSFCell.CELL_TYPE_STRING, Boolean.TRUE }); listHeaderName.add(new Object[] { "Color", XSSFCell.CELL_TYPE_STRING, Boolean.FALSE }); listHeaderName.add(new Object[] { "Size", XSSFCell.CELL_TYPE_STRING, Boolean.FALSE }); listHeaderName.add(new Object[] { "Barcode", XSSFCell.CELL_TYPE_STRING, Boolean.FALSE }); listHeaderName.add(new Object[] { "Stock Qty", XSSFCell.CELL_TYPE_NUMERIC, Boolean.TRUE }); listHeaderName.add(new Object[] { "Average Cost", XSSFCell.CELL_TYPE_NUMERIC, Boolean.TRUE, Boolean.TRUE }); listHeaderName.add(new Object[] { "List Price", XSSFCell.CELL_TYPE_NUMERIC, Boolean.TRUE, Boolean.TRUE }); listHeaderName .add(new Object[] { "Member Price", XSSFCell.CELL_TYPE_NUMERIC, Boolean.FALSE, Boolean.TRUE }); return listHeaderName; } @Override public void parsePricatExcel() { parsePricatExcel(true); } /** * Get data by version definition. * * @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; } protected int getHeaderRowNo() { return headerRowNo; } }