程式碼
# -*- coding: utf-8 -*- from xlrd import open_workbook import sqlite3 as sqlite def getSegment(xlsx, rowStart, rowNum, colStart, colNum): global dbCursor fieldName = xlsx.cell(rowStart - 1, 0).value fieldName = fieldName.strip('\n') fieldName = "".join(fieldName.split()) for rowData in range(rowStart, rowStart + rowNum): dbSQL = "INSERT INTO MYDATA(YEAR, CITY, FNAME, FVALUE) VALUES(%s, '%s', '%s', %f)" fieldYear = xlsx.cell(rowData, 0).value for colData in range(colStart, colStart + colNum): fieldCity = xlsx.cell(1, colData).value fieldValue = xlsx.cell(rowData, colData).value if type(fieldValue) is not float: fieldValue = 0.00 dbSQL2 = dbSQL % (fieldYear, fieldCity, fieldName, fieldValue) dbCursor.execute(dbSQL2) def query1(dbSQL): global dbCursor fieldNameList = [u'年份', u'城市', u'欄位', u'值'] for col in fieldNameList: print '%s\t' % (col.encode('big5')), print dbCursor.execute(dbSQL) rowList = dbCursor.fetchall() for row in rowList: print '%d\t%s\t%s\t%12.2f' % (row[0], row[1], row[2], row[3]) def query2(dbSQL): global dbCursor fieldNameList = [u'欄位', u'平均值'] for col in fieldNameList: print '%s\t' % (col.encode('big5')), print dbCursor.execute(dbSQL) rowList = dbCursor.fetchall() for row in rowList: print '%s\t%12.2f' % (row[0], row[1]) def query3(dbSQL): global dbCursor fieldNameList = [u'年份', u'欄位', u'平均值'] for col in fieldNameList: print '%s\t' % (col.encode('big5')), print dbCursor.execute(dbSQL) rowList = dbCursor.fetchall() for row in rowList: print '%d\t%s\t%12.2f' % (row[0], row[1], row[2]) def getStat(id): if id == 1: # 詳細 query1("SELECT * FROM MYDATA ORDER BY YEAR, CITY, FNAME") elif id == 2: # 各統計量平均值 query2("SELECT FNAME, AVG(FVALUE) AS MEAN FROM MYDATA GROUP BY FNAME ORDER BY FNAME") elif id == 3: # 各年度所有統計量平均值 query3("SELECT YEAR, FNAME, AVG(FVALUE) AS MEAN FROM MYDATA GROUP BY YEAR, FNAME ORDER BY YEAR, FNAME") def getStatByCity(cityName, id): if id == 1: # 詳細 dbSQL = "SELECT * FROM MYDATA WHERE CITY='%s' ORDER BY YEAR, CITY, FNAME" % (cityName) query1(dbSQL) elif id == 2: # 各統計量平均值 dbSQL = "SELECT FNAME, AVG(FVALUE) AS MEAN FROM MYDATA WHERE CITY='%s' GROUP BY FNAME ORDER BY FNAME" % (cityName) query2(dbSQL) elif id == 3: # 各年度所有統計量平均值 dbSQL = "SELECT YEAR, FNAME, AVG(FVALUE) AS MEAN FROM MYDATA WHERE CITY='%s' GROUP BY YEAR, FNAME ORDER BY YEAR, FNAME" % (cityName) print dbSQL query3(dbSQL) #-- main --# dbConn = sqlite.connect(':memory:') dbCursor = dbConn.cursor() dbCursor.execute("CREATE TABLE MYDATA(YEAR INT, CITY TEXT, FNAME TEXT, FVALUE FLOAT)") filePath = '/Users/Emprogria/Documents/Birdy' xlsxFile = '3.xlsx' wb = open_workbook('%s/%s' % (filePath, xlsxFile)) xlsx = wb.sheet_by_index(0) for i in range(3, 394, 17): getSegment(xlsx, i, 16, 3, 22) # getStat(3) getStatByCity(u'臺北市', 3) dbConn.close()