Python+Django 要如何從db篩選資料並下載成excel或csv檔?(已解決)

django python excel download csv

甜點螞蟻教主 2022-05-11 10:22:40 ‧ 1244 瀏覽

分享至

想請教各位，我用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),
]