#!/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操作表格
利用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()
View Code
#!/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)
A1
#!/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)
像数据库中存入A2字段
#!/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)
C1
#!/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)
C2
#!/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)
C3
#!/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)
D1,D2,D3,D4,E1,E2,E3
#!/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)
View Code
#!/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)
View Code
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
