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.
