整合營銷服務商
電腦端+手機端+微信端=數據同步管理
免費咨詢熱線：
電腦端+手機端+微信端=數據同步管理
免費咨詢熱線：
位好，在前面有一期作品中，我們曾經在Excel中實現了萬年歷的制作，當時也有很多網友看過我的那期頭條文章或頭條視頻，附帶有評價和收藏，在此向你們表示感謝。
另外，有個名叫“斑斕虎zcy”的粉絲評論說我做的萬年歷如果含有農歷就完美了，對這位粉絲的提議我欣然接受，另外，雖然我沒采用“斑斕虎zcy”粉絲提供的公歷轉農歷的關鍵技術模版，但對“斑斕虎zcy”粉絲的熱心表示深深的謝意！
剛剛上一期，我為大家分享了自己弄的公歷<---->農歷互轉的技術與方法，也有很多網友看了這期頭條文章或視頻，說明大家對這個方法也很認可的，謝謝各位啦！接下來，我準備用兩種公歷<---->農歷互轉的方法實現帶農歷的萬年歷設計吧！為了區分起見，我們暫定本期的題目為“頭條文章--Excel中帶農歷的萬年歷設計方法一”、下期作品的題目為“頭條文章--Excel中帶農歷的萬年歷設計方法二”。
本期，我們先來用第一種方法實現吧。
一、Excel前端帶農歷萬年歷界面設計
關于界面的設計，這里和上次那一期萬年歷的界面一樣，這里不做過多描述，這里就只以截圖直接呈現給各位吧。如下圖所示
圖1 帶農歷的萬年歷界面
二、用方法一實現帶農歷萬年歷的功能代碼
模塊1中代碼如下：
'強勢自定義“公歷”<---->“農歷”互轉函數
'原創：互聯網
'修正：號作者“跟我學Office高級辦公應用” 2019/10/12
'---農歷數據定義---
'先以 Hexadecimal_To_Binary 函數還原成長度為 18 的字符串，其定義如下：
'前12個字節代表1-12月：1為大月，0為小月；壓縮成十六進制(1-3位)
'第13位為閏月的情況，1為大月30天，0為小月29天；(4位)
'第14位為閏月的月份，如果不是閏月為0，否則給出月份(5位)
'最后4位為當年農歷新年的公歷日期，如0131代表1月31日；當作數值轉十六進制(6-7位)
'定義如下農歷(陰歷)日期常量(1899～2100，共202年，但是事實上我們只需要用到1900～2100這201年即可）
Private Const ylData="AB500D2,4BD0883," _
& "4AE00DB,A5700D0,54D0581,D2600D8,D9500CC,655147D,56A00D5,9AD00CA,55D027A,4AE00D2," _
& "A5B0682,A4D00DA,D2500CE,D25157E,B5500D6,56A00CC,ADA027B,95B00D3,49717C9,49B00DC," _
& "A4B00D0,B4B0580,6A500D8,6D400CD,AB5147C,2B600D5,95700CA,52F027B,49700D2,6560682," _
& "D4A00D9,EA500CE,6A9157E,5AD00D6,2B600CC,86E137C,92E00D3,C8D1783,C9500DB,D4A00D0," _
& "D8A167F,B5500D7,56A00CD,A5B147D,25D00D5,92D00CA,D2B027A,A9500D2,B550781,6CA00D9," _
& "B5500CE,535157F,4DA00D6,A5B00CB,457037C,52B00D4,A9A0883,E9500DA,6AA00D0,AEA0680," _
& "AB500D7,4B600CD,AAE047D,A5700D5,52600CA,F260379,D9500D1,5B50782,56A00D9,96D00CE," _
& "4DD057F,4AD00D7,A4D00CB,D4D047B,D2500D3,D550883,B5400DA,B6A00CF,95A1680,95B00D8," _
& "49B00CD,A97047D,A4B00D5,B270ACA,6A500DC,6D400D1,AF40681,AB600D9,93700CE,4AF057F," _
& "49700D7,64B00CC,74A037B,EA500D2,6B50883,5AC00DB,AB600CF,96D0580,92E00D8,C9600CD," _
& "D95047C,D4A00D4,DA500C9,755027A,56A00D1,ABB0781,25D00DA,92D00CF,CAB057E,A9500D6," _
& "B4A00CB,BAA047B,B5500D2,55D0983,4BA00DB,A5B00D0,5171680,52B00D8,A9300CD,795047D," _
& "6AA00D4,AD500C9,5B5027A,4B600D2,96E0681,A4E00D9,D2600CE,EA6057E,D5300D5,5AA00CB," _
& "76A037B,96D00D3,4AB0B83,4AD00DB,A4D00D0,D0B1680,D2500D7,D5200CC,DD4057C,B5A00D4," _
& "56D00C9,55B027A,49B00D2,A570782,A4B00D9,AA500CE,B25157E,6D200D6,ADA00CA,4B6137B," _
& "93700D3,49F08C9,49700DB,64B00D0,68A1680,EA500D7,6AA00CC,A6C147C,AAE00D4,92E00CA," _
& "D2E0379,C9600D1,D550781,D4A00D9,DA400CD,5D5057E,56A00D6,A6C00CB,55D047B,52D00D3," _
& "A9B0883,A9500DB,B4A00CF,B6A067F,AD500D7,55A00CD,ABA047C,A5A00D4,52B00CA,B27037A," _
& "69300D1,7330781,6AA00D9,AD500CE,4B5157E,4B600D6,A5700CB,54E047C,D1600D2,E960882," _
& "D5200DA,DAA00CF,6AA167F,56D00D7,4AE00CD,A9D047D,A2D00D4,D1500C9,F250279,D5200D1"
'定義農歷 (陰歷)每月的漢字大寫日期“天”
Private Const ylMd0="初一初二初三初四初五初六初七初八初九初十十一十二十三十四十五" _
& "十六十七十八十九二十廿一廿二廿三廿四廿五廿六廿七廿八廿九三十 "
'定義農歷 (陰歷)一年中的漢字大寫日期“月”
Private Const ylMn0="正二三四五六七八九十冬臘"
'定義農歷 (陰歷)年中的“天干”(如：甲乙丙丁......等)
Private Const ylTianGan0="甲乙丙丁戊已庚辛壬癸"
'定義農歷 (陰歷)年中的“地支”(如：子丑寅卯辰......等)
Private Const ylDiZhi0="子丑寅卯辰巳午未申酉戌亥"
'定義農歷 (陰歷)年中的“屬相”(如：鼠牛虎兔龍......等)
Private Const ylShu0="鼠牛虎兔龍蛇馬羊猴雞狗豬"
Public shp_year_select As Shape, y '定義公有全局變量年份選擇組合框shp_year_select和用于存儲選擇的年份變量y，以便所有的過程都可以調用和回傳數據
Sub Run_Fill_Calender() '運行填充日歷
[b4].Select
n=shp_year_select.ControlFormat.Value
y=shp_year_select.ControlFormat.List(n)
[O1]=y & " 年歷" & "[" & Mid(GetYLDate(y & "-6-1"), 4, 6) & "]"
Fill_Calender_Datas '調用“填充日歷數據”過程
[a65535]=y '將選擇過的年份存儲在單元格"A65535"中
End Sub
Sub Fill_Calender_Datas() '填充日歷數據
Dim rg(1 To 12) As Range '定義12個元素的的范圍區域對象數組
'為區域對象數組的每個區域對象元素對象指派這12個區域對象具體的實體
Set rg(1)=[b5:h10]: Set rg(2)=[j5:p10]: Set rg(3)=[r5:x10]: Set rg(4)=[z5:af10]
Set rg(5)=[b15:h20]: Set rg(6)=[j15:p20]: Set rg(7)=[r15:x20]: Set rg(8)=[z15:af20]
Set rg(9)=[b25:h30]: Set rg(10)=[j25:p30]: Set rg(11)=[r25:x30]: Set rg(12)=[z25:af30]
For i=1 To 12
Select Case i
Case 1, 3, 5, 7, 8, 10, 12: days_31 y, i, rg(i)
Case 4, 6, 9, 11: days_30 y, i, rg(i)
Case 2: days_29_Or_28 y, i, rg(i)
End Select
Next
End Sub
Sub Erse_Calender_Datas() '清空日歷數據
Dim rg As Range
Set rg=[5:10,15:20,25:30]
[b4].Select
rg.ClearContents
[O1]="---- 年歷[-----年]"
yr=Year(Date)
'以下是定位當今日期的年份在表單組合框中顯示
For i=1 To shp_year_select.ControlFormat.ListCount
If yr=Val(shp_year_select.ControlFormat.List(i)) Then
n=i
Exit For
End If
Next
shp_year_select.ControlFormat.ListIndex=n
End Sub
Sub days_31(y, m, r As Range) '月大--31天
Dim da As Date, d
r.ClearContents
week_str="日一二三四五六"
d=1
da=CDate(y & "-" & m & "-" & d) '將字符串動態轉換為真正的日期
ws=Mid(Format(da, "[$-804]aaaa"), 3) '從轉換為星期XX的字符串中提取大寫星期幾的漢字保存在ws中
First_Day_Pos_In_Week_Area=InStr(week_str, ws) '每月初始的1號在日歷星期區域的定位位置
For d=1 To 31
da=CDate(y & "-" & m & "-" & d) '將字符串動態轉換為真正的日期
ws=Mid(Format(da, "[$-804]aaaa"), 3) '從轉換為星期XX的字符串中提取大寫星期幾的漢字保存在ws中
Other_Day_Pos_In_Week_Area=InStr(week_str, ws)
'實際的每月的號數應該加上每月初始的1號在日歷星期區域的定位位置減去1“”d + (First_Day_Pos_In_Week_Area - 1)，為了在第7個位置仍然將該號 _
數放在該行，所以還得再減去1“d + (First_Day_Pos_In_Week_Area - 1) - 1”，然后再除7取整，同時乘以7后加上該號數在日歷中星期區域的實際列數 _
位置，即可得到該號數在日歷區域的設計位置
p=Int((d + (First_Day_Pos_In_Week_Area - 1) - 1) / 7) * 7 + Other_Day_Pos_In_Week_Area
yl_md=Right(GetYLDate(da), 4) '調用轉農歷(陰歷)函數，取后四個漢字月日日期字符
yl_m=Left(yl_md, 2) '拆解陰歷月日中的月份
yl_d=Right(yl_md, 2) '拆解陰歷月日中的日子
If yl_d="初一" Then yl_d=yl_m '若拆解的日子是“初一”，則即刻用該月的月份替代該陰歷月份的首個日子
r(p)=d & Chr(10) & yl_d '將公歷日期和對應的農歷日期合在一起填入到p處正確位置
If da=Date Then r(p).Select '若選擇年份后不斷瞬時生成的日期da和現在的日期匹配，則將當前填充的日期單元格選擇成活動狀態
Next
End Sub
Sub days_30(y, m, r As Range) '月小--30天
Dim da As Date, d
r.ClearContents
week_str="日一二三四五六"
d=1
da=CDate(y & "-" & m & "-" & d) '將字符串動態轉換為真正的日期
ws=Mid(Format(da, "[$-804]aaaa"), 3) '從轉換為星期XX的字符串中提取大寫星期幾的漢字保存在ws中
First_Day_Pos_In_Week_Area=InStr(week_str, ws) '每月初始的1號在日歷星期區域的定位位置
For d=1 To 30
da=CDate(y & "-" & m & "-" & d) '將字符串動態轉換為真正的日期
ws=Mid(Format(da, "[$-804]aaaa"), 3) '從轉換為星期XX的字符串中提取大寫星期幾的漢字保存在ws中
Other_Day_Pos_In_Week_Area=InStr(week_str, ws)
'實際的每月的號數應該加上每月初始的1號在日歷星期區域的定位位置減去1“”d + (First_Day_Pos_In_Week_Area - 1)，為了在第7個位置仍然將該號 _
數放在該行，所以還得再減去1“d + (First_Day_Pos_In_Week_Area - 1) - 1”，然后再除7取整，同時乘以7后加上該號數在日歷中星期區域的實際列數 _
位置，即可得到該號數在日歷區域的設計位置
p=Int((d + (First_Day_Pos_In_Week_Area - 1) - 1) / 7) * 7 + Other_Day_Pos_In_Week_Area
yl_md=Right(GetYLDate(da), 4) '調用轉農歷(陰歷)函數，取后四個漢字月日日期字符
yl_m=Left(yl_md, 2) '拆解陰歷月日中的月份
yl_d=Right(yl_md, 2) '拆解陰歷月日中的日子
If yl_d="初一" Then yl_d=yl_m '若拆解的日子是“初一”，則即刻用該月的月份替代該陰歷月份的首個日子
r(p)=d & Chr(10) & yl_d '將公歷日期和對應的農歷日期合在一起填入到p處正確位置
If da=Date Then r(p).Select '若選擇年份后不斷瞬時生成的日期da和現在的日期匹配，則將當前填充的日期單元格選擇成活動狀態
Next
End Sub
Sub days_29_Or_28(y, m, r As Range) '閏年2月份29天，平年2月份28天(例如2020年就是閏年)
Dim da As Date, d
r.ClearContents
week_str="日一二三四五六"
d=1
da=CDate(y & "-" & m & "-" & d) '將字符串動態轉換為真正的日期
ws=Mid(Format(da, "[$-804]aaaa"), 3) '從轉換為星期XX的字符串中提取大寫星期幾的漢字保存在ws中
First_Day_Pos_In_Week_Area=InStr(week_str, ws) '每月初始的1號在日歷星期區域的定位位置
If Is_LeepYear(y) Then '閏年2月份天數
For d=1 To 29
da=CDate(y & "-" & m & "-" & d) '將字符串動態轉換為真正的日期
ws=Mid(Format(da, "[$-804]aaaa"), 3) '從轉換為星期XX的字符串中提取大寫星期幾的漢字保存在ws中
Other_Day_Pos_In_Week_Area=InStr(week_str, ws)
'實際的每月的號數應該加上每月初始的1號在日歷星期區域的定位位置減去1“”d + (First_Day_Pos_In_Week_Area - 1)，為了在第7個位置仍然將該 _
號數放在該行，所以還得再減去1“d + (First_Day_Pos_In_Week_Area - 1) - 1”，然后再除7取整，同時乘以7后加上該號數在日歷中星期區域的實 _
際列數位置，即可得到該號數在日歷區域的設計位置
p=Int((d + (First_Day_Pos_In_Week_Area - 1) - 1) / 7) * 7 + Other_Day_Pos_In_Week_Area
yl_md=Right(GetYLDate(da), 4) '調用轉農歷(陰歷)函數，取后四個漢字月日日期字符
yl_m=Left(yl_md, 2) '拆解陰歷月日中的月份
yl_d=Right(yl_md, 2) '拆解陰歷月日中的日子
If yl_d="初一" Then yl_d=yl_m '若拆解的日子是“初一”，則即刻用該月的月份替代該陰歷月份的首個日子
r(p)=d & Chr(10) & yl_d '將公歷日期和對應的農歷日期合在一起填入到p處正確位置
If da=Date Then r(p).Select '若選擇年份后不斷瞬時生成的日期da和現在的日期匹配，則將當前填充的日期單元格選擇成活動狀態
Next
Else '平年2月份天數
For d=1 To 28
da=CDate(y & "-" & m & "-" & d) '將字符串動態轉換為真正的日期
ws=Mid(Format(da, "[$-804]aaaa"), 3) '從轉換為星期XX的字符串中提取大寫星期幾的漢字保存在ws中
Other_Day_Pos_In_Week_Area=InStr(week_str, ws)
'實際的每月的號數應該加上每月初始的1號在日歷星期區域的定位位置減去1“”d + (First_Day_Pos_In_Week_Area - 1)，為了在第7個位置仍然將該 _
號數放在該行，所以還得再減去1“d + (First_Day_Pos_In_Week_Area - 1) - 1”，然后再除7取整，同時乘以7后加上該號數在日歷中星期區域的實 _
際列數位置，即可得到該號數在日歷區域的設計位置
p=Int((d + (First_Day_Pos_In_Week_Area - 1) - 1) / 7) * 7 + Other_Day_Pos_In_Week_Area
yl_md=Right(GetYLDate(da), 4) '調用轉農歷(陰歷)函數，取后四個漢字月日日期字符
yl_m=Left(yl_md, 2) '拆解陰歷月日中的月份
yl_d=Right(yl_md, 2) '拆解陰歷月日中的日子
If yl_d="初一" Then yl_d=yl_m '若拆解的日子是“初一”，則即刻用該月的月份替代該陰歷月份的首個日子
r(p)=d & Chr(10) & yl_d '將公歷日期和對應的農歷日期合在一起填入到p處正確位置
If da=Date Then r(p).Select '若選擇年份后不斷瞬時生成的日期da和現在的日期匹配，則將當前填充的日期單元格選擇成活動狀態
Next
End If
End Sub
Function Is_LeepYear(y) As Boolean '給定的年份是否為閏年LeepYear的判斷
If (y Mod 400=0) Or (y Mod 100 <> 0 And y Mod 4=0) Then
Is_LeepYear=True
Else
Is_LeepYear=False
End If
End Function
'自定義“公歷轉農歷”日期函數
Function GetYLDate(ByVal strDate As String) As String
On Error GoTo ExitFunction_Label
If Not IsDate(strDate) Then Exit Function '如果參數strDate非日期的無效字符串，則退出本函數工作
'定義setDate--設置的未來日期，tYear--未來日期的本年份，tMonth--本月份，tDay--本日子
Dim setDate As Date, tYear As Integer, tMonth As Integer, tDay As Integer
setDate=CDate(strDate) '為該GetYLDate()函數參數的字符串轉換后的日期賦予設定的日期
tYear=Year(setDate): tMonth=Month(setDate): tDay=Day(setDate) '年、月、日分別取值
'如果不是有效有日期，退出
If tYear > 2100 Or tYear < 1900 Then Exit Function
'定義daList()--是元素為18位日期二進制字符串數組，conDate--農歷新年日期，thisMonths--本年的二進制 _
月份信息(可能包含閏月)
Dim daList() As String * 18, conDate As Date, thisMonths As String
'定義AddYear--是相對1900年遞增的年，AddMonth--月份增量，AddDay--天數增量，getDay--農歷新年和設 _
之日期相差天數
Dim AddYear As Integer, AddMonth As Integer, AddDay As Integer, getDay As Integer
'定義YLyear--農歷(陰歷)年的字符串，YLShuXing--農歷(陰歷)年的屬相
Dim YLyear As String, YLShuXing As String
'定義dd0--農歷(陰歷)年的陰歷日子，mm0--農歷(陰歷)年的陰歷月，ganzhi()--每個元素為2個字符的天干地 _
支數組
Dim dd0 As String, mm0 As String, ganzhi(0 To 59) As String * 2
'定義RunYue--農歷(陰歷)年是否閏月的布爾型標志，RunYue1--農歷(陰歷)年閏月月份
Dim RunYue As Boolean, RunYue1 As Integer, mDays As Integer, i As Integer
'加載2年內的農歷數據
ReDim daList(tYear - 1 To tYear)
daList(tYear - 1)=Hexadecimal_To_Binary(Mid(ylData, (tYear - 1900) * 8 + 1, 7))
daList(tYear)=Hexadecimal_To_Binary(Mid(ylData, (tYear - 1900 + 1) * 8 + 1, 7))
AddYear=tYear
initYL:
AddMonth=CInt(Mid(daList(AddYear), 15, 2))
AddDay=CInt(Mid(daList(AddYear), 17, 2))
conDate=DateSerial(AddYear, AddMonth, AddDay) '農歷新年日期
getDay=DateDiff("d", conDate, setDate) + 1 '相差天數
If getDay < 1 Then AddYear=AddYear - 1: GoTo initYL
thisMonths=Left(daList(AddYear), 14) '前14位為本年的二進制月份信息(可能有閏月)存于thisMonths中
RunYue1=Val("&H" & Right(thisMonths, 1)) '閏月月份
If RunYue1 > 0 Then '如果有閏月，則立即修正本年的二進制月份信息thisMonths，形成真正有效的二進制序 _
列信息
thisMonths=Left(thisMonths, RunYue1) & Mid(thisMonths, 13, 1) & Mid(thisMonths, RunYue1 + 1)
End If
thisMonths=Left(thisMonths, 13) '最后一次修正本年的二進制月份信息thisMonths，直接取13個月的情況
For i=1 To 13 '遍歷1～13個月，找到并計算含閏月的有效天數，同時退出循環
mDays=29 + CInt(Mid(thisMonths, i, 1))
If getDay > mDays Then
getDay=getDay - mDays
Else
If RunYue1 > 0 Then '如果有閏月，則進一步根據i的值情況做如下處理
If i=RunYue1 + 1 Then RunYue=True '若i確系為閏月，則將閏月標志置為真
If i > RunYue1 Then i=i - 1 '若i大于閏月月份，則將將i回退修正
End If
AddMonth=i '最終記錄下i作為真正的增量月份存入AddMonth
AddDay=getDay '同時，將得到的天數差作為增量天數
Exit For
End If
Next
dd0=Mid(ylMd0, (AddDay - 1) * 2 + 1, 2) '用查找表的形式定位當前日期對應的農歷(陰歷)日子
mm0=Mid(ylMn0, AddMonth, 1) + "月" '用查找表的形式定位當前日期對應的農歷(陰歷)月份
For i=0 To 59 '0～59表示60年一個甲子，表示以60年一個輪回的形式，通過查找表精準定位每年的天干地支
ganzhi(i)=Mid(ylTianGan0, (i Mod 10) + 1, 1) + Mid(ylDiZhi0, (i Mod 12) + 1, 1)
Next
YLyear=ganzhi((AddYear - 4) Mod 60) '通過查找表形式得出陰歷年的天干地支表示形式
YLShuXing=Mid(ylShu0, ((AddYear - 4) Mod 12) + 1, 1) '通過查找表形式得出陰歷年的屬相表示形式
If RunYue Then mm0="閏" & mm0 '如果某陰歷月份有閏月，特別加上“閏X月”的形式
GetYLDate="農歷：" & YLyear & "(" & YLShuXing & ")年" & mm0 & dd0 '拼接當前日期的完整農歷信息
ExitFunction_Label:
End Function
'將壓縮的陰歷字符還原
Private Function Hexadecimal_To_Binary(ByVal strHex As String) As String '十六進制轉二進制
Dim i As Integer, i1 As Integer, tmpV As String
Const hStr="0123456789ABCDEF"
Const bStr="0000000100100011010001010110011110001001101010111100110111101111"
tmpV=UCase(Left(strHex, 3))
'以下是十六進制轉二進制的具體操作
For i=1 To Len(tmpV)
i1=InStr(hStr, Mid(tmpV, i, 1))
Hexadecimal_To_Binary=Hexadecimal_To_Binary & Mid(bStr, (i1 - 1) * 4 + 1, 4)
Next
Hexadecimal_To_Binary=Hexadecimal_To_Binary & Mid(strHex, 4, 2)
'十六進制轉十進制
Hexadecimal_To_Binary=Hexadecimal_To_Binary & "0" & CStr(Val("&H" & Right(strHex, 2)))
End Function
ThisWorkbook中代碼如下：
Private Sub Workbook_Open() '工作簿一打開即刻初始化表單組合框數據并且在組合框中顯示之前選擇過的年份
Set shp_year_select=Sheets(1).Shapes("年份選擇")
shp_year_select.ControlFormat.RemoveAllItems
'萬年歷的年份范圍初步設定為“1900～2100”
For i=1900 To 2100
shp_year_select.ControlFormat.AddItem i
Next
'以下是重新還原表單組合框控件之前選定過的年份顯示
yr=[a65535]
For i=1 To shp_year_select.ControlFormat.ListCount
If yr=Val(shp_year_select.ControlFormat.List(i)) Then
n=i '遍歷整個表單組合框所有元素，查找與yr是否相匹配的元素，若找到即刻記下該編號并存于n中
Exit For
End If
Next
shp_year_select.ControlFormat.ListIndex=n '讓表單組合框顯示找到的之前選擇過的年份
End Sub
三、用方法一實現帶農歷萬年歷運行效果測試
(一)選擇年份，呈待生成帶農歷萬年歷狀態。如下圖所示
圖2 選擇年份準備生成帶農歷萬年歷
(二)點擊選擇的年份，生成實實在在的帶農歷的萬年歷。如下圖所示
圖3 生成帶農歷萬年歷效果
(三)壓下<清除日歷數據>按鈕，準備進行帶農歷的萬年歷數據清除。如下圖所示
圖4 準備清除帶農歷萬年歷數據
(四)壓下狀態下的<清除日歷數據>按鈕情況下點擊該按鈕，完成帶農歷萬年歷數據的清除，并將年份組合框內的顯示提示年份置為最新當前時間的年份。如下圖所示
圖5 清除帶農歷萬年歷數據結果
四、技術亮點小結
(一)充分利用尋找農歷閏月方法和壓縮的農歷字符還原方法完成公歷轉農歷
(二)在定位Excel的萬年歷數據填充單元格時，用字符串處理函數處理農歷生成的數據
(三)存儲記憶上次打開萬年歷的數據
好了，本期我們就分享到這里吧，希望大家喜歡和收藏哦！
最后，還是感謝大家的持續關注(頭條號：跟我學Office高級辦公)、推廣、點評哦！謝謝大家繼續關注下期第二中方法實現帶農歷的萬年歷設計！
金流量表（Cash Flow Statement），是指反映企業在一定會計期間現金和現金等價物流入和流出的報表。現金流量表是企業財務報表的三個基本報告之一（另外兩個是資產負債表和損益表）。
為了全面系統地揭示企業一定時期的財務狀況、經營成果和現金流量，財務報表需按財政部會計準則的標準格式設計，因此，財務報表的典型特征是數據更新頻繁、分析維度多、數據來源復雜，常規的報表工具很難同時滿足上述所有需求。
借助葡萄城 純前端表格控件 SpreadJS 和 服務端表格組件 GcExcel 來設計財務報表模板，可以在滿足財務數據展示、計算、決策分析的同時，提供如 Excel 一般的使用體驗，并可直接復用財務系統原始的 Excel 報表模板，減少從本地到線上的數據遷移工作量。
葡萄城表格技術產品 SpreadJS 和 GcExcel 兼容 Excel 數據格式，在設計財務報表模板時，可以為用戶提供高度類似 Excel 的使用體驗；在分析財務數據時，可以提供超過 450 種計算公式和 32 種圖表類型，既可滿足用戶自定義、跨表格引用、異步調用等多場景計算需求，又可實現豐富的數據可視化效果，建立如 Excel 般強大的數據分析能力。
葡萄城表格組件突破了 Excel 對數據處理性能的限制，通過接口調用，很容易就可以實現實時數據更新。
SpreadJS 組件包含的在線表格編輯器提供了類 Excel 的 UI 設計元素，可直接在 Angular、 React、 Vue 等前端框架中調用，實現高效的模板設計、在線編輯和數據綁定，為最終用戶帶來更為流暢的使用體驗。
無需借助后臺代碼和第三方組件，SpreadJS 和 GcExcel 均支持導入導出 Excel 格式的報表，單元格級別的操作顆粒度使報表自定義變得更加輕松。
財務報表設計過程中，可繼續沿用 Excel 的設計模式，可同步識別 Excel 的樣式（字體、字號、顏色等）、數據結構、數據透視表等內容，模板設計的成本更低、財務系統更易用。
通過二次開發，組件可支持報表多 Sheet 頁展示，可將一個期間內的多張不同報表展現在同一個報表文件中，同步數據、同步查看和同步存檔。
本博客將學習如何使用類似 Excel 的 JavaScript 電子表格解決方案 SpreadJS 在前端創建現金流日歷。此日歷將廣泛使用以下強大功能：
1. 動態數組公式 - 根據一個公式將多個結果返回到一系列單元格。此示例使用 SEQUENCE 和 FILTER 函數。
2. RANGEBLOCKSPARKLINE(template_range, data_expr) - 此迷你圖允許開發人員將單元格范圍模板 (template_range) 定義為單個單元格類型，并將該模板應用于單元格以將一組數據 (data_expr) 加載到模板中。該模板可以包括多行和/或多列。
最終效果如圖所示：
要創建我們的現金流日歷，我們需要創建如下所述的三張表：
1. 數據源表
2. 模板表
3. 現金流日歷：渲染表
我們示例的數據源是交易列表。
我們創建了一個更動態的表格，當我們需要數據而不是單元格范圍時，我們可以引用 Table1。
此表包含有關 TransactionID、交易類型、交易日期、公司名稱、帳戶名稱、存款金額和取款的信息。
此頁面包含我們將用來呈現現金流日歷中發生的交易的模板范圍。
此處的此單元格范圍將用作包含現金流日歷中所需信息的單元格的模板。
我們要做的第一件事是排列單元格，然后設置單元格的綁定路徑。
它可以通過 Javascript 使用 SpreadJS setBindingPath 方法來完成。
templateSheet.setBindingPath(0, 1, "month");
templateSheet.setBindingPath(1, 2, "date");
templateSheet.setBindingPath(2, 2, "start");
templateSheet.setBindingPath(3, 2, "withdrawals");
templateSheet.setBindingPath(4, 2, "deposits");
templateSheet.setBindingPath(5, 2, "end");當然，上邊這步操作也有不用寫代碼的方法——用SpreadJS設計器，下載SpreadJS安裝包，在下載的安裝包中，從“\SpreadJS.Release.x.x.x\Designer\Designer Runtime”路徑下找到設計器的安裝包，完成安裝后，按照下列步驟操作：
1. 單擊數據選項卡上的模板菜單 - 字段列表面板將出現在右側
2. 將鼠標懸停在 Start 分支上并通過單擊綠色 + 按鈕添加字段 *請注意，你可以使用“x”按鈕刪除字段并使用位于分支右側的設置修改這些字段
3. 拖動模板范圍所需單元格中的字段
為了使現金短缺（期末余額為負）的日子可以用紅色著色，期末余額為正的日子用綠色著色，中性的用黑色著色，我們可以使用條件格式。在設計器上可以這樣操作：
1. 在合并時選擇日期單元格“A2:D2”
2. 條件格式 → 新規則
3. 通常，鍵入并選擇使用公式來確定要格式化的單元格
4. 輸入你的公式，在我們的例子中='Cell Template'!$C>0
5. 單擊格式→填充→選擇綠色作為字體顏色
6. 重復相同的步驟，但使用公式：='Cell Template'!$C<0 *請注意，對于余額為負的情況，顏色應設置為紅色
第 1 步：添加 MonthPicker 元素
我們日歷的第一個元素是可變月份元素。要添加它，請使用 MonthPicker，這是 SpreadJS 中的一種下拉單元格樣式。
JavaScript:
var monthPickerStyle=new GC.Spread.Sheets.Style();
monthPickerStyle.dropDowns=[
{
type: GC.Spread.Sheets.DropDownType.monthPicker,
option: {
startYear: 2019,
stopYear: 2021,
height: 300,
}
}
];
sheet.setStyle(2, 5, monthPickerStyle);
SpreadJS設計器：
選擇單元格（在我們的例子中為 B2）
1. 主頁選項卡 → 單元格下拉菜單 → 月份選擇器
2. 在命令右側，單擊...
3. 設置選取器的開始、結束年份和高度
然后，我們在進行計算時為包含月份的單元格指定一個名稱。
1. 在公式選項卡上，選擇名稱管理器
2. 在彈出窗口中，單擊新建按鈕
3. 設置單元格的名稱。在我們的示例中：name: currentMonth
參考：$D。你還可以添加評論并更改引用對象
第 2 步：創建現金流日歷
使用 SEQUENCE(rows,columns,start,step) 函數來分配我們日歷中的日期。這允許我們稍后在 CellClick 上檢索單元格值。 B4 單元格的公式為：
=SEQUENCE(6,7,currentMonth-WEEKDAY(currentMonth)+1,1)JavaScript:
cashflowSheet.setFormula(3, 1, '=SEQUENCE(6,7,currentMonth-WEEKDAY(currentMonth)+1,1)');我們還沒有為這些單元格使用格式化程序。
下一步是使用條件格式來使屬于其他月份的日期成為可能，但所選日期為空白：
1. 選擇 B4:H9 然后選擇日歷的日期 → 條件格式
2. 從下拉列表中選擇新規則，然后選擇“使用公式確定要格式化為規則類型的單元格”
3. 輸入你的公式，在我們的例子中為“=MONTH(B4)<>MONTH(currentMonth)” - 此格式僅適用于月份與下拉列表中選擇的月份不同的單元格
4. 單擊格式
5. 編號 → 自定義
6. 輸入”;;;”作為格式化程序將所有正確的單元格設為空白
下面的步驟包括使用 RANGEBLOCKSPARKLINE，它將 TemplateSheet 中的單元格范圍用作單個單元格類型，并使用 OBJECT 函數將模板應用于代表我們現金流日歷中日期的所有單元格中。
由于我們使用 SEQUENCE 為這些單元格設置值，因此我們將使用 RANGEBLOCKSPARKLINE 作為格式。
1. 選擇單元格區域 B4:H9
2. 格式→更多數字格式→自定義
3. 將格式化程序設置為：
=RANGEBLOCKSPARKLINE('Cell Template'!$A:$D,OBJECT("date",@,"start",IFERROR(SUM(FILTER(Table1[Deposit],Table1[Date]<@))-SUM(FILTER(Table1[Withdrawal],Table1[Date]<@)),0),"withdrawals",IFERROR(SUM(FILTER(Table1[Withdrawal],Table1[Date]=@)),0),"deposits",IFERROR(SUM(FILTER(Table1[Deposit],Table1[Date]=@)),0),"month",MONTH($A)))作為第一個參數，它將單元格范圍作為 TemplateSheet 中的模板。
作為第二個參數，它需要一個 OBJECT，該 OBJECT 從位于數據源表的 Table1 中獲取數據。
1. [日期]：單元格的當前值
2. [開始]：之前所有存款的總和 - 之前所有提款的總和
3. [提款]：當前提款的總和
4. [存款]：當前存款的總和
5. [end]:[start] + 所有當前存款的總和 - 所有當前提款的總和
使用公式是綁定并返回一個范圍模板，以便更輕松地使用范圍模板。
這是最終輸出：
如上圖所示，包含日歷天數的單元格提供有關開始/結束余額、存款總額和提款總額的信息。
第 3 步：獲取每日交易
如果我們想從 DataSource 頁面中提取所有交易的列表，我們可以借助 SelectionChanged 事件。當這些事件發生時，SpreadJS 中的工作表將其事件綁定到特定操作。
在我們的示例中，當用戶從日歷中選擇日期時，我們使用了這個方便的 SpreadJS 功能來提取所有交易的列表。
我們為包含所選日期、存款和取款的單元格指定一個名稱，因為它更容易進行計算，并且表格將包含有關交易的信息。為 currentMonth 創建名稱范圍的步驟是：
1. 在公式選項卡上，選擇名稱管理器
2. 在彈出窗口中，單擊新建按鈕
3. 設置單元格的名稱
在我們的示例中：
name:當前選擇；refer to:='Cash-Flow'!$Bname:當前存款；refer to:=FILTER(tblTransactions[Type]:tblTransactions[Withdrawal],(tblTransactions[Date]=CurrentSelection)*(tblTransactions[Deposit]>0))name:當前取款；refer to:=FILTER(tblTransactions[Type]:tblTransactions[Withdrawal],(tblTransactions[Date]=CurrentSelection)*(tblTransactions[Withdrawal]>0))設置不同的公式來獲取所有存款列表、所有提款列表、結束和開始余額。
1. 起始余額（之前所有存款的總和 - 之前所有取款的總和）：=IFERROR((SUM(FILTER(tblTransactions[Deposit],tblTransactions[Date]<$B))-SUM(FILTER(tblTransactions[Withdrawal],tblTransactions [日期]<$B))),0)
2. 結束余額（起始余額 + 當前存款的總和 - 當前提款的總和）：=IFERROR(D13+(SUM(FILTER(tblTransactions[Deposit],tblTransactions[Date]=$B))-SUM(FILTER(tblTransactions[Withdrawal] ,tblTransactions[日期]=$B))),0)
其中 D13 是起始余額：
1. 存款：=IFERROR(FILTER(currentDeposits,{1,0,1,1,0}),"")
2. 取款：=IFERROR(FILTER(currentWithdrawals,{1,0,1,0,1}),"")
目前手動插入 currentSelection。要根據用戶日期選擇進行更改，請執行下一步。
在 JavaScript 中創建事件處理函數（見下文）：
// on day selection, update a cell used in filtering the data to show detailed transaction list
cashflowSheet.bind(GC.Spread.Sheets.Events.SelectionChanged, function (sender, args) {
const sheet=args.sheet;
const row=args.newSelections[0].row;
const col=args.newSelections[0].col;
if ((row < 3 || row >=3 + 6)
|| (col < 1 || col >=1 + 7))
return;
// set the current date cell so that FILTER would update.
sheet.setValue(10, 1, sheet.getValue(row, col));
});一旦用戶單擊單元格，上面的代碼就會檢查單元格是否在日歷邊界內 (B4:H9)。否則，它會更新 currentSelection，因此，所有用于獲取余額和有關交易信息的公式都會在它們指向更改的選定日期時給出正確的結果。
上面的示例是使用 SpreadJS 功能增強你的應用程序并將你的內容從一組簡單的數據轉換為一個引人入勝、超級有用的類似 Excel 的儀表板的眾多方法之一。
這個 JavaScript 組件提供了數百個統計和財務函數和公式，可幫助你在財務應用程序中輕松創建各種元素。
了解更多純前端表格在線demo示例，請搜索并訪問“葡萄城官網”！
士 發自 凹非寺
量子位 | 公眾號 QbitAI
除了吃灰，Kindle還能干什么？
不是泡面蓋，而是你的智能管家，每天出門前，提醒你天氣情況、即將到達的包裹。
這玩意有人已經造出來了。
一位做前端與設計的美國小哥，將一塊10多年前的老Kindle改造成了一塊能顯示日歷、天氣、網購包裹、家務提醒的智能顯示板。
整個過程中，無需再買任何硬件，操作流程也簡單。
在reddit上，該項目已獲得500+個贊，有網友表示自己已改造了好幾塊了。
具體如何操作的？
往下看。
之所以上手改造，始于小哥希望在手機之外能有個顯示工具掛墻上，隨時提醒自己時間日程、天氣、快遞哪天到……等等信息。
考慮到Kindle價格比樹莓派還便宜，基于Linux操作系統，易破解調試，且墨水屏比發光屏幕更自然地融入家中環境，他果斷從網上花30美元購買了一臺二手Kindle4，約合211.5人民幣。
但仍有三個問題要解決：
Kindle會自動進入睡眠狀態并顯示屏保程序；
如果想更新屏幕數據，每次瀏覽器頁面都會重新加載，觀感很差；
Kindle瀏覽器還有一些難看又冗余的元素，比如標題、URL欄，且并不好看；
基于上述原因，小哥設計了一套解決思路：
部署一個能獲取網頁屏幕截圖的服務器，讓Kindle作為終端顯示屏，每分鐘下載一次該截圖。
鑒于市面上已有部分平臺提供定時截屏服務，但收費不低，小哥決定自己編寫一個小型服務器，使用 Puppeteer截圖，再依靠ImageMagick轉換格式。
帶著這一規劃，他開動了。
先是設置圖像服務器。
小哥自己編寫了代碼并在Heroku創建一個免費賬戶，在平臺填好項目名，就能一鍵部署。（現已開源，人人可用，鏈接見文末）
退出前，別忘了記下URL。
接著，回到Kindle本體。
連上WiFi，對其進行越獄，網上相關教程有很多，跟著步驟走就行，注意下載文件及流程與版本號相符就行。（相關資料已附在參考鏈接）
然后，安裝USBNetwork插件。復制.bin文件進去就行，接著通過Settings設置->更新Kindle，完成安裝。
斷開Kindle與電腦的連接，通過設備鍵盤，輸入;debugOn
回車，啟動調試模式。
繼續打開鍵盤，輸入~usbNetwork，回車，等待幾秒，再輸入;debugOff，回車。
重新連接電腦。在桌面打開命令行，輸入ssh root@192.168.15.244并回車。然后輸入默認密碼——mario。
現在，你已經可對Kindle進行編程了。
接著，調整驅動從「只讀」變成「可寫」狀態，輸入mntroot rw并回車。
再輸入下方代碼，創建圖像下載腳本，讓你能打開并編輯。
nano /mnt/us/script.sh
在編輯器中輸入下面代碼，注意「Insert_your_URL_from_step_2_here」部分，替換為前面第二步復制的URL：
curl Insert_your_URL_from_step_2_here -o status.png
eips -c
eips -c
eips -g status.png
上述代碼中，第一行是保存圖像到status.png文件，兩個eips-c命令是為了清除屏幕，最后一行為顯示圖像。
完成后，輸入control-O，再輸入control-X，保存并退出編輯器。
然后運行腳本測試一下，輸入/script.sh，就能看見屏幕上能看見圖像顯示。
最后一步， 設置定時程序，讓其每分鐘刷新。
由于Kindle系統與其他Linux設備一樣，其中有個cron工具，可按計劃定時運行。
只需打開配置文件：
nano /etc/crontab/root
將下面命令添加到底部：
*****/mnt/us/script.sh
同樣，輸入?O，回車，再輸入?X，最后重啟：
/etc/init.d/cron restart
BINGO！搞定！
上述魔改的小哥名叫Matt Healy，是一位設計師兼前端工程師。
他目前也是一家SaaS平臺的聯合創始人，主要為其他企業提供用戶/客戶喜好研究與需求洞察服務，閑暇時間，他還做了個自己的主頁。
不止他一人，其實改造墨水屏設備的玩家還有不少。
比如一位芬蘭程序員Kimmo做了個墨水屏天氣顯示器，每天出門前提醒自己穿什么衣服合適。
這兩天，他的改造帖子沖上了HackerNews前3。
更早時候，還有一位荷蘭小哥，女票是英國文學老師，他用Kindle做了個文學時鐘送給了對方。
當程序啟動后，Kindle將會以文學名著段落顯示當前時間，此外，還能猜測該句來自哪本書。按下翻書按鈕，屏幕就會顯示答案，深得女票喜愛。
最后問問， 你覺得Kindle還能用來干什么？
參考鏈接：
[1]https://matthealy.com/Kindle
[2]https://wiki.mobileread.com/wiki/Kindle_Hacks_Information#Jail_break_JB
[3]https://blog.adafruit.com/2021/01/29/your-next-smart-home-device-is-a-30-used-Kindle-iot-internetofthings-eink-epaper-lankybutmacho/
[4]https://www.reddit.com/r/Kindle/comments/l75hjz/your_next_smart_home_device_is_a_30_used_Kindle/
[5]https://github.com/lankybutmacho/web-to-Kindle-heroku
[6]https://kimmo.blog/posts/7-building-eink-weather-display-for-our-home/
[7]https://www.instructables.com/Literary-Clock-Made-From-E-reader/
— 完 —
量子位 QbitAI · 頭條號簽約
關注我們，第一時間獲知前沿科技動態
*請認真填寫需求信息，我們會在24小時內與您取得聯系。
