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