想請教各位,我用py+dj做網頁和pandas套件。
主要是想把篩選完的不同學號資料,存檔輸出成Excel檔案。
目前是先測試抓網頁上的資料並存成CSV檔,雖然目前是可以執行下載按鈕並存檔到電腦;但問題是會抓不到資料,只會存成空檔案...(問題1)
另外因考量到存取效率問題,打算從db裡面抓篩選完不同學號資料並存檔成excel,
請問要怎麼修改才好?(問題2)
卡很久都還沒頭緒@@
謝謝各位!!
Models:
class CourseData(models.Model):
sid = models.CharField(max_length=20, verbose_name="學號")
name = models.CharField(max_length=20, verbose_name="姓名")
phone = models.CharField(max_length=20, verbose_name="手機")
email = models.CharField(max_length=100, verbose_name="信箱")
sdept = models.CharField(max_length=50, verbose_name="學生系所")
scls = models.CharField(max_length=50, verbose_name="班級")
scampus = models.CharField(max_length=20, verbose_name="校區")
cid = models.CharField(max_length=20, verbose_name="選課代號")
ctype = models.CharField(max_length=20, verbose_name="學制")
cdept = models.CharField(max_length=50, verbose_name="開課系所")
ccls = models.CharField(max_length=50, verbose_name="開課班級")
ccampus = models.CharField(max_length=20, verbose_name="上課校區")
teacher = models.CharField(max_length=20, verbose_name="上課教師")
cdeptemail = models.CharField(max_length=100, verbose_name="開課單位信箱")
teacheremail = models.CharField(max_length=100, verbose_name="上課教師信箱")
ctime = models.CharField(max_length=50, verbose_name="上課時間")
place = models.CharField(max_length=20, verbose_name="上課教室")
subject = models.CharField(max_length=50, verbose_name="科目")
mcls = models.CharField(max_length=50, verbose_name="合班班級")
snum = models.PositiveIntegerField(default=0, verbose_name="人數 ")
duration = models.CharField(max_length=50, verbose_name="防疫假期間")
def __str__(self):
return self.name
class StudentData(models.Model):
sid = models.CharField(max_length=20, verbose_name="學號")
name = models.CharField(max_length=20, verbose_name="姓名")
stype1 = models.CharField(max_length=20, verbose_name="部別")
stype2 = models.CharField(max_length=20, verbose_name="學制")
dept = models.CharField(max_length=20, verbose_name="系所")
deptfullname = models.CharField(max_length=50, verbose_name="系所全名")
clasaname = models.CharField(max_length=50, verbose_name="班級名稱")
phone = models.CharField(max_length=20, verbose_name="學生手機")
campus = models.CharField(max_length=20, verbose_name="校區")
def __str__(self):
return self.name
class Department(models.Model):
name = models.CharField(max_length=50, verbose_name="系所簡稱")
def __str__(self):
return self.name
class CDepartment(models.Model):
name = models.CharField(max_length=50, verbose_name="開課單位")
def __str__(self):
return self.name
Template:
{% extends "base.html" %}
{% block headmessage %}查詢系統{% endblock %}
{% block main-content %}
<hr>
<script>
function changesid(x) {
window.location = "/bysid/"+x.value+"/";
}
function changedepart(x) {
window.location = "/byssdepart/"+x.value+"/";
}
// function changecdepart(x) {
// window.location = "/bycdepart/"+x.value+"/";
// }
</script>
<form action="/bysid/" method="POST">
{% csrf_token %}
學號查詢:<input name="sid" size="50" placeholder="Search" >
<input type="submit" value="查詢">
<!-- <select style="width: 150px;" onchange="changesid(this)">
<option value=0>請選擇學號</option>
{% for sid in ssid %}
<option value={{ sid.id }}>{{ sid.sid }}</option>
{% endfor %}
</select> -->
學生系所查詢:
<select style="width: 200px;" onchange="changedepart(this)">
<option value=0>請選擇系所</option>
{% for dept in sdepartments %}
<option value={{ dept.id }}>{{ dept.name }}</option>
{% endfor %}
</select>
</form>
<br>
{% csrf_token %}
{{ form }}
<button onclick="window.location.href='/down_file'">下載檔案</button>
</form>
<hr>
以下共顯示{{ num }}筆資料:
<table class="table table-striped table-hover table-xxl text-nowrap">
<tr>
<td>學號</td><td>姓名</td><td>手機</td><td>學生信箱</td><td>學生系所</td>
<td>學生班級</td><td>學生校區</td><td>選課代號</td><td>開課學制</td><td>開課系所</td>
<td>開課班級</td><td>上課校區</td><td>上課教師</td><td>開課單位信箱</td><td>開課老師信箱</td>
<td>上課時間</td><td>上課教室</td><td>科目</td><td>合班班級</td><td>實收人數</td><td>防疫假期間</td>
</tr>
{% for c in course %}
<tr>
<td><small>{{c.sid}}</small></td> <!--學號-->
<td><small>{{c.name}}</small></td> <!--學生姓名-->
<td><small>{{c.phone}}</small></td> <!--手機-->
<td><small>{{c.email}}</small></td><!--學生信箱-->
<td><small>{{c.sdept}}</small></td><!--學生系所-->
<td><small>{{c.scls}}</small></td><!--學生班級-->
<td><small>{{c.scampus}}</small></td> <!--學生校區-->
<td><small>{{c.cid}}</small></td> <!--選課代號-->
<td><small>{{c.ctype}}</small></td> <!--開課學制-->
<td><small>{{c.cdept}}</small></td><!--開課系所-->
<td><small>{{c.ccls}}</small></td><!--開課班級-->
<td><small>{{c.ccampus}}</small></td><!--上課校區-->
<td><small>{{c.teacher}}</small></td><!--上課教師-->
<td><small>{{c.cdeptemail}}</small></td><!--開課單位信箱-->
<td><small>{{c.teacheremail}}</small></td><!--開課老師信箱-->
<td><small>{{c.ctime}}</small></td><!--上課時間-->
<td><small>{{c.place}}</small></td><!--上課教室-->
<td><small>{{c.subject}}</small></td><!--科目-->
<td><small>{{c.mcls}}</small></td><!--合班班級-->
<td><small>{{c.snum}}</small></td><!--實收人數-->
<td><small>{{c.duration}}</small></td><!--防疫假期間-->
</tr>
{% endfor %}
</table>
{% endblock %}
view:
系所查詢
def search_by_ssdepart(request, id=1):
sdepartments = Department.objects.all()
scdepartments = CDepartment.objects.all()
ssid = CourseData.objects.all()
try:
target_depart = Department.objects.get(id=id)
course = CourseData.objects.filter(sdept=target_depart).order_by("teacher")
ssid = StudentData.objects.filter(dept="資管系")
except:
course = course = CourseData.objects.filter(sdept="環工程").order_by("teacher")
num = len(course)
return render(request, "list.html")
學號查詢
def search_by_sid(request,pk=1):
sdepartments = Department.objects.all()
cdepartments = CDepartment.objects.all()
ssid = CourseData.objects.all()
if request.method == "POST":
sid = request.POST.get("sid").strip()
if "," in sid:
course = CourseData.objects.filter(sid="NA")
sids = sid.split(",")
for n in sids:
courses = CourseData.objects.filter(sid=n.strip())
course = course | courses
else:
course = CourseData.objects.filter(sid=sid).order_by("teacher")
num = len(course)
return render(request, "list.html")
return redirect("/")
檔案下載
def down_file(request,id=1):
url="http://127.0.0.1:8000/byssdepart/4/"
down_file = pd.read_html(url)
df = down_file[0]
response = HttpResponse(content_type='text/csv')
writer = csv.writer(response)
response.write(codecs.BOM_UTF8)
try:
writer.writerow(list(df.columns))
for i in range(len(df)):
writer.writerow(list(df.loc[i].values))
response['Content-Disposition'] = 'attachment; filename= "Repost.csv"'
except:
response['Content-Disposition'] = 'attachment; filename= "Error.csv"'
return response
Url:
urlpatterns = [
path('', views.index),
path('query-excel/', views.query_excel),
path('student/', views.student),
path('student/dept/<str:dept>/', views.student),
path('byname/', views.search_by_name),
path('bytname/', views.search_by_tname),
path('bytname/<str:name>/', views.search_by_tname),
path('bydepart/<int:id>/', views.search_by_depart),
path('bycdepart/<int:id>/', views.search_by_cdepart),
path('byssdepart/<int:id>/', views.search_by_ssdepart),
path('list/', views.list),
path('list/dept/<str:dept>/', views.list),
path('bysid/', views.search_by_sid),
path('bysid/<int:pk>/' , views.search_by_sid),
path('down_file/', views.down_file),
path('down_file/<int:id>/', views.down_file),
path('admin/', admin.site.urls),
]
https://xlsxwriter.readthedocs.io/example_django_simple.html
這個是出xlsx檔的,只要用python排好2D list直接套用就可以。
我工作上有碰到類似需求
我是用 openpyxl 整理好以後stream到前端
前端用 ajax 承接資料
給樓主參考看看 030