Hi,
Look at the ImportProductServices.java, here you will get the
productImportFromSpreadsheet() service to import the product data from Excel
sheet.
You just need to put your Excel sheet in your ${OFBIZ-HOME}/spreadsheet
directory (need to create). Now go to webtools run service
productImportFromSpreadsheet without passing any parameter. If your Excell
Sheet has some other Product entity field then those needs to be handle by
modifying the service.Rishi Solanki Enterprise Software Developer HotWax Media Pvt. Ltd. On Fri, Nov 13, 2009 at 10:57 AM, S K Pradeep kumar < [email protected]> wrote: > Study about webtools there is one data import facility study that and use > it > for your requirements. > > > With regards, > S K Pradeep kumar, > > > > On Thu, Nov 12, 2009 at 8:05 PM, su2 <[email protected]> wrote: > > > > > Hi Pradeep, > > > > Sorry still little confuse. How am I going to use DemoProduct.xml file? I > > understood the file I can have all my required data there, but after that > > what should I be doing in order to insert the data in entities? > > > > Thank you. > > Shuchi - > > > > S K Pradeep kumar wrote: > > > > > > you can find the xml filse in the data folder in all the application/* > > > module , to create the new products use DemoProduct.xml file. > > > > > > With regards, > > > S K Pradeep kumar, > > > > > > > > > > > > On Thu, Nov 12, 2009 at 7:12 PM, su2 <[email protected]> wrote: > > > > > >> > > >> Hi Pradeep, > > >> > > >> Thanks for your response. > > >> > > >> Sorry I am new to ofbiz. But I am not sure where to define the entity > > >> names > > >> and the entity-field names where I want to insert the data(as all the > > >> information like price and others for the product is not stored in > only > > >> 'Product' entity, I need to insert the data in multiple entities)? > > >> > > >> Do I have to specify in XML? Do you have any sample xml file? I want > to > > >> insert data for new products > > >> > > >> Also, I see entity "DataImportProduct". What is that for? > > >> > > >> Once again thank you for the help. > > >> > > >> Shuchi- > > >> > > >> > > >> S K Pradeep kumar wrote: > > >> > > > >> > Hi shuchi, > > >> > > > >> > Convert your .cvs to xml and use webtools entity import. > > >> > > > >> > With regards, > > >> > S K Pradeep kumar, > > >> > > > >> > > > >> > > > >> > On Thu, Nov 12, 2009 at 12:52 AM, su2 <[email protected]> wrote: > > >> > > > >> >> > > >> >> 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. > > >> >> > > >> > > > >> > > > >> > > >> -- > > >> View this message in context: > > >> > > > http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p620283.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-tp160724p620305.html > > Sent from the OFBiz - User mailing list archive at Nabble.com. > > >
