Unable to get images from django model to excel sheet

Submitted 1 year, 10 months ago
Ticket #38
Views 344
Language Django
Priority Urgent
Status Closed

I am creating to get data from django model to excel sheet,while doing so I am getting all the data except image,in that I am getting URL of image

I am posting the link of my question on stack overflow, please sove this ,it's urgent

https://stackoverflow.com/questions/62848215/unable-to-get-images-on-excel-sheet-using-openpyxl-package-django

Submitted on Jul 08, 20
add a comment

3 Answers

Verified

are you able to retrieve the Image url from admin panel.?. And what data you are getting when you do obj.Image1.url in your script?

Submitted 1 year, 10 months ago

yes i am able to retrieve in admin pannel,while downloading the excel sheet i am getting the root address of the image in the excel sheet under image

- ashutoshmishra333 1 year, 10 months ago

obj.Image1.url=getting the url of the root location of image.

- ashutoshmishra333 1 year, 10 months ago

are your getting 'media/images/{filename}.jpg' ? . If yes then you need to join os.path.join(BASE_DIR, 'media') in your script so that you will get full path of image in local PC which will have the location of the image.

- bhavana 1 year, 10 months ago

I have done this in my settings.py, Or do I need to add some other place,

- ashutoshmishra333 1 year, 10 months ago

you have to do in your excel download script, because that script doesnt have any connection to your web server. As you are trying to download files from Web server to desktop. Think about like this, ImageField if you are using in frontend then it has be render from web server but we are trying to download the file from webserver to desktop, then we have to provide everything explicitly in order to download the files.Also can you send me the output you are getting when you use "obj.image.url" option

- bhavana 1 year, 10 months ago

@Bhavana,thaks for ur help, can we discuss this tomorrow,as I am unable to get to my system.

- ashutoshmishra333 1 year, 10 months ago

And where do I need to add is.path ,in my script?,

- ashutoshmishra333 1 year, 10 months ago

Below are the sample one I used in one of my project but this one CSV,
def export_creds_csv(request): user_details = request.user

    export_obj = Task.objects.create(user=user_details)


    response = HttpResponse(content_type='text/csv')
    response['Content-Disposition'] = 'attachment; filename="Task.csv"'

    writer = csv.writer(response)
    writer.writerow(['Name', 'Server', ' Environment',
         'Name','Password'])

    creds= Some.objects.all().values_list('Applnname', 'Applnservername', 'Applnenv',
         'Applnusername','Applnpassword')
    for cred in creds:
        writer.writerow(cred)

    return response
- bhavana 1 year, 10 months ago

sorry i misunderstood your question, check the above sample code, if it helps.

- bhavana 1 year, 10 months ago

What u posted above is right and my code is also working,I am getting all the data on excel sheet as required Except the image,I want image on excel sheet,obj.Image11.URL=media/images/{filename}.jpg'

- ashutoshmishra333 1 year, 10 months ago

can you show your settings MEDIA variable & also project urls.py file info. seems like issue in your settings to grab the image.url path.

- bhavana 1 year, 10 months ago

OK,let me try

- ashutoshmishra333 1 year, 10 months ago

You could also check this in your Django shell just like below,

from blogs.models import Post p = Post.objects.first() p.blog_image <ImageFieldFile: default.jpg> p.blog_image.url
If you are getting the full path, then you should able to get the full path in your download as well.

- arun 1 year, 10 months ago

or you can use obj.image.path this will also return the path as long if you are not using any storage like AWS etc

- arun 1 year, 10 months ago

STATIC_URL = '/static/'

MEDIA_ROOT = os.path.join(BASE_DIR, 'media/') MEDIA_URL = '/media/'

- ashutoshmishra333 1 year, 10 months ago

urls.py

from rest_framework import routers from office.views import * from django.conf.urls import url from django.conf.urls.static import static

router = routers.DefaultRouter()

router.register(r'Task', TaskViewSet,basename='Task') urlpatterns = router.urls

- ashutoshmishra333 1 year, 10 months ago

obj.image.path=home/ashu/Desktop/django/task/media/media/images/Most-Popular-Sports-in-America.jpg

- ashutoshmishra333 1 year, 10 months ago

did you tried the 2 options which i have posted before one on Django shell and another is path?. Check below sample code,

r = Room.objects.first() r.image <ImageFieldFile: cover_pics.JPG> r.image.url '/media/cover_pics.JPG' r.image.path 'C:\Gui_Django\hotelmanagement\media\cover_pics.JPG'

project.urls.py looks like below ,
if settings.DEBUG: urlpatterns += static(settings.MEDIA_URL, document_root=settings.MEDIA_ROOT)
settings.py file ,
STATIC_ROOT = os.path.join(BASE_DIR, 'static') STATIC_URL = '/static/'

MEDIA_ROOT = os.path.join(BASE_DIR, 'media')
MEDIA_URL = '/media/'
- arun 1 year, 10 months ago

using shell getting media/images/Most-Popular-Sports-in-America.jpg

- ashutoshmishra333 1 year, 10 months ago

@Admin tried all,output i still same

- ashutoshmishra333 1 year, 10 months ago

In both conditions I am getting only the URL of the image,not the image on excel sheet

- ashutoshmishra333 1 year, 10 months ago

Id Name Image1 Image2 Date 1 Ashutosh /home/ashu/Desktop/django/task/media/media/images/Most-Popular-Sports-in-America.jpg /home/ashu/Desktop/django/task/media/media/images/Most-Popular-Sports-in-America_FC5tPy0.jpg 2020-07-01 2 Sumit /home/ashu/Desktop/django/task/media/media/images/Most-Popular-Sports-in-America_PscVBTH.jpg /home/ashu/Desktop/django/task/media/media/images/Most-Popular-Sports-in-America_ZdsbkXn.jpg 2020-06-26

- ashutoshmishra333 1 year, 10 months ago

hey from django we only able to download the URL, you have to do embedding the image in excel outside. Not from django. we are download the model as a data right?..At least i never see this before. excel

- arun 1 year, 10 months ago

Can u tell mW how can I do this,its my task

- ashutoshmishra333 1 year, 10 months ago

After the download excel you can do by some sort of script or vba. You can refer the link which I sent

- arun 1 year, 10 months ago

@Admin what you posted about excel,that is not as per requirement.I have searched a lot about this,not getting the desired result Could you suggest something else

- ashutoshmishra333 1 year, 10 months ago

As i mentioned, there is no straight forward approach to display image in excel from django. Django return the data in URL or image path, in excel you have to do some kind of customization to display as image.

- arun 1 year, 10 months ago

import openpyxl

wb = openpyxl.Workbook() ws = wb.worksheets[0] img = openpyxl.drawing.image.Image('test.jpg') img.anchor(ws.cell('A1')) ws.add_image(img) wb.save('out.xlsx')

i found this,i have tried this its working . will it work there,or as u mentioned django return only the url ,we cannot directly post image there.

- ashutoshmishra333 1 year, 10 months ago

The options we tried it should work , but it will write the file path only. But seems like you are requirement is showing that image in excel right?. That option as far as I know , we will not be able to do that when we write the excel from Django. but after the excel download , we can write script/VBA script to handle it to display the image. May be if you find any info , how to display the image in excel from Django download , then please post the details it would help others as well.

- arun 1 year, 10 months ago

you would not belive i have been trying this for last 20 days,i have posted this question on every website ,whatsapp group,telegram group,where i could get response,but unfurtunately all void.i will give some more time on this.Anyways thanks for your help.If i get the result i will post here.

- ashutoshmishra333 1 year, 10 months ago

Meanwhile I will also search from my end. If i find any info keep you posted here.

- arun 1 year, 10 months ago

http://my-django-python.blogspot.com/2015/05/how-to-create-excel-file-with-images.html

see this for once,i am not getting this completely

- ashutoshmishra333 1 year, 10 months ago

we need to convert the image url to the image again,that is the issue

- ashutoshmishra333 1 year, 10 months ago

https://stackoverflow.com/questions/62876915/unknown-url-typ-media-photo-ashu-jpg-django hey admin just see this,just solve this,resolve this .

- ashutoshmishra333 1 year, 10 months ago

We dont understand. why should we resolve this issue in Stack Overflow?.. As I mentioned if you find answer, you can posted here so that it would help somebody. But as far as I know there is no straight forward approach as you expected. So we are closing the ticket from our end.

- arun 1 year, 10 months ago

Sry, it would not happen again,,

- ashutoshmishra333 1 year, 10 months ago

hey admin,i got the iage on excel sheet,finally done,need some time to get complete output,but my initial problem has solved.i will share the code here to help everyone

- ashutoshmishra333 1 year, 10 months ago

Yes pls share the code here

- arun 1 year, 10 months ago

Hey @ashutoshmishra333 share me the code bro need it badly !!!

Submitted 11 months, 2 weeks ago

class TaskViewSet(viewsets.ViewSet):

    def list(self,request):
            try:

                output = io.BytesIO()
                
                workbook=xlsxwriter.Workbook(output)
                worksheet=workbook.add_worksheet()

                product=Task.objects.all()
            

                heading=['Id','Name','Image1','Image2','Date']

                for counter,head in enumerate(heading):
                    worksheet.write(0,counter,head)
                
                
                for count,data in enumerate(product):
                    count=count*7+1

                    worksheet.write(count,0,data.Id)
                    worksheet.write(count,1,data.Name)

                    new=data.Image1.url
                    main=request.build_absolute_uri('/')
                    main=main[:-1]
                    url=main+new
                    image_data=BytesIO(urlopen(url).read())
                    worksheet.set_column(2,3,16.5)
                    worksheet.insert_image(count,2,url,{'image_data':image_data,'x_scale': 0.1, 'y_scale': 0.2})

                    new2=data.Image2.url
                    main2=request.build_absolute_uri('/')
                    main2=main2[:-1]
                    url2=main2+new2
                    image_data2=BytesIO(urlopen(url2).read())
                    worksheet.insert_image(count,3,url2,{'image_data':image_data2,'x_scale': 0.1, 'y_scale': 0.2})
                    worksheet.set_column(4,4,11)
                    worksheet.write(count,4,str(data.Date))
                    
                print(count)
                
                workbook.close()
                output.seek(0)
                response = HttpResponse(output.read(),content_type='application/ms-excel')
                response['Content-Disposition']='attachment; filename="users.xls"'
                return response
            except Exception as error:
                traceback.print_exc()
                return Response({"message": str(error), "success": False}, status=status.HTTP_200_OK)

Submitted 8 months, 2 weeks ago


Wanna Post Ad?
Reach a large group of audience by posting an ad about your business here. For more details
Drop us a Message