django工程调用pymssql模块实现sqlserver2008数据库表导出到excel

最新推荐文章于 2022-08-02 05:27:31 发布

lsysafe

最新推荐文章于 2022-08-02 05:27:31 发布

阅读量2.7w

点赞数

本文链接：https://blog.csdn.net/lsysafe/article/details/82221789

版权

urls.py加入：

url(r'^exp/$',views.exp,name='exp'),

views.py加入该方法：

def exp(request):
if request.method=="POST":
host=request.POST.get("dbip")
ports=request.POST.get("dbport")
dbuname=request.POST.get("dbuname")
dbpwd=request.POST.get("dbpwd")
dbname=request.POST.get("databases")
sqlstr=request.POST.get("sqlstr")
expsrts=""
try:
expsrts=dbs2.exportdbtitle(host,ports,dbuname,dbpwd,dbname,sqlstr)
except Exception:
return render(request,"expdatabase.html",{"login_err":"FAIL"})

return render(request,"expdatabase.html",{"login_err":"downloadlink:ftp://FTP服务器IP地址/excel/"+expsrts})
else:
return render(request,"expdatabase.html",{"login_err":"no set"})

dbs2.py写好exportdbtitle的方法：

import pymssql #导入这个模块，当然，也要导入其它代码当中有用到的模块，这里就不一一列举了

def exportdbtitle(hosts,ports,users,pwds,dbs,sql_str):

conn=pymssql.connect(host=hosts,port=ports,user=users,password=pwds,database=dbs,charset="UTF-8")
cursor = conn.cursor()

cursor.execute(sql_str)
data=cursor.fetchall()
index = cursor.description
titles=[]
nowtime=time.strftime('%Y-%m-%d-%H-%M-%S',time.localtime(time.time()))+""
for i in range(len(index)):
titles.append(index[i][0])

# workbook2007 = xlsxwriter.Workbook('c:\\exports.xlsx') #创建目标excel表格
workbook2007 = xlsxwriter.Workbook('.\\excel\\exports'+nowtime+'.xlsx') #创建目标excel表格

#此处将上一行的目录发布为FTP服务器的EXCEL目录，方便将来用户下载导出的EXCEL文件，并设置好访问权限

worksheet2007 = workbook2007.add_worksheet()
fw=open('DSE_WR_W_DAY.txt','w')
format = workbook2007.add_format()
formattitle =workbook2007.add_format({'bold': True, 'font_color': 'black'})

col=0
for t1 in titles:
print t1

worksheet2007.write(0,col,str(t1),formattitle)
col=col+1

#同时输出到excel和txt
[[[[worksheet2007.write(i+1,j,str(value),format),fw.write(str(value)),fw.write('\t')] for j,value in enumerate(value_r)] ,fw.write('\n')] for i,value_r in enumerate(data)]
workbook2007.close()
fw.close()
expstr='exports'+nowtime+'.xlsx'
return expstr

#print ('excel创建完成')

前端的expdatabase.html

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Title</title>
</head>
<body>
<form id="loginForm" action="{% url 'exp' %}" method="POST"> {% csrf_token %}
IP\实例<input type="text" class="form-control" name="dbip" placeholder="IP\实例" value="默认IP\实例名"></br>
实例端口<input type="text" class="form-control" name="dbport" placeholder="实例端口" value="1444"></br>
连接帐号<input type="text" class="form-control" name="dbuname" placeholder="连接帐号"></br>
连接密码<input type="password" class="form-control" name="dbpwd" placeholder="连接密码"></br>
数据库名<input type="text" class="form-control" name="databases" placeholder="数据库名"></br>
SQL语句<input type="text" class="form-control" name="sqlstr" placeholder="SQL语句"></br>
<button class="btn btn-success btn-block" type="submit">
<b>确定导出</b>
</button>

<h4 style="color: red"><b>{{ login_err }}</b></h4>
</form>
</body>
</html>

演示效果：

输入对应的信息，确定导出数据，系统返回下载链接，为确保数据的安全，设置好FTP的访问权限

lsysafe

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
django工程调用pymssql模块实现sqlserver2008数据库表导出到excel

urls.py加入：url(r'^exp/$',views.exp,name='exp'),views.py加入该方法：def exp(request): if request.method=="POST": host=request.POST.get("dbip") ports=request.POST.get("dbport") ...
复制链接

扫一扫