Python操作表格

#!/usr/bin/env python3
#-*- coding:utf-8 -*-
'''
Administrator 
2018/10/23 
'''
from win32com.client import Dispatch
import win32com.client

class easyExcel:


    def __init__(self, filename=None):  # 打开文件或者新建文件（如果不存在的话）
        self.xlApp = win32com.client.Dispatch('Excel.Application')
        if filename:
            self.filename = filename
            self.xlBook = self.xlApp.Workbooks.Open(filename)
        else:
            self.xlBook = self.xlApp.Workbooks.Add()
            self.filename = ''

    def save(self, newfilename=None):  # 保存文件
        if newfilename:
            self.filename = newfilename
            self.xlBook.SaveAs(newfilename)
        else:
            self.xlBook.Save()

    def close(self):  # 关闭文件
        self.xlBook.Close(SaveChanges=0)
        del self.xlApp

    def getCell(self, sheet, row, col):  # 获取单元格的数据
        "Get value of one cell"
        sht = self.xlBook.Worksheets(sheet)
        return sht.Cells(row, col).Value

    def setCell(self, sheet, row, col, value):  # 设置单元格的数据
        "set value of one cell"
        sht = self.xlBook.Worksheets(sheet)
        sht.Cells(row, col).Value = value

    def setCellformat(self, sheet, row, col):  # 设置单元格的数据
        "set value of one cell"
        sht = self.xlBook.Worksheets(sheet)
        sht.Cells(row, col).Font.Size = 15  # 字体大小
        sht.Cells(row, col).Font.Bold = True  # 是否黑体
        sht.Cells(row, col).Name = "Arial"  # 字体类型
        sht.Cells(row, col).Interior.ColorIndex = 3  # 表格背景
        # sht.Range("A1").Borders.LineStyle = xlDouble
        sht.Cells(row, col).BorderAround(1, 4)  # 表格边框
        sht.Rows(3).RowHeight = 30  # 行高
        sht.Cells(row, col).HorizontalAlignment = -4131  # 水平居中xlCenter
        sht.Cells(row, col).VerticalAlignment = -4160  #

    def deleteRow(self, sheet, row):
        sht = self.xlBook.Worksheets(sheet)
        sht.Rows(row).Delete()  # 删除行
        sht.Columns(row).Delete()  # 删除列

    def getRange(self, sheet, row1, col1, row2, col2):  # 获得一块区域的数据，返回为一个二维元组
        "return a 2d array (i.e. tuple of tuples)"
        sht = self.xlBook.Worksheets(sheet)
        return sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Value

    def addPicture(self, sheet, pictureName, Left, Top, Width, Height):  # 插入图片
        "Insert a picture in sheet"
        sht = self.xlBook.Worksheets(sheet)
        sht.Shapes.AddPicture(pictureName, 1, 1, Left, Top, Width, Height)

    def cpSheet(self, before):  # 复制工作表
        "copy sheet"
        shts = self.xlBook.Worksheets
        shts(1).Copy(None, shts(1))

    def inserRow(self, sheet, row):
        sht = self.xlBook.Worksheets(sheet)
        sht.Rows(row).Insert(1)

    # 下面是一些测试代码。


if __name__ == "__main__":
    # PNFILE = r'c:/screenshot.bmp'
    xls = easyExcel(r'd:\jason.li\Desktop\empty_book.xlsx')
    # xls.addPicture('Sheet1', PNFILE, 20,20,1000,1000)
    # xls.cpSheet('Sheet1')
    xls.setCell('sheet1', 2, 'A', 88)
    row = 1
    col = 1
    print("*******beginsetCellformat********")
    # while(row<5):
    #   while(col<5):
    #       xls.setCellformat('sheet1',row,col)
    #       col += 1
    #       print("row=%s,col=%s" %(row,col))
    #   row += 1
    #   col=1
    #   print("*******row********")
    # print("*******endsetCellformat********")
    # print("*******deleteRow********")
    # xls.deleteRow('sheet1',5)
    xls.inserRow('sheet1', 7)
    xls.save()
    xls.close()

 

 

利用Python调用个人电脑上的excel表格处理文件10/23

 

#!/usr/bin/env python3
#-*- coding:utf-8 -*-
'''
Administrator 
2018/10/23 
'''
from win32com.client import Dispatch
import win32com.client
import uuid,os
class autoExcel:
    def __init__(self):
        self.ExcelApp = win32com.client.Dispatch('Excel.Application')
        self.filename=""
        self.newfilename=""

        # self.ExcelApp.Visible=True
    def get_file(self,filename=None):
        if filename:
            self.filename = filename
            self.excel = self.ExcelApp.Workbooks.Open(self.filename)

    def get_cell_value(self,sheet,row=0,col=0):#获取单元格的数据
        return self.excel.Worksheets(sheet).Cells(row,col).Value
    def set_cell_value(self,sheet,row=0,col=0,value=None):#重置单元格中的数据
        self.excel.Worksheets(sheet).Cells(row,col).Value=value
        return None
    #获取sheet数量
    def get_sheets(self):
        return self.excel.Worksheets.Count
    #获取表格中的行数
    def get_rows_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Rows.Count

    # 获取表格中的列数
    def get_columns_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Columns.Count
    def uid(self):
        return str(uuid.uuid1())
    def save(self,newfilename=None):
        if newfilename:
            self.filename=newfilename
            self.excel.SaveAs(newfilename)
        else:
            self.excel.Save()
    def close(self):
        self.excel.Close(SaveChanges=0)
    def close_app(self):
        del self.ExcelApp

    def __str__(self):
        return self.filename




if __name__ == "__main__":
    path=r"C:\Users\Administrator\Desktop\23家上报\23家上报"
    new_path=r"C:\Users\Administrator\Desktop\23家上报\new_23"
    file_list=os.listdir(path)
    try:
        app = autoExcel()
        for file in file_list:
            to_path=os.path.join(path,file)
        # new_path=r"C:\Users\Administrator\Desktop\23家上报\new_23\2018-08-10光利设计单位基本情况调研表汇总（开封）.xls"
            app.get_file(to_path)
            # a=app.get_rows_num(1)# 整数 表1 第3行，第1列
            # a1=app.get_columns_num(1)
            # b1=app.get_rows_num(1)
            # a2=app.get_columns_num(2)
            b2=app.get_rows_num(2)
            # if a1==6 and b1==157 and a2==19:
            print(app)
            # print("2行数",b2)
            # print("2列数",a2)
            # print("1行数", b1)
            # print("1列数",a1 )
            for c_row in [6,7,8,15,18]:
                for c_col in range(5,b2+1):
                    c_value=str(app.get_cell_value(2,c_col,c_row))
                    if c_value == "None":
                        app.set_cell_value(2, c_col, c_row, None)
                    else:
                        app.set_cell_value(2, c_col, c_row, c_value[:4])
            app.save()
            app.close()
    except Exception as e:
        print(e)
    finally:
        app.close_app()

    # path=r"C:\Users\Administrator\Desktop\23家上报\23家上报\商丘设计院2018-08-30基本情况调研表汇总.xls"
    #
    # app=autoExcel()
    # app.get_file(path)
    # a2 = app.get_rows_num(2)
    # for c_row in [6,7,8,15,18]:
    #     for c_col in range(5,a2+1):
    #         c_value=str(app.get_cell_value(2,c_col,c_row))
    #         print(c_col,c_row,"前",c_value)
            # if c_value == "None":
            #     app.set_cell_value(2, c_col, c_row,None)
            # else:
            #     app.set_cell_value(2, c_col, c_row, c_value[:4])
            # c_value = str(app.get_cell_value(2, c_col, c_row))
            # print(c_col,c_row,"后", c_value)
    # app.save()



    # item=str(app.get_cell_value(2,6,8))

    # print(type(item),item)
    # print(item[:4])
    # app.set_cell_value(2,6,6,item[:4])

    # item = app.get_cell_value(2, 5, 6)
    #
    # print(type(item), item)
    # print()
    # app.save()

    # msg=input("是否需要关闭文件>>:Y|y").strip().lower()
    # if msg=="y":
    #     app.close()

 

#!/usr/bin/env python3
#-*- coding:utf-8 -*-
'''
Administrator 
2018/10/23 
'''
from win32com.client import Dispatch
import win32com.client
import uuid,os
class autoExcel:
    def __init__(self):
        self.ExcelApp = win32com.client.Dispatch('Excel.Application')
        self.filename=""
        self.newfilename=""

        # self.ExcelApp.Visible=True
    def get_file(self,filename=None):
        if filename:
            self.filename = filename
            self.excel = self.ExcelApp.Workbooks.Open(self.filename)

    def get_cell_value(self,sheet,row=0,col=0):#获取单元格的数据
        return self.excel.Worksheets(sheet).Cells(row,col).Value
    def set_cell_value(self,sheet,row=0,col=0,value=None):#重置单元格中的数据
        self.excel.Worksheets(sheet).Cells(row,col).Value=value
        return None
    #获取sheet数量
    def get_sheets(self):
        return self.excel.Worksheets.Count
    #获取表格中的行数
    def get_rows_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Rows.Count

    # 获取表格中的列数
    def get_columns_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Columns.Count
    def uid(self):
        return str(uuid.uuid1())
    def save(self,newfilename=None):
        if newfilename:
            self.filename=newfilename
            self.excel.SaveAs(newfilename)
        else:
            self.excel.Save()
    def close(self):
        self.excel.Close(SaveChanges=0)
    def close_app(self):
        del self.ExcelApp

    def __str__(self):
        return self.filename




if __name__ == "__main__":
    path=r"C:\Users\Administrator\Desktop\23家上报\23家上报"
    new_path=r"C:\Users\Administrator\Desktop\23家上报\new_23"
    file_list=os.listdir(path)
    try:
        app = autoExcel()
        for file in file_list:
            to_path=os.path.join(path,file)
            app.get_file(to_path)
            # if a1==6 and b1==157 and a2==19:
            print(app)
            a1=str(app.get_cell_value(1,9,5))
            a1=a1.rstrip("万元")
            app.set_cell_value(1, 9, 5, a1)
            a2018=str(app.get_cell_value(1,72,5))
            a2018=a2018.rstrip("万元")
            app.set_cell_value(1, 72, 5, a2018)
            a2019=str(app.get_cell_value(1,73,5))
            a2019=a2019.rstrip("万元")
            app.set_cell_value(1, 73, 5, a2019)
            a2020=str(app.get_cell_value(1,74,5))
            a2020=a2020.rstrip("万元")
            app.set_cell_value(1, 74, 5, a2020)
            nub=[]
            for i in range(36,71,2):
                aaa=str(app.get_cell_value(1, i, 5)).rstrip("万元")
                app.set_cell_value(1, i, 5, aaa)

                        #app.set_cell_value(2, c_col, c_row, c_value[:4])
            app.save()

            print(a1,a2018,a2019,a2020,nub)
            app.close()
    except Exception as e:
        print(e)
    finally:
        app.close_app()

 

---

---

---

---

 

#!/usr/bin/env python3
#-*- coding:utf-8 -*-
'''
Administrator 
2018/10/24 
'''
import pymysql
from win32com.client import Dispatch
import win32com.client
import uuid,os
class autoExcel:
    def __init__(self):
        self.ExcelApp = win32com.client.Dispatch('Excel.Application')
        self.filename=""
        self.newfilename=""

        # self.ExcelApp.Visible=True
    def get_file(self,filename=None):
        if filename:
            self.filename = filename
            self.excel = self.ExcelApp.Workbooks.Open(self.filename)

    def get_cell_value(self,sheet,row=0,col=0):#获取单元格的数据
        return self.excel.Worksheets(sheet).Cells(row,col).Value
    def set_cell_value(self,sheet,row=0,col=0,value=None):#重置单元格中的数据
        self.excel.Worksheets(sheet).Cells(row,col).Value=value
        return None
    #获取sheet数量
    def get_sheets(self):
        return self.excel.Worksheets.Count
    #获取表格中的行数
    def get_rows_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Rows.Count

    # 获取表格中的列数
    def get_columns_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Columns.Count
    def uid(self):
        return str(uuid.uuid1())
    def save(self,newfilename=None):
        if newfilename:
            self.filename=newfilename
            self.excel.SaveAs(newfilename)
        else:
            self.excel.Save()
    def close(self):
        self.excel.Close(SaveChanges=0)
    def close_app(self):
        del self.ExcelApp

    def __str__(self):
        return self.filename

def savedb(data_list):
    try:
        db=pymysql.connect(rset="utf8")
        cursor=db.cursor()
        # SQL="insert into smalldata(name);"
        # db_rows=cursor.execute(SQL)
        db_rows =cursor.executemany("insert into smalldata(name)values(%s)", data_list)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统")
    finally:
        cursor.close()
        db.close()
def updatedb(numlist):
    try:
        db=pymysql.connect(arset="utf8")
        cursor=db.cursor()
        #SQL="update smalldata set A1=50 WHERE name='开封光利电力设计有限公司'"
        # SQL = 'update smalldata set A1=%s WHERE name=%s' % (numlist[1], numlist[0])
        # print(SQL)
        db_rows=cursor.execute('update smalldata set A1=60.0177849745583 ')
        #db_rows =cursor.executemany('update smalldata set A2=%s WHERE name=%s',numlist)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统",e)
    finally:
        cursor.close()
        db.close()
if __name__=="__main__":
    app = autoExcel()
    path=r"C:\Users\admin\Desktop\23家上报\23家上报"
    file_list = os.listdir(path)
    try:
        app = autoExcel()
        list_data=[]
        sum_list=[]
        people_list=[]
        for file in file_list:
            to_path=os.path.join(path,file)
            app.get_file(to_path)

            name=app.get_cell_value(1,3,5)
            number = app.get_cell_value(1, 18, 5)
            a21=app.get_cell_value(1,60,5)

            a22=app.get_cell_value(1,62,5)

            a23=app.get_cell_value(1,64,5)

            a24=app.get_cell_value(1,66,5)

            a25 = app.get_cell_value(1, 68, 5)

            a26 = app.get_cell_value(1, 70, 5)


            sub_list=[a21,a22,a23,a24,a25,a26]
            sum=0
            for i in sub_list:
                if i is None or i == "None":
                    continue
                else:
                    sum+=float(i)
            list_data.append((sum,number,name))
            #print(a2)
            app.close()
            sum_list.append(sum)
            people_list.append(number)
        print(sum_list)
        new_sum=0
        new_people=0
        for j in sum_list:
            new_sum+=j
        print(people_list)
        for k in people_list:
            new_people+=int(k)
        print(new_sum,new_people)

        result=new_sum/new_people
        print(result)
    except Exception as e:
        print(e)
    finally:
        app.close_app()
    # savedb(list_data)
    print(list_data)
    updatedb(result)

 

#!/usr/bin/env python3
#-*- coding:utf-8 -*-
'''
Administrator 
2018/10/24 
'''
import pymysql
from win32com.client import Dispatch
import win32com.client
import uuid,os
class autoExcel:
    def __init__(self):
        self.ExcelApp = win32com.client.Dispatch('Excel.Application')
        self.filename=""
        self.newfilename=""

        # self.ExcelApp.Visible=True
    def get_file(self,filename=None):
        if filename:
            self.filename = filename
            self.excel = self.ExcelApp.Workbooks.Open(self.filename)

    def get_cell_value(self,sheet,row=0,col=0):#获取单元格的数据
        return self.excel.Worksheets(sheet).Cells(row,col).Value
    def set_cell_value(self,sheet,row=0,col=0,value=None):#重置单元格中的数据
        self.excel.Worksheets(sheet).Cells(row,col).Value=value
        return None
    #获取sheet数量
    def get_sheets(self):
        return self.excel.Worksheets.Count
    #获取表格中的行数
    def get_rows_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Rows.Count

    # 获取表格中的列数
    def get_columns_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Columns.Count
    def uid(self):
        return str(uuid.uuid1())
    def save(self,newfilename=None):
        if newfilename:
            self.filename=newfilename
            self.excel.SaveAs(newfilename)
        else:
            self.excel.Save()
    def close(self):
        self.excel.Close(SaveChanges=0)
    def close_app(self):
        del self.ExcelApp

    def __str__(self):
        return self.filename

def savedb(data_list):
    try:
        db=pymysql.connect(.......,charset="utf8")
        cursor=db.cursor()
        # SQL="insert into smalldata(name);"
        # db_rows=cursor.execute(SQL)
        db_rows =cursor.executemany("insert into smalldata(name)values(%s)", data_list)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统")
    finally:
        cursor.close()
        db.close()
def updatedb(numlist):
    try:
        db=pymysql.connect(ht="utf8")
        cursor=db.cursor()
        #SQL="update smalldata set A1=50 WHERE name='开封光利电力设计有限公司'"
        # SQL = 'update smalldata set A1=%s WHERE name=%s' % (numlist[1], numlist[0])
        # print(SQL)
        # db_rows=cursor.execute(SQL)
        db_rows =cursor.executemany('update smalldata set A2=%s WHERE name=%s',numlist)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统",e)
    finally:
        cursor.close()
        db.close()
if __name__=="__main__":
    app = autoExcel()
    path=r"C:\Users\admin\Desktop\23家上报\23家上报"
    file_list = os.listdir(path)
    try:
        app = autoExcel()
        list_data=[]
        for file in file_list:
            to_path=os.path.join(path,file)
            app.get_file(to_path)

            name=app.get_cell_value(1,3,5)
            number = app.get_cell_value(1, 18, 5)
            a21=app.get_cell_value(1,60,5)

            a22=app.get_cell_value(1,62,5)

            a23=app.get_cell_value(1,64,5)

            a24=app.get_cell_value(1,66,5)

            a25 = app.get_cell_value(1, 68, 5)

            a26 = app.get_cell_value(1, 70, 5)


            sub_list=[a21,a22,a23,a24,a25,a26]
            sum=0
            for i in sub_list:
                if i is None or i == "None":
                    continue
                else:
                    sum+=float(i)
            # list_data.append((name, sum, number))
            a2=sum/float(number)
            list_data.append((a2,name))
            #print(a2)
            app.close()

    except Exception as e:
        print(e)
    finally:
        app.close_app()
    # savedb(list_data)
    print(list_data)
    updatedb(list_data)

 

#!/usr/bin/env python3
#-*- coding:utf-8 -*-
'''
Administrator 
2018/10/24 
'''
import pymysql
from win32com.client import Dispatch
import win32com.client
import uuid,os
class autoExcel:
    def __init__(self):
        self.ExcelApp = win32com.client.Dispatch('Excel.Application')
        self.filename=""
        self.newfilename=""

        # self.ExcelApp.Visible=True
    def get_file(self,filename=None):
        if filename:
            self.filename = filename
            self.excel = self.ExcelApp.Workbooks.Open(self.filename)

    def get_cell_value(self,sheet,row=0,col=0):#获取单元格的数据
        return self.excel.Worksheets(sheet).Cells(row,col).Value
    def set_cell_value(self,sheet,row=0,col=0,value=None):#重置单元格中的数据
        self.excel.Worksheets(sheet).Cells(row,col).Value=value
        return None
    #获取sheet数量
    def get_sheets(self):
        return self.excel.Worksheets.Count
    #获取表格中的行数
    def get_rows_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Rows.Count

    # 获取表格中的列数
    def get_columns_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Columns.Count
    def uid(self):
        return str(uuid.uuid1())
    def save(self,newfilename=None):
        if newfilename:
            self.filename=newfilename
            self.excel.SaveAs(newfilename)
        else:
            self.excel.Save()
    def close(self):
        self.excel.Close(SaveChanges=0)
    def close_app(self):
        del self.ExcelApp

    def __str__(self):
        return self.filename

def savedb(data_list):
    try:
        db=pymysql.connect(ho8")
        cursor=db.cursor()
        # SQL="insert into smalldata(name);"
        # db_rows=cursor.execute(SQL)
        db_rows =cursor.executemany("insert into smalldata(name)values(%s)", data_list)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统")
    finally:
        cursor.close()
        db.close()
def updatedb(numlist):
    try:
        db=pymysql.connect(ho="utf8")
        cursor=db.cursor()
        #SQL="update smalldata set A1=50 WHERE name='开封光利电力设计有限公司'"
        # SQL = 'update smalldata set A1=%s WHERE name=%s' % (numlist[1], numlist[0])
        # print(SQL)
        #db_rows=cursor.execute('update smalldata set C1=%s',numlist)
        db_rows =cursor.executemany('update smalldata set C1=%s WHERE name=%s',numlist)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统",e)
    finally:
        cursor.close()
        db.close()
if __name__=="__main__":
    app = autoExcel()
    path=r"C:\Users\admin\Desktop\23家上报\23家上报"
    file_list = os.listdir(path)
    try:
        app = autoExcel()
        list_data=[]
        sum=0
        for file in file_list:
            to_path=os.path.join(path,file)
            app.get_file(to_path)

            name=app.get_cell_value(1,3,5)
            c11=app.get_cell_value(1,18,5)
            c22 = app.get_cell_value(1, 19, 5)
            c1=float(c11)/float(c22)
            list_data.append((c1,name))



            app.close()




    except Exception as e:
        print(e)
    finally:
        app.close_app()
    print(list_data)
    updatedb(list_data)

 

#!/usr/bin/env python3
#-*- coding:utf-8 -*-
'''
Administrator 
2018/10/24 
'''
import pymysql
from win32com.client import Dispatch
import win32com.client
import uuid,os
class autoExcel:
    def __init__(self):
        self.ExcelApp = win32com.client.Dispatch('Excel.Application')
        self.filename=""
        self.newfilename=""

        # self.ExcelApp.Visible=True
    def get_file(self,filename=None):
        if filename:
            self.filename = filename
            self.excel = self.ExcelApp.Workbooks.Open(self.filename)

    def get_cell_value(self,sheet,row=0,col=0):#获取单元格的数据
        return self.excel.Worksheets(sheet).Cells(row,col).Value
    def set_cell_value(self,sheet,row=0,col=0,value=None):#重置单元格中的数据
        self.excel.Worksheets(sheet).Cells(row,col).Value=value
        return None
    #获取sheet数量
    def get_sheets(self):
        return self.excel.Worksheets.Count
    #获取表格中的行数
    def get_rows_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Rows.Count

    # 获取表格中的列数
    def get_columns_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Columns.Count
    def uid(self):
        return str(uuid.uuid1())
    def save(self,newfilename=None):
        if newfilename:
            self.filename=newfilename
            self.excel.SaveAs(newfilename)
        else:
            self.excel.Save()
    def close(self):
        self.excel.Close(SaveChanges=0)
    def close_app(self):
        del self.ExcelApp

    def __str__(self):
        return self.filename

def savedb(data_list):
    try:
        db=pymysql.connect(ho8")
        cursor=db.cursor()
        # SQL="insert into smalldata(name);"
        # db_rows=cursor.execute(SQL)
        db_rows =cursor.executemany("insert into smalldata(name)values(%s)", data_list)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统")
    finally:
        cursor.close()
        db.close()
def updatedb(numlist):
    try:
        db=pymysql.connect(houtf8")
        cursor=db.cursor()
        #SQL="update smalldata set A1=50 WHERE name='开封光利电力设计有限公司'"
        # SQL = 'update smalldata set A1=%s WHERE name=%s' % (numlist[1], numlist[0])
        # print(SQL)
        db_rows=cursor.execute('update smalldata set C2=%s',numlist)
        #db_rows =cursor.executemany('update smalldata set C2=%s WHERE name=%s',numlist)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统",e)
    finally:
        cursor.close()
        db.close()
if __name__=="__main__":
    app = autoExcel()
    path=r"C:\Users\admin\Desktop\23家上报\23家上报"
    file_list = os.listdir(path)
    try:
        app = autoExcel()
        list_data=[]
        sum=0
        for file in file_list:
            to_path=os.path.join(path,file)
            app.get_file(to_path)

            name=app.get_cell_value(1,3,5)
            c2=app.get_cell_value(1,25,5)
            sum+=float(c2)
            list_data.append((name,c2))
            app.close()

        avg=sum/23

        print(avg)
    except Exception as e:
        print(e)
    finally:
        app.close_app()
    print(list_data)
    updatedb(avg)

 

#!/usr/bin/env python3
#-*- coding:utf-8 -*-
'''
Administrator 
2018/10/24 
'''
import pymysql
from win32com.client import Dispatch
import win32com.client
import uuid,os
class autoExcel:
    def __init__(self):
        self.ExcelApp = win32com.client.Dispatch('Excel.Application')
        self.filename=""
        self.newfilename=""

        # self.ExcelApp.Visible=True
    def get_file(self,filename=None):
        if filename:
            self.filename = filename
            self.excel = self.ExcelApp.Workbooks.Open(self.filename)

    def get_cell_value(self,sheet,row=0,col=0):#获取单元格的数据
        return self.excel.Worksheets(sheet).Cells(row,col).Value
    def set_cell_value(self,sheet,row=0,col=0,value=None):#重置单元格中的数据
        self.excel.Worksheets(sheet).Cells(row,col).Value=value
        return None
    #获取sheet数量
    def get_sheets(self):
        return self.excel.Worksheets.Count
    #获取表格中的行数
    def get_rows_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Rows.Count

    # 获取表格中的列数
    def get_columns_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Columns.Count
    def uid(self):
        return str(uuid.uuid1())
    def save(self,newfilename=None):
        if newfilename:
            self.filename=newfilename
            self.excel.SaveAs(newfilename)
        else:
            self.excel.Save()
    def close(self):
        self.excel.Close(SaveChanges=0)
    def close_app(self):
        del self.ExcelApp

    def __str__(self):
        return self.filename

def savedb(data_list):
    try:
        db=pymysql.connect(hotf8")
        cursor=db.cursor()
        # SQL="insert into smalldata(name);"
        # db_rows=cursor.execute(SQL)
        db_rows =cursor.executemany("insert into smalldata(name)values(%s)", data_list)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统")
    finally:
        cursor.close()
        db.close()
def updatedb(numlist):
    try:
        db=pymysql.connect(hosttf8")
        cursor=db.cursor()
        #SQL="update smalldata set A1=50 WHERE name='开封光利电力设计有限公司'"
        # SQL = 'update smalldata set A1=%s WHERE name=%s' % (numlist[1], numlist[0])
        # print(SQL)
        #db_rows=cursor.execute('update smalldata set C3=%s',numlist)
        db_rows =cursor.executemany('update smalldata set C3=%s WHERE name=%s',numlist)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统",e)
    finally:
        cursor.close()
        db.close()
if __name__=="__main__":
    app = autoExcel()
    path=r"C:\Users\admin\Desktop\23家上报\23家上报"
    file_list = os.listdir(path)
    try:
        app = autoExcel()
        list_data=[]
        sum=0
        for file in file_list:
            to_path=os.path.join(path,file)
            app.get_file(to_path)

            name=app.get_cell_value(1,3,5)
            c2=app.get_cell_value(1,25,5)
            c2=int(c2)
            list_data.append((c2,name))
            app.close()

        avg=sum/23

        print(avg)
    except Exception as e:
        print(e)
    finally:
        app.close_app()
    print(list_data)
    #updatedb(list_data)

 

#!/usr/bin/env python3
#-*- coding:utf-8 -*-
'''
Administrator 
2018/10/24 
'''
import pymysql
from win32com.client import Dispatch
import win32com.client
import uuid,os
class autoExcel:
    def __init__(self):
        self.ExcelApp = win32com.client.Dispatch('Excel.Application')
        self.filename=""
        self.newfilename=""

        # self.ExcelApp.Visible=True
    def get_file(self,filename=None):
        if filename:
            self.filename = filename
            self.excel = self.ExcelApp.Workbooks.Open(self.filename)

    def get_cell_value(self,sheet,row=0,col=0):#获取单元格的数据
        return self.excel.Worksheets(sheet).Cells(row,col).Value
    def set_cell_value(self,sheet,row=0,col=0,value=None):#重置单元格中的数据
        self.excel.Worksheets(sheet).Cells(row,col).Value=value
        return None
    #获取sheet数量
    def get_sheets(self):
        return self.excel.Worksheets.Count
    #获取表格中的行数
    def get_rows_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Rows.Count

    # 获取表格中的列数
    def get_columns_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Columns.Count
    def uid(self):
        return str(uuid.uuid1())
    def save(self,newfilename=None):
        if newfilename:
            self.filename=newfilename
            self.excel.SaveAs(newfilename)
        else:
            self.excel.Save()
    def close(self):
        self.excel.Close(SaveChanges=0)
    def close_app(self):
        del self.ExcelApp

    def __str__(self):
        return self.filename

def savedb(data_list):
    try:
        db=pymysql.connect(host8")
        cursor=db.cursor()
        # SQL="insert into smalldata(name);"
        # db_rows=cursor.execute(SQL)
        db_rows =cursor.executemany("insert into smalldata(name)values(%s)", data_list)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统")
    finally:
        cursor.close()
        db.close()
def updatedb(numlist):
    try:
        db=pymysql.connect(hostf8")
        cursor=db.cursor()
        #SQL="update smalldata set A1=50 WHERE name='开封光利电力设计有限公司'"
        # SQL = 'update smalldata set A1=%s WHERE name=%s' % (numlist[1], numlist[0])
        # print(SQL)
        #db_rows=cursor.execute('update smalldata set C3=%s',numlist)
        db_rows =cursor.executemany('update smalldata set D1=%s,D2=%s,D3=%s,D4=%s,E1=%s,E2=%s,E3=%s WHERE name=%s',numlist)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统",e)
    finally:
        cursor.close()
        db.close()
if __name__=="__main__":
    app = autoExcel()
    path=r"C:\Users\admin\Desktop\23家上报\23家上报"
    file_list = os.listdir(path)
    try:
        app = autoExcel()
        list_data=[]
        sum=0
        for file in file_list:
            to_path=os.path.join(path,file)
            app.get_file(to_path)
            name=app.get_cell_value(1,3,5)
            new_list=[]
            # for i in [76,79,88,91,100,118,121]:
            #     icell=app.get_cell_value(1,i,5)
            #     if icell is None or icell =="None":
            #         new_list.append([icell,1])
            #     else:
            #         new_list.append([icell,0])
            # list_data.append([name,new_list])
            d1=app.get_cell_value(1,76,5)
            if d1 is None or d1 =="None" or d1==0 :
                d11=1
            else:
                d11=0.95
            d2 = app.get_cell_value(1, 79, 5)
            if d2 is None or d2 =="None" or d2==0:
                d22=1
            else:
                d22=0.97
            d3 = app.get_cell_value(1, 88, 5)
            if d3 is None or d3 =="None" or d3==0:
                d33=1
            else:
                d33=0.96
            d4 = app.get_cell_value(1, 91, 5)
            if d4 is None or d4 =="None" or d4==0:
                d44=1
            else:
                d44=0.99
            e1= app.get_cell_value(1, 100, 5)
            if e1 is None or e1 =="None" or e1==0:
                e11=1
            else:
                e11=0.92
            e2 = app.get_cell_value(1, 118, 5)
            if e2 is None or e2 =="None" or e2==0:
                e22=1
            else:
                e22=0.9
            e3 = app.get_cell_value(1, 121, 5)
            if e3 is None or e3 =="None" or e3==0:
                e33=1
            else:
                e33=0.92
            # list_data.append((d1,d11,d2,d22,d3,d33,d4,d44,e1,e11,e2,e22,e3,e33,name))
            list_data.append((d11,d22,d33,d44,e11,e22,e33, name))






            app.close()



    except Exception as e:
        print(e)
    finally:
        app.close_app()
    for item in list_data:
        print(item)
    updatedb(list_data)

 

#!/usr/bin/env python3
#-*- coding:utf-8 -*-
'''
Administrator
2018/10/24
'''
import pymysql
from win32com.client import Dispatch
import win32com.client
import uuid,os
import matplotlib.pyplot as plt

def analyze(results):
    #条形图
    plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
    plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号
    # 有中文出现的情况，需要u'内容'
    print("I will generate histograms directly ！！")
    key = []
    value = []
    for i in results:
        key.append(i[0])
        # print(type(i[0]))
        value.append(float(i[1]))
    X=range(23)
    Y = value#数量

    plt.bar(X,Y,label=u"分数")
    plt.xticks(rotation=45)

    plt.xticks(X, key)
    for x, y in zip(X, Y):
        plt.text(x, y + 0.05, y, ha='center', va='bottom')
    plt.title(u'河南省电力设计单位承载力分析对比图')
    plt.xlabel(u'设计单位名称 ')
    plt.ylabel(u'分数')
    plt.show()

class autoExcel:
    def __init__(self):
        self.ExcelApp = win32com.client.Dispatch('Excel.Application')
        self.filename=""
        self.newfilename=""

        # self.ExcelApp.Visible=True
    def get_file(self,filename=None):
        if filename:
            self.filename = filename
            self.excel = self.ExcelApp.Workbooks.Open(self.filename)

    def get_cell_value(self,sheet,row=0,col=0):#获取单元格的数据
        return self.excel.Worksheets(sheet).Cells(row,col).Value
    def set_cell_value(self,sheet,row=0,col=0,value=None):#重置单元格中的数据
        self.excel.Worksheets(sheet).Cells(row,col).Value=value
        return None
    #获取sheet数量
    def get_sheets(self):
        return self.excel.Worksheets.Count
    #获取表格中的行数
    def get_rows_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Rows.Count

    # 获取表格中的列数
    def get_columns_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Columns.Count
    def uid(self):
        return str(uuid.uuid1())
    def save(self,newfilename=None):
        if newfilename:
            self.filename=newfilename
            self.excel.SaveAs(newfilename)
        else:
            self.excel.Save()
    def close(self):
        self.excel.Close(SaveChanges=0)
    def close_app(self):
        del self.ExcelApp

    def __str__(self):
        return self.filename

def savedb(data_list):
    try:
        db=pymysql.connect(htf8")
        cursor=db.cursor()
        # SQL="insert into smalldata(name);"
        # db_rows=cursor.execute(SQL)
        db_rows =cursor.executemany("insert into smalldata(name)values(%s)", data_list)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统")
    finally:
        cursor.close()
        db.close()
def updatedb(numlist):
    try:
        db=pymysql.connect(hoharset="utf8")
        cursor=db.cursor()
        #SQL="update smalldata set A1=50 WHERE name='开封光利电力设计有限公司'"
        # SQL = 'update smalldata set A1=%s WHERE name=%s' % (numlist[1], numlist[0])
        # print(SQL)
        db_rows=cursor.execute('update smalldata set B1=1,B2=1,B3=1,B4=1')
        #db_rows =cursor.executemany('update smalldata set D1=%s,D2=%s,D3=%s,D4=%s,E1=%s,E2=%s,E3=%s WHERE name=%s',numlist)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统",e)
    finally:
        cursor.close()
        db.close()

def getdb(SQL):
    try:
        db = pymysql.connect(hos="utf8")
        cursor = db.cursor()
        db_rows=cursor.execute(SQL)
        print("受影响的行数", db_rows)
        data=cursor.fetchall()
        return data
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统", e)
    finally:
        cursor.close()
        db.close()
if __name__=="__main__":
    data=getdb("select * from smalldata where C3>0;")
    list_class=[]
    for row in data:
        name=row[1]
        score=(row[3]/row[2])*row[8]*(row[9]/row[10])*row[11]*row[12]*row[13]*row[14]*row[15]*row[16]*row[17]
        score='%.3f' % score
        list_class.append([name,score])
    data0 = getdb("select * from smalldata where C3=0;")
    for row0 in data0:
        name = row0[1]
        score0 = (row0[3] / row0[2]) * row0[8]  * row0[11] * row0[12] * row0[13] * row0[14] * row0[15] * row0[16] * row0[17]
        score0= '%.3f' % score0
        list_class.append([name, score0])
    ky_count_sorted = sorted(list_class, key=lambda item: item[1], reverse=True)
    for num,item in enumerate(ky_count_sorted):
        print(num,item)
    print(list_class)

    analyze(ky_count_sorted)

 

#!/usr/bin/env python3
#-*- coding:utf-8 -*-
'''
Administrator
2018/10/24
'''
import pymysql
from win32com.client import Dispatch
import win32com.client
import uuid,os
import matplotlib.pyplot as plt

def analyze(results):
    #条形图
    plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
    plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号
    # 有中文出现的情况，需要u'内容'
    print("I will generate histograms directly ！！")
    key = []
    value = []
    for i in results:
        key.append(i[0])
        # print(type(i[0]))
        value.append(float(i[1]))
    X=range(23)
    Y = value#数量

    plt.bar(X,Y,label=u"分数",color="g")
    plt.xticks(rotation=45)

    plt.xticks(X, key)
    for x, y in zip(X, Y):
        plt.text(x, y + 0.05, y, ha='center', va='bottom')
    plt.title(u'河南省电力设计单位承载力分析对比图')
    plt.xlabel(u'设计单位名称 ')
    plt.ylabel(u'分数')
    plt.show()
def analyze2(results):
    #条形图
    plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
    plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号
    # 有中文出现的情况，需要u'内容'
    print("I will generate histograms directly ！！")
    key = []
    value = []
    value2=[]
    for i in results:
        key.append(i[0])
        # print(type(i[0]))
        value.append(i[1])
        value2.append(i[2])
    X=range(0,46,2)
    X2=range(1,47,2)
    Y = value#数量
    Y2=value2

    plt.plot(X,Y,label=u"分数",color="yellow")
    plt.bar(X, Y2, label=u"分数")
    plt.xticks(rotation=45)

    plt.xticks(X, key)
    for x, y in zip(X, Y2):
        plt.text(x, y + 0.05, y, ha='center', va='bottom')
    plt.title(u'设计单位人均产值和全省人均产值对比图')
    plt.xlabel(u'设计单位名称 ')
    plt.ylabel(u'人均产值')
    plt.show()
class autoExcel:
    def __init__(self):
        self.ExcelApp = win32com.client.Dispatch('Excel.Application')
        self.filename=""
        self.newfilename=""

        # self.ExcelApp.Visible=True
    def get_file(self,filename=None):
        if filename:
            self.filename = filename
            self.excel = self.ExcelApp.Workbooks.Open(self.filename)

    def get_cell_value(self,sheet,row=0,col=0):#获取单元格的数据
        return self.excel.Worksheets(sheet).Cells(row,col).Value
    def set_cell_value(self,sheet,row=0,col=0,value=None):#重置单元格中的数据
        self.excel.Worksheets(sheet).Cells(row,col).Value=value
        return None
    #获取sheet数量
    def get_sheets(self):
        return self.excel.Worksheets.Count
    #获取表格中的行数
    def get_rows_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Rows.Count

    # 获取表格中的列数
    def get_columns_num(self,sheet):
        return self.excel.Worksheets(sheet).UsedRange.Columns.Count
    def uid(self):
        return str(uuid.uuid1())
    def save(self,newfilename=None):
        if newfilename:
            self.filename=newfilename
            self.excel.SaveAs(newfilename)
        else:
            self.excel.Save()
    def close(self):
        self.excel.Close(SaveChanges=0)
    def close_app(self):
        del self.ExcelApp

    def __str__(self):
        return self.filename

def savedb(data_list):
    try:
        db=pymysql.connect(ho")
        cursor=db.cursor()
        # SQL="insert into smalldata(name);"
        # db_rows=cursor.execute(SQL)
        db_rows =cursor.executemany("insert into smalldata(name)values(%s)", data_list)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统")
    finally:
        cursor.close()
        db.close()
def updatedb(numlist):
    try:
        db=pymysql.connect(ho")
        cursor=db.cursor()
        #SQL="update smalldata set A1=50 WHERE name='开封光利电力设计有限公司'"
        # SQL = 'update smalldata set A1=%s WHERE name=%s' % (numlist[1], numlist[0])
        # print(SQL)
        db_rows=cursor.execute('update smalldata set B1=1,B2=1,B3=1,B4=1')
        #db_rows =cursor.executemany('update smalldata set D1=%s,D2=%s,D3=%s,D4=%s,E1=%s,E2=%s,E3=%s WHERE name=%s',numlist)
        print("受影响的行数",db_rows)
        db.commit()
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统",e)
    finally:
        cursor.close()
        db.close()

def getdb(SQL):
    try:
        db = pymysql.connect(hotf8")
        cursor = db.cursor()
        db_rows=cursor.execute(SQL)
        print("受影响的行数", db_rows)
        data=cursor.fetchall()
        return data
    except Exception as e:
        db.rollback()
        print("数据有误，无法存入系统", e)
    finally:
        cursor.close()
        db.close()
if __name__=="__main__":
    data=getdb("select name,A1,A2 from smalldata")
    list_class=[]
    for row in data:
        name = row[0]
        s_score=row[1]
        s_core='%.3f' % s_score
        p_score=row[2]
        p_score='%.3f' % p_score
        list_class.append([name,float(s_core),float(p_score)])

    print(list_class)
    ky_count_sorted = sorted(list_class, key=lambda item:item[2],reverse=True)
    # for num,item in enumerate(ky_count_sorted):
    #     print(num,item)
    print(ky_count_sorted)

    analyze2(ky_count_sorted)