Upload data via Excel sheet using django-excel

Question

I building a website using Django and I would like to create a functionality in which an user via an excel sheet can upload information and populate the database. So I install the django-excel package and follow the example:

settings.py:

FILE_UPLOAD_HANDLERS = ("django_excel.ExcelMemoryFileUploadHandler",
                    "django_excel.TemporaryExcelFileUploadHandler")

urls.py:

url(r'^import_sheet/', views.import_sheet, name="import_sheet"),

views.py:

class UploadFileForm(forms.Form):
   file = forms.FileField()

def upload(request):
if request.method == "POST":
    form = UploadFileForm(request.POST, request.FILES)
    if form.is_valid():
        filehandle = request.FILES['file']
        return excel._make_response(filehandle.get_sheet(),"xslx",file_name="download")  
    else:
        form = UploadFileForm()
        return render(request,'upload_form.html',{
            'form': form,
            'title': 'Excel file upload',
            'header': 'Please choose a valid excel file'
        })

def import_sheet(request):
    if request.method == "POST":
        form = UploadFileForm(request.POST,
                          request.FILES)
        if form.is_valid():
            request.FILES['file'].save_to_database(
                model= quote_input,
                mapdict= ["value_date", "value", "type", 'name'])
            return HttpResponse("OK")
        else:
            return HttpResponseBadRequest()
    else:
        form = UploadFileForm()
    return render(
        request,
        'upload_form.html',
        {'form': form})

models.py:

class quote_input(models.Model):
    value_date  = models.DateField()
    value       = models.FloatField()
    type        = models.TextField(max_length=254)
    name        = models.TextField(max_length=254)
    # link
    fund            = models.ForeignKey(Fund)
    benchmark       = models.ForeignKey(Benchmark)
    factor          = models.ForeignKey(Factors)
    stress_factor   = models.ForeignKey(Stress_Factors)

When I try this code by uploading a basic excel sheet:

I got an OK (HttpResponse) but when I looked at the database, I have uploaded nothing.

Does someone have the same problem? or can provide another example that I can use? another methodology?

Another question: within the django-excel documentation, there is a reference about a function "choice_func" which is defined as a custom formatting function. Can anyone provide more information about this function?

Answer 1

Here's a version of my code, I use xlrd library.

import re
import xlrd

from agencies.models import Invite
from extras.permissions import send_mail_wrapper
from sms.sms_api import send_sms


def handle_uploaded_file(request, f):
    book = xlrd.open_workbook(file_contents=f.read())
    for sheet in book.sheets():
        number_of_rows = sheet.nrows
        number_of_columns = sheet.ncols

        for row in range(1, number_of_rows):

            first_name = (sheet.cell(row, 0).value)
            last_name = (sheet.cell(row, 1).value)
            email = (sheet.cell(row, 2).value)

            phone_cell = (sheet.cell(row, 3).value)
            if phone_cell:
                phone = re.sub("[^0-9]", "", str(phone_cell))
            else:
                phone = ""
            gender = (sheet.cell(row, 4).value).upper()

            if email != "":
                invite, created = Invite.objects.get_or_create(agency=request.user.agency, email=email)

                if email and created:
                    send_sms(phone)
                    send_mail_wrapper(
                        "You \'ve been invited", 'email/invite_email.html',
                        {}, [email], from_email='Casey <[email protected]>')

And the view:

class StudentBulkAddView(ListView):
    model = Student
    template_name = 'student_add.html'

    @method_decorator(login_required)
    def dispatch(self, *args, **kwargs):
        return super(DashboardStudentBulkAddView, self).dispatch(*args, **kwargs)

    def post(self, request):
        if self.request.user.is_authenticated:
            try:
                handle_uploaded_file(request, request.FILES['student_file'])
                success = True

And the template:

                <form action="{% url 'students:student_dashboard_bulk_invite' %}" method="POST"
                      enctype="multipart/form-data">
                    <div class="form-settings notes-form">
                        {% csrf_token %}
                        {{ upload_form }}
                        <button id="invite-tutor-btn" class="btn btn-primary margin-top"
                                data-loading-text="Loading..." type="submit">Save
                        </button>
                    </div>
                </form>

Answer 2

I think all you need to do is this:

def upload(request):
    form = None
    if request.method == "POST":
        form = UploadFileForm(request.POST, request.FILES)
        if form.is_valid():
            filehandle = request.FILES['file']
            # call import_sheet to import the sheet into your database
            return import_sheet(request)  
    form = form or UploadFileForm()
    return render(request,'upload_form.html',{
        'form': form,
        'title': 'Excel file upload',
        'header': 'Please choose a valid excel file'
    })