#coding:utf-8
import MySQLdb
import xlsxwriter
import datetime
import os
today = datetime.date.today()
oldfile=‘demo‘+str(today.day-2)+‘.xlsx‘
newfile=‘demo‘+str(today.day-1)+‘.xlsx‘
if os.path.isfile(oldfile) :
    os.remove(oldfile)
else:
    pass

def getdata():
    conn = MySQLdb.connect(host=‘服务器IP地址‘, user=‘root‘, passwd=‘服务器密码‘, db=‘local_db‘, port=3306, charset=‘utf8‘)
    cursor = conn.cursor()
    test_sql = ‘‘‘
        SELECT DATE(订单日期) AS 订单日期,ROUND(SUM(金额)) AS 总计 ,ROUND(SUM(IF(城市="北京",金额,NULL))) AS 北京,ROUND(SUM(IF(城市="成都",金额,NULL))) AS 成都
       ,ROUND(SUM(IF(城市="杭州",金额,NULL))) AS 杭州,ROUND(SUM(IF(城市="济南",金额,NULL))) AS 济南,ROUND(SUM(IF(城市="长春",金额,NULL))) AS 长春
       ,ROUND(SUM(IF(城市="青岛",金额,NULL))) AS 青岛,ROUND(SUM(IF(城市="南京",金额,NULL))) AS 南京,ROUND(SUM(IF(城市="郑州",金额,NULL))) AS 郑州
       ,ROUND(SUM(IF(城市="西安",金额,NULL))) AS 西安
        FROM a003_order
        WHERE 金额>0 AND DATE(订单日期)>=DATE_ADD(CURRENT_DATE,INTERVAL -15 DAY) AND DATE(订单日期)<CURRENT_DATE
        GROUP BY DATE(订单日期)
    ‘‘‘
    count=cursor.execute(test_sql)
    conn.commit()
    data=http://www.mamicode.com/cursor.fetchall()"订单日期", 1: "总计", 2: "北京", 3: "成都", 4: "杭州", 5: "济南", 6: "长春", 7: "青岛", 8: "南京", 9: "郑州", 10: "西安"}
    for i in range(0, 11):
        title = dic1[i].decode(‘utf-8‘)
        worksheet.write(0, i, title)
    for x in range(0, 15):
        for y in range(0, 11):
            db = str(data[x][y]).decode(‘utf-8‘)
            worksheet.write(x + 1, y, db)
    workbook.close()
print write_excel()