フォーマットルーチンの高速化を行いました。データ部に関しては、2次元配列による高速化手法が適用可能ですが、フォーマットについては、その手は使えません。しかし、フォーマットのみなので、コピぺ手法が適用可能です。行っているのは、
１）Person0, Day0でフォーマットを作成、ThisMonthにコピペ展開
２）１）で作成したフォーマットをAll_Staffにコピペ展開
これで、35人28日の場合、10秒程度となりました。1セルづつ書き込むのに比べて、体感10倍速となりました。
def post_main():
#pdb.set_trace()
print("Hello post main world!")
print('\n\n*********Processing Post operation *************\n')
import win32com.client#Import pywin32
phases=len(dayphase_list)
#
#Launch Excel
try :
xl = win32com.client.Dispatch("Excel.Application")
except:
print("can not invoke excel")
exit()
#Show working
xl.Visible = False
os.chdir(project_file_path)
file=os.path.join(project_file_path,"excel_post_export.xlsx")
wb = xl.Workbooks.Open(file)
# Excelsheet object
ws = wb.Worksheets(1)
make_initial_format_improved(ws)
#make_data_improved(ws,phases,True)
wb.Close(True)# True:Save False=Default:Not Save
# Excel Quit
xl.Quit()
print('\n\n*********Processed Post Operation.*************\n')
def clear(ws):
ws.UsedRange.ClearContents()
ws.UsedRange.ClearFormats()
def set_font(ws,addr,value):
ws.Range(addr).Value=value
ws.Range(addr).Font.Name = "Yu Gothic UI"
# Set Cell font size
ws.Range(addr).Font.Size = 14
# Set Bold
ws.Range(addr).Font.Bold = True
def make_initial_format_improved(ws):
clear(ws)
set_font(ws,"D2","Work Schedule")
set_font(ws,"AW2","")
#ws.Range("AW2").Formula='=Year(E7)'
ws.Range("AW2","AY2").Merge()
ws.Range("AW2").Font.Underline = xlUnderlineStyleSingle
ws.Range("AW1","AY1").Merge()
set_font(ws,"AW1",'Year')
#ws.Range("AZ2").Formula='=Month(E7)'
ws.Range("AZ2").Font.Underline = xlUnderlineStyleSingle
ws.Range("AZ2","BB2").Merge()
set_font(ws,"AZ1",'Month')
ws.Range("AZ2","BB2").Merge()
#ws.Range("BC2").Formula='=Month(E7)'
ws.Range("BC2").Font.Underline = xlUnderlineStyleSingle
ws.Range("BC2","BD2").Merge()
set_font(ws,"BC1",'Day')
ws.Range("BC1","BD1").Merge()
row_start=8
row=row_start
col_start=2
col=col_start
day_col_offset=3
persons=len(staffdef)
days=len(ThisMonth)
phases=len(dayphase_list)
cols=phases*days
day_processed=0
row=row_start-2
#Draw Top Header
for day in ThisMonth:
col=col_start+day_col_offset+day_processed
if day_processed==0:
day_str=daydef[day]
#print(day_str,int(day_str[8:]))
ws.Range("AW2").Formula='='+day_str[0:4]#Year
ws.Range("AZ2").Formula='='+day_str[5:7]#Month
ws.Range("BC2").Formula='='+day_str[8:]#Dat
print(get_relative_addr_str(ws,row-1,col))
ws.Cells(row-1,col).Formula='=Date(AW2,AZ2,BC2)'#
ws.Cells(row ,col).Formula='=Day('+get_relative_addr_str(ws,row-1,col)+')'
day_color_format2(ws,row,col,-1)
merge_and_align(ws,row ,col,row,col+phases-1)
ws.Cells(row+1 ,col).Formula='='+get_relative_addr_str(ws,row-1,col)
ws.Cells(row+1,col).NumberFormatLocal='aaa'
day_color_format2(ws,row+1,col,-2)
merge_and_align(ws,row+1,col,row+1,col+phases-1)
elif day_processed==phases:
ws.Cells(row-1,col).Formula='='+get_relative_addr_str(ws,row-1,col-phases)+'+1'
ws.Cells(row ,col).Formula='=Day('+get_relative_addr_str(ws,row-1,col)+')'
day_color_format2(ws,row,col,-1)
merge_and_align(ws,row ,col,row,col+phases-1)
ws.Cells(row+1 ,col).Formula='='+get_relative_addr_str(ws,row-1,col) #'=Day('+get_relative_addr_str(ws,row-1,col)+')'
ws.Cells(row+1,col).NumberFormatLocal='aaa'
day_color_format2(ws,row+1,col,-2)
merge_and_align(ws,row+1,col,row+1,col+phases-1)
else:
ws.Range(ws.Cells(row-1,col-phases),ws.Cells(row-1,col-1)).Copy(ws.Range(ws.Cells(row-1,col),ws.Cells(row-1,col)))
ws.Range(ws.Cells(row,col-phases),ws.Cells(row+1,col-1)).Copy(ws.Range(ws.Cells(row,col),ws.Cells(row,col)))
day_processed +=phases
#Draw Person Property and Data Area
row=row_start
for person in All_Staff:
day_processed=0
row =row_start+person*4
if person==All_Staff[0]:
#name=staffdef[person]
#ws.Cells(row,col_start).Value=name
col=col_start
merge_and_align(ws,row,col,row+3,col)
ws.Cells(row,col+1).Value='Preferred'
merge_and_align(ws,row,col+1,row+1,col+1)
ws.Cells(row+2,col+1).Value='Sc3 Solution'
merge_and_align(ws,row+2,col+1,row+3,col+1)
ws.Cells(row,col+2).Value='Shift'
merge_and_align(ws,row,col+2,row,col+2)
ws.Cells(row+2,col+2).Value='Shift'
merge_and_align(ws,row+2,col+2,row+2,col+2)
ws.Cells(row+1,col+2).Value='Task'
merge_and_align(ws,row+1,col+2,row+1,col+2)
ws.Cells(row+3,col+2).Value='Task'
merge_and_align(ws,row+3,col+2,row+3,col+2)
for day in ThisMonth:
#print("row=",row,"row_start=",row_start)
col=col_start+day_col_offset+day_processed
merge_and_align(ws,row,col,row,col+phases-1)
merge_and_align(ws,row+2,col,row+2,col+phases-1)
ws.Range(ws.Cells(row,col),ws.Cells(row+3,col+phases-1)).Borders.LineStyle = xlContinuous
ws.Range(ws.Cells(row,col),ws.Cells(row+3,col+phases-1)).Borders.weight=xlThin
day_processed +=phases
else:
data_start_col=col_start
data_end_col=data_start_col+day_col_offset+phases*len(ThisMonth)-1
ws.Range(ws.Cells(row-4,data_start_col),ws.Cells(row-1,data_end_col)).Copy(ws.Range(ws.Cells(row,data_start_col),ws.Cells(row,data_start_col)))
#Fill in Staff names
list2 = [['' for i in range(1)] for j in range(len(All_Staff)*4)]#Constructor
#wk=ws.Range(ws.Cells(row_start,col_start),ws.Cells(row_start+4*len(All_Staff)-1,col_start)).Value#Values ->2 dim array
print("list2=",len(list2))
#[[],[],[]...
for person in All_Staff:
list2[person*4][0]=staffdef[person]
ws.Range(ws.Cells(row_start,col_start),ws.Cells(row_start+4*len(All_Staff)-1,col_start)).Value=list2#Restore it
ws.Rows(5).EntireRow.Hidden = True#Hide intemidiate Results
def get_relative_addr_str(ws,row,col):
str=ws.Cells(row,col).address
str=str.replace('$','')
return str
def day_color_format2(ws,row,col,row_offset):
ws.Cells(row,col).FormatConditions.Delete()#Add(xlCellValue, xlGreaterEqual, '=85')
ws.Cells(row,col).FormatConditions.Add(xlExpression, xlEqual, '=Weekday('+get_relative_addr_str(ws,row+row_offset,col)+')=7') #SAT:7 Sun:1
ws.Cells(row,col).FormatConditions.Add(xlExpression, xlEqual, '=Weekday('+get_relative_addr_str(ws,row+row_offset,col)+')=1') #SAT:7 Sun:1
ws.Cells(row,col).FormatConditions(1).Interior.ColorIndex=20 #LightBlue
ws.Cells(row,col).FormatConditions(2).Interior.ColorIndex=22 #
def merge_and_align(ws,row_start1,col_start1,row_end1,col_end1,weight=xlThin):
print(row_start1,col_start1,row_end1,col_end1)
ws.Range(ws.Cells(row_start1,col_start1), ws.Cells(row_end1,col_end1)).Merge()
ws.Range(ws.Cells(row_start1,col_start1), ws.Cells(row_end1,col_end1)).VerticalAlignment = xlCenter
ws.Range(ws.Cells(row_start1,col_start1), ws.Cells(row_end1,col_end1)).HorizontalAlignment = xlCenter
ws.Range(ws.Cells(row_start1,col_start1), ws.Cells(row_end1,col_end1)).Borders.LineStyle = xlContinuous
ws.Range(ws.Cells(row_start1,col_start1), ws.Cells(row_end1,col_end1)).Borders.Weight = weight
0 件のコメント:
コメントを投稿