def historyInfor(self): """ 统计历史数据 :return: """ wb1 = xw.Book(fileName) detail_sheet1 = xw.Sheet("Sheet2") historyData = data[~data['交易时间'].apply(lambda x: x.month == today.month and x.day == today.day)] for subData in historyData.groupby('交易类型'): if subData[0] == '消费': tradeTotal = len(subData[1]['交易订单ID']) # 交易笔数 tradeNum = len(set(subData[1]['用户uid'])) # 人数 tradeAmount = subData[1]['交易金额'].sum() # 交易金额 detail_sheet1.range('A4').options(transpose=True).value = '历史信息<' + str(today) detail_sheet1.range('B4').options(transpose=True).value = tradeTotal detail_sheet1.range('C4').options(transpose=True).value = tradeAmount detail_sheet1.range('D4').options(transpose=True).value = tradeNum wb1.save() if subData[0] == '提现': tradeTotal = len(subData[1]['交易订单ID']) # 交易笔数 tradeNum = len(set(subData[1]['用户uid'])) # 人数 tradeAmount = subData[1]['交易金额'].sum() # 交易金额 detail_sheet1.range('J4').options(transpose=True).value = '历史信息<' + str(today) detail_sheet1.range('K4').options(transpose=True).value = tradeTotal detail_sheet1.range('L4').options(transpose=True).value = tradeAmount detail_sheet1.range('M4').options(transpose=True).value = tradeNum wb1.save()
def arrange_seat(input_filename, output_filename): with open(input_filename) as input_file: students = json.load(input_file) shuffle(students) student_num = len(students) col_num = sum(len(cols) for cols in AVAILABLE_COLS_IN_EACH_ROOM.values()) col_length = ceil(student_num / col_num) available_rows = range(START_ROW, START_ROW + (col_length - 1) * 2 + 1, 2) xb = xw.Book(output_filename) counter = 0 try: for room, cols in AVAILABLE_COLS_IN_EACH_ROOM.items(): xw.Sheet(room).activate() print(room) for col in cols: for row in available_rows: print('{}{}'.format(col, row), students[counter]) xw.Range('{}{}'.format(col, row)).value = students[counter] counter += 1 except IndexError: pass xb.save() xb.close()
def save_to_excel(): saveFileName = 'C:\\Users\\ms\\Desktop\\Weibo.xlsx' wb = xw.Book(saveFileName) detail_sheet = xw.Sheet("Sheet1") colList = [ 'screen_name', 'id', 'gender', 'verified', 'description', 'statuses_count', 'follow_count', 'followers_count', 'created_at', 'text', 'source', 'reposts_count', 'attitudes_count', 'comments_count' ] dataList = [ screen_name, ID, gender, verified, description, statuses_count, follow_count, followers_count, created_at, text, source, reposts_count, attitudes_count, comments_count ] xw.Range('A1').expand('table').value = colList colNum = [ 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N' ] detail_sheet.range('A2').options(transpose=True).value = dataList[0] detail_sheet.range('B2').options(transpose=True).value = dataList[1] detail_sheet.range('C2').options(transpose=True).value = dataList[2] detail_sheet.range('D2').options(transpose=True).value = dataList[3] detail_sheet.range('E2').options(transpose=True).value = dataList[4] detail_sheet.range('F2').options(transpose=True).value = dataList[5] detail_sheet.range('G2').options(transpose=True).value = dataList[6] detail_sheet.range('H2').options(transpose=True).value = dataList[7] detail_sheet.range('I2').options(transpose=True).value = dataList[8] detail_sheet.range('J2').options(transpose=True).value = dataList[9] detail_sheet.range('K2').options(transpose=True).value = dataList[10] detail_sheet.range('L2').options(transpose=True).value = dataList[11] detail_sheet.range('M2').options(transpose=True).value = dataList[12] detail_sheet.range('N2').options(transpose=True).value = dataList[13] wb.save()
def autofit_excel(wkbk, list_of_sheetnames_or_number=None): """ Autofits excel sheets columns and rows Arguments: wkbk {xlwings workbook} -- Xlwings Workbook object Keyword Arguments: list_of_sheetnames_or_number {list} -- List of strings or integer (default: {None}) """ try: wb = xw.Workbook(wkbk) print(wb.fullname) if list_of_sheetnames_or_number: for i in list_of_sheetnames_or_number: xw.Sheet(i).autofit() else: for i in xw.Sheet.all(): i.autofit() except TypeError: print( "Excel file could not be found, Available excel files are as follows:" ) wb = xw.Workbook() if len(wb.xl_app.Workbooks) == 1: print("There is no opened Excel Workbook") else: for i in wb.xl_app.Workbooks: print(i.Name) wb.close()
def _make_sample_workbook(sheetname, addr, value): import xlwings as xw wb = xw.Workbook() xw.Sheet(1).name = sheetname xw.Range(addr).value = value return wb
def lastWeekInfor(self): """ 统计上周的数据 :return: """ wb1 = xw.Book(fileName) detail_sheet1 = xw.Sheet("Sheet2") lastWeekData = data[data['交易时间'].apply(lambda x: (x.month == self.getLastSunday().month and x.day <= self.getLastSunday().day) and (x.month == self.getLastMonday().month and x.day >= self.getLastMonday().day))] # 获取上周之前的数据 lastWeekBefor = data[ data['交易时间'].apply(lambda x: (x.month == self.getLastMonday().month and x.day < self.getLastMonday().day) or (x.month < self.getLastMonday().month))] for subData in lastWeekBefor.groupby('交易类型'): if subData[0] == '消费': lastWeekBeforUconsume = list(set(subData[1]['用户uid'])) elif subData[0] == '提现': lastWeekBeforUdraw = list(set(subData[1]['用户uid'])) for subData in lastWeekData.groupby('交易类型'): if subData[0] == '消费': newUser = [] tradeTotal = len(subData[1]['交易订单ID']) # 交易笔数 tradeNum = len(set(subData[1]['用户uid'])) # 人数 tradeAmount = subData[1]['交易金额'].sum() # 交易金额 for i in set(subData[1]['用户uid']): if i not in lastWeekBeforUconsume: newUser.append(i) detail_sheet1.range('A3').options(transpose=True).value = '上周:' + str(self.getLastMonday()) + '~' + str( self.getLastSunday()) detail_sheet1.range('B3').options(transpose=True).value = tradeTotal detail_sheet1.range('C3').options(transpose=True).value = tradeAmount detail_sheet1.range('D3').options(transpose=True).value = tradeNum detail_sheet1.range('E3').options(transpose=True).value = len(newUser) wb1.save() if subData[0] == '提现': newUser = [] tradeTotal = len(subData[1]['交易订单ID']) # 交易笔数 tradeNum = len(set(subData[1]['用户uid'])) # 人数 tradeAmount = subData[1]['交易金额'].sum() # 交易金额 for i in set(subData[1]['用户uid']): if i not in lastWeekBeforUdraw: newUser.append(i) detail_sheet1.range('J3').options(transpose=True).value = '上周:' + str(self.getLastMonday()) + '~' + str(self.getLastSunday()) detail_sheet1.range('K3').options(transpose=True).value = tradeTotal detail_sheet1.range('L3').options(transpose=True).value = tradeAmount detail_sheet1.range('M3').options(transpose=True).value = tradeNum detail_sheet1.range('N3').options(transpose=True).value = len(newUser) wb1.save()
def test_xlwings_smoketest(self): import xlwings as xw sheetname = "shitt" addr = "f6" table = pd.DataFrame([[1, 2], [True, "off"]], columns=list("ab")) with xw_no_save_Workbook() as wb: xw.Sheet(1).name = sheetname xw.Range(addr).value = table
def add_cycle_run_as_sheet(veh_id, mdl_out): sheet = "cycle_%s" % veh_id try: sh = xw.Sheet(sheet) log.info("Sheet(%s) already exists. Clearing it", sheet) sh.clear() except Exception: xw.Sheet.add(sheet) xw.Range(sheet, 'A1').value = mdl_out['cycle_run']
def ExcelReadtoList(): import tkinter as tk from tkinter import filedialog root = tk.Tk() root.withdraw() F = filedialog.askopenfilename() import xlwings as xw wb = xw.Workbook(F) Results = {} Result = {} endRow = 500 endCol = 100 sheetNo = 1 while True: try: carrier = xw.Sheet(sheetNo).name Results[carrier] = xw.Range(sheetNo, (1, 1), (endRow, endCol)).value maxCol = 1 for i in range(0, endRow): countCol = endCol - 1 for j in range(endCol - 1, -1, -1): if Results[carrier][i][j] != None: break else: countCol -= 1 if maxCol < countCol: maxCol = countCol maxRow = 1 for i in range(0, endCol): countRow = endRow - 1 for j in range(endRow - 1, -1, -1): if Results[carrier][j][i] != None: break else: countRow -= 1 if maxRow < countRow: maxRow = countRow Result[carrier] = xw.Range(sheetNo, (1, 1), (maxRow + 1, maxCol + 1)).value sheetNo += 1 except: wb.close() print( 'Completed!\nBe noted maximum of %d rows and %d columns have been tested!!!' % (endRow, endCol)) return Result
def get_file_list(): """get the list of files in the folder""" wb = xw.Workbook.caller() path_input = xw.Range('Macro', 'C2').value l_file_path = glob.glob(path_input + '*.*') l_file_name = [l.split('/')[-1] for l in l_file_path] xw.Range('File', 'A2:B100').clear_contents() xw.Range('File', 'A2').options(transpose=True).value = l_file_path xw.Range('File', 'B2').options(transpose=True).value = l_file_name xw.Sheet('File').activate()
def test_xlwings_smoketest(self): import xlwings as xw sheetname = 'shitt' addr = 'f6' table = pd.DataFrame([[1, 2], [True, 'off']], columns=list('ab')) wb = xw.Workbook() try: xw.Sheet(1).name = sheetname xw.Range(addr).value = table finally: close_workbook(wb)
def get_file_list(): """get the list of files in the folder""" wb = xw.Workbook.caller() path_input = xw.Range('Macro', 'FilePath').value l_file_path = glob.glob(path_input + '[!~]*.*') l_file_name = [l.split('/')[-1] for l in l_file_path] xw.Range('Macro', 'FileField').clear_contents() xw.Range('Macro', 'C_FilePath').options(transpose=True).value = l_file_path xw.Range('Macro', 'C_FileName').options(transpose=True).value = l_file_name xw.Sheet('Macro').activate() wb.macro('ShowMsg')("Choose DataType for all the listed files")
def test_xlwings_smoketest(self): import xlwings as xw sheetname = "shitt" addr = "f6" table = pd.DataFrame([[1, 2], [True, "off"]], columns=list("ab")) wb = xw.Workbook() try: xw.Sheet(1).name = sheetname xw.Range(addr).value = table finally: close_workbook(wb)
def readExcel(self): self.wb = xw.Workbook(self.excelPath) self.sheet = xw.Sheet('PythonOrder') self.range = xw.Range(self.excelArea) self.__orderList = [OrderedDict()] self.__tempParaList = [] for row in self.range.value: paraName, paraValue = row[0], row[1] if paraName is not None: if paraName in self.__tempParaList: self.__tempParaList = [] self.__orderList.append(OrderedDict()) self.__tempParaList.append(paraName) self.__orderList[-1][str(paraName)] = self._paraType[str( paraName)](paraValue)
def project_list(file): # 작업 내용: 현재 예실대비 표에서 유지되는 당기 진행 과제 목록을 인쇄한다.. # 시트 타입: 시트이름이 문자면 요약 시트고, 숫자면 개별과제 시트로 구별한다. # Test 방법: shtCount 변수에 적은 값을 넣어 테스트 해 본 후 전체를 실행한다. print file wb = xw.Workbook(file) # 둘 다 같음. sumShtName = "과제목록" # 둘 다 같음. formShtName = "공통서식" # 둘 다 같음. shtCount = 20 # 테스트 할 시트 수... =20-12\ #VB ''shtCount = Sheets.Count ''당기 총 시트 수... 테스트 동안은 코멘트 상태로 # shtCount = xw.Sheet.count() # 당기 총 시트 수... 테스트 동안은 코멘트 상태로 #VB If shtCount > Sheets.Count Then #VB shtCount = Sheets.Count #VB End If if shtCount > xw.Sheet.count(): shtCount = xw.Sheet.count() print "진짜 시트 개수 : ", xw.Sheet.count(), "테스트 시트 개수 : " , shtCount #VB ''-------------------------------------------------------------------------------- # -------------------------------------------------------------------------------- #VB Dim nameRow As Integer: nameRow = 1 ''표이름 nameRow = 1 # 표이름 #VB Dim yearRow As Integer: yearRow = 2 ''당기연도 yearRow = 2 # 당기연도 #VB Dim supplyRow As Integer ''공급표 #VB Dim demandRow As Integer ''수요표 #VB Dim listRow As Integer ''목록표 #VB Dim listContRow As Integer ''목록 내용 #VB Dim i As Integer #VB Dim j As Integer #VB Dim usedRows As Integer #VB Dim projName As String #VB Dim sumShtCount As Integer ''시트 이름 갯수, non-numeric #VB Dim shtName As String #VB Dim sumSht As Worksheet ''요약 시트 #VB Dim formSht As Worksheet #VB Dim ws As Worksheet ''개별 시트 #VB ''상수값 초기화, Run time 에서 #VB Sheets(formShtName).Activate #VB Set formSht = Sheets(formShtName) xw.Sheet(formShtName).activate() formSht = xw.Sheet(formShtName) #VB Sheets(sumShtName).Activate #VB Set sumSht = Sheets(sumShtName) xw.Sheet(sumShtName).activate() sumSht = xw.Sheet(sumShtName) #VB Set sumSht = Application.ActiveSheet # sumSht = Application.ActiveSheet <= 주석 풀어야 함. <= 안 써도 될 듯 #VB usedRows = sumSht.UsedRange.Rows.Count # https://github.com/ZoomerAnalytics/xlwings/issues/112 usedRows = len(sumSht.xl_sheet.UsedRange.Rows) #VB supplyRow = WorksheetFunction.Match("공급표", sumSht.Range(Cells(1, 2), Cells(usedRows, 2)), 0) #VB demandRow = WorksheetFunction.Match("수요표", sumSht.Range(Cells(1, 2), Cells(usedRows, 2)), 0) #VB listRow = WorksheetFunction.Match("목록표", sumSht.Range(Cells(1, 2), Cells(usedRows, 2)), 0) #VB listContRow = WorksheetFunction.Match("목록식1", sumSht.Range(Cells(1, 2), Cells(usedRows, 2)), 0) supplyRow = get_match_row(u"공급표", [1,2], usedRows) demandRow = get_match_row(u"수요표", [1,2], usedRows) listRow = get_match_row(u"목록표", [1,2], usedRows) listContRow = get_match_row(u"목록식1", [1,2], usedRows) #print supplyRow #print demandRow #print listRow #print listContRow #VB ''목록표 머리줄과 내용 사이 지움 #VB sumSht.Range("A" & listRow + 1 & ":AC" & listContRow - 1).Clear xw.Range("A"+str(listRow+1)+":AC"+str(listContRow - 1)).clear() # 둘 다 같음. sumShtCount = 0 #VB For i = 1 To shtCount for i in xrange(1, shtCount+1): #VB shtName = Sheets.Item(i).Name shtName = xw.Sheet(i).name print shtName #VB ''다른 공통요약 시트에는 붙여넣기 안하고, #VB ''개별 시트에만 붙여넣기 한다 #VB If Not IsNumeric(shtName) Then #VB sumShtCount = sumShtCount + 1 #VB GoTo Next_wsBuf #VB End If if not shtName.isdigit(): sumShtCount += 1 continue #VB Set ws = Sheets(shtName) #ws = xw.Sheet(shtName) #VB ws.Range("A" & nameRow & ":AC" & nameRow + 8).Clear #VB ''이전에 사용한 I/F Buf를 지운다, #VB ''총 9행 지운다. xw.Range(shtName, "A"+str(nameRow)+":AC"+str(nameRow+8)).clear() #VB ''개별시트 마다 요약표 이름을 등록함. #VB sumSht.Range("B2") = sumShtName ''B2: 요약표 이름 xw.Range(sumShtName, "B2").value = sumShtName print xw.Range(sumShtName, "B2").value, sumShtName #VB sumSht.Range("A" & nameRow & ":AC" & yearRow).Copy _ #VB ws.Range("A" & nameRow) xw.Range(shtName,"A"+str(nameRow)).value = xw.Range(sumShtName,"A"+str(nameRow)+":AC"+str(yearRow)).value #VB sumSht.Range("A" & supplyRow & ":AC" & supplyRow + 2).Copy _ #VB ws.Range("A" & supplyRow) # sdr1982 - 공급표의 아래 부분(supplyRow+1)은 값을 보내고 윗 부분(supplyRow)은 수식을 보냄. xw.Sheet(shtName).activate() xw.Range(shtName,"A"+str(supplyRow+1)).value = xw.Range(sumShtName,"A"+str(supplyRow+1)+":AC"+str(supplyRow+2)).value xw.Range(shtName,"A"+str(supplyRow)+":AC"+str(supplyRow+1)).formula = xw.Range(sumShtName,"A"+str(supplyRow)+":AC"+str(supplyRow+1)).formula #VB ''공통서식.과제기간,당기기간 #VB formSht.Range("B10:R15").Copy ws.Range("B10") xw.Range(shtName,"B10").value = xw.Range(formShtName,"B10:R15").value #VB ''Form시트에서 함수형 셀 복사, 서식과 수식 C&P #VB ''formSht.Range("C1:T2").Copy ''한 번 copy해서 각기 다른 기능을 골라쓴다. #VB ''ws.Range("C1").PasteSpecial xlPasteFormats #VB ''ws.Range("C1").PasteSpecial xlPasteFormulas #VB Next_wsBuf: #VB Next i #VB '' 과제번호 시트 목록을 개별 시트 C열에 작성한다. #VB '' 다른 공통요약 시트들은 그대로 통과한다. #VB '' 작성중인 공통요약 시트에서 이전 목록 내용을 지운 후, 다시 채운다 #VB sumSht.Range("C" & listContRow & ":AC" & usedRows).Clear xw.Sheet(sumShtName).activate() xw.Range(sumShtName, "C"+str(listContRow)+":AC"+str(usedRows)).clear() # 둘 다 같음. rowCount = listContRow #'' #VB For i = 1 To shtCount shtSumCount = 0 for i in xrange(1, shtCount+1): #VB shtName = Sheets.Item(i).Name shtName = xw.Sheet(i).name #VB If Not IsNumeric(shtName) Then #VB sumShtCount = sumShtCount + 1 #VB GoTo Next_wsBuf #VB End If if not shtName.isdigit(): shtSumCount += 1 continue #VB ''과제번호 열에 서식과 값 붙여넣기 #VB ''과제번호 열에 서식 먼저 붙여넣기 #VB sumSht.Range("C" & demandRow + 1).Copy ''중단점. 목록식1> 과제번호 #VB sumSht.Range("C" & rowCount + 0).PasteSpecial xlPasteFormats xw.Range(sumShtName,"C"+str(rowCount)).value = xw.Range(sumShtName, "C"+str(demandRow+1)).value #VB ''sumSht.Range("C" & demandRow + 2).Copy ''목록식2> 과제번호 #VB ''sumSht.Range("C" & rowCount + 1).PasteSpecial xlPasteFormats #VB ''그리고 과제번호 열에 값 붙여넣기 #VB sumSht.Range("C" & rowCount + 0) = shtName xw.Range(sumShtName,"C"+str(rowCount)).value = shtName #VB ''sumSht.Range("C" & rowCount + 1) = shtName #VB ''sumSht.Range("C" & rowCount + 2) = "" # 둘 다 같음. rowCount = rowCount + 1 #VB NextPIDList: #VB Next i #VB ''공통요약 셀에 서식 붙여넣기 #VB sumSht.Range("B" & listRow) = "목록표" xw.Range(sumShtName,"B"+str(listRow)).value = "목록표" print "B"+str(listRow), "목록표" #VB sumSht.Range("A" & listContRow) = "listContRow" xw.Range(sumShtName, "A"+str(listContRow)).value = "listContRow" print "A"+str(listContRow), "listContRow" #VB sumSht.Range("B" & listRow).Font.Color = RGB(255, 0, 0) #xw.Range(sumShtName,"B"+str(listRow)).color = (255,0,0) #VB sumSht.Range("B" & listRow).Font.Size = 12 #VB sumSht.Range("B" & listRow).HorizontalAlignment = xlCenter #VB '' 공통요약 머리줄은 한 번만 인쇄한다. #VB sumSht.Range("C" & demandRow & ":U" & demandRow).Copy #VB sumSht.Range("C" & listRow).PasteSpecial (xlPasteAll) xw.Range(sumShtName,"C"+str(listRow)).value = xw.Range(sumShtName, "C"+str(demandRow)+":U"+str(demandRow)).value #VB ''-------------------------------------------------------------------------------- #VB ''수요표의 수요식1를 목록표의 목록식1에 붙여넣기 한다. #VB sumSht.Range("D" & demandRow + 1 & ":U" & demandRow + 1).Copy #VB sumSht.Range("D" & listContRow + 0).PasteSpecial xlPasteAllUsingSourceTheme # TODO - 수식 복사 함수 xw.Range(sumShtName,"D"+str(listContRow)+":U"+str(listContRow)).formula = xw.Range(sumShtName, "D"+str(demandRow+1)+":U"+str(demandRow+1)).formula #print sumShtName, "D"+str(demandRow+1)+":U"+str(demandRow+1), xw.Range(sumShtName,"D"+str(demandRow+1)+":U"+str(demandRow+1)).formula #src_tuples = xw.Range(sumShtName,"D"+str(demandRow+1)+":U"+str(demandRow+1)).formula #tar = [] #for tuple in src_tuples: # for s in tuple: # tar.append(s.replace("C11","C"+str(listContRow))) #xw.Range(sumShtName,"D"+str(listContRow)+":U"+str(listContRow)).formula = tar #VB ''sumSht.Range("D" & demandRow + 2 & ":U" & demandRow + 2).Copy #VB ''sumSht.Range("D" & listContRow + 1).PasteSpecial xlPasteAllUsingSourceTheme #VB ''sumSht.Range("D" & listContRow + 2) = "" # 둘 다 같음. rowCount = listContRow + 1 #VB For i = shtSumCount + 1 To shtCount ''PID 첫번은 이미 기록, n-1개 추가 print "shtSumCount", shtSumCount, "shtCount", shtCount for i in xrange(shtSumCount + 1, shtCount+1): #VB shtName = Application.Sheets(i).Name shtName = xw.Sheet(i).name #VB If Not IsNumeric(shtName) Then #VB GoTo NextListCont #VB End If if not shtName.isdigit(): continue # 다음 줄 부터 수식으로 복사하는 방법 알 필요 있음 ㄷㄷ #VB ''첫번 인스턴스에서 서식과 수식을 복사해서 시트 갯수 만큼 붙여넣기한다 #VB ''행 서식과 수식 붙여넣기 (과제번호는 앞서 완료) #VB sumSht.Range("D" & listContRow & ":U" & listContRow).Copy #VB sumSht.Range("D" & rowCount).PasteSpecial xlPasteFormats #try: # xw.Application(wb).xl_app.Run('sumShtName = "과제목록"') # xw.Application(wb).xl_app.Run('Set sumSht = Sheets(sumShtName)') # xw.Application(wb).xl_app.Run('sumSht.Range("D" & listContRow & ":U" & listContRow).Copy') # xw.Application(wb).xl_app.Run('sumSht.Range("D" & rowCount).PasteSpecial xlPasteFormats') #except pythoncom.com_error as e: # print e # print e[0] # print e[1] # print e[2] # print e[2][2] # break #xw.Range(sumShtName,"D"+str(rowCount)).value = xw.Range(sumShtName, "D"+str(listContRow)+":U"+str(listContRow)).value #VB ''행 수식 붙여넣기 (과제번호는 앞서 완료) #VB ''sumSht.Range("D" & listContRow + 0 & ":U" & listContRow + 0).Copy #VB sumSht.Range("D" & rowCount).PasteSpecial xlPasteFormulas #print sumShtName, "D"+str(demandRow+1)+":U"+str(demandRow+1), xw.Range(sumShtName,"D"+str(listContRow)+":U"+str(listContRow)).formula src_tuples = xw.Range(sumShtName,"D"+str(listContRow)+":U"+str(listContRow)).formula tar = [] for tuple in src_tuples: for s in tuple: tar.append(s.replace("C11","C"+str(rowCount))) xw.Range(sumShtName,"D"+str(rowCount)+":U"+str(rowCount)).formula = tar #print sumShtName, "D"+str(rowCount), xw.Range(sumShtName,"D"+str(rowCount)).formula #print sumShtName, "D"+str(rowCount), xw.Range(sumShtName,"D"+str(rowCount)).value #print sumShtName, "D"+str(listContRow), xw.Range(sumShtName,"D"+str(listContRow)+":U"+str(listContRow)).formula # 둘 다 같음. rowCount = rowCount + 1
#!python
"836-168", "836-169", "836-170", "836-171", "836-172", "836-173", "836-174", "836-175", "836-176", "836-177", "836-178", "836-179", "836-180", "836-181", "836-182", "836-183", "836-184", "836-185", "836-186" ] 其他设备 = [ "947-080", "947-168", "947-169", "947-170", "947-069", "947-165", "947-166", "947-167", "947-096", "947-097", "947-129", "947-171", "947-172", "947-173", "947-186", "947-187", "947-188", "569-027", "569-041" ] # app=xw.App(visible=True,add_book=False) filepath = r"F:\py workplace\aa\venv\app\2018年01维修情况.xls" book = xw.Book(filepath) book.activate() sheet = xw.Sheet("工单列表") sheet.activate() data = xw.Range("A1:H2000").value 精大稀故障时间 = 0 数控设备故障时间 = 0 关键设备故障时间 = 0 锻压设备故障时间 = 0 铸造设备故障时间 = 0 热处理设备故障时间 = 0 其他设备故障时间 = 0 isinlist = False # 遍历维修情况 for id, 工单号, 设备编号, 报修日期, 状态, 故障原因, 修复情况, 维修时间 in data: isinlist = False
xl.Range(1, (1, 10)).offset(eachStock + 2, 0).value = trader.max_min()[0] xl.Range(1, (1, 10)).offset(eachStock + 2, 0).number_format = 'R$ #.##0,00;-R$ #.##0,00' xl.Range(1, (1, 11)).offset( eachStock + 2, 0).value = (trader.max_min()[0] - trader.max_min()[-1]) / trader.max_min()[0] xl.Range(1, (1, 11)).offset(eachStock + 2, 0).number_format = '#.##0,00 %;- #.##0,00 %' xl.Range(1, (1, 12)).offset( eachStock + 2, 0).value = trader.max_min()[0] - trader.max_min()[-1] xl.Range(1, (1, 12)).offset(eachStock + 2, 0).number_format = 'R$ #.##0,00;-R$ #.##0,00' xl.Range(1, (1, 13)).offset(eachStock + 2, 0).value = trader.buy_count xl.Range(1, (1, 14)).offset(eachStock + 2, 0).value = trader.sell_count xl.Range(1, (1, 15)).offset(eachStock + 2, 0).value = trader.total_cash_log[-1] / cash - 1 xl.Range(1, (1, 15)).offset(eachStock + 2, 0).number_format = '#.##0,00 %;- #.##0,00 %' xl.Range(1, (1, 16)).offset(eachStock + 2, 0).value = trader.total_cash_log[-1] - cash xl.Range(1, (1, 16)).offset(eachStock + 2, 0).number_format = 'R$ #.##0,00;-R$ #.##0,00' xl.Sheet(1).autofit() eachStock += 1
for i in range(0,8): Fert(mo =5,d=1,fmat='FE004',fapp='AP003',fdep=0,famn=fert[i],famp=0,famk=0,famc=-99,famo=-99,focd=-99,fername=-99,nwyr='yes') gt = GREET(crop='Maize',cultivar='GDD2600',soil='Loam',weather='UNME',st_yr=2015,plant_month=5,plant_date=1,ppop=pop[i],pmeth='S',row_space=75,pdepth=5,mode='B',irrsim='F',h_mo=10,h_day=15,w_suff='01',batchfile='run.v46',ofile='output.OUT',fuel='corn') g = gt.Model() path = 'C:/DSGT/DSSAT46/cweb-research/SensA' os.rename('saved.csv',os.path.join(path,'ppop_%d.csv'%(pop[i]))) ''' i = 77 j = 96 k = 115 pop = range(5, 13) fert = range(100, 260, 10) wb = xw.Book('C:/DSSAT46/DSSAT_wrapper/src/root/nested/SensA/ppop_res.xlsx') ws = xw.Sheet('ppop_5') for p in pop: for f in fert: Fert(mo=5, d=1, fmat='FE004', fapp='AP003', fdep=0, famn=f, famp=0, famk=0, famc=-99, famo=-99, focd=-99, fername=-99, nwyr='yes')
import xlwings as xw import xml.dom.minidom # open xml dom = xml.dom.minidom.parse('C:\\Users\ms\Desktop\cds.xml') # save as excel loss_rateFile = 'C:\\Users\\ms\\Desktop\\cds.xlsx' wb=xw.Book(loss_rateFile) # open file detail_sheet=xw.Sheet("Sheet1") #sheet_name detail_sheet.range('A1').options(transpose=True).value= 'Name' detail_sheet.range('B1').options(transpose=True).value = 'Type' detail_sheet.range('C1').options(transpose=True).value= 'Value' detail_sheet.range('D1').options(transpose=True).value = 'Unit' detail_sheet.range('E1').options(transpose=True).value= 'Description' #得到文档元素对象 root = dom.documentElement itemlist = root.getElementsByTagName('Parameter') for i in range(len(itemlist)): item = itemlist[i] itemName=item.getAttribute("name") detail_sheet.range('A' + str(i+2)).options(transpose=True).value= itemName #print (un) itemType=item.getAttribute("type") detail_sheet.range('B' + str(i+2)).options(transpose=True).value= itemType itemValue = item.getAttribute('value') detail_sheet.range('C' + str(i+2)).options(transpose=True).value= itemValue
from InputCreator import DSSATFile as DF from InputCreator import DSSATModel as DM from InputCreator import Fertilizer as Fert import xlwings as xw pop = range(5, 13) fert = list(range(10, 260, 10)) fert = fert + [300, 400, 500, 600] print(fert) wb = xw.Book('C:/DSSAT46/DSSAT_wrapper/src/root/nested/SensA/ppop.xlsx') print('Next') ws = xw.Sheet('Sheet1') j = 66 i = 2 for p in pop: for f in fert: Fert(famn=f, fmat='FE004', fapp='AP003') a = DF(irrsim='F', weather='UNME', st_yr=2015, w_suff='01', ppop=p) a.Batch() a.Control() y, irr = DM().Run() print(i) print('%s%d' % (chr(j), i)) ws.range('%s%d' % (chr(j), i)).value = y i += 1 j += 1 i = 2
def getSheetIndex(sheet): ''' Retorna el indice e una hoja, dado su nombre. ''' return xw.Sheet(sheet).index
def yesterdayInfor(self): """ 统计昨天数据 :return: """ # 昨天的日期 yesterday = (datetime.date.today() + datetime.timedelta(days=-1)).strftime('%Y-%m-%d') yesterday = datetime.datetime.strptime(yesterday, "%Y-%m-%d") yesterday = yesterday.date() wb1 = xw.Book(fileName) detail_sheet1 = xw.Sheet("Sheet2") detail_sheet1.range('B1').options(transpose=True).value = '交易笔数' detail_sheet1.range('C1').options(transpose=True).value = '交易金额' detail_sheet1.range('D1').options(transpose=True).value = '交易人数' detail_sheet1.range('E1').options(transpose=True).value = '首次交易人数' detail_sheet1.range('K1').options(transpose=True).value = '交易笔数' detail_sheet1.range('L1').options(transpose=True).value = '交易金额' detail_sheet1.range('M1').options(transpose=True).value = '交易人数' detail_sheet1.range('N1').options(transpose=True).value = '首次交易人数' # 昨天统计 yesterdayData = data[data['交易时间'].apply(lambda x: x.month == yesterday.month and x.day == yesterday.day)] # 获取昨天之前的数据 yesBefor = data[data['交易时间'].apply(lambda x: (x.month == 7 and x.day < yesterday.day) or (x.month < yesterday.month))] for subData in yesBefor.groupby('交易类型'): if subData[0] == '消费': yesBeforUconsume = list(set(subData[1]['用户uid'])) elif subData[0] == '提现': yesBeforUdraw = list(set(subData[1]['用户uid'])) for subData in yesterdayData.groupby('交易类型'): if subData[0] == '消费': newUser = [] tradeTotal = len(subData[1]['交易订单ID']) # 交易笔数 tradeNum = len(set(subData[1]['用户uid'])) # 人数 tradeAmount = subData[1]['交易金额'].sum() # 交易金额 for i in set(subData[1]['用户uid']): if i not in yesBeforUconsume: newUser.append(i) # 保存到表格 detail_sheet1.range('A1').options(transpose=True).value = '消费' detail_sheet1.range('A2').options(transpose=True).value = '昨日:' + str(yesterday) detail_sheet1.range('B2').options(transpose=True).value = tradeTotal detail_sheet1.range('C2').options(transpose=True).value = tradeAmount detail_sheet1.range('D2').options(transpose=True).value = tradeNum detail_sheet1.range('E2').options(transpose=True).value = len(newUser) wb1.save() if subData[0] == '提现': newUser = [] tradeTotal = len(subData[1]['交易订单ID']) # 交易笔数 tradeNum = len(set(subData[1]['用户uid'])) # 人数 tradeAmount = subData[1]['交易金额'].sum() # 交易金额 for i in set(subData[1]['用户uid']): if i not in yesBeforUdraw: newUser.append(i) detail_sheet1.range('J2').options(transpose=True).value = '昨日(7.16)' detail_sheet1.range('J1').options(transpose=True).value = '提现' detail_sheet1.range('K2').options(transpose=True).value = tradeTotal detail_sheet1.range('L2').options(transpose=True).value = tradeAmount detail_sheet1.range('M2').options(transpose=True).value = tradeNum detail_sheet1.range('N2').options(transpose=True).value = len(newUser) wb1.save()
import os import xlrd import xlwt import xlwings as xw os.chdir(r'C:\Users\Administrator\Desktop\新建文件夹') filename = '干部年报.xls' wb = xw.Book(filename) sheet = xw.Sheet('1总名册') print(sheet) last_row_index = xw.Range('A6').expand('table').last_cell.row print(last_row_index) rn = 'A6:S' + str(last_row_index - 1) work_deail = xw.Range(rn).value print(work_deail) wb.close() filename2 = '测试汇总表格.xlsx' wb2 = xw.Book(filename2) ws = xw.Sheet('sheet1') last_row_index1 = xw.Range('A6').expand('table').last_cell.row