# from win32com.client import Dispatch
# filePath = r'D:\Users\Administrator\Desktop\\输出文件\台湾森鸿头程直111112222发.xlsx'
# xlApp = Dispatch("Excel.Application")
# xlApp.Visible = False
# xlBook = xlApp.Workbooks.Open(filePath)
# xlBook.Save()
# xlBook.Close()
import xlwings
filePath = r'D:\Users\Administrator\Desktop\\输出文件\台湾森鸿头程直111112222发.xlsx'
app = xlwings.App(visible=False, add_book=False)
app.display_alerts = False
wb = app.books.open(filePath, update_links=False, read_only=True, data_only=True)
# wb.Save()
wb.close()
app.quit()
def deleteExcel(self, filePath):
writer = pd.ExcelWriter(filePath, engine='openpyxl') # 初始化写入对象
wb = load_workbook(filePath,data_only=True)
wb.save(filePath)
n, remainder = divmod(n - 1, 26)
string = chr(65 + remainder) + string
return string
if len(sys.argv) <= 1:
print("Please enter a command or file name!")
elif sys.argv[1] == "download":
# have option to download current active sheet or enter a name
pass
elif path.isfile(sys.argv[1]):
filename = sys.argv[1].split("/")[len(sys.argv[1].split("/")) - 1]
try:
app = xlw.apps[xlw.apps.keys()[0]]
except IndexError:
app = xlw.App()
spread = app.books.add()
#spread.name = filename
sheetMetadata = {} # dictionary: each key is a sheet name, each value is a dictionary of fields and their SQL definition lines to make sure it saves correctly
sqlfile = open(sys.argv[1], 'r')
line = sqlfile.readline()
while line:
if line[:12] == "CREATE TABLE":
sheetName = line[14:len(line)-4]
currSheet = spread.sheets.add(sheetName, after=spread.sheets[len(spread.sheets)-1])
sheetMetadata[sheetName] = {}
# 导入模块
import xlwings as xw
import datetime
# 日期格式编写
month = str(datetime.datetime.now().month)
day = str(datetime.datetime.now().day)
time_str = month + "月" + day + "日"
# 创建实例
app = xw.App(visible=True, add_book=False)
# 打开工作簿
workbook = app.books.add()
# 打开工作表
worksheet = workbook.sheets.add(time_str)
# 定义风格样式
def sheet_Style():
# 合并第一行单元格
fg1 = worksheet.range("A1:D1")
fg1.api.Merge()
fg1.value = "***超市营销记录"
fg1.api.Font.Size = 25
fg1.api.HorizontalAlignment = -4108
fg1.api.VerticalAlignment = -4108
# 设置第二行单元格
fg2 = worksheet.range("A2:D2")
fg2.value = ["类目", "单价", "数量", "总价"]
fg2.api.Font.Bold = True
fg2.api.HorizontalAlignment = -4108
def test_xlwings(self):
import xlwings as xw
app = xw.App(False, False)
app.visible = False
def setUpClass(cls):
cls.app1 = xw.App(visible=False, spec=SPEC)
cls.app2 = xw.App(visible=False, spec=SPEC)
usage: autofill(wbs1,"AA1:AA"+str(wbs1_rowcount))
"""
last_year = sheet.range(rangee)
last_value = "blank"
for cell in last_year:
cell_value = cell.value
# 不为空值
if cell_value:
last_value = cell_value
else:
cell.value = last_value
try:
print "\n"
app1 = xw.App(visible=True, add_book=False) # 只打开不新增工作簿
app1.display_alerts = False # 关闭Excel的提示和警告信息
# app1.screen_updating = False # 不更新屏幕显示
app1.screen_updating = True
# 打开清理统计表
wb1 = app1.books.open(infile)
wbs1 = wb1.sheets[0]
wbs2 = wb1.sheets[1]
# v1 = sheet1.range("a1:a7").value
# v1 = sheet1.range("d1:d300").expand().value #TODO 不清楚expand的作用
wbs2_rowcount = wbs2.api.UsedRange.Rows.count
ss = wbs2.range("A1:A" + str(wbs2_rowcount)).value
for s in ss:
print s
autofill(wbs2, "A1:A" + str(wbs2_rowcount))
def CreateCharts():
page = Page()
app = xw.App(visible=False, add_book=False)
app.display_alerts = False
app.screen_updating = False
book = app.books.open(filepath)
sheet = book.sheets['sheet1']
_list1 = []
_list2 = []
_list3 = []
_list4 = []
_list5 = []
# 从 excel 中获取数据
cells1 = sheet.range('F3:K3').value # 第 1 支球队
cells2 = sheet.range('F4:K4').value # 第 2 支球队
cells3 = sheet.range('F5:K5').value # 第 3 支球队
cells4 = sheet.range('F6:K6').value # 第 4 支球队
cells5 = sheet.range('F7:K7').value # 第 5 支球队
# print(data1)
for cell1,cell2,cell3,cell4,cell5 in zip(cells1,cells2,cells3,cells4,cells5):
_list1.append(cell1)
_list2.append(cell2)
_list3.append(cell3)
_list4.append(cell4)
_list5.append(cell5)
v1 = [_list1]
v2 = [_list2]
v3 = [_list3]
v4 = [_list4]
v5 = [_list5]
chart = Radar("进攻属性")
schema = [
("进球", 15),
("射门", 20),
("射正", 9),
("绝佳机会", 20),
("机会把握率", 70),
("过人", 16),
# ("被侵犯", 18),
# ("越位", 5),
]
chart.config(schema)
chart.add("利物浦", v1)
chart.add("诺维奇", v2)
chart.add("曼城", v3)
chart.add("西汉姆联", v4)
chart.add("伯恩利", v5)
page.add(chart)
# 玫瑰图
attr = ["衬衫", "羊毛衫", "雪纺衫", "裤子", "高跟鞋", "袜子"]
v2 = [19, 21, 32, 20, 20, 33]
chart = Pie("饼图-玫瑰图示例", title_pos='center')
chart.add("商品A", attr, v2, is_random=True,
radius=[30, 75], rosetype='area',
is_legend_show=False, is_label_show=True)
page.add(chart)
# 雷达图
schema = [
("销售", 6500), ("管理", 16000), ("信息技术", 30000),
("客服", 38000), ("研发", 52000), ("市场", 25000)
]
v1 = [[4300, 10000, 28000, 35000, 50000, 19000]]
v2 = [[5000, 14000, 28000, 31000, 42000, 21000]]
v3 = [[3000, 9000, 27890, 33000, 43098, 18000]]
v4 = [[3890, 12098, 29022, 32000, 41000, 20000]]
chart = Radar("雷达图-默认指示器")
chart.config(schema)
chart.add("预算分配", v1)
chart.add("实际开销", v2)
chart.add("ddd", v3)
chart.add("fff", v4)
page.add(chart)
app.kill()
return page
def create_tables(project, document):
pythoncom.CoInitialize()
app = xw.App(add_book=False)
wb = app.books.open('C:\\文件库\\Projects\\Company' +
str(project.company_id) + '\\' + project.projectName +
"\\" + project.projectName + ".xlsx")
styles = document.styles
for table in document.tables:
for column in table.columns:
i = 0
for cell in column.cells:
i = i + 1
if (cell.text == "工程组成"):
otherEngineering = json.loads(project.otherEngineering)
environmentalEngineering = json.loads(
project.environmentalEngineering)
rows = 5 + len(environmentalEngineering)
columns = 3
newtable = cell.add_table(rows, columns)
for s in styles:
if s.name == "Table Grid":
print(s.name)
newtable.style = s
newtable.cell(0, 0).text = '项目'
newtable.cell(0, 1).text = '内容'
newtable.cell(0, 2).text = '用途'
for t in range(1, rows):
if (t <= 4):
newtable.cell(
t, 0).text = otherEngineering[t - 1]["project"]
newtable.cell(
t, 1).text = otherEngineering[t - 1]["content"]
newtable.cell(
t, 2).text = otherEngineering[t - 1]["use"]
else:
newtable.cell(t,
0).text = environmentalEngineering[
t - 5]["project"] + str(t - 4)
newtable.cell(t,
1).text = environmentalEngineering[
t - 5]["content"]
newtable.cell(
t, 2).text = environmentalEngineering[t -
5]["use"]
cell.merge(column.cells[i])
cell.merge(column.cells[i - 2])
print(cell.text)
print(column.cells[i - 2].text)
if (cell.text == "敏感点表"):
sensitiveInfoWater = json.loads(project.sensitiveInfoWater)
sensitiveInfoReserve = json.loads(
project.sensitiveInfoReserve)
sensitiveInfoHouse = json.loads(project.sensitiveInfoHouse)
rows = len(sensitiveInfoWater) + len(
sensitiveInfoReserve) + len(sensitiveInfoHouse) + 3
columns = 5
newtable = cell.add_table(rows, columns)
for s in styles:
if s.name == "Table Grid":
print(s.name)
newtable.style = s
newtable.cell(0, 0).text = "环境要素"
newtable.cell(0, 1).text = "环境敏感点"
newtable.cell(0, 2).text = "方位"
newtable.cell(0, 3).text = "距离(m)"
newtable.cell(0, 4).text = "环境保护目标"
for t in range(1, rows):
if (t <= len(sensitiveInfoWater)):
newtable.cell(t, 0).text = sensitiveInfoWater[
t - 1]["environmentalElements"]
newtable.cell(t, 1).text = sensitiveInfoWater[
t - 1]["environmentalSensitivePoint"]
newtable.cell(
t,
2).text = sensitiveInfoWater[t -
1]["orientation"]
newtable.cell(
t,
3).text = sensitiveInfoWater[t - 1]["distance"]
newtable.cell(t, 4).text = sensitiveInfoWater[
t - 1]["environmentalObjective"]
elif (t > len(sensitiveInfoWater)
and t <= len(sensitiveInfoWater) + 2):
if (t - len(sensitiveInfoWater)) == 1:
newtable.cell(t, 0).text = "大气环境"
newtable.cell(t, 1).text = ""
newtable.cell(t, 2).text = "---"
newtable.cell(t, 3).text = "---"
newtable.cell(
t, 4).text = "《环境空气质量标准》（GB3095-2012）二级标准；"
newtable.cell(t, 0).merge(newtable.cell(t, 1))
if (t - len(sensitiveInfoWater)) == 2:
newtable.cell(t, 0).text = "声环境"
newtable.cell(t, 1).text = ""
newtable.cell(t, 2).text = "---"
newtable.cell(t, 3).text = "---"
newtable.cell(
t,
4).text = "《声环境质量标准》（GB3096-2008）中的2类标准；"
newtable.cell(t, 0).merge(newtable.cell(t, 1))
elif (t > len(sensitiveInfoWater) + 2
and t <= len(sensitiveInfoWater) + 2 +
len(sensitiveInfoReserve)):
newtable.cell(t, 0).text = sensitiveInfoReserve[
t - len(sensitiveInfoWater) -
3]["environmentalElements"]
newtable.cell(t, 1).text = ""
newtable.cell(t, 2).text = sensitiveInfoReserve[
t - len(sensitiveInfoWater) - 3]["orientation"]
newtable.cell(t, 3).text = sensitiveInfoReserve[
t - len(sensitiveInfoWater) - 3]["distance"]
newtable.cell(t, 4).text = sensitiveInfoReserve[
t - len(sensitiveInfoWater) -
3]["environmentalObjective"]
newtable.cell(t, 0).merge(newtable.cell(t, 1))
elif (t > len(sensitiveInfoWater) + 2 +
len(sensitiveInfoReserve)):
newtable.cell(t, 0).text = sensitiveInfoHouse[
t - len(sensitiveInfoWater) - 3 -
len(sensitiveInfoReserve
)]["environmentalElements"]
newtable.cell(t, 1).text = ""
newtable.cell(t, 2).text = sensitiveInfoHouse[
t - len(sensitiveInfoWater) - 3 -
len(sensitiveInfoReserve)]["orientation"]
newtable.cell(t, 3).text = sensitiveInfoHouse[
t - len(sensitiveInfoWater) - 3 -
len(sensitiveInfoReserve)]["distance"]
newtable.cell(t, 4).text = sensitiveInfoHouse[
t - len(sensitiveInfoWater) - 3 -
len(sensitiveInfoReserve
)]["environmentalObjective"]
newtable.cell(t, 0).merge(newtable.cell(t, 1))
cell.merge(column.cells[i])
cell.merge(column.cells[i - 2])
if (cell.text == "废气排放标准表"):
emissionStandard = json.loads(project.emissionStandard)
rows = len(emissionStandard) + 1
columns = 5
newtable = cell.add_table(rows, columns)
for s in styles:
if s.name == "Table Grid":
print(s.name)
newtable.style = s
newtable.cell(0, 0).text = "污染物"
newtable.cell(0, 1).text = "最高允许排放浓度（mg / m3）"
newtable.cell(0, 2).text = "最高允许排放速率（kg / h） "
newtable.cell(0, 3).text = "无组织排放监控点浓度限值（mg / m3）"
newtable.cell(0, 4).text = "标准"
for t in range(1, rows):
newtable.cell(
t, 0).text = emissionStandard[t - 1]["pollutant"]
newtable.cell(t, 1).text = emissionStandard[
t - 1]["maximumAllowableEmissionConcentration"]
newtable.cell(t, 2).text = emissionStandard[
t - 1]["maximumAllowableEmissionRate"]
newtable.cell(
t,
3).text = emissionStandard[t -
1]["emissionMonitoring"]
newtable.cell(
t, 4).text = emissionStandard[t - 1]["standard"]
cell.merge(column.cells[i])
cell.merge(column.cells[i - 2])
if (cell.text == "废气部分"):
for p in cell.paragraphs:
for r in p.runs:
if (r.text == "废气"):
formalrun = r
formalstyle = formalrun.style
exhaustGas = json.loads(project.exhaustGas)
for t in range(len(exhaustGas)):
gasName = exhaustGas[t]['gasName']
gassheet = wb.sheets[gasName]
basesheet = wb.sheets["废气信息"]
gassheet.range(
'l4').value = exhaustGas[t]['year_discharge_wo']
gassheet.range(
'm4').value = exhaustGas[t]['hour_discharge_wo']
gassheet.range(
'n4').value = exhaustGas[t]['concentration_wo']
gassheet.range(
'o4').value = exhaustGas[t]['year_discharge_woo1']
gassheet.range(
'p4').value = exhaustGas[t]['hour_discharge_woo1']
gassheet.range(
'm9').value = exhaustGas[t]['year_discharge_woo1']
gassheet.range(
'n9').value = exhaustGas[t]['hour_discharge_woo1']
gassheet.range(
'h14').value = exhaustGas[t]['year_discharge_woo2']
if (exhaustGas[t]['remark'] == '有组织排放'):
newtable = cell.add_table(3, 9)
for a in range(0, 3):
for b in range(0, 9):
if (gassheet[a + 1, b + 7].value == None):
newtable.cell(a, b).text = " "
else:
newtable.cell(a, b).text = str(
gassheet[a + 1, b + 7].value)
for s in styles:
if s.name == "Table Grid":
newtable.style = s
elif (exhaustGas[t]['remark'] == '无组织排放1'):
newtable = cell.add_table(3, 7)
for a in range(0, 3):
for b in range(0, 7):
if (gassheet[a + 6, b + 7].value == None):
newtable.cell(a, b).text = " "
else:
newtable.cell(a, b).text = str(
gassheet[a + 6, b + 7].value)
for s in styles:
if s.name == "Table Grid":
newtable.style = s
elif (exhaustGas[t]['remark'] == '无组织排放2'):
newtable = cell.add_table(3, 5)
for a in range(0, 3):
for b in range(0, 5):
if (gassheet[a + 11, b + 7].value == None):
newtable.cell(a, b).text = " "
else:
newtable.cell(a, b).text = str(
gassheet[a + 11, b + 7].value)
for s in styles:
if s.name == "Table Grid":
newtable.style = s
for p in cell.paragraphs:
if (p.text == ""):
r = p.add_run(text="(" + str(t + 1) + ")" +
gasName + ":",
style=formalstyle)
r.add_text(
basesheet["L" +
str((t + 1) * 7 + 1)].value)
cell.merge(column.cells[i])
cell.merge(column.cells[i - 2])
if (cell.text == "废气2"):
exhaustGas = json.loads(project.exhaustGas)
basep = ""
for p in cell.paragraphs:
if (p.text == "废气2"):
basep = p
for t in range(len(exhaustGas)):
gasName = exhaustGas[t]['gasName']
basesheet = wb.sheets["废气信息"]
r = basep.add_run(text="(" + str(t + 1) + ")" +
gasName + ":" + "\n")
r = basep.add_run(
text=basesheet["L" + str((t + 1) * 7 + 1)].value +
"\n")
cell.merge(column.cells[i])
cell.merge(column.cells[i - 2])
if (cell.text == "噪声表"):
table_creater("噪声表", wb, 5, cell, i, column, styles)
if (cell.text == "三表"):
table_creater("三表", wb, 5, cell, i, column, styles)
if (cell.text == "三同时表"):
table_creater("三同时表", wb, 5, cell, i, column, styles)
wb.close()
app.quit()
'''
import xlwings as xw
# import time
import datetime
# from tqdm import tqdm
# def openexcel(filename):
filepath = "cupWeight.csv" # 与程序同目录下
filepath2 = "称重数据汇总（含模板）-C1.xlsx"
# filepath2 = "C1.xlsx"
# 打开一个表格
try:
# 实例化一个book
cupweight = ''
cupweight_wb = xw.App(visible=False, add_book=False).books.open(filepath)
smmary_wb = xw.App(visible=True, add_book=False).books.open(filepath2)
# swb2 = xw.App(visible=True, add_book=False).books.open(filepath2)
print('open xsl')
except:
print("找不到名为cupWeight/称重数据汇总（含模板）-C1 的文件")
if __name__ == '__main__':
print("starting")
sheetname = datetime.datetime.now().strftime('%m-%d')
try:
sum2sht = smmary_wb.sheets["模板"]
# sum2sht.api.Copy(Before=sum2sht.api)
# sum2sht.api.Name = '3111'
import os
import shutil
import xlwings as xw
# 程序不可见，只打开不新建工作薄，屏幕更新关闭
app_readExcel = xw.App(visible=False, add_book=False)
app_readExcel.display_alerts = False
app_readExcel.screen_updating = False
# 创建工程目录
def make_prj_dir(prefix, name):
prj_dir = prefix + '\\' + 'prj{}.mpj'.format(name)
if os.path.exists(prj_dir):
print('Already have the project directory:\n{}.\n'
'Project will continue to use this directory.'.format(prj_dir))
else:
os.mkdir(prj_dir)
return prj_dir
# 移动txt文件
def move_txt(src, dst, file, label):
# 确保目的路径存在，避免文件丢失
assert os.path.isabs(src)
if label == 'Ⅰ':
d_name = '1'
elif label == 'Ⅱ':
d_name = '2'
elif label == 'Ⅲ':
d_name = '3'
def excel_app():
app = xlwings.App(add_book=False, visible=True)
try:
yield app
finally:
app.kill()
def test_app_instance(self):
app = xw.App()
wb = create_report('template_with_links.xlsx', 'output.xlsx', app=app, book_settings={'update_links': False}, **data)
self.assertEqual(wb.sheets[0]['M1'].value, 'Text for update_links')
wb.app.quit()
def open(self):
if not self.wb:
xw.App(visible=self.visible)
logger.info('Case "%s" open' % self.name)
self.wb = xw.Book(self._file)
import xlwings as xw
app = xw.App(visible=True, add_book=False) # 使用xlwings的app启动
wb = app.books.open(
'C:\\Users\\Administrator\\Desktop\\2020年7月份一号项目生产数据每小时汇报.xlsx') # 打开文件
sheet = wb.sheets['氯化系统（7号炉）'] # 选定sheet
all = sheet.range('A1:Q109') # 获取有内容的range
print(all.value)
all.api.CopyPicture() # 复制图片区域
sheet.api.Paste() # 粘贴
img_name = '1氯化系统（7号炉）'
pic = sheet.pictures[0] # 当前图片
pic.api.Copy() # 复制图片
from PIL import ImageGrab
img = ImageGrab.grabclipboard() # 获取剪贴板的图片数据
img.save("C:\\Users\\Administrator\\Desktop\\Report\\" + img_name +
".PNG") # 保存图片
pic.delete() # 删除sheet上的图片
wb.close() # 不保存，直接关闭
app.quit() # 退出app
# -*- coding: UTF-8 -*-
import os
import sys
import shutil
import re
import xlwings as xls
import pprint
if getattr(sys, 'frozen', False):
bundle_dir = sys._MEIPASS
else:
bundle_dir = os.path.dirname(os.path.abspath(__file__))
os.chdir(bundle_dir)
app = xls.App(visible=False, add_book=True)
print("遍历指定目录下所有的文件和文件夹，包括子目录内的")
list_dirs = os.walk(sys.path[0])
for root, dirs, files in list_dirs:
for f in files:
# 分离文件名与扩展名，仅显示txt后缀的文件
if os.path.splitext(f)[1] == '.xlsx' or os.path.splitext(
f)[1] == '.xls':
print("正在处理：", f)
file_path = os.path.join(root, f)
app.books.open(file_path)
for sht in app.books[f].sheets:
for shp in sht.shapes:
if shp.type == "text_box":
text = shp.api.TextFrame2.TextRange.Text
#搜索的目标字符串，如果包含子字符串返回开始的索引值，否则返回-1。
def averagediff(self):
data=DS.Raw.loc[DS.Ir,DS.Ic]
data=data[[self.XcomboBox.currentText(),self.YcomboBox.currentText()]]
var=np.array(['A','B'])
var=var.T
vc=data.count(axis=0)
nu=vc.sum(axis=0)
nu=nu-2
vc=np.array(vc)
vc=vc.T
vm=data.mean(axis=0,skipna=True)
vm=np.array(vm)
vm=vm.T
vsum=data.sum(axis=0,skipna=True)
vsum=np.array(vsum)
vsum=vsum.T
difference=vm[1]-vm[0]
if not self.randomcheckBox.isChecked(): # standard t-Student test on average
Pvar=data.var(axis=0,skipna=True)
Pvar=Pvar*(vc-1)
Pvar=Pvar.sum(axis=0)
Pvar=Pvar/nu
Vdiff=Pvar*(1/vc[1]+1/vc[0])
Serr=np.sqrt(Vdiff)
tt=abs(difference)/Serr
p0=stats.t.pdf(tt,nu)
t1=stats.t.ppf(0.95,nu)
t2=stats.t.ppf(0.99,nu)
if(tt>=t1):
res="Two average are different"
else:
res="Two averages are equal"
self.appExcel=xw.App()
wb=xw.books.active
sht=wb.sheets[0]
sht.range('A1').value='Variable'
sht.range('B1').value=var
sht.range('A2').value='Number'
sht.range('B2').value=vc
sht.range('A3').value='Sum'
sht.range('B3').value=vsum
sht.range('A4').value='Dof'
sht.range('B4').value=np.array(nu)
sht.range('A5').value='Difference'
sht.range('B5').value=difference
sht.range('A6').value='Average'
sht.range('B6').value=vm
sht.range('A7').value='Pooled var.'
sht.range('B7').value=Pvar
sht.range('A7').value='Difference Variance'
sht.range('B7').value=Vdiff
sht.range('A8').value='Standard Error'
sht.range('B8').value=Serr
sht.range('A9').value='t-Student'
sht.range('B9').value=tt
sht.range('A10').value='p-Student x100'
sht.range('B10').value=p0*100
sht.range('A11').value='t.lim(5%)'
sht.range('B11').value=t1
sht.range('A12').value='t.lim(1%)'
sht.range('B12').value=t2
sht.range('A13').value=res
else: # randomizations
val=np.array(data)
val=val.flatten()
nval=vc[1]+vc[0]
npoints=int(self.spinBox.value())
x=np.zeros(npoints)
for i in range(npoints):
ia=sample(range(nval),vc[0])
ya=val[ia]
yb=np.delete(val,ia)
x[i]=yb.mean()-ya.mean()
iqr=np.percentile(x, [75, 25])
iqr=iqr[0]-iqr[1]
n=x.size
dx=abs(np.amax(x)-np.amin(x))
nbins=floor(dx/(2*iqr)*n**(1/3))+1
bins=np.linspace(np.amin(x),np.amax(x),nbins)
fig=plt.figure()
ax=fig.add_subplot(111)
ax.hist(x,bins=bins,normed=1,histtype='bar',color='b',alpha=0.5,orientation='vertical',label="X")
ax.axvline(x=difference,linewidth=2, color='red')
yc,xc=np.histogram(x,bins=bins,normed= True)
tot=0
tota=0
for i in range(len(xc)-1):
tot=tot+yc[i]
if(difference<=xc[i]):
tota=tota+yc[i]
pa=tota/tot*100
ax.set_title('Randomized Histogram : P(x>d)= '+str(round(pa,2))+'%)')
fig.show()
def start():
global zichan_xls, bumen_xls
try:
app = xw.App(visible=False, add_book=False)
zichan_xls = app.books.open(zichan)
bumen_xls = app.books.open(bumen1)
""""
获取这两个表单的最大长度
"""
zichan_xls_sheet = zichan_xls.sheets(1)
bumen_xls_sheet = bumen_xls.sheets(1)
zichan_xls_sheet_info = zichan_xls_sheet.used_range
bumen_xls_sheet_info = bumen_xls_sheet.used_range
nrows1 = zichan_xls_sheet_info.last_cell.row
ncols1 = zichan_xls_sheet_info.last_cell.column
nrows2 = bumen_xls_sheet_info.last_cell.row
ncols2 = bumen_xls_sheet_info.last_cell.column
print("资产表的行数", nrows1, "列数", ncols1)
print("部门表行数", nrows2, "列数", ncols2)
zichan_dep_arr = []
admin_dep_arr = set()
for i in range(2,nrows2 + 1):
item1 = bumen_xls_sheet.range(i, 1).value
item2 = bumen_xls_sheet.range(i, 2).value
item3 = bumen_xls_sheet.range(i, 3).value
admin_dep_arr.add(item1)
admin_dep_arr.add(item2)
admin_dep_arr.add(item3)
print(item1, item2, item3)
num1 = 1
num2 = 1
diff_bumen = []
diff_danwei = []
for i in range(2,nrows1 + 1):
danwei = zichan_xls_sheet.range(i,12).value
bumen = zichan_xls_sheet.range(i,13).value
bianhao = zichan_xls_sheet.range(i, 1).value
# zichan_dep_arr.append(item)
item = {"bianhao":bianhao,"value":bumen}
if bumen not in admin_dep_arr:
print(num1)
print(item)
num1 = num1 + 1
diff_bumen.append(item)
zichan_xls_sheet.range(i, 13).value = ""
if danwei not in admin_dep_arr:
print(num2)
print(item)
num2 = num2 + 1
diff_danwei.append(item)
zichan_xls_sheet.range(i, 12).value = ""
fileObject = open('C:\\Users\\15118\\Desktop\待处理\\result.txt', 'w')
for ip in diff_bumen:
fileObject.write(str(ip))
fileObject.write('\n')
fileObject.write("================================")
for ip in diff_danwei:
fileObject.write(str(ip))
fileObject.write('\n')
fileObject.close()
zichan_xls.save()
bumen_xls.save()
zichan_xls.app.quit()
os.startfile(data_dir)
pass
except Exception as result:
print(result)
zichan_xls.save()
bumen_xls.save()
zichan_xls.app.quit()
pass
pass
def test_len(self):
n_original = len(xw.apps)
app = xw.App(spec=SPEC)
wb = app.books.add()
self.assertEqual(n_original + 1, len(xw.apps))
app.quit()
@LastEditors: Guo Yulong
@LastEditTime: 2019-01-30 19:17:08
'''
import settings
import city_process
import pandas as pd
import xlwings as xw
import win32com.client as com
from win32com.client import constants as c
import re
import os
import warnings
warnings.filterwarnings('ignore')
app = xw.App(add_book=False, visible=settings.isVisible)
app.display_alerts = False
app.screen_updating = False
template_wb = app.books.open(settings.GetRecentFile(settings.origPath, '市级地图'))
city = template_wb.sheets['城市'].range(1, 1).options(pd.DataFrame,
index=False,
expand='table').value
source_wb = app.books.open(settings.GetRecentFile(settings.sourcePath,
'.xlsx'))
data = source_wb.sheets[0].range(1, 1).options(pd.DataFrame,
index=False,
expand='table').value.merge(
city,
left_on='目标城市',
def test_quit(self):
app = xw.App()
n_apps = len(xw.apps)
app.quit()
time.sleep(1) # needed for Mac Excel 2011
self.assertEqual(n_apps - 1, len(xw.apps))
# -*- coding: utf-8 -*-
import xlwings as xw
app = xw.App(visible=False) # Hide the workbook while Python is preparing it
wb = xw.Book() # Opens a workbook
sht = wb.sheets[0] # Activates the Shhet[0]
Value = "Testing" # Prepare a text to write
sht.range('A10').value = Value # Writes the text
wb.save(
r'C:\Users\jcfarina\DEVELOPMENT\learningXlwings\000-hideWorkbook\Prueba.xlsx'
) # Saves the workbook
wb.close() # Close the workbook
app.kill() # Kill the application (it closes everything)
'''
mlist = []
if type(_str) == str:
mlist = re.findall(_argv + '(.*?)' + _argv, _str)
if _islist:
return mlist
else:
return tuple(mlist)
def get_rulelist(self, _str):
rlist = {}
for i in Rule.rulelist:
v = Rule.return_search(Rule.rulelist[i], _str)
rlist[i] = v
return rlist
if __name__ == "__main__":
app = xlwings.App(visible=True, add_book=False) #避免显示Excel 且多文件打开
wk = app.books.open(
r'J:\\pythonWorkPlace\\ExcelExport\\ExcelExport\\templates\\对账单模板.xlsx'
)
ws = wk.sheets.active
rows = ws.api.usedRange.Rows
print(rows)
# for i in range(1,1000):
# print(i)
# s = tool.max_A_Z(i)
# print(s)
#print(s)
def __init__(self,
filepath=None,
overwrite_file=False,
visible=None,
silent=None,
app=None,
load_addins=None):
global global_app
xw_wkb = None
self.delayed_filepath = None
self.filepath = None
self.new_workbook = False
self.active_workbook = filepath == -1
if filepath is None:
self.new_workbook = True
if isinstance(filepath, str):
basename, ext = os.path.splitext(filepath)
if ext:
# XXX: we might want to be more precise than .xl* because I am unsure writing .xls
# (or anything other than .xlsx and .xlsm) would work
if not ext.startswith('.xl'):
raise ValueError(
f"'{ext}' is not a supported file extension")
if not os.path.isfile(filepath) and not overwrite_file:
raise ValueError(
f"File {filepath} does not exist. Please give the path to an existing file "
f"or set overwrite_file argument to True")
if os.path.isfile(filepath) and overwrite_file:
self.filepath = filepath
# we create a temporary file to work on. In case of crash, the original is not destroyed.
# the temporary file is renamed as the original file at close.
filepath = basename + '~' + ext
if not os.path.isfile(filepath):
self.new_workbook = True
else:
# try to target an open but unsaved workbook. We cannot use the same code path as for other options
# because we do not know which Excel instance has that book
xw_wkb = xw.Book(filepath)
app = xw_wkb.app
# active workbook use active app by default
if self.active_workbook and app not in {None, "active"}:
raise ValueError(
"to connect to the active workbook, one must use the 'active' Excel instance "
"(app='active' or app=None)")
# unless explicitly set, app is set to visible for brand new or active book.
# For unsaved_book it is left intact.
if visible is None:
if filepath is None or self.active_workbook:
visible = True
elif xw_wkb is None:
# filepath is not None and we target a real file (not an unsaved book)
visible = False
if app is None:
if self.active_workbook:
app = "active"
elif visible:
app = "new"
else:
app = "global"
if load_addins is None:
load_addins = visible and app == "new"
if app == "new":
app = xw.App(visible=visible, add_book=False)
if not visible:
_disable_screen_updates(app)
elif app == "active":
app = xw.apps.active
elif app == "global":
if global_app is None:
atexit.register(kill_global_app)
if global_app is None or not is_app_alive(global_app):
global_app = xw.App(visible=visible, add_book=False)
if not visible:
_disable_screen_updates(global_app)
app = global_app
assert isinstance(app, xw.App)
# activate XLA(M) addins, if nee
# By default, add-ins are not activated when an Excel Workbook is opened via COM
if load_addins:
xl_app = app.api
for i in range(1, xl_app.AddIns.Count + 1):
addin = xl_app.AddIns(i)
addin_path = addin.FullName
if addin.Installed and '.xll' not in addin_path.lower():
xl_app.Workbooks.Open(addin_path)
if visible:
app.visible = visible
if silent is None:
silent = not visible
update_links_backup = app.api.AskToUpdateLinks
display_alerts_backup = app.display_alerts
if silent:
# try to update links silently instead of asking: "Update", "Don't Update", "Help"
app.api.AskToUpdateLinks = False
# in case some links cannot be updated, continue instead of asking: "Continue" or "Edit Links..."
app.display_alerts = False
if filepath is None:
# creates a new/blank Book
xw_wkb = app.books.add()
elif self.active_workbook:
xw_wkb = app.books.active
elif xw_wkb is None:
# file already exists (and is a file)
if os.path.isfile(filepath):
xw_wkb = app.books.open(filepath)
else:
# let us remember the path
self.delayed_filepath = filepath
xw_wkb = app.books.add()
if silent:
app.api.AskToUpdateLinks = update_links_backup
app.display_alerts = display_alerts_backup
self.xw_wkb = xw_wkb
def pcasave(self):
self.appExcel=xw.App()
wb=xw.books.active
sht=wb.sheets[0]
sht.name='DataSet'
Ts=DS.Ts[DS.Ir]
Lr=DS.Lr[DS.Ir]
Lr=Lr[-Ts]
Lr=Lr[np.newaxis]
Lc=DS.Lc[DS.Ic]
Lc=Lc[np.newaxis]
sht.range('B1').value=Lc
sht.range('A2').value='mean'
sht.range('B2').value=PCA.Xm
sht.range('A3').value='std'
sht.range('B3').value=PCA.Xstd
if(DS.Cl!=None):
sht.range('B5').value=range(len(DS.Cl))
sht.range('A6').value='Group Class'
sht.range('B6').value=DS.Cl
sht.range('A7').value=Lr.T
sht.range('B7').value=PCA.Xc
sht=wb.sheets[1]
sht.name='Loading'
sht.range('A1').value='n.comp.'
sht.range('A2').value='Eigenvalues'
sht.range('A3').value='Residual Var.'
sht.range('A5').value=Lc.T
sht.range('B1').value=np.array(range(1,PCA.ncp+1))
sht.range('B2').value=PCA.ei
sht.range('B3').value=PCA.rv
sht.range('B5').value=PCA.lo
sht=wb.sheets[2]
sht.name='Scores'
sht.range('A2').value=Lr.T
sht.range('B1').value=np.array(range(1,PCA.ncp+1))
sht.range('B2').value=PCA.sco
wb.sheets.add(name='Residuals')
sht=wb.sheets['Residuals']
sht.range('B1').value=Lc
sht.range('A2').value=Lr.T
sht.range('B2').value=PCA.res
wb.sheets.add(name='T2-Q')
sht=wb.sheets['T2-Q']
sht.range('B1').value='T2'
sht.range('A2').value=Lr.T
sht.range('B2').value=PCA.ht2[np.newaxis].T
sht.range('C1').value='Q'
sht.range('C2').value=PCA.Q[np.newaxis].T
wb.sheets.add(name='Qlim-MQ')
sht=wb.sheets['Qlim-MQ']
sht.range('A1').value='Qlim(95%)'
sht.range('A2').value=PCA.Q95
sht.range('A3').value='Qlim(99%)'
sht.range('A4').value=PCA.Q99
sht.range('B1').value='MQ'
sht.range('B2').value=PCA.MQ
wb.sheets.add(name='Tlim-MT')
sht=wb.sheets['Tlim-MT']
sht.range('A1').value='Tlim(95%)'
sht.range('A2').value=PCA.T95
sht.range('A3').value='Tlim(99%)'
sht.range('A4').value=PCA.T99
sht.range('B1').value='MT'
sht.range('B2').value=PCA.MT
wb.sheets.add(name='SSX')
sht=wb.sheets['SSX']
sht.range('B1').value='SSX'
sht.range('B2').value=PCA.ssx[np.newaxis].T
sht.range('A2').value=Lc.T
try:
wb.sheets.add(name='SSX')
sht=wb.sheets['SSX']
sht.range('B2').value=PCA.Xcv
sht.range('B1').value=Lc
sht.range('A2').value=Lr.T
except:
pass
try:
wb.sheets.add(name='Additional')
sht=wb.sheets['Additional']
sht.range('B2').value=PCA.TXt
sht.range('B1').value=np.array(range(1,PCA.ncp+1))
except:
pass
#指定の列に対して、リストの文字列が含まれているかどうか、one-hot化する関数
def add_noun_cols(df, col, list):
for i in range(len(list)):
df.at[df[col].str.contains(list[i]), col[0] + str(i)] = 1
df.at[df[col[0] + str(i)].isnull(), col[0] + str(i)] = 0
return df
##項目リスト
col_list = ['名称', '見出し', '明細']
db_cols = ['code', 'name', 'header', 'item']
noun_cols = ['name', 'header', 'item']
#Excelを起動しておく
app = xw.App()
#対象のExcelファイルを1つずつ取得する
for sheet_file in excel_files:
sheet = pd.read_excel(sheet_file, encoding='Shift_JISx0213')
#名称～明細までで、空の項目があれば''で埋める
sheet[col_list] = sheet[col_list].fillna('')
#カタカナと記号は全角、数値は半角に統一
for i in range(len(sheet)):
for j in range(len(col_list)):
sheet.at[i, col_list[j]] = clean_zh(sheet.at[i, col_list[j]])
#学習モデルと単語リストを取得
with open(os.path.join(home_path, model), mode='rb') as f:
def read_ecxel(file_path, sheet_no):
# my_print('pd.read_excel(file_path, sheet_no, sheet_name=None)', pd.read_excel(file_path, sheet_name=0))
all_data = pd.read_excel(file_path, sheet_name=sheet_no, skiprows=3)
pythoncom.CoInitialize() # 不加的话打开的情况下再下载会报错
app = xw.App(visible=False, add_book=False)
app.display_alerts = False # 不显示警告
app.screen_updating = False # 禁止更新
wb = app.books.open(file_path)
try:
for k in all_data:
_count = len(all_data[k]) # 行数
data = all_data[k]
all_receivable = data.Amount.sum() # 总回款
sales = 0
data1 = data.groupby(['Payment Type',
'Transaction type']).sum().reset_index()
for i in range(len(data1)):
if data1.iloc[i][
'Payment Type'] == 'Product charges' and data1.iloc[i][
'Transaction type'] == 'Order Payment':
sales += data1.iloc[i]['Amount'] # 总销售量
data2 = data.groupby(
['Payment Type', 'Transaction type',
'Product Title']).sum().reset_index()
Portable_Speaker = 0 # 音箱
lamp = 0 # 灯串
auricolare = 0 # 耳机
hook = 0 # 挂钩
for i in range(len(data2)):
if data2.iloc[i][
'Payment Type'] == 'Product charges' and data2.iloc[i][
'Transaction type'] == 'Order Payment':
if data2.iloc[i]['Product Title'].startswith(
'Portable Speaker'):
Portable_Speaker += data2.iloc[i]['Quantity'] # 音箱
if data2.iloc[i]['Product Title'].startswith(
('String Lights', '16.4ft', '50 LED', 'Outdoor',
'BNZHome')):
lamp += data2.iloc[i]['Quantity'] # 灯串
if data2.iloc[i]['Product Title'].startswith('Deep Bass'):
auricolare += data2.iloc[i]['Quantity'] # 耳机
if data2.iloc[i]['Product Title'].startswith('Wall Hooks'):
hook += data2.iloc[i]['Quantity'] # 耳机
sheet = wb.sheets[k]
sheet.range('H{}'.format(_count +
14)).value = ['回款合计', all_receivable]
sheet.range('H{}'.format(_count + 15)).value = ['总销售额', sales]
sheet.range('D{}'.format(_count + 16)).value = [
'产品名称', '采购价RMB', 'FBA头程费', '销量', '总采购成本RMB', '总FBA头程成本RMB'
]
sheet.range('D{}'.format(_count + 17)).value = [
'音箱', '46', '3.5', Portable_Speaker, Portable_Speaker * 46,
Portable_Speaker * 3.5
]
sheet.range('D{}'.format(_count + 18)).value = [
'灯串', '22', '3', lamp, lamp * 22, lamp * 3
]
sheet.range('D{}'.format(_count + 19)).value = [
'耳机', '87.5', '3.5', auricolare, auricolare * 87.5,
auricolare * 3.5
]
sheet.range('D{}'.format(_count + 20)).value = [
'挂钩', '8.1', '1.18', hook, hook * 8.1, hook * 1.18
]
sheet.range('D{}'.format(_count + 21)).value = [
'花洒', '106', '25', '0', '0', '0'
]
sheet.range('D{}'.format(_count + 22)).value = [
'合计', '', '', Portable_Speaker + lamp + auricolare + hook,
round(
Portable_Speaker * 46 + lamp * 22 + auricolare * 87.5 +
hook * 8.1, 2),
round(
Portable_Speaker * 3.5 + lamp * 3 + auricolare * 3.5 +
hook * 1.18, 2)
]
print('sheet{}插入完毕'.format(k))
except:
print('出错了，错误信息:{}'.format(traceback.format_exc()))
finally:
wb.save()
wb.close()
app.quit()
print('excel{}写入完毕'.format(file_path))
import pandas as pd
import xlwings as xw
df = pd.read_excel("月销售表.xlsx")
figure = plt.figure()
plt.rcParams["font.sans-serif"] = ["SimHei"]
plt.rcParams["axes.unicode_minus"] = False
x = df["月份"]
y = df["销售额"]
plt.plot(x, y, color="red", linewidth=3, linestyle="solid")
plt.title(label="月销售额趋势图",
fontdict={
"color": "black",
"size": 30
},
loc="center")
for a, b in zip(x, y):
plt.text(a,
b + 0.2, (a, "%.0f" % b),
ha="center",
va="bottom",
fontsize=10)
plt.axis("off")
app = xw.App(visible=False)
workbook = app.books.open("月销售表.xlsx")
worksheet = workbook.sheets["Sheet1"]
worksheet.pictures.add(figure, name="tupian1", update=True, left=200)
workbook.save("折线图.xlsx")
workbook.close()
app.quit()
# s1.insert(len(s1),11)
# aa.extend(b)
# aa.extend(aa)
# aa.extend(aa)
# print(s2,s1)
import xlwings as xw
import time
from tqdm import tqdm
# print(xw.__version__)
filepath = "cupWeight2.xlsx" # 与程序同目录下
filepath2 = "C1.xlsx"
# cupwb1 = xw.Book(filepath)
# swb2 = xw.Book(filepath2)
# cupwb1 = xw.App(visible=True, add_book=False).books.open(filepath)
swb2 = xw.App(visible=True, add_book=False).books.open(filepath2)
#
try:
# # sum2sht = wb2.sheets("模板").range('A1').expand().value
sum2sht = swb2.sheets["模板"]
# # num=wb2.sheets('模板').range(1, 1).expand().shape
# sht1= swb2.sheets['Sheet1']
# cupsheet= cupwb1.sheets[1]
# cupsheet.range('v2:e5').clear_contents()
sum2sht.api.Copy(Before=sum2sht.api)
sht1 = swb2.sheets['模板 (2)']
sht1.name = '0402'
sht12 = swb2.sheets['0402']
SR = int(excode["方阻(kΩ/□)"])
# 创建实验条件数据列
excondition = [
note + "+" + sub1 + "+" + sub2, metaltype, Ar, H2, CH4, time,
power, pressure, temp, SR
]
# # 调试输出
# print(excondition)
# print(date)
# 打开指定的EXCEL文件
try:
# 开始对 excel 文件进行操作
inexcel = args.excel
# 创建 App 进程
app = xw.App(visible=False, add_book=False)
# 链接工作表
wb = app.books.open(inexcel)
# 对指定工作表进行编辑
sht = wb.sheets['Ratio Metadata']
# 获取当前EXCEL表格的行数与列数
info = sht.range('A1').expand('table')
row = info.last_cell.row
col = info.last_cell.column
# 计算出要添加的一行位置
rowl = row + 1
# # 调试输出
# print('原表格最后一行：'+str(row))
# print('数据添加所在行：'+str(rowl))
# 注入EXCEL公式
sht.range('A' +
def open(self, file_path):
app = xw.App(visible=True, add_book=False)
wb = app.books(file_path)
return wb
