[Python知识库]Django实现excel导入导出

想必大家都困扰于大批量配置的繁琐和沉重的工作量，这个时候采用文件导入导出的方式能较大程度缓解配置带给系统使用人员的精神压力。基于这个目的，本文将详细介绍基于Django如何实现excel文件的导入导出。
工欲善其事，必先利其器。首先需要安装依赖的模块包如下：

• XlsxWriter
• xlrd
• openpyxl
• pytz

上述功能包下载地址：https://pypi.org/
接下来重头戏。

models.py-定义表模型

class Host_List(models.Model):
    id = models.AutoField
    host_ip = models.CharField(max_length=100,unique=True)
    host_name = models.CharField(max_length=16,null=True,blank=True)
    create_time = models.DateTimeField(auto_now_add=True)

urls.py-定义接口入口

from . import files

urlpatterns = [
    url(r'^upload/',files.upload),
    url(r'^download/',files.download),
]

files.py-编写上传下载实现逻辑

import datetime
import pytz
import json
import operator
from django.core import serializers
import os
import xlrd
import time
import openpyxl
import xlsxwriter
from django.http import HttpResponse,StreamingHttpResponse

def download_template(request,filename):
    date_p = datetime.datetime.now().date()
    str_p = str(date_p)  
    fp = open('/etc/files/static/download/'+filename, 'rb')
    response = StreamingHttpResponse(fp)
    # response = FileResponse(fp)
    response['Content-Type'] = 'application/octet-stream'
    filename=filename.split('.')
    filename=filename[0]+'_'+str_p+'.xlsx'
    response['Content-Disposition'] = 'attachment;filename="%s"' % filename
    return response
    fp.close()
    
def upload(request):
    response={}
    status='success'
    if request.method == 'POST':
        file_obj = request.FILES.get('file')
        current_time=str(time.time())
        f = open(os.path.join('/etc/files', 'static', 'pic', current_time+file_obj.name), 'wb')
        print(file_obj,type(file_obj))
        for chunk in file_obj.chunks():
            f.write(chunk) ##数据流写入服务端本机excel文件中
        f.close()
        book = xlrd.open_workbook(os.path.join('/etc/files', 'static', 'pic', current_time+file_obj.name))##打开服务端excel文件
        sheet1 = book.sheets()[0]        
        result=[]
        for i in range(1,sheet1.nrows):
            tmp=[sheet1.cell(i,0).value,sheet1.cell(i,1).value]
            result.append(tmp)
        for i in range(0,len(result)-1):##判断excel中是否有重复IP
            for j in range(i+1,len(result)):
            	if (result[i][0]==result[j][0]):
                  response={}
                  response['status']='dumplicate'
                  response['host_ip']=result[i][0]
                  json_data = json.dumps(response)
                  return HttpResponse(json_data)
        host_list=Host_List.objects.all()
        for i in range(0,len(result)):#判断excel中IP信息是否已存在数据库中
            for var in host_list:
                if result[i][0].strip()==var.host_ip.strip():
                    response={}
                    response['status']='exists'
                    response['host_ip']=result[i][0]
                    json_data = json.dumps(response)
                    return HttpResponse(json_data)                   
        for i in range(0,len(result)):  ##excel中数据写入数据库                     
            hostlistadd=Host_List(host_ip=result[i][0],host_name=result[i],create_time=datetime.datetime.now())
            hostlistadd.save()  
            if hostlistadd.id>0:
                response={}
                response['status']='success'                      
                json_data = json.dumps(response)
                return HttpResponse(json_data)  
        
         
def hostsdownload(request):
    f=xlsxwriter.Workbook('/etc/files/static/download/hostmodel.xlsx')
    bold_head = f.add_format({
            'bold':  True,  # 字体加粗
            'border': 1,  # 单元格边框宽度
            'align': 'left',  # 水平对齐方式
            'valign': 'vcenter',  # 垂直对齐方式
            'fg_color': '#48D1CC',  # 单元格背景颜色
            'text_wrap': True,  # 是否自动换行
        })

    worksheet1=f.add_worksheet('服务器模板')
    data=['服务器IP','服务器名称']
    worksheet1.write_row("A1",data,bold_head) 

    tmp=[]
    host_list=Host_List.objects.all()
    for var in host_list:
        tmp.append(var.host_ip)
        worksheet1.data_validation("A2:A1000", {'validate':'list', 'source':tmp})         	     
        tmp=[]
        tmp.append(var.host_ip)
        tmp.append(var.host_name)
        result.append(tmp)
    for i in range(len(result)):
        worksheet1.write(i+1,0,result[i][0])
        worksheet1.write(i+1,1,result[i][1])
    
    f.close()
    result=download_template(request,'hostmodel.xlsx')
    return result 

files.js

function fileupload(){
	var form_data=new FormData();
    var file_info=$('input[name="file"]')[0].files[0];
    var isok=0;
    form_data.append('file',file_info);
    $.ajax({
      url:'/upload/',
      type:'POST',
      async:false,
      data:form_data,
      processData:false,
      contentType:false,
      success:function(callback){
          console.log('return:'+callback);
		      json_data=JSON.parse(callback);
		      if(json_data['status']=='modelerror'){
		      	alert('模板类型异常');
		      	}
		      else if(json_data['status']=='dumplicate'){
		      	alert('导入文件中存在重复IP：'+json_data['host_ip']); 
		      	}
		      else if(json_data['status']=='exists'){
		      	alert('IP已存在：'+json_data['host_ip']);
		      	}
		      else if(json_data['status']=='success'){
		      	alert('导入成功');
		      	}
		      else{
		      	alert('导入失败，原因请分析');
		      	}
		      	
		    	console.log('return:'+json_data);
		    	isok=1;				
		
		}

});

}

function filedownload(){
    window.location.href='/download/';

}

upload.html-编写html实现逻辑

<div class="form-group" >
  <div class="input-group">
    <input type="text" class="form-control" placeholder="选择文件" name="filename">
    <input type="file" name="file" class="form-control" style="position:absolute;z-index: 10;opacity:0;" />                                        
  </div>
</div>
<div class="king-btn king-info" style="margin-left:10px;" id="fileupload">导入</div>

download.html

<div class="king-btn king-info" style="margin-left:10px;" id="filedownload">导出</div>