You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ofbiz.apache.org by Nalin Chandra <na...@palindromesoftware.com> on 2009/10/28 02:08:41 UTC
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
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.
>
>
>
--
View this message in context: http://n4.nabble.com/how-to-import-data-from-excel-sheet-tp160724p277368.html
Sent from the OFBiz - User mailing list archive at Nabble.com.