4週28日固定ではなく、月毎の勤務表の場合、たとえば2月29日がない場合もありえます。そういう場合のフォーマット対応も行いました。
ソース上では、one_month_modeで前回のコードに追加修正しています。
最初のDayの日の月と、当該セルの月が違ったら、ブランクにしたり背景色を白にしたり、という処理を追加で記述しています。
それとは別に、Pythonでハマった点は、listの代入は、参照コピーということです。
今月2=list(今月) で値コピーにしています。
def make_initial_format_improved(ws,one_month_mode=False):
if one_month_mode==True:
print("one_month mode formatting..")
day_str=daydef[今月[0]]
day=int(day_str[8:])#Day
if day !=1 :
print("Fatal Error. 今月 must start with 1.")
exit()
if len(今月)>31:
print("Fatal Error. Excessive 今月 Length.")
exit()
else:
print("nominal mode formatting..")
今月2=list(今月)#Deep Copy
if one_month_mode==True:
for m in range(31)[len(今月):]:
今月2.append(m+今月[0])
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()
ws.Columns(2).EntireColumn.ColumnWidth = 20
ws.Columns(3).EntireColumn.ColumnWidth = 10
ws.Columns(4).EntireColumn.ColumnWidth = 5
row_start=8
row=row_start
col_start=2
col=col_start
day_col_offset=3
persons=len(staffdef)
days=len(今月2)
phases=len(dayphase_list)
cols=phases*days
day_processed=0
row=row_start-2
#Draw Top Header
for day in 今月2:
col=col_start+day_col_offset+day_processed
for ph in range(phases):
ws.Columns(col+ph).EntireColumn.ColumnWidth = 2
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:]#Day
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'
if one_month_mode:
ws.Cells(row ,col).Formula='=if($AZ$2 <> Month('+get_relative_addr_str(ws,row-1,col)+'),"",Day('+get_relative_addr_str(ws,row-1,col)+'))'
else:
ws.Cells(row ,col).Formula='=Day('+get_relative_addr_str(ws,row-1,col)+')'
day_color_format2(ws,row,col,-1,one_month_mode)
merge_and_align(ws,row ,col,row,col+phases-1)
if one_month_mode:
#print("")
ws.Cells(row+1 ,col).Formula='=if($AZ$2 <> Month('+get_relative_addr_str(ws,row-1,col)+'),"",'+get_relative_addr_str(ws,row-1,col)+')'
else:
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,one_month_mode)
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 今月2:
#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(今月2)-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,one_month_mode=False):
ws.Cells(row,col).FormatConditions.Delete()#Add(xlCellValue, xlGreaterEqual, '=85')
if one_month_mode:
ws.Cells(row,col).FormatConditions.Add(xlExpression, xlGreater,'=Month('+get_relative_addr_str(ws,row+row_offset,col)+')>$AZ$2')
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=2 #White
ws.Cells(row,col).FormatConditions(2).Interior.ColorIndex=20 #LightBlue
ws.Cells(row,col).FormatConditions(3).Interior.ColorIndex=22 #
else:
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 件のコメント:
コメントを投稿