会python真的可以为所欲为0(回)
359天前
这里还有人吗1(回)
544天前这里还有人吗0(回)
544天前
每天面对着电脑屏幕，敲打键盘。我所面对的并不只是代码，而是一种生活方式。0(回)
769天前到处都是羊，不想上班0(回)
849天前
鸽子
0(回)
853天前#!/usr/bin/env python |
# coding:utf-8 |
# cx_Oracle 用于访问oracle和导出数据 |
import cx_Oracle |
# xlsxwriter 用于生成xlsx文件 |
import xlsxwriter |
import time |
import sys |
def orcale_excl(sql,filename): |
conn_str = r"gkxb60/gkxb2018@192.168.70.176:1521/cmsxb" |
con = cx_Oracle.connect(conn_str) |
cursor = con.cursor() |
# 定义SQL脚本 由于脚本包含中文，使用decode('utf-8').encode('gbk') 对其进行转换 |
print("报表生成开始。。。。") |
query1 = cursor.execute(sql) # 执行查询 |
print(type(query1)) |
title = [i[0] for i in query1.description] |
date_now = time.strftime("%Y%m%d", time.localtime()) |
# 文件名及其路径 |
report_name = 'D:\\' +filename+ date_now + '.xlsx' |
# 生成xlsx格式oracle查询统计报表 |
workbook = xlsxwriter.Workbook(report_name, {'constant_memory': True}) |
worksheet = workbook.add_worksheet() |
data = cursor.fetchall() |
worksheet.write_row(0, 0, title) |
for row, row_date in enumerate(data): |
worksheet.write_row(row + 1, 0, row_date) |
print("报表已生成，请在%s中查看" %report_name) |
cursor.close() |
con.close() |
workbook.close() |
def getsql(): |
cstcode = input("请输入客户代码：") |
createdate=input("请输入创建日期：") |
filename = input("请输入你要生成的报表名称：") |
sqlstr = """ |
select c.cstcode, c.dname, sum(a.sumvalue) |
from scm_salbill_hdr a, pub_clients c |
where a.cstid = c.cstid""" |
if cstcode != '': #按客户查 |
sql = sqlstr + " and c.cstcode = " + "'" + cstcode + "'" + " group by c.cstcode, c.dname" |
elif createdate!="": #按日期查 |
sql = sqlstr + " and a.createyear=" + "'" + createdate + "'" + " group by c.cstcode, c.dname" |
else: |
sql = sqlstr + " group by c.cstcode, c.dname" |
print(sql) |
orcale_excl(sql, filename) |
print(""" |
******************************************************************* |
报表生成系统V1.0 |
欢迎使用，国药控股陕西有限公司，报表生成系统。 |
1、按客户生成报表 |
0、系统退出 |
******************************************************************* |
""") |
while True: |
mycheck=input("请选择：") |
if mycheck=='0': |
break |
elif mycheck=='1': |
getsql() |
