Hi Jacques, I want to insert all the excel data into the database. I looked at viewdatafile screen, and looked like, using that I can not insert all my excel data into database tables.
Jacques Le Roux wrote: > > I'd use CSV with https://localhost:8443/webtools/control/viewdatafile. I > let you find more on this ML archives... > > Jacques > > From: "su2" <[email protected]> >> >> Hi Nalin, >> >> We have similar requirement where we want to upload/import all the >> product >> information from excel sheet. >> >> I would really appreciate if you could tell me, how we can achieve that. >> >> Thank you. >> Shuchi- >> >> Nalin Chandra wrote: >>> >>> Hi All >>> >>> I want to import the product related data from excel sheet. I did it but >>> i >>> have one problem in this. >>> I excel sheet i have absolute path of image now i want to upload the >>> image >>> from that absolute path to our application as well store the relative >>> path >>> in data base as we did in product content section. >>> i am able to store the relative path in database but image in not >>> uploading in the required folder. >>> >>> Main problem is that here i am not using any html form. >>> >>> i paste the code below for it >>> >>> public static String uploadCategory(HttpServletRequest request, >>> HttpServletResponse response) { >>> >>> try >>> { >>> String filename = "D:/category.xls"; >>> WorkbookSettings ws = new WorkbookSettings(); >>> ws.setLocale(new Locale("en", "EN")); >>> Workbook workbook = Workbook.getWorkbook(new >>> File(filename),ws); >>> Sheet s = workbook.getSheet(0); >>> readCategoryDataSheet(s, request); >>> workbook.close(); >>> } >>> catch (IOException e) >>> { >>> e.printStackTrace(); >>> } >>> catch (BiffException e) >>> { >>> e.printStackTrace(); >>> } >>> >>> return "success"; >>> } >>> >>> private static void readCategoryDataSheet(Sheet s, >>> HttpServletRequest >>> request) >>> { >>> Cell rowData[] = null; >>> int successCount = 0; >>> int failCount = 0; >>> int rows = s.getRows(); >>> int column = s.getColumns(); >>> String productCategoryId = ""; >>> String productCategoryTypeId = ""; >>> String primaryParentCategoryId = ""; >>> String categoryName = ""; >>> String description = ""; >>> String longDescription = ""; >>> String categoryImageUrl = ""; >>> String linkOneImageUrl = ""; >>> String prodCatalogId = ""; >>> String prodCatalogCategoryTypeId = ""; >>> String sequenceNum = ""; >>> >>> for (int i = 1; i < rows; i++) { >>> rowData = s.getRow(i); >>> if (rowData[0].getContents().length() != 0) { >>> for (int j = 0; j < column; j++) { >>> switch (j) { >>> case 0: >>> productCategoryId = >>> rowData[j].getContents(); >>> case 1: >>> productCategoryTypeId = >>> rowData[j].getContents(); >>> case 2: >>> primaryParentCategoryId >>> = >>> rowData[j].getContents(); >>> case 3: >>> categoryName = >>> rowData[j].getContents(); >>> case 4: >>> description = >>> rowData[j].getContents(); >>> case 5: >>> longDescription = >>> rowData[j].getContents(); >>> case 6: >>> categoryImageUrl = >>> rowData[j].getContents(); >>> case 7: >>> linkOneImageUrl = >>> rowData[j].getContents(); >>> default: >>> break; >>> } >>> } >>> } >>> String imageFilenameFormat = >>> UtilProperties.getPropertyValue("catalog", "image.filename.format"); >>> String imageServerPath = >>> UtilProperties.getPropertyValue("catalog", "image.server.path"); >>> String imageUrlPrefix = >>> UtilProperties.getPropertyValue("catalog", "image.url.prefix"); >>> >>> >>> // upload image >>> FlexibleStringExpander filenameExpander = new >>> FlexibleStringExpander(imageFilenameFormat); >>> String catImageUrl = ""; >>> String linkImageUrl = ""; >>> if(categoryImageUrl != null && >>> categoryImageUrl.length() > 0){ >>> Object forLock = new Object(); >>> String contentType = null; >>> String categoryImageLocation = >>> filenameExpander.expandString(UtilMisc.toMap("location", "categories", >>> "type", "category", "id", productCategoryId)); >>> String filePathPrefix = ""; >>> String filenameToUse = >>> categoryImageLocation; >>> if (categoryImageLocation.lastIndexOf("/") >>> != >>> -1) { >>> filePathPrefix = >>> categoryImageLocation.substring(0, >>> categoryImageLocation.lastIndexOf("/") >>> + 1); // adding 1 to include the trailing slash >>> filenameToUse = >>> categoryImageLocation.substring(categoryImageLocation.lastIndexOf("/") + >>> 1); >>> } >>> >>> int i1; >>> if (contentType != null && (i1 = >>> contentType.indexOf("boundary=")) != -1) { >>> contentType = contentType.substring(i1 + >>> 9); >>> contentType = "--" + contentType; >>> } >>> >>> String defaultFileName = filenameToUse + >>> "_temp"; >>> HttpRequestFileUpload uploadObject = new >>> HttpRequestFileUpload(); >>> >>> uploadObject.setOverrideFilename(defaultFileName); >>> uploadObject.setSavePath(imageServerPath + >>> "/" >>> + filePathPrefix); >>> try{ >>> uploadObject.doUpload(request); >>> >>> }catch(IOException e){ >>> Debug.logInfo("Image uploading failure", >>> module); >>> } >>> String categoryImageFileName = >>> uploadObject.getFilename(); >>> >>> if (categoryImageFileName != null && >>> categoryImageFileName.length() > 0) { >>> if >>> (categoryImageFileName.lastIndexOf(".") >>> > 0 && categoryImageFileName.lastIndexOf(".") < >>> categoryImageFileName.length()) { >>> filenameToUse += >>> categoryImageFileName.substring(categoryImageFileName.lastIndexOf(".")); >>> } else { >>> filenameToUse += ".jpg"; >>> } >>> try{ >>> String characterEncoding = >>> request.getCharacterEncoding(); >>> catImageUrl = imageUrlPrefix + "/" + >>> filePathPrefix + java.net.URLEncoder.encode(filenameToUse, >>> characterEncoding); >>> }catch(Exception e){ >>> System.out.println("Incoding Problem"); >>> } >>> >>> try { >>> File file = new File(imageServerPath >>> + >>> "/" + filePathPrefix, defaultFileName); >>> File file1 = new >>> File(imageServerPath >>> + "/" + filePathPrefix, filenameToUse); >>> try { >>> file1.delete(); >>> } catch(Exception e) { >>> System.out.println("error >>> deleting >>> existing file (not neccessarily a problem)"); >>> } >>> file.renameTo(file1); >>> } catch(Exception e) { >>> e.printStackTrace(); >>> } >>> } >>> } >>> >>> // end of upload image >>> >>> Timestamp fromDate = UtilDateTime.nowTimestamp(); >>> GenericDelegator delegator = (GenericDelegator) >>> request.getAttribute("delegator"); >>> try { >>> GenericValue DataImportCategoryList = >>> delegator.findByPrimaryKey("DataImportCategory", >>> UtilMisc.toMap("productCategoryId", productCategoryId)); >>> >>> if(DataImportCategoryList != null){ >>> String categoryId = >>> DataImportCategoryList.getString("productCategoryId"); >>> if(categoryId.equals(productCategoryId)){ >>> failCount++; >>> } >>> }else { >>> GenericValue newImportCategory = >>> delegator.makeValue("DataImportCategory", null); >>> >>> newImportCategory.set("productCategoryId", >>> productCategoryId.trim()); >>> if(productCategoryTypeId != null && >>> productCategoryTypeId.length() >>> > 0){ >>> newImportCategory.set("productCategoryTypeId", >>> productCategoryTypeId.trim()); >>> } else { >>> >>> newImportCategory.set("productCategoryTypeId", "CATALOG_CATEGORY"); >>> } >>> if(primaryParentCategoryId != null && >>> primaryParentCategoryId.length() > 0) >>> newImportCategory.set("primaryParentCategoryId", >>> primaryParentCategoryId); >>> newImportCategory.set("categoryName", categoryName.trim()); >>> newImportCategory.set("description", description); >>> newImportCategory.set("longDescription", longDescription); >>> newImportCategory.set("categoryImageUrl", catImageUrl); >>> newImportCategory.set("linkOneImageUrl", linkImageUrl); >>> newImportCategory.set("fromDate", >>> fromDate); >>> try { >>> >>> delegator.create(newImportCategory); >>> Debug.logInfo("Successfully >>> imported category ["+productCategoryId+" from row no "+ i+1 +"].", >>> module); >>> successCount++; >>> } catch (GenericEntityException e) >>> { >>> >>> Debug.logWarning(e.getMessage(), >>> module); >>> } >>> >>> } >>> } catch(GenericEntityException e) { >>> Debug.logError("Exception occured :"+e.getMessage(), module); >>> } >>> } >>> >>> } >>> >>> >>> it create the temp file in corresponding directory but there is no >>> actual >>> image at that path. >>> >>> So any one have any idea regarding this please suggest me. >>> >>> >>> Thanks. >>> >>> Nalin Chandra >>> >> >> -- >> View this message in context: >> http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p585955.html >> Sent from the OFBiz - User mailing list archive at Nabble.com. >> > > > -- View this message in context: http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p586989.html Sent from the OFBiz - User mailing list archive at Nabble.com.
