List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet getLastRowNum
@Override public int getLastRowNum()
From source file:org.ofbiz.product.spreadsheetimport.ImportProductServices.java
License:Apache License
/** * This method is responsible to import spreadsheet data into "Product" and * "InventoryItem" entities into database. The method uses the * ImportProductHelper class to perform its operation. The method uses "Apache * POI" api for importing spreadsheet (xls files) data. * * Note : Create the spreadsheet directory in the ofbiz home folder and keep * your xls files in this folder only.//from w ww . jav a2s.com * * @param dctx the dispatch context * @param context the context * @return the result of the service execution */ public static Map<String, Object> productImportFromSpreadsheet(DispatchContext dctx, Map<String, ? extends Object> context) { Delegator delegator = dctx.getDelegator(); Locale locale = (Locale) context.get("locale"); // System.getProperty("user.dir") returns the path upto ofbiz home // directory String path = System.getProperty("user.dir") + "/spreadsheet"; List<File> fileItems = FastList.newInstance(); if (UtilValidate.isNotEmpty(path)) { File importDir = new File(path); if (importDir.isDirectory() && importDir.canRead()) { File[] files = importDir.listFiles(); // loop for all the containing xls file in the spreadsheet // directory for (int i = 0; i < files.length; i++) { if (files[i].getName().toUpperCase().endsWith("XLS")) { fileItems.add(files[i]); } } } else { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportDirectoryNotFound", locale)); } } else { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportPathNotSpecified", locale)); } if (fileItems.size() < 1) { return ServiceUtil.returnError( UtilProperties.getMessage(resource, "ProductProductImportPathNoSpreadsheetExists", locale) + path); } for (File item : fileItems) { // read all xls file and create workbook one by one. List<Map<String, Object>> products = FastList.newInstance(); List<Map<String, Object>> inventoryItems = FastList.newInstance(); POIFSFileSystem fs = null; HSSFWorkbook wb = null; try { fs = new POIFSFileSystem(new FileInputStream(item)); wb = new HSSFWorkbook(fs); } catch (IOException e) { Debug.logError("Unable to read or create workbook from file", module); return ServiceUtil.returnError(UtilProperties.getMessage(resource, "ProductProductImportCannotCreateWorkbookFromFile", locale)); } // get first sheet HSSFSheet sheet = wb.getSheetAt(0); int sheetLastRowNumber = sheet.getLastRowNum(); for (int j = 1; j <= sheetLastRowNumber; j++) { HSSFRow row = sheet.getRow(j); if (row != null) { // read productId from first column "sheet column index // starts from 0" HSSFCell cell2 = row.getCell(2); cell2.setCellType(HSSFCell.CELL_TYPE_STRING); String productId = cell2.getRichStringCellValue().toString(); // read QOH from ninth column HSSFCell cell5 = row.getCell(5); BigDecimal quantityOnHand = BigDecimal.ZERO; if (cell5 != null && cell5.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) quantityOnHand = new BigDecimal(cell5.getNumericCellValue()); // check productId if null then skip creating inventory item // too. boolean productExists = ImportProductHelper.checkProductExists(productId, delegator); if (productId != null && !productId.trim().equalsIgnoreCase("") && !productExists) { products.add(ImportProductHelper.prepareProduct(productId)); if (quantityOnHand.compareTo(BigDecimal.ZERO) >= 0) inventoryItems.add(ImportProductHelper.prepareInventoryItem(productId, quantityOnHand, delegator.getNextSeqId("InventoryItem"))); else inventoryItems.add(ImportProductHelper.prepareInventoryItem(productId, BigDecimal.ZERO, delegator.getNextSeqId("InventoryItem"))); } int rowNum = row.getRowNum() + 1; if (row.toString() != null && !row.toString().trim().equalsIgnoreCase("") && productExists) { Debug.logWarning("Row number " + rowNum + " not imported from " + item.getName(), module); } } } // create and store values in "Product" and "InventoryItem" entity // in database for (int j = 0; j < products.size(); j++) { GenericValue productGV = delegator.makeValue("Product", products.get(j)); GenericValue inventoryItemGV = delegator.makeValue("InventoryItem", inventoryItems.get(j)); if (!ImportProductHelper.checkProductExists(productGV.getString("productId"), delegator)) { try { delegator.create(productGV); delegator.create(inventoryItemGV); } catch (GenericEntityException e) { Debug.logError("Cannot store product", module); return ServiceUtil.returnError(UtilProperties.getMessage(resource, "ProductProductImportCannotStoreProduct", locale)); } } } int uploadedProducts = products.size() + 1; if (products.size() > 0) Debug.logInfo("Uploaded " + uploadedProducts + " products from file " + item.getName(), module); } return ServiceUtil.returnSuccess(); }
From source file:org.ofbiz.tools.rest.FixOfcTools.java
/** * ?Xls?// w w w. j a va 2s . co m * @param filePathAndName */ public static void xls2XmlForProductByCategory(String filePathAndName, String filePath) { long featureIdx = 1000; long category0Idx = 1; long featureTypeIdx = 1000; String featureTypePrefix = "FT-"; String featurePrefix = "FE-"; String productCategoryPrefix = "YUNTOU"; long category1Idx = 1; long category2Idx = 1; long category3Idx = 1; //?? String temp_ProdCatalogCategory = "<ProdCatalogCategory productCategoryId=\"#11#\" prodCatalogId=\"ytRootCatalog\" prodCatalogCategoryTypeId=\"PCCT_BROWSE_ROOT\" fromDate=\"2014-05-01 12:00:00.0\" />"; String temp_ProductCategory = "<ProductCategory productCategoryId=\"#11#\" productCategoryTypeId=\"CATALOG_CATEGORY\" categoryName=\"#21#\" />"; String temp_ProductCategoryRollup = "<ProductCategoryRollup parentProductCategoryId=\"#11#\" productCategoryId=\"#21#\" fromDate=\"2014-05-01 12:00:00.0\" />"; String temp_featuryCategory = "<ProductFeatureCategory productFeatureCategoryId=\"#11#\" description=\"#21#\"/>"; String temp_featuryCategoryAppl = "<ProductFeatureCategoryAppl productFeatureCategoryId=\"#11#\" productCategoryId=\"#21#\" fromDate=\"2014-05-01 12:00:00.0\"/>"; String temp_ProductFeatureType = "<ProductFeatureType productFeatureTypeId=\"#11#\" description=\"#21#\"/>"; String temp_ProductFeature = "<ProductFeature productFeatureId=\"#11#\" productFeatureTypeId=\"#21#\" productFeatureCategoryId=\"#31#\" description=\"#41#\"/>"; Map<String, String> featureIdMap = new HashMap<String, String>(); Map<String, String> featureMap = new HashMap<String, String>(); Map<String, String> featureTypeMap = new HashMap<String, String>(); Map<String, String> featureTypeIdMap = new HashMap<String, String>(); Map<String, String> productCategoryMemberMap = new HashMap<String, String>(); Map<String, String> featureCategoryMap = new HashMap<String, String>(); Map<String, String> featureCategorApplMap = new HashMap<String, String>(); Map<String, String> prodCatalogCategoryMap = new HashMap<String, String>(); Map<String, String> productCategoryRollupMap = new HashMap<String, String>(); Map<String, String> productCategory0Map = new HashMap<String, String>(); Map<String, String> productCategory1Map = new HashMap<String, String>(); Map<String, String> productCategory2Map = new HashMap<String, String>(); Map<String, String> productCategory3Map = new HashMap<String, String>(); Map<String, String> productCategoryId0Map = new HashMap<String, String>(); Map<String, String> productCategoryId1Map = new HashMap<String, String>(); Map<String, String> productCategoryId2Map = new HashMap<String, String>(); Map<String, String> productCategoryId3Map = new HashMap<String, String>(); HSSFWorkbook workbook = null; HSSFSheet sheet = null; try { // workbook = new HSSFWorkbook(new FileInputStream(new File(filePathAndName))); sheet = workbook.getSheetAt(0); int rowEnd = sheet.getLastRowNum(); //? int i = 1; for (; i < rowEnd + 1; i++) { System.out.println("?" + i + ""); HSSFRow row = sheet.getRow(i); //1. String productCategoryId0 = ""; if (UtilValidate.isNotEmpty(convertCell(row.getCell(0)))) { if (!productCategory0Map.containsKey(convertCell(row.getCell(0)))) { productCategoryId0 = productCategoryPrefix + String.format("%02d", category0Idx++); String productCategory = temp_ProductCategory.replace("#11#", productCategoryId0); productCategory = productCategory.replace("#21#", convertCell(row.getCell(0))); productCategory0Map.put(convertCell(row.getCell(0)), productCategory); //? String featuryCategory = temp_featuryCategory.replace("#11#", productCategoryId0); featuryCategory = featuryCategory.replace("#21#", convertCell(row.getCell(0))); featureCategoryMap.put(convertCell(row.getCell(0)), featuryCategory); //?? String featuryCategoryAppl = temp_featuryCategoryAppl.replace("#11#", productCategoryId0); featuryCategoryAppl = featuryCategoryAppl.replace("#21#", productCategoryId0); featureCategorApplMap.put(productCategoryId0, featuryCategoryAppl); productCategoryId0Map.put(convertCell(row.getCell(0)), productCategoryId0); } else { productCategoryId0 = productCategoryId0Map.get(convertCell(row.getCell(0))); } //1 if (UtilValidate.isNotEmpty(productCategoryId0)) { if (UtilValidate.isEmpty(prodCatalogCategoryMap.get(productCategoryId0))) { String ProdCatalogCategory = temp_ProdCatalogCategory.replace("#11#", productCategoryId0); prodCatalogCategoryMap.put(productCategoryId0, ProdCatalogCategory); } } } //2, String productCategoryId1 = ""; if (UtilValidate.isNotEmpty(convertCell(row.getCell(1)))) { if (!productCategoryId1Map.containsKey(convertCell(row.getCell(1)))) { productCategoryId1 = productCategoryId0 + "b" + category1Idx++; String productCategory = temp_ProductCategory.replace("#11#", productCategoryId1); productCategory = productCategory.replace("#21#", convertCell(row.getCell(1))); productCategory1Map.put(productCategoryId1, productCategory); //? String featuryCategory = temp_featuryCategory.replace("#11#", productCategoryId1); featuryCategory = featuryCategory.replace("#21#", convertCell(row.getCell(1))); featureCategoryMap.put(productCategoryId1, featuryCategory); //?? String featuryCategoryAppl = temp_featuryCategoryAppl.replace("#11#", productCategoryId1); featuryCategoryAppl = featuryCategoryAppl.replace("#21#", productCategoryId1); featureCategorApplMap.put(productCategoryId1, featuryCategoryAppl); productCategoryId1Map.put(convertCell(row.getCell(1)), productCategoryId1); } else { productCategoryId1 = productCategoryId1Map.get(convertCell(row.getCell(1))); } //2 if (UtilValidate.isNotEmpty(productCategoryId0) & UtilValidate.isNotEmpty(productCategoryId1)) { if (UtilValidate.isEmpty(productCategoryRollupMap.get(productCategoryId1))) { String ProductCategoryRollup = temp_ProductCategoryRollup.replace("#11#", productCategoryId0); ProductCategoryRollup = ProductCategoryRollup.replace("#21#", productCategoryId1); productCategoryRollupMap.put(productCategoryId1, ProductCategoryRollup); } } } // String productCategoryId2 = ""; if (UtilValidate.isNotEmpty(convertCell(row.getCell(2)))) { if (!productCategoryId2Map.containsKey(convertCell(row.getCell(2)))) { productCategoryId2 = productCategoryId1 + "c" + category2Idx++; String productCategory = temp_ProductCategory.replace("#11#", productCategoryId2); productCategory = productCategory.replace("#21#", convertCell(row.getCell(2))); productCategory2Map.put(productCategoryId2, productCategory); //? String featuryCategory = temp_featuryCategory.replace("#11#", productCategoryId2); featuryCategory = featuryCategory.replace("#21#", convertCell(row.getCell(2))); featureCategoryMap.put(productCategoryId2, featuryCategory); //?? String featuryCategoryAppl = temp_featuryCategoryAppl.replace("#11#", productCategoryId2); featuryCategoryAppl = featuryCategoryAppl.replace("#21#", productCategoryId2); featureCategorApplMap.put(productCategoryId2, featuryCategoryAppl); productCategoryId2Map.put(convertCell(row.getCell(2)), productCategoryId2); } else { productCategoryId2 = productCategoryId2Map.get(convertCell(row.getCell(2))); } //23 if (UtilValidate.isNotEmpty(productCategoryId1) & UtilValidate.isNotEmpty(productCategoryId2)) { if (UtilValidate.isEmpty(productCategoryRollupMap.get(productCategoryId2))) { String ProductCategoryRollup = temp_ProductCategoryRollup.replace("#11#", productCategoryId1); ProductCategoryRollup = ProductCategoryRollup.replace("#21#", productCategoryId2); productCategoryRollupMap.put(productCategoryId2, ProductCategoryRollup); } } } //4 String productCategoryId3 = ""; if (UtilValidate.isNotEmpty(convertCell(row.getCell(3)))) { if (!productCategoryId3Map.containsKey(convertCell(row.getCell(3)))) { productCategoryId3 = productCategoryId2 + "d" + category3Idx++; String productCategory = temp_ProductCategory.replace("#11#", productCategoryId3); productCategory = productCategory.replace("#21#", convertCell(row.getCell(3))); productCategory3Map.put(productCategoryId3, productCategory); //? String featuryCategory = temp_featuryCategory.replace("#11#", productCategoryId3); featuryCategory = featuryCategory.replace("#21#", convertCell(row.getCell(3))); featureCategoryMap.put(productCategoryId3, featuryCategory); //?? String featuryCategoryAppl = temp_featuryCategoryAppl.replace("#11#", productCategoryId3); featuryCategoryAppl = featuryCategoryAppl.replace("#21#", productCategoryId3); featureCategorApplMap.put(productCategoryId3, featuryCategoryAppl); productCategoryId3Map.put(convertCell(row.getCell(3)), productCategoryId3); } else { productCategoryId3 = productCategoryId3Map.get(convertCell(row.getCell(3))); } //34 if (UtilValidate.isNotEmpty(productCategoryId2) & UtilValidate.isNotEmpty(productCategoryId3)) { if (UtilValidate.isEmpty(productCategoryRollupMap.get(productCategoryId3))) { String ProductCategoryRollup = temp_ProductCategoryRollup.replace("#11#", productCategoryId2); ProductCategoryRollup = ProductCategoryRollup.replace("#21#", productCategoryId3); productCategoryRollupMap.put(productCategoryId3, ProductCategoryRollup); } } } //?.? String categoryId = productCategoryId3; if (UtilValidate.isEmpty(productCategoryId3)) { categoryId = productCategoryId2; } if (UtilValidate.isEmpty(productCategoryId2)) { categoryId = productCategoryId1; } //?? isReturn: for (int j = 4; j <= 31; j++) { String productFeatureTypeId = ""; if (j == 4) { productFeatureTypeId = "BRAND"; } else if (j == 5) { productFeatureTypeId = "SIZE"; } else if (j == 6) { productFeatureTypeId = "COLOR"; } else if (j == 7) { productFeatureTypeId = "ORIGIN"; } else if (j == 8) { productFeatureTypeId = "ACCESSORY"; } else if (j == 9) { productFeatureTypeId = "AMOUNT"; } else if (j == 10) { productFeatureTypeId = "NET_WEIGHT"; } else if (j == 11) { productFeatureTypeId = "ARTIST"; } else if (j == 12) { productFeatureTypeId = "BILLING_FEATURE"; } else if (j == 13) { productFeatureTypeId = "CARE"; } else if (j == 14) { productFeatureTypeId = "DIMENSION"; } else if (j == 15) { productFeatureTypeId = "EQUIP_CLASS"; } else if (j == 16) { productFeatureTypeId = "FABRIC"; } else if (j == 17) { productFeatureTypeId = "GENRE"; } else if (j == 18) { productFeatureTypeId = "GIFT_WRAP"; } else if (j == 19) { productFeatureTypeId = "HARDWARE_FEATURE"; } else if (j == 20) { productFeatureTypeId = "HAZMAT"; } else if (j == 21) { productFeatureTypeId = "LICENSE"; } else if (j == 22) { productFeatureTypeId = "OTHER_FEATURE"; } else if (j == 23) { productFeatureTypeId = "PRODUCT_QUALITY"; } else if (j == 24) { productFeatureTypeId = "SOFTWARE_FEATURE"; } else if (j == 25) { productFeatureTypeId = "STYLE"; } else if (j == 26) { productFeatureTypeId = "SYMPTOM"; } else if (j == 27) { productFeatureTypeId = "TOPIC"; } else if (j == 28) { productFeatureTypeId = "TYPE"; } else if (j == 29) { productFeatureTypeId = "WARRANTY"; } else if (j == 30) { productFeatureTypeId = "MODEL_YEAR"; } else if (j == 31) { productFeatureTypeId = "YEAR_MADE"; } String cellVal = convertCell(row.getCell(j)); if (UtilValidate.isEmpty(cellVal)) { continue isReturn; } if (UtilValidate.isNotEmpty(cellVal)) { String[] featureValue = cellVal.split("==="); for (String feature : featureValue) { String featureId = featurePrefix + featureIdx++; String ProductFeature = temp_ProductFeature.replace("#11#", featureId); ProductFeature = ProductFeature.replace("#21#", productFeatureTypeId);// ProductFeature = ProductFeature.replace("#31#", categoryId); ProductFeature = ProductFeature.replace("#41#", feature); featureMap.put(feature, ProductFeature); featureIdMap.put(feature, featureId); } } } //??? for (int j = 32; j <= 190; j = j + 2) { String cellValInfo = convertCell(row.getCell(j)); String cellVal = convertCell(row.getCell(j + 1)); if (UtilValidate.isNotEmpty(cellValInfo) && UtilValidate.isNotEmpty(cellVal)) { //? String featureTypeId = featureTypePrefix + featureTypeIdx++; if (!featureTypeMap.containsKey(cellValInfo)) { String ProductFeatureType = temp_ProductFeatureType.replace("#11#", featureTypeId); ProductFeatureType = ProductFeatureType.replace("#21#", cellValInfo);// featureTypeMap.put(cellValInfo, ProductFeatureType); featureTypeIdMap.put(cellValInfo, featureTypeId); } else { featureTypeId = featureTypeIdMap.get(cellValInfo); } String[] featureValue = cellVal.split("==="); for (String feature : featureValue) { String featureId = featurePrefix + featureIdx++; String ProductFeature = temp_ProductFeature.replace("#11#", featureId); ProductFeature = ProductFeature.replace("#21#", featureTypeId);// ProductFeature = ProductFeature.replace("#31#", categoryId); ProductFeature = ProductFeature.replace("#41#", feature); featureMap.put(feature, ProductFeature); featureIdMap.put(feature, featureId); } } } } } catch (IOException e) { e.printStackTrace(); } // xml String line = "\r\n"; StringBuffer sb = new StringBuffer(); sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" + line); sb.append("<entity-engine-xml>" + line); sb.append(StringUtils.join(productCategory0Map.values(), line)); sb.append(line + line); sb.append(StringUtils.join(productCategory1Map.values(), line)); sb.append(line + line); sb.append(StringUtils.join(productCategory2Map.values(), line)); sb.append(line + line); sb.append(StringUtils.join(productCategory3Map.values(), line)); sb.append(line + line); sb.append(StringUtils.join(productCategoryRollupMap.values(), line)); sb.append(line + line); sb.append(StringUtils.join(prodCatalogCategoryMap.values(), line)); sb.append(line + line); sb.append(StringUtils.join(productCategoryMemberMap.values(), line)); sb.append(line + line); sb.append(StringUtils.join(featureCategoryMap.values(), line)); sb.append(line + line); sb.append(StringUtils.join(featureCategorApplMap.values(), line)); sb.append(line + line); sb.append(StringUtils.join(featureTypeMap.values(), line)); sb.append(line + line); sb.append(StringUtils.join(featureMap.values(), line)); sb.append(line + line); sb.append(line + "</entity-engine-xml>"); // System.out.println(sb.toString()); try { String str = sb.toString(); // str = str.replace("\"", ""); str = str.replace("'", ""); String outPath = "C:/Users/Administrator/Desktop/" + filePath + ".xml"; PrintWriter pw = new PrintWriter(new FileWriter(outPath)); pw.write(str.toString()); pw.close(); } catch (IOException e) { e.printStackTrace(); } }
From source file:org.ofbiz.tools.rest.FixOfcTools.java
/** * ?Xls?/*from www .java2 s.c o m*/ * @param filePathAndName */ public static void xls2XmlForProduct(String filePathAndName) { long featureIdx = 2000; String featurePrefix = "FT-"; long invetoryIdx = 4000; Timestamp now = new Timestamp(System.currentTimeMillis()); //?? String temp_Product = "<Product productId=\"#11#\" productName=\"#21#\" internalName=\"#41#\" primaryProductCategoryId=\"#31#\" productTypeId=\"FINISHED_GOOD\" taxable=\"N\" chargeShipping=\"N\" autoCreateKeywords=\"Y\" isVirtual=\"N\" isVariant=\"N\" introductionDate=\"#51#\" salesDiscontinuationDate=\"#61#\" createdDate=\"#71#\" quantityUomId=\"#81#\" createdByUserLogin=\"admin\" />"; String temp_ProductRole = "<ProductRole productId=\"#11#\" partyId=\"CompanyCq\" roleTypeId=\"SHIP_FROM_VENDOR\" fromDate=\"2001-05-13 12:00:00.0\"/> "; String temp_ProductFeature = "<ProductFeature productFeatureId=\"#11#\" productFeatureTypeId=\"#21#\" productFeatureCategoryId=\"#31#\" description=\"#41#\"/>"; String temp_ProductFeatureAppl = "<ProductFeatureAppl productId=\"#11#\" productFeatureId=\"#21#\" productFeatureApplTypeId=\"STANDARD_FEATURE\" fromDate=\"#31#\" />"; String temp_ProductCategoryMember = "<ProductCategoryMember productId=\"#11#\" productCategoryId=\"#21#\" fromDate=\"2001-05-13 12:00:00.000\"/>"; String temp_ProductPrice = "<ProductPrice productId=\"#11#\" productPricePurposeId=\"#41#\" productPriceTypeId=\"#21#\" currencyUomId=\"CNY\" productStoreGroupId=\"_NA_\" fromDate=\"2001-05-13 12:00:00.0\" price=\"#31#\"/>"; String temp_ProductAttribute = "<ProductAttribute productId=\"#11#\" attrName=\"#21#\"><attrValue><![CDATA[ #31# ]]></attrValue></ProductAttribute>"; String temp_InventoryItem = "<InventoryItem facilityId=\"WebStoreWarehouse\" inventoryItemId=\"#11#\" inventoryItemTypeId=\"NON_SERIAL_INV_ITEM\" datetimeReceived=\"2008-08-01 08:00:00.000\" productId=\"#21#\" ownerPartyId=\"CompanyCq\" currencyUomId=\"CNY\" unitCost=\"200\"/>"; String temp_InventoryItemDetail = "<InventoryItemDetail inventoryItemId=\"#11#\" inventoryItemDetailSeqId=\"0001\" effectiveDate=\"2001-05-13 12:00:00.0\" availableToPromiseDiff=\"228\" quantityOnHandDiff=\"228\" accountingQuantityDiff=\"228\"/>"; Map<String, String> productMap = new HashMap<String, String>(); Map<String, String> productRoleMap = new HashMap<String, String>(); Map<String, String> inventoryItemMap = new HashMap<String, String>(); Map<String, String> inventoryItemDetailMap = new HashMap<String, String>(); Map<String, String> productPriceMap = new HashMap<String, String>(); Map<String, String> productAttributeMap = new HashMap<String, String>(); Map<String, String> productCategoryMemberMap = new HashMap<String, String>(); Map<String, String> featureMap = new HashMap<String, String>(); Map<String, String> featureIdMap = new HashMap<String, String>(); Map<String, String> featureApplMap = new HashMap<String, String>(); HSSFWorkbook workbook = null; HSSFSheet sheet = null; try { // workbook = new HSSFWorkbook(new FileInputStream(new File(filePathAndName))); sheet = workbook.getSheetAt(0); int rowEnd = sheet.getLastRowNum(); //? int i = 1; for (; i < rowEnd + 1; i++) { System.out.println("?" + i + ""); HSSFRow row = sheet.getRow(i); String nowTime = UtilDateTime.nowTimestamp().toString(); if (UtilValidate.isEmpty(convertCell(row.getCell(2)))) { continue; } //? String productCategoryId1 = convertCell(row.getCell(1)); String productId = convertCell(row.getCell(2)); String productName = convertCell(row.getCell(3)); String quantityUom = convertCell(row.getCell(4)); String defaultPrice = convertCell(row.getCell(5)); String listPrice = convertCell(row.getCell(6)); String skuId = convertCell(row.getCell(8)); String Product = temp_Product.replace("#11#", productId); Product = Product.replace("#21#", productName); Product = Product.replace("#31#", productCategoryId1); Product = Product.replace("#41#", skuId); Product = Product.replace("#51#", nowTime.substring(0, 19)); Product = Product.replace("#61#", getOneYearsAgoTime().substring(0, 19)); Product = Product.replace("#71#", nowTime.substring(0, 19)); String quantityUomId = ""; if (UtilValidate.isNotEmpty(quantityUom)) { quantityUom = quantityUom.trim(); if (quantityUom.equals("")) { quantityUomId = "WT_tao"; } else if (quantityUom.equals("")) { quantityUomId = "WT_pian"; } else if (quantityUom.equals("?")) { quantityUomId = "WT_tai"; } else if (quantityUom.equals("")) { quantityUomId = "WT_ge"; } else if (quantityUom.equals("")) { quantityUomId = "WT_yanmi"; } else if (quantityUom.equals("")) { quantityUomId = "WT_pingfangmi"; } else if (quantityUom.equals("")) { quantityUomId = "WT_pingfangmi"; } else if (quantityUom.equals("?")) { quantityUomId = "WT_kg"; } else if (quantityUom.equals("")) { quantityUomId = "WT_jian"; } else if (quantityUom.equals("")) { quantityUomId = "WT_tong"; } else if (quantityUom.equals("")) { quantityUomId = "WT_dui"; } else if (quantityUom.equals("")) { quantityUomId = "WT_fu"; } else if (quantityUom.equals("")) { quantityUomId = "WT_gen"; } else if (quantityUom.equals("")) { quantityUomId = "WT_he"; } else if (quantityUom.equals("")) { quantityUomId = "WT_zhi"; } else if (quantityUom.equals("")) { quantityUomId = "WT_ping"; } else if (quantityUom.equals("?")) { quantityUomId = "WT_ting"; } else if (quantityUom.equals("")) { quantityUomId = "WT_dai"; } else if (quantityUom.equals("?")) { quantityUomId = "WT_he"; } } Product = Product.replace("#81#", quantityUomId); productMap.put(productId, Product); String ProductRole = temp_ProductRole.replace("#11#", productId); productRoleMap.put(productId, ProductRole); long inventoryId = invetoryIdx++; String InventoryItem = temp_InventoryItem.replace("#21#", productId); InventoryItem = InventoryItem.replace("#11#", String.valueOf(inventoryId)); inventoryItemMap.put(productId, InventoryItem); String InventoryItemDetail = temp_InventoryItemDetail.replace("#11#", String.valueOf(inventoryId)); inventoryItemDetailMap.put(productId, InventoryItemDetail); //?? String ProductCategoryMember = temp_ProductCategoryMember.replace("#11#", productId); ProductCategoryMember = ProductCategoryMember.replace("#21#", productCategoryId1); productCategoryMemberMap.put(productId, ProductCategoryMember); // if (UtilValidate.isNotEmpty(convertCell(row.getCell(5)))) { String ProductPrice = temp_ProductPrice.replace("#11#", productId); ProductPrice = ProductPrice.replace("#21#", "DEFAULT_PRICE"); ProductPrice = ProductPrice.replace("#31#", defaultPrice); ProductPrice = ProductPrice.replace("#41#", "PURCHASE"); productPriceMap.put(productId + "default", ProductPrice); } // if (UtilValidate.isNotEmpty(convertCell(row.getCell(6)))) { String ProductPrice = temp_ProductPrice.replace("#11#", productId); ProductPrice = ProductPrice.replace("#21#", "LIST_PRICE"); ProductPrice = ProductPrice.replace("#31#", listPrice); ProductPrice = ProductPrice.replace("#41#", "PURCHASE"); productPriceMap.put(productId + "list", ProductPrice); } String productFeatureTypeId = "BRAND"; String cellVal = convertCell(row.getCell(9)); if (UtilValidate.isNotEmpty(cellVal)) { String featureId = featurePrefix + featureIdx++; //? if (featureMap.containsKey(cellVal)) { String ProductFeatureAppl = temp_ProductFeatureAppl.replace("#11#", productId); ProductFeatureAppl = ProductFeatureAppl.replace("#21#", featureIdMap.get(cellVal)); ProductFeatureAppl = ProductFeatureAppl.replace("#31#", now.toString()); featureApplMap.put(featureId, ProductFeatureAppl); } else { //?? String ProductFeature = temp_ProductFeature.replace("#11#", featureId); ProductFeature = ProductFeature.replace("#21#", productFeatureTypeId);// ProductFeature = ProductFeature.replace("#31#", productCategoryId1); ProductFeature = ProductFeature.replace("#41#", cellVal); featureMap.put(cellVal, ProductFeature); featureIdMap.put(cellVal, featureId); String ProductFeatureAppl = temp_ProductFeatureAppl.replace("#11#", productId); ProductFeatureAppl = ProductFeatureAppl.replace("#21#", featureId); ProductFeatureAppl = ProductFeatureAppl.replace("#31#", now.toString().subSequence(0, 19)); featureApplMap.put(featureId, ProductFeatureAppl); } } //?? for (int j = 10; j <= 45; j = j + 2) { String cellValInfo = convertCell(row.getCell(j)); String cellValue = convertCell(row.getCell(j + 1)); if (UtilValidate.isNotEmpty(cellValInfo) & UtilValidate.isNotEmpty(cellValue)) { String ProductAttribute = temp_ProductAttribute.replace("#11#", productId); ProductAttribute = ProductAttribute.replace("#21#", cellValInfo); ProductAttribute = ProductAttribute.replace("#31#", cellValue); productAttributeMap.put(productId + j, ProductAttribute); } } } } catch (IOException e) { e.printStackTrace(); } // xml String line = "\r\n"; StringBuffer sb = new StringBuffer(); sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\"?>" + line); sb.append("<entity-engine-xml>" + line); sb.append(StringUtils.join(productMap.values(), line)); sb.append(line + line); sb.append(StringUtils.join(productRoleMap.values(), line)); sb.append(line + line); sb.append(StringUtils.join(productPriceMap.values(), line)); sb.append(line + line); sb.append(StringUtils.join(inventoryItemMap.values(), line)); sb.append(line + line); sb.append(StringUtils.join(inventoryItemDetailMap.values(), line)); sb.append(line + line); sb.append(StringUtils.join(productCategoryMemberMap.values(), line)); sb.append(line + line); sb.append(StringUtils.join(featureMap.values(), line)); sb.append(line + line); sb.append(StringUtils.join(featureApplMap.values(), line)); sb.append(line + line); sb.append(StringUtils.join(productAttributeMap.values(), line)); sb.append(line + "</entity-engine-xml>"); // System.out.println(sb.toString()); try { String str = sb.toString(); // str = str.replace("\"", ""); str = str.replace("'", ""); String outPath = "C:/Users/Administrator/Desktop/productData.xml"; PrintWriter pw = new PrintWriter(new FileWriter(outPath)); pw.write(str.toString()); pw.close(); } catch (IOException e) { e.printStackTrace(); } }
From source file:org.olat.search.service.document.file.ExcelDocument.java
License:Apache License
@Override protected String readContent(final VFSLeaf leaf) throws IOException, DocumentException { BufferedInputStream bis = null; int cellNullCounter = 0; int rowNullCounter = 0; int sheetNullCounter = 0; try {//from w w w . j a v a2 s. c om bis = new BufferedInputStream(leaf.getInputStream()); final StringBuilder content = new StringBuilder(bis.available()); final POIFSFileSystem fs = new POIFSFileSystem(bis); final HSSFWorkbook workbook = new HSSFWorkbook(fs); for (int sheetNumber = 0; sheetNumber < workbook.getNumberOfSheets(); sheetNumber++) { final HSSFSheet sheet = workbook.getSheetAt(sheetNumber); if (sheet != null) { for (int rowNumber = sheet.getFirstRowNum(); rowNumber <= sheet.getLastRowNum(); rowNumber++) { final HSSFRow row = sheet.getRow(rowNumber); if (row != null) { for (int cellNumber = row.getFirstCellNum(); cellNumber <= row .getLastCellNum(); cellNumber++) { final HSSFCell cell = row.getCell(cellNumber); if (cell != null) { // if (cell.getCellStyle().equals(HSSFCell.CELL_TYPE_NUMERIC)) if (cell.getCellType() == Cell.CELL_TYPE_STRING) { content.append(cell.getStringCellValue()).append(' '); } } else { // throw new DocumentException(); cellNullCounter++; } } } else { rowNullCounter++; } } } else { sheetNullCounter++; } } if (log.isDebug()) { if ((cellNullCounter > 0) || (rowNullCounter > 0) || (sheetNullCounter > 0)) { log.debug("Read Excel content cell=null #:" + cellNullCounter + ", row=null #:" + rowNullCounter + ", sheet=null #:" + sheetNullCounter); } } return content.toString(); } catch (final Exception ex) { throw new DocumentException("Can not read XLS Content. File=" + leaf.getName()); } finally { if (bis != null) { bis.close(); } } }
From source file:org.openelis.bean.QcChartReport1Bean.java
License:Open Source License
private HSSFWorkbook fillWorkbook(HSSFWorkbook wb, ArrayList<Value> values, String qcName, String qcType, String qcFormat, Integer plotType, ReportStatus status) throws Exception { int rowIndex, sheetIndex, valueIndex; HSSFSheet sheet; Row row;//ww w . j a v a 2s . c om String lastAnalyte, lastFormat; lastAnalyte = "___"; lastFormat = "___"; sheet = null; sheetIndex = 1; valueIndex = 0; try { baseFont = wb.createFont(); baseFont.setFontName("Arial"); baseFont.setFontHeightInPoints((short) 8); baseStyle = wb.createCellStyle(); baseStyle.setFont(baseFont); headerFont = wb.createFont(); headerFont.setColor(IndexedColors.WHITE.getIndex()); headerFont.setFontName("Arial"); headerFont.setFontHeightInPoints((short) 8); headerStyle = wb.createCellStyle(); headerStyle.setAlignment(CellStyle.ALIGN_LEFT); headerStyle.setVerticalAlignment(CellStyle.VERTICAL_BOTTOM); headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); headerStyle.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex()); headerStyle.setFont(headerFont); maxChars = new ArrayList<Integer>(); worksheetColumnMap = new HashMap<String, HashMap<String, Integer>>(); worksheetHeaders = new ArrayList<String>(); worksheetHeaderNames = new ArrayList<String>(); if (qcColumns != null && !qcColumns.isEmpty()) rowIndex = 32; else rowIndex = 3; for (Value value : values) { valueIndex++; if ("N".equals(value.getIsPlot())) continue; if (!lastAnalyte.equals(value.getAnalyteName())) { if (!"___".equals(lastAnalyte)) { while (rowIndex < sheet.getLastRowNum()) { sheet.removeRow(sheet.getRow(rowIndex)); rowIndex++; } finishSheet(sheet, wb, qcName, qcType, lastAnalyte); } sheet = wb.getSheet("Sheet" + (sheetIndex++)); if (sheet == null) sheet = wb.createSheet(); lastAnalyte = value.getAnalyteName(); if (qcColumns != null && !qcColumns.isEmpty()) rowIndex = 32; else rowIndex = 3; lastFormat = "___"; if (Constants.dictionary().CHART_TYPE_FIXED.equals(plotType)) setStatisticCells(wb, sheet, value); } if (!lastFormat.equals(value.getWorksheetFormat())) { lastFormat = value.getWorksheetFormat(); if (qcColumns == null || qcColumns.isEmpty()) loadWorksheetFormat(lastFormat); } row = sheet.createRow(rowIndex++); setBaseCells(value, row); setResultCells(value, row, qcFormat, lastFormat); status.setPercentComplete(70 * (valueIndex / values.size()) + 20); session.setAttribute("qcChartReport", status); } finishSheet(sheet, wb, qcName, qcType, lastAnalyte); while (sheetIndex < wb.getNumberOfSheets()) wb.removeSheetAt(sheetIndex); } finally { baseFont = null; baseStyle = null; headerFont = null; headerStyle = null; maxChars = null; worksheetColumnMap = null; worksheetHeaders = null; worksheetHeaderNames = null; } return wb; }
From source file:org.openelis.bean.QcChartReport1Bean.java
License:Open Source License
private void finishSheet(HSSFSheet sheet, HSSFWorkbook wb, String qcName, String qcType, String sheetName) { int i, columnIndex; ArrayList<DictionaryDO> tempQcColumns; DictionaryDO dict;/* ww w. j a v a 2 s .com*/ HashSet<Integer> emptyColumns; Name rangeName; Row row; String rangeFormula; if (qcColumns != null && !qcColumns.isEmpty()) row = sheet.getRow(32); else row = sheet.getRow(3); emptyColumns = new HashSet<Integer>(); for (i = 0; i < row.getLastCellNum(); i++) { if (i >= maxChars.size() || maxChars.get(i) == 0) emptyColumns.add(i); } setHeaderCells(sheet, qcName, qcType, sheetName); if (qcColumns != null && !qcColumns.isEmpty()) { tempQcColumns = new ArrayList<DictionaryDO>(); tempQcColumns.addAll(qcColumns); for (i = tempQcColumns.size() - 1; i > -1; i--) { if (emptyColumns.contains(i + 5)) { tempQcColumns.remove(i); removeColumn(sheet, i + 5); maxChars.remove(i + 5); } } rangeName = getName(wb, sheet, "RowNumber"); if (rangeName == null) { rangeName = wb.createName(); rangeName.setSheetIndex(wb.getSheetIndex(sheet)); rangeName.setNameName("RowNumber"); } rangeFormula = sheet.getSheetName() + "!$" + CellReference.convertNumToColString(0) + "$33:" + "$" + CellReference.convertNumToColString(0) + "$" + (sheet.getLastRowNum() + 1); rangeName.setRefersToFormula(rangeFormula); /* * Create named ranges for the graph to be able to locate the appropriate * data */ columnIndex = 5; for (i = 0; i < tempQcColumns.size(); i++) { dict = tempQcColumns.get(i); if (!DataBaseUtil.isEmpty(dict.getCode())) { rangeName = getName(wb, sheet, dict.getCode()); if (rangeName == null) { rangeName = wb.createName(); rangeName.setSheetIndex(wb.getSheetIndex(sheet)); rangeName.setNameName(dict.getCode()); } rangeFormula = rangeName.getRefersToFormula(); if (rangeFormula != null && rangeFormula.length() > 0 && !"$A$2".equals(rangeFormula.substring(rangeFormula.indexOf("!") + 1))) rangeFormula += ","; else rangeFormula = ""; rangeFormula += sheet.getSheetName() + "!$" + CellReference.convertNumToColString(columnIndex) + "$33:" + "$" + CellReference.convertNumToColString(columnIndex) + "$" + (sheet.getLastRowNum() + 1); rangeName.setRefersToFormula(rangeFormula); } columnIndex++; } /* * make each column wide enough to show the longest string in it; the * width for each column is set as the maximum number of characters in * that column multiplied by 256; this is because the default width of * one character is 1/256 units in Excel */ for (i = 5; i < maxChars.size(); i++) sheet.setColumnWidth(i, maxChars.get(i) * 256); } else if (worksheetHeaders != null && worksheetHeaders.size() > 0) { /* * make each column wide enough to show the longest string in it; the * width for each column is set as the maximum number of characters in * that column multiplied by 256; this is because the default width of * one character is 1/256 units in Excel */ for (i = 0; i < maxChars.size(); i++) sheet.setColumnWidth(i, maxChars.get(i) * 256); } wb.setSheetName(wb.getSheetIndex(sheet), sheetName); sheet.setForceFormulaRecalculation(true); maxChars.clear(); }
From source file:org.openelis.bean.QcChartReport1Bean.java
License:Open Source License
private void removeColumn(HSSFSheet sheet, Integer columnIndex) { int i, j;//from ww w .j a v a2s .c o m Cell cell; Row row; for (i = 31; i <= sheet.getLastRowNum(); i++) { row = sheet.getRow(i); cell = row.getCell(columnIndex); if (cell != null) row.removeCell(row.getCell(columnIndex)); for (j = columnIndex + 1; j < row.getLastCellNum(); j++) { cell = row.getCell(j); if (cell != null) ((HSSFRow) row).moveCell((HSSFCell) cell, (short) (j - 1)); } } }
From source file:org.openmrs.module.kenyametadatatools.mflsync.MflSyncFromRemoteSpreadsheetTask.java
License:Open Source License
/** * Imports a MFL spreadsheet from a stream * @param stream the input stream/*from ww w . j a v a 2 s .co m*/ * @throws IOException if an error occurred */ protected void importXls(InputStream stream) throws IOException { POIFSFileSystem poifs = new POIFSFileSystem(stream); HSSFWorkbook wbook = new HSSFWorkbook(poifs); HSSFSheet sheet = wbook.getSheetAt(0); for (int r = sheet.getFirstRowNum() + 1; r <= sheet.getLastRowNum(); ++r) { HSSFRow row = sheet.getRow(r); String code = String.valueOf(((Double) cellValue(row.getCell(0))).intValue()); String name = (String) cellValue(row.getCell(1)); String province = (String) cellValue(row.getCell(2)); String type = (String) cellValue(row.getCell(6)); if (StringUtils.isEmpty(name)) { TaskEngine.logError("Unable to import location " + code + " with empty name"); } else if (StringUtils.isEmpty(code)) { TaskEngine.logError("Unable to import location '" + name + "' with invalid code"); } else { importLocation(code, name.trim(), province, type); } } }
From source file:org.opensprout.osaf.util.ExcelUtils.java
License:Open Source License
/** * Read uploaded exel file and make entity object. * @param excelFile uploaded excel file. * @param startRow first row number to map entity. * @param values additional infomations to map entity. * @param callback validate and mapping template. *///from w w w . j a v a 2 s . c o m public static void processExcelFile(String excelFile, int startRow, Map<String, Object> values, ExcelUploadTemplate callback) { POIFSFileSystem fs = null; HSSFWorkbook wb = null; FileInputStream fileInputStream = null; try { fileInputStream = new FileInputStream(excelFile); fs = new POIFSFileSystem(fileInputStream); wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); if (sheet.getLastRowNum() < 1) throw new ExcelUploadException("Invalid Excel File - empty rows"); if (!callback.checkColumnHeader(sheet.getRow(0))) throw new ExcelUploadException("Invalid Excel File - Invalid Column Header."); for (int i = startRow; i <= sheet.getLastRowNum(); i++) { HSSFRow row = sheet.getRow(i); callback.makeEntity(row, values); } } catch (Exception e) { throw new ExcelUploadException(e); } finally { try { if (fileInputStream != null) { fileInputStream.close(); fileInputStream = null; } } catch (IOException e) { throw new ExcelUploadException(e); } } }
From source file:org.opentaps.common.util.UtilCommon.java
License:Open Source License
/** * Reads a simply formatted, single sheet Excel document into a list of <code>Map</code>. * @param stream an <code>InputStream</code> of the excel document * @param columnNames a List containing the keys to use when mapping the columns into the Map (column 1 goes in the Map key columnNames 1, etc ...) * @param skipRows number of rows to skip, typically 1 to skip the header row * @return the List of Map representing the rows * @throws IOException if an error occurs *///from w w w .j a v a 2 s . c o m public static List<Map<String, String>> readExcelFile(InputStream stream, List<String> columnNames, int skipRows) throws IOException { POIFSFileSystem fs = new POIFSFileSystem(stream); HSSFWorkbook wb = new HSSFWorkbook(fs); HSSFSheet sheet = wb.getSheetAt(0); int sheetLastRowNumber = sheet.getLastRowNum(); List<Map<String, String>> rows = new ArrayList<Map<String, String>>(); for (int j = skipRows; j <= sheetLastRowNumber; j++) { HSSFRow erow = sheet.getRow(j); Map<String, String> row = new HashMap<String, String>(); for (int i = 0; i < columnNames.size(); i++) { String columnName = columnNames.get(i); HSSFCell cell = erow.getCell(i); String s = ""; if (cell != null) { // check if cell contains a number BigDecimal bd = null; try { double d = cell.getNumericCellValue(); bd = BigDecimal.valueOf(d); } catch (Exception e) { // do nothing } if (bd == null) { s = cell.toString().trim(); } else { // if cell contains number trim the tailing zeros so that for example postal code string // does not appear as a floating point number s = bd.toPlainString(); // convert XX.XX000 into XX.XX s = s.replaceFirst("^(-?\\d+\\.0*[^0]+)0*\\s*$", "$1"); // convert XX.000 into XX s = s.replaceFirst("^(-?\\d+)\\.0*$", "$1"); } } Debug.logInfo("readExcelFile cell (" + j + ", " + i + ") as (" + columnName + ") == " + s, MODULE); row.put(columnName, s); } rows.add(row); } return rows; }