使用django-excel通过excel表上传数据 [英] upload data via excel sheet using django-excel
问题描述
我没有在网络上找到有关我的问题的任何内容。所以我希望有人可以帮助我。
我使用Django构建一个网站,我想创建一个功能,用户可以通过excel表上传信息并填充数据库。所以我安装django-excel包,并按照示例:
settings.py :
FILE_UPLOAD_HANDLERS =(django_excel.ExcelMemoryFileUploadHandler,
django_excel.TemporaryExcelFileUploadHandler)
urls.py :
url(r' ^ import_sheet /',views.import_sheet,name =import_sheet),
py
class UploadFileForm(forms.Form):
file = forms.FileField()
def upload(request):
如果request.method ==POST:
form = UploadFileForm(request.POST,request.FILES)
如果窗体。 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':'请选择一个有效的excel文件'
})
def import_sheet(request):
if request.method ==POST:
form = UploadFileForm(request.POST,
request.FILES)
如果form.is_valid():
request.FILES ['file']。save_to_database(
model = quote_input,
mapdict = [value_date,value,type
return HttpResponse(OK)
else:
return HttpResponseBadRequest()
else:
form = UploadFileForm()
return render (
请求,
'upload_form.html',
{'form':form})
models.py :
class quote_input(models.Model) :
value_date = models.DateFiel d()
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)
当我通过上传一个基本的excel表来尝试这个代码:
我有一个OK(HttpResponse),但是当我查看数据库时,我没有上传任何内容。
有人有同样的问题吗?还是可以提供另一个可以使用的例子?另一种方法?
另一个问题:在django-excel文档中,有一个函数choice_func的引用,它定义为一个自定义格式化函数。有人可以提供更多有关此功能的信息吗?
非常感谢您的帮助
Gilles
以下是我的代码版本,我使用 xlrd 库。
import re
import xlrd
来自agency.models import Invite
from extras.permissions从sms.sms_api导入send_mail_wrapper
import send_sms
def handle_uploaded_file(request,f):
book = xlrd.open_workbook(file_contents = f.read())
在book.sheets()中的工作表:
number_of_rows = sheet.nrows
number_of_columns = sheet.ncols
对于行范围(1,number_of_rows):
first_name =(sheet.cell(row,0).value)
last_name =(sheet.cell(row,1).value)
email =(sheet.cell行,2).value)
phone_cell =(sheet.cell(row,3).value)
如果phone_ce ll
phone = re.sub([^ 0-9],,str(phone_cell))
else:
phone =
gender = sheet.cell(row,4).value).upper()
如果email!=:
invite,created = Invite.objects.get_or_create(agency = request.user。
如果电子邮件和创建:
send_sms(电话)
send_mail_wrapper(
你已被邀请,电子邮件/ invite_email.html',
{},[email],from_email ='Casey< success@email.com>')
和视图:
class StudentBulkAddView(ListView):
model = Student
template_name ='student_add.html'
@method_decorator(login_required)
def dispatch(self,* args,** kwargs):
return super(DashboardStudentBulkAddView,个体经营).dispatc h(* args,** kwargs)
def post(self,request):
如果self.request.user.is_authenticated:
try:
handle_uploaded_file(请求,request.FILES ['student_file'])
success = True
模板:
< 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-btnclass =btn btn-primary margin-top
data -loading-text =Loading ...type =submit>保存
< / button>
< / div>
< / form>
I didn't find anything on the web regarding my issue. So I hope that somebody can help me. 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 formating function. Does anyone can provide more information about this function? Thank you very much in advance for your help
Gilles
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 <success@email.com>')
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>
这篇关于使用django-excel通过excel表上传数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!