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
