how to import data from excel sheet
Locked
how to import data from excel sheet
 
|
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 |
Re: how to import data from excel sheet
|
|
images, that are displayed are stored physically at
/framework/images/webapp/images/catalog /frameework/images/webapp/images/products/large /framework/images/webapp/images/products/medium /framework/images/webapp/images/products/small /framework/images/webapp/images/products/additional the last one is used with the service addAdditionalViewForProductFromURL which take the image in that directory and resizes it for the other product directories. nalin chandra sent the following on 5/9/2009 11:17 PM: > 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 -- 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. |
Re: how to import data from excel sheet
|
|
Thanks for reply.
But that is not my problem. Once again i clear my problem. I want to upload all the information about product category etc from the excel sheet where we write all the required information about product and category. In that excel sheet i also mentioned the local system path of image. Now i want to upload the image from local system to remote server using path of that. Now i read the local system file path from excel sheet as shown in bold. I know we need a form to upload the file <form method="post" action="uploadCategory" enctype="multipart/form-data"> file: <input type="file" name="file-upload"> <input type="submit"> </form> . but i my case there is no form. i got the file path in my method now i want to upload the that file from local system to remote server. I think now you under stand my problem. with the use of this we have no need to upload the product detail one by one to the server. waiting for your reply. Thanks Nalin Chandra > > 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 |
Re: how to import data from excel sheet
|
|
In reply to this post by BJ Freeman
Ok the way to upload changes with out a form.
First understand that in the form way the image is uploaded into server memory, from your desktop, then you read the data from the server memory and put it into a file. This is a function of the webbrowser not ofbiz. The problem is that a server can not read from you local drive. You can only do this thru a web browser. so you must either put the images on the server, thru FTP then reference them that way in your excel. or put the images on a machine that is exposed to the internet with a web server and give a http:// type url to be uploaded, in your excel. nalin chandra sent the following on 5/10/2009 7:24 AM: > Thanks for reply. > > But that is not my problem. > Once again i clear my problem. > I want to upload all the information about product category etc from the > excel sheet where we write all the required information about product and > category. In that excel sheet i also mentioned the local system path of > image. Now i want to upload the image from local system to remote server > using path of that. Now i read the local system file path from excel sheet > as shown in bold. > > I know we need a form to upload the file > <form method="post" action="uploadCategory" enctype="multipart/form-data"> > file: <input type="file" name="file-upload"> > <input type="submit"> > </form> . > > but i my case there is no form. i got the file path in my method now i want > to upload the that file from local system to remote server. > > I think now you under stand my problem. > with the use of this we have no need to upload the product detail one by one > to the server. > > waiting for your reply. > > Thanks > > Nalin Chandra > > > > >> 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 > > -- 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. |
Re: how to import data from excel sheet
|
|
Hi
This is long time i back on this forum. thanks for all your suggestions. I solve this problem with different method. Now we store the path of the image using Excel sheet and upload the corresponding images in the respective folder. Nalin Chandra Ofbiz developer
|
Re: how to import data from excel sheet
|
|
In reply to this post by BJ Freeman
and Update to ofbiz trunk after release 4.0 lets you pass a URL to the
service and it will import, size the images and put one for each size in the correct folder. you can see this work but going to the product content page and paste a URL in the upload box glad you found a solution. Nalin Chandra sent the following on 10/27/2009 6:08 PM: > Hi > > This is long time i back on this forum. > > thanks for all your suggestions. I solve this problem with different method. > Now we store the path of the image using Excel sheet and upload the > corresponding images in the respective folder. > > > Nalin Chandra > Ofbiz developer > > > > BJ Freeman wrote: >> Ok the way to upload changes with out a form. >> First understand that in the form way the image is uploaded into server >> memory, from your desktop, then you read the data from the server memory >> and put it into a file. This is a function of the webbrowser not ofbiz. >> The problem is that a server can not read from you local drive. You can >> only do this thru a web browser. >> >> so you must either put the images on the server, thru FTP then reference >> them that way in your excel. >> or put the images on a machine that is exposed to the internet with a >> web server and give a http:// type url to be uploaded, in your excel. >> >> >> nalin chandra sent the following on 5/10/2009 7:24 AM: >>> Thanks for reply. >>> >>> But that is not my problem. >>> Once again i clear my problem. >>> I want to upload all the information about product category etc from the >>> excel sheet where we write all the required information about product and >>> category. In that excel sheet i also mentioned the local system path of >>> image. Now i want to upload the image from local system to remote server >>> using path of that. Now i read the local system file path from excel >>> sheet >>> as shown in bold. >>> >>> I know we need a form to upload the file >>> <form method="post" action="uploadCategory" >>> enctype="multipart/form-data"> >>> file: <input type="file" name="file-upload"> >>> <input type="submit"> >>> </form> . >>> >>> but i my case there is no form. i got the file path in my method now i >>> want >>> to upload the that file from local system to remote server. >>> >>> I think now you under stand my problem. >>> with the use of this we have no need to upload the product detail one by >>> one >>> to the server. >>> >>> waiting for your reply. >>> >>> Thanks >>> >>> Nalin Chandra >>> >>> >>> >>> >>>> 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 >>> >> -- >> 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. >> >> >> > -- 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. |
|
|
In reply to this post by Nalin Chandra
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-
|
Re: how to import data from excel sheet
Administrator
|
I'd use CSV with https://localhost:8443/webtools/control/viewdatafile. I let you find more on this ML archives...
Jacques From: "su2" <[hidden email]> > > 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. > |
|
|
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.
|
Re: how to import data from excel sheet
|
|
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 <[hidden email]> 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" <[hidden email]> > >> > >> 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. > |
Re: how to import data from excel sheet
|
Administrator
|
And for that use viewdatafile.... There are messages in user ML explaining how to do it...
Jacques From: "S K Pradeep kumar" <[hidden email]> > 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 <[hidden email]> 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" <[hidden email]> >> >> >> >> 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. >> > |
|
|
In reply to this post by S K Pradeep kumar
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-
|
Re: how to import data from excel sheet
|
|
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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> > >> >> > >> >> 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. > |
|
|
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 -
|
Re: how to import data from excel sheet
|
|
In reply to this post by Jacques Le Roux
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" <[hidden email]> >>> 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. |
Re: how to import data from excel sheet
|
|
In reply to this post by su2
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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> > >> >> >> > >> >> >> 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. > |
Re: how to import data from excel sheet
|
|
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 < [hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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 <[hidden email]> 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" <[hidden email]> > > >> >> >> > > >> >> >> 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. > > > |
|
|
Hi Rishi,
I was not able to find the ImportProductServices.java file. Where can I locate it? Thanks. Shuchi-
|
|
|
In reply to this post by Rishi Solanki
Hi RIshi,
I answered myself, I got the patch . Built and restarted ofbiz. I used the same exploratory xls file with the patch. As per the documentation, by Clicking on Import Text I got the result as “Got 3 entities to write to the datasource.” After restarting ofbiz, I do not see the xls data entry into my database Entities - 'Product' and 'InventoryItem' Am I missing something? Thank you for the help. Shuchi- This will schedule “productImport” service
|
«
Return to OFBiz - User
|
1 view|%1 views
| Free forum by Nabble | Edit this page |