look in the /framework/datafile/dtd/datafiles.xsd it has how to make your data definition file. also http://docs.ofbiz.org/display/OFBENDUSER/OFBiz's+Data+File+Tools http://docs.ofbiz.org/display/OFBIZ/Handling+of+External+data once you build the data definintion files then you can use https://localhost:8443/webtools/control/viewdatafile
su2 sent the following on 11/11/2009 11:22 AM: > 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. >>> >> >> > -- BJ Freeman http://www.businessesnetwork.com/automation http://bjfreeman.elance.com http://www.linkedin.com/profile?viewProfile=&key=1237480&locale=en_US&trk=tab_pro Systems Integrator.
