python

一   excel表格自動(dòng)將數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫(kù)，限制文件后綴名為.xlsx

import xlrd
from datetime import datetime
from xlrd import xldate_as_tuple
#根據(jù)有多少個(gè)sheets去創(chuàng)建多少個(gè)表，path為excel表格的路徑
def createtable(path):
    # 讀取excel
    data = xlrd.open_workbook(path)
    # 根據(jù)sheet索引獲取sheet的內(nèi)容
    print("excel全部的sheet為：", data.sheet_names())
    sheet_names = data.sheet_names()
    table_one = data.sheet_by_index(0)
    print("一個(gè)sheet的全部列名為", table_one.row_values(0))
    conn = psycopg2.connect(database='test', user='postgres', password='root', host='localhost')
    cur = conn.cursor()
    for i in range(0, len(sheet_names)):
        #當(dāng)前sheet的名字
        table_name = sheet_names[i]
        # 當(dāng)前的sheet
        now_table = data.sheet_by_index(i)
        # 獲得當(dāng)前sheet的列數(shù)就是 屬性數(shù)
        cols_num = now_table.ncols
        # 獲得當(dāng)前表格的行數(shù)，就是有多少的數(shù)據(jù)量
        rows_numn = now_table.nrows
        # 獲得當(dāng)前的屬性的數(shù)組，其實(shí)就是第一例的值
        attrs = now_table.row_values(0)
        #判斷表格是否存在
        cur.execute("SELECT to_regclass('%s') is not null" % table_name)
        flag = cur.fetchone()[0]
        print('flag',flag)
        if flag :
            print('存在了,直接將表的內(nèi)容插入')
            # 將當(dāng)前的sheet插入到數(shù)據(jù)庫(kù)
            for k in range(1, rows_numn):
                row_vlaue = now_table.row_values(k)
                print(row_vlaue)
                print(','.join(attrs))
                # 處理要插入的數(shù)據(jù)，把非字符串的數(shù)據(jù)轉(zhuǎn)換成字符串類(lèi)型，同事將字符串變成 sql語(yǔ)句需要的類(lèi)型
                for a in range(0, len(row_vlaue)):
                    ctype = now_table.cell(k, a).ctype
                    print('ctype', ctype)
                    #ctype： 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
                    if ctype ==2 and  row_vlaue[a] % 1 ==0 :
                        tmp = int(row_vlaue[a])
                        row_vlaue[a] = str(tmp)
                    if ctype == 3 :
                        d = datetime(*xldate_as_tuple(row_vlaue[a],0))
                        row_vlaue[a] = d.strftime('%Y-%m-%d')
                    c = row_vlaue[a]
                    row_vlaue[a] = "'" + c + "'"
                print(','.join(row_vlaue))
                sql = "INSERT INTO %s(%s) VALUES(%s)" % (table_name, ','.join(attrs), ','.join(row_vlaue))
                print(sql)
                cur.execute(sql)
                conn.commit()
        else:
            cur.execute("CREATE TABLE " + table_name + "();")
            conn.commit()
            # 為sheet進(jìn)行建表，
            cur.execute("ALTER TABLE %s ADD COLUMN  id SERIAL primary key  ;" % table_name)
            conn.commit()
            #        cur.execute("CREATE SEQUENCE users_id_seq  START WITH 1  INCREMENT BY 1  NO MINVALUE  NO MAXVALUE  CACHE 1;" )
            #       conn.commit()
            cur.execute("alter table  %s alter column id set default nextval('users_id_seq'); " % table_name)
            conn.commit()
            for j in range(0, cols_num):
                cur.execute("ALTER TABLE %s ADD COLUMN %s VARCHAR(200);" % (table_name, attrs[j]))
                conn.commit()
            # 將當(dāng)前的sheet插入到數(shù)據(jù)庫(kù)
            for k in range(1, rows_numn):
                row_vlaue = now_table.row_values(k)
                print(row_vlaue)
                print(','.join(attrs))
                # 處理要插入的數(shù)據(jù)，把非字符串的數(shù)據(jù)轉(zhuǎn)換成字符串類(lèi)型，同事將字符串變成 sql語(yǔ)句需要的類(lèi)型
                for a in range(0, len(row_vlaue)):
                    ctype = now_table.cell(k, a).ctype
                    print('ctype', ctype)
                    # ctype： 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error
                    if ctype == 2 and row_vlaue[a] % 1 == 0:
                        tmp = int(row_vlaue[a])
                        row_vlaue[a] = str(tmp)
                    if ctype == 3:
                        d = datetime(*xldate_as_tuple(row_vlaue[a], 0))
                        row_vlaue[a] = d.strftime('%Y-%m-%d')
                    c = row_vlaue[a]
                    row_vlaue[a] = "'" + c + "'"
                print(','.join(row_vlaue))
                sql = "INSERT INTO %s(%s) VALUES(%s)" % (table_name, ','.join(attrs), ','.join(row_vlaue))
                print(sql)
                cur.execute(sql)
                conn.commit()
    conn.close()

二   數(shù)據(jù)庫(kù)信息導(dǎo)出到excel表格,生成 .xls格式的表格（.xlsx格式打不開(kāi)）

文件1 dbToExcel.py  

注意：修改對(duì)應(yīng)數(shù)據(jù)庫(kù)的連接方式，我這里是postgresql，返回結(jié)果"ok"代表成功

import xlwt
import psycopg2
import os
import datetime
def tableExportToXlsx(sql):#sql 為數(shù)據(jù)庫(kù)查詢(xún)語(yǔ)句，將會(huì)把查詢(xún)的數(shù)據(jù)導(dǎo)出
    table_name = "acts"
    conn = psycopg2.connect(database='test',user='postgres',password='root',host='localhost')
    cur = conn.cursor()
    cur.execute(sql)
    #重置游標(biāo)位置
    cur.scroll(0,mode='absolute')
    #搜取所有的結(jié)果
    results = cur.fetchall()
    #獲取屬性名
    attrs = cur.description
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet(table_name,cell_overwrite_ok=True)
    #寫(xiě)入表格的屬性值
    for i in range(0,len(attrs)):
        sheet.write(0,i,attrs[i][0])
        print('表格屬性：',attrs[i][0])
    #將數(shù)據(jù)庫(kù)的數(shù)據(jù)導(dǎo)入表格
    row = 1
    col = 0
    for row in range(1,len(results)+1):
        print('寫(xiě)',row,'行數(shù)據(jù)')
        for col in range(0,len(attrs)):
            sheet.write(row,col,results[row-1][col])
            print(results[row-1][col])
    nowpath = os.path.dirname(__file__)
    print("現(xiàn)在的目錄是" + nowpath)
    act_path = os.path.dirname(nowpath)
    app_path = os.path.dirname(act_path)
    file_path = app_path + '\\xlsx_tmp'
    export_time = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
    file_name = 'act-{0}.xls'.format(export_time)
    print('文件路徑為' +os.path.join(file_path,file_name))
    workbook.save(os.path.join(file_path,file_name))
    if os.path.isfile(os.path.join(file_path,file_name)):
        print('數(shù)據(jù)庫(kù)中成功導(dǎo)出數(shù)據(jù)')
        return {'path':file_path,'name':file_name}
    else:
        print('數(shù)據(jù)庫(kù)導(dǎo)出錯(cuò)誤')
        return  'error'