PYQT+ADO

#!/usr/bin/python
# coding=gbk
import sys, os, datetime,glob,csv
from ado_o import Ui_MainWindow
from PyQt5.QtWidgets import QApplication, QMainWindow,QTableWidget,QTableWidgetItem,QHeaderView
import   matplotlib.pyplot as plt
from pylab import *


#from sqlalchemy import create_engine




# import tab
# from PyQt5 import QtCore, QtGui, QtWidgets
from PyQt5.QtWidgets import QApplication, QMainWindow
from PyQt5 import QtWidgets, QtCore, QtGui


# from PyQt5 import QtCore, QtGui, QtWidgets
class win(QMainWindow, Ui_MainWindow):  ##QMainWindow要导入,Ui_MainWindow对应jk.ui的类名

    def __init__(self):

        super(win, self).__init__()

        self.setupUi(self)

        self.pushButton.clicked.connect(self.on_click_1)
        self.pushButton_2.clicked.connect(self.on_click_2)

        import configparser, os

        curpath = os.path.realpath("sql_config.ini")
        conf = configparser.ConfigParser()
        # conf=configparser.configparser()
        conf.read(curpath,encoding="GBK")
        self.textEdit.setText((conf.get("sql_c", "sql_y")))

        # ip_list = conf.get("ftp", "ip")
        #conf.set("sql_c", "sql_y", self.textEdit.toPlainText())


        #conf.write(open('sql_config.ini', "r+"))



    def on_click_1(self):
        import win32com.client, os
        #import configparser

        #curpath = os.path.realpath("sql_config.ini")
        #conf = configparser.ConfigParser()
        # conf=configparser.configparser()
        #conf.read(curpath, encoding="GBK")
        #self.textEdit.setText((conf.get("sql_c", "sql_y")))

        # ip_list = conf.get("ftp", "ip")
        #conf.set("sql_c", "sql_y", chr(34)+self.textEdit.toPlainText()+chr(34))


        #conf.write(open('sql_config.ini', "r+",encoding='GBK'))

        # conf.write(open('sql_config.ini', "r+"))
        cnn = win32com.client.Dispatch(r'ADODB.connection')
        rs = win32com.client.Dispatch(r'ADODB.Recordset')


        cnn.Open("Provider = Microsoft.ace.Oledb.12.0;Extended Properties =TEXT;Data Source =" + os.getcwd() + "\")
        # print("Provider = Microsoft.ace.Oledb.12.0;Extended Properties =TEXT;Data Source ="+os.getcwd()+"\")
        #print(self.textEdit.toPlainText())
        sql =str(self.textEdit.toPlainText())
        print(sql)
        # s = cnn.Execute(sql)
        rs.Open(sql, cnn, 1, 3)

        # print(rs.Fields.count)
        # print(rs.RecordCount)
        if rs.RecordCount == 0:
            sys.exit()
        else:
            with open('./提取结果.csv', 'w', encoding='GBK')   as out_f:

                for i in range(0, rs.Fields.count):
                    out_f.write(rs.Fields(i).Name + chr(44))
                out_f.write('n')
                out_f.closed

            with open('./提取结果.csv', 'a',encoding='GBK')  as out_f:
                rs.MoveFirst()
                num = 1
                while not rs.EOF:
                    for j in range(0, rs.Fields.count):
                        out_f.write(str(rs.Fields.Item(j).Value) + chr(44))
                    out_f.write('n')
                    rs.MoveNext()
            # cnn.Close()
            # rs.Close()

            print("csv_sql exetue is done!")
    def on_click_2(self):
        import win32com.client, os
        import tkinter as tk
        from tkinter import filedialog
        #import configparser
        #curpath = os.path.realpath("sql_config.ini")
        #conf = configparser.ConfigParser()
        # conf=configparser.configparser()
        #conf.read(curpath, encoding="GBK")
        #self.textEdit.setText((conf.get("sql_c", "sql_y")))

        # ip_list = conf.get("ftp", "ip")
        #conf.set("sql_c", "sql_y", chr(34)+self.textEdit.toPlainText()+chr(34))

        #conf.write(open('sql_config.ini', "r+",encoding='GBK'))
        root = tk.Tk()
        root.withdraw()
        # find_file = filedialog.askdirectory(title="请选择要查找的文件夹")  # 获得选择好的文件夹
        Files_r = filedialog.askopenfilename(title="请选择要筛选的EXCEL表", filetypes=[('EXCEL文件', '*.xlsx')])  # 获得选择好的文件
        if not Files_r:
            print("没有选EXCEL表！")
        else:
            cnn = win32com.client.Dispatch(r'ADODB.connection')
            rs = win32com.client.Dispatch(r'ADODB.Recordset')

            # cnn.Open ("Provider = Microsoft.ace.Oledb.12.0;Extended Properties =TEXT;Data Source ="+os.getcwd()+"\")
            cnn.Open("provider=microsoft.ace.oledb.12.0;Extended Properties =excel 12.0;data source=" + Files_r)
            # print("Provider = Microsoft.ace.Oledb.12.0;Extended Properties =TEXT;Data Source ="+os.getcwd()+"\")
            sql=(self.textEdit.toPlainText())
            #sql = "select * from [EUtranRelationTDD$]"
            # s = cnn.Execute(sql)
            rs.Open(sql, cnn, 1, 3)

            # print(rs.Fields.count)
            # print(rs.RecordCount)
            if rs.RecordCount == 0:
                sys.exit()
            else:
                with open('./提取结果.csv', 'w', encoding='GBK')   as out_f:

                    for i in range(0, rs.Fields.count):
                        out_f.write(rs.Fields(i).Name + chr(44))
                    out_f.write('n')
                    out_f.closed

                with open('./提取结果.csv', 'a',encoding='GBK')  as out_f:
                    rs.MoveFirst()
                    num = 1
                    while not rs.EOF:
                        for j in range(0, rs.Fields.count):
                            out_f.write(chr(34) + str(rs.Fields.Item(j).Value) + chr(34) + chr(44))
                        out_f.write('n')
                        rs.MoveNext()
                # cnn.Close()
                # rs.Close()

                print("EXCEL_sql exetue is done!")




if __name__ == '__main__':
    #QCoreApplication.setAttribute(Qt.AA_EnableHighDpiScaling)
    app = QApplication(sys.argv)
    # MainWindow = QMainWindow()
    w = win()
    w.show()
    # ui = Ui_MainWindow()
    # ui.setupUi(MainWindow)
    # MainWindow.show()
    sys.exit(app.exec_())