# 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