跳至主要內容

站内资源&API

Black2025年9月29日大约 5 分钟APIAPI

站内资源&API

日期相关

法定节假日

从2007年元旦开始，数据分三列：日期（数字字符串）、节日名称和调班。数据示例：

[
    ["20061230",null,1],["20061231",null,1],["20070101","元旦节",null],
    ["20070102","元旦节",null],["20070103","元旦节",null],["20070217",null,1],
    ["20070218","春节",null],["20070219","春节",null],["20070220","春节",null],
    ["20070221","春节",null],["20070222","春节",null],["20070223","春节",null],
    ...
    ["20251001","国庆节、中秋节",null],["20251002","国庆节、中秋节",null]
    ["20251003","国庆节、中秋节",null],["20251004","国庆节、中秋节",null],
    ["20251005","国庆节、中秋节",null],["20251006","国庆节、中秋节",null],
    ["20251007","国庆节、中秋节",null],["20251008","国庆节、中秋节",null],
    ...
    ["20260927","中秋节",null],["20261001","国庆节",null],["20261002","国庆节",null],
    ["20261003","国庆节",null],["20261004","国庆节",null],["20261005","国庆节",null],
    ["20261006","国庆节",null],["20261007","国庆节",null],["20261010",null,1]
]

访问链接：https://www.blackexcel.cn/static/file/holidays.json

数据说明：

  1. 数据来自信息公开，搜索“节假日”即可。
  2. 使用UTF-8编码。
  3. 截止2026年，共包含元旦节、春节、清明节、端午节、中秋节、国庆节以及国庆节、中秋节（两个节日可能重叠）。
  4. 第三列为调班，该列为1时表示当天为工作日（且一定是周六或者周日）。

使用说明：
使用时转换日期格式并与只存在的日期表进行“合并查询即可”，示例代码：

let
    源 = Table.FromRows(
        Json.Document(Web.Contents("https://www.blackexcel.cn/static/file/holidays.json")), 
        {"日期", "节日", "调班"}
    ),
    更改的类型 = Table.TransformColumnTypes(源,{{"日期", type date}, {"节日", type text}, {"调班", Int64.Type}})
in
    更改的类型

阴历日期

您可能需要阴历表，此处给出一种本地算法，您可以将下面的代码写入一个查询（假设名为fx），然后使用类似fx(#date(2025, 1, 1), #date(2025, 12, 31))的形式调用它即可。另外，你也可以在代码高亮处根据给出的说明自定义自己需要的列。

注意

考虑到您很可能会自定义列，因此并未对结果设置列类型，这需要您自己设置列类型。
设置列类型可修改70行处内容，但新手建议调用后设置列类型，操作比较简单。

let 
    LunarInfo = {
        0x04bd8,0x04ae0,0x0a570,0x054d5,0x0d260,0x0d950,0x16554,0x056a0,0x09ad0,0x055d2,
        0x04ae0,0x0a5b6,0x0a4d0,0x0d250,0x1d255,0x0b540,0x0d6a0,0x0ada2,0x095b0,0x14977,
        0x04970,0x0a4b0,0x0b4b5,0x06a50,0x06d40,0x1ab54,0x02b60,0x09570,0x052f2,0x04970,
        0x06566,0x0d4a0,0x0ea50,0x06e95,0x05ad0,0x02b60,0x186e3,0x092e0,0x1c8d7,0x0c950,
        0x0d4a0,0x1d8a6,0x0b550,0x056a0,0x1a5b4,0x025d0,0x092d0,0x0d2b2,0x0a950,0x0b557,
        0x06ca0,0x0b550,0x15355,0x04da0,0x0a5b0,0x14573,0x052b0,0x0a9a8,0x0e950,0x06aa0,
        0x0aea6,0x0ab50,0x04b60,0x0aae4,0x0a570,0x05260,0x0f263,0x0d950,0x05b57,0x056a0,
        0x096d0,0x04dd5,0x04ad0,0x0a4d0,0x0d4d4,0x0d250,0x0d558,0x0b540,0x0b6a0,0x195a6,
        0x095b0,0x049b0,0x0a974,0x0a4b0,0x0b27a,0x06a50,0x06d40,0x0af46,0x0ab60,0x09570,
        0x04af5,0x04970,0x064b0,0x074a3,0x0ea50,0x06b58,0x055c0,0x0ab60,0x096d5,0x092e0,
        0x0c960,0x0d954,0x0d4a0,0x0da50,0x07552,0x056a0,0x0abb7,0x025d0,0x092d0,0x0cab5,
        0x0a950,0x0b4a0,0x0baa4,0x0ad50,0x055d9,0x04ba0,0x0a5b0,0x15176,0x052b0,0x0a930,
        0x07954,0x06aa0,0x0ad50,0x05b52,0x04b60,0x0a6e6,0x0a4e0,0x0d260,0x0ea65,0x0d530,
        0x05aa0,0x076a3,0x096d0,0x04afb,0x04ad0,0x0a4d0,0x1d0b6,0x0d250,0x0d520,0x0dd45,
        0x0b5a0,0x056d0,0x055b2,0x049b0,0x0a577,0x0a4b0,0x0aa50,0x1b255,0x06d20,0x0ada0,
        0x14b63,0x09370,0x049f8,0x04970,0x064b0,0x168a6,0x0ea50,0x06b20,0x1a6c4,0x0aae0,
        0x0a2e0,0x0d2e3,0x0c960,0x0d557,0x0d4a0,0x0da50,0x05d55,0x056a0,0x0a6d0,0x055d4,
        0x052d0,0x0a9b8,0x0a950,0x0b4a0,0x0b6a6,0x0ad50,0x055a0,0x0aba4,0x0a5b0,0x052b0,
        0x0b273,0x06930,0x07337,0x06aa0,0x0ad50,0x14b55,0x04b60,0x0a570,0x054e4,0x0d160,
        0x0e968,0x0d520,0x0daa0,0x16aa6,0x056d0,0x04ae0,0x0a9d4,0x0a2d0,0x0d150,0x0f252,
        0x0d520
    }, 
    ReadLunarInfo = (n) => 
        let 
            LeapMonth = Number.BitwiseAnd(n, 0xF), 
            LeapMonthAddDays = Number.BitwiseShiftRight(n, 16), 
            MonthDays = List.Generate(() => 11, each _ >= 0, each _ - 1, each Number.BitwiseAnd(Number.BitwiseShiftRight(n, _ + 4), 1) + 29)
        in 
            [
                LeapMonth = LeapMonth, 
                LeapMonthDays = if LeapMonth > 0 then LeapMonthAddDays + 29 else 0, 
                MonthDaysList = MonthDays, 
                YearDays = List.Sum(MonthDays) + (if LeapMonth > 0 then LeapMonthDays else 0), 
                MonthDaysDetail = if LeapMonth > 0 then List.InsertRange(List.Zip({{1..12}, MonthDays, List.Repeat({0}, 12)}),  LeapMonth, {{LeapMonth, LeapMonthDays, 1}}) else List.Zip({{1..12}, MonthDays, List.Repeat({0}, 12)})
            ], 
    LunarYearNames = {"〇", "一", "二", "三", "四", "五", "六", "七", "八", "九"}, 
    LunarMonthNames = {"正月", "二月", "三月", "四月", "五月", "六月", "七月", "八月", "九月", "十月", "冬月", "腊月"}, 
    LunarDayNames = {"初一", "初二", "初三", "初四", "初五", "初六", "初七", "初八", "初九", "初十", "十一", "十二", "十三", "十四", "十五", "十六", "十七", "十八", "十九", "二十", "廿一", "廿二", "廿三", "廿四", "廿五", "廿六", "廿七", "廿八", "廿九", "三十"}, 
    GetLunarYearTable = (lunarYear, startDate) => 
        let 
            info = ReadLunarInfo(LunarInfo{lunarYear - 1900}), 
            result = List.Accumulate(
                info[MonthDaysDetail], 
                [TotalDays = 0, r = {}], 
                (s, v) => [
                    TotalDays = s[TotalDays] + v{1}, 
                    r = s[r] & 
                    List.Transform(
                        {1..v{1}}, 
                        each [
                        // 修改此处内容既可以自定义需要的列
                        // 阴历年、阴历月序号、阴历日序号、是否阴历闰月、阴历年日索引
                        // lunarYear、lunarMonth、lunarDay、IsLeap、YearDayIndex
                        日期 = startDate + #duration(s[TotalDays] + _ - 1, 0, 0, 0), 
                        阴历年 = Text.Combine(List.Reverse(List.Generate(()=>lunarYear,each _>0,each Number.IntegerDivide(_, 10), each LunarYearNames{Number.Mod(_, 10)}))) & "年", 
                        阴历月 = (if v{2} = 1 then "闰" else "") & LunarMonthNames{v{0} - 1}, 
                        阴历日 = LunarDayNames{_ - 1},
                        No_阴历年 = lunarYear, 
                        No_阴历月 = v{0}, 
                        No_阴历日 = _, 
                        闰月 = v{2}, 
                        No_阴历年日序号 = s[TotalDays] + _
                        ]
                    )
                ]     
            )[r]
        in 
            Table.FromRecords(result), 
    GetRange = (StartDate as date, EndDate as date) as table => 
    let
        DiffDays = Duration.TotalDays(EndDate - #date(1900, 1, 31)) + 1, 
        LunarYearRange = List.Select(
            List.Generate(
                () => [i = 0, TotalDays = 0], 
                each _[TotalDays] < DiffDays, 
                each [i = _[i] + 1, TotalDays = _[TotalDays] + ReadLunarInfo(LunarInfo{[i]})[YearDays]], 
                each [i = [i], TotalDays = [TotalDays], dt = #date(1900, 1, 31) + #duration([TotalDays], 0, 0, 0), ReadDate = ReadLunarInfo(LunarInfo{[i]}), InRange = (dt + #duration(ReadDate[YearDays] - 1, 0, 0, 0) >= StartDate) and (dt - #duration(1, 0, 0, 0) <= EndDate)]
            ),
            each [InRange]
        ), 
        Result = Table.Combine(List.Transform(LunarYearRange, each GetLunarYearTable([i] + 1900, [dt])))
    in
        Table.SelectRows(Result, each [日期] >= StartDate and [日期] <= EndDate)
in 
    GetRange

节气日期

从1901-2100年（含两端）的节气日期，以JSON形式返回。数据示例：

[
    ["19010106","小寒"],["19010121","大寒"],["19010204","立春"],["19010219","雨水"],
    ["19010306","惊蛰"],["19010321","春分"],["19010405","清明"],["19010421","谷雨"],
    ["19010506","立夏"],["19010522","小满"],["19010606","芒种"],["19010622","夏至"],
    ...
    ["21000707","小暑"],["21000723","大暑"],["21000807","立秋"],["21000823","处暑"],
    ["21000907","白露"],["21000923","秋分"],["21001008","寒露"],["21001023","霜降"],
    ["21001107","立冬"],["21001122","小雪"],["21001207","大雪"],["21001222","冬至"]
]

访问链接：https://www.blackexcel.cn/static/file/solarterms.json

您也可以直接使用下面的PowerQuery示例代码：

let
    源 = Table.FromRows(
        Json.Document(Web.Contents("https://www.blackexcel.cn/static/file/solarterms.json")), 
        {"日期", "节气"}
    ),
    更改的类型 = Table.TransformColumnTypes(源,{{"日期", type date}, {"节气", type text}})
in
    更改的类型