1 | using System;
|
---|
2 | using System.Collections.Generic;
|
---|
3 | using System.Linq;
|
---|
4 | using System.Text;
|
---|
5 | using OfficeOpenXml.FormulaParsing.ExpressionGraph;
|
---|
6 | using OfficeOpenXml.Utils;
|
---|
7 |
|
---|
8 | namespace OfficeOpenXml.FormulaParsing.Excel.Functions.DateTime
|
---|
9 | {
|
---|
10 | public class Workday : ExcelFunction
|
---|
11 | {
|
---|
12 | public override CompileResult Execute(IEnumerable<FunctionArgument> arguments, ParsingContext context)
|
---|
13 | {
|
---|
14 | ValidateArguments(arguments, 2);
|
---|
15 | var startDate = System.DateTime.FromOADate(ArgToInt(arguments, 0));
|
---|
16 | var nWorkDays = ArgToInt(arguments, 1);
|
---|
17 | var resultDate = System.DateTime.MinValue;
|
---|
18 | var workdaysCounted = 0;
|
---|
19 | var tmpDate = startDate;
|
---|
20 | // first move forward to the first monday
|
---|
21 | while (tmpDate.DayOfWeek != DayOfWeek.Monday && (nWorkDays - workdaysCounted) > 0)
|
---|
22 | {
|
---|
23 | if (!IsHoliday(tmpDate)) workdaysCounted++;
|
---|
24 | tmpDate = tmpDate.AddDays(1);
|
---|
25 | }
|
---|
26 | // then calculate whole weeks
|
---|
27 | var nWholeWeeks = (nWorkDays - workdaysCounted) / 5;
|
---|
28 | tmpDate = tmpDate.AddDays(nWholeWeeks * 7);
|
---|
29 | workdaysCounted += nWholeWeeks * 5;
|
---|
30 |
|
---|
31 | // calculate the rest
|
---|
32 | while (workdaysCounted < nWorkDays)
|
---|
33 | {
|
---|
34 | tmpDate = tmpDate.AddDays(1);
|
---|
35 | if (!IsHoliday(tmpDate)) workdaysCounted++;
|
---|
36 | }
|
---|
37 | resultDate = AdjustResultWithHolidays(tmpDate, arguments);
|
---|
38 | return CreateResult(resultDate.ToOADate(), DataType.Date);
|
---|
39 | }
|
---|
40 |
|
---|
41 | private System.DateTime AdjustResultWithHolidays(System.DateTime resultDate,
|
---|
42 | IEnumerable<FunctionArgument> arguments)
|
---|
43 | {
|
---|
44 | if (arguments.Count() == 2) return resultDate;
|
---|
45 | var holidays = arguments.ElementAt(2).Value as IEnumerable<FunctionArgument>;
|
---|
46 | if (holidays != null)
|
---|
47 | {
|
---|
48 | foreach (var arg in holidays)
|
---|
49 | {
|
---|
50 | if (ConvertUtil.IsNumeric(arg.Value))
|
---|
51 | {
|
---|
52 | var dateSerial = ConvertUtil.GetValueDouble(arg.Value);
|
---|
53 | var holidayDate = System.DateTime.FromOADate(dateSerial);
|
---|
54 | if (!IsHoliday(holidayDate))
|
---|
55 | {
|
---|
56 | resultDate = resultDate.AddDays(1);
|
---|
57 | }
|
---|
58 | }
|
---|
59 | }
|
---|
60 | }
|
---|
61 | else
|
---|
62 | {
|
---|
63 | var range = arguments.ElementAt(2).Value as ExcelDataProvider.IRangeInfo;
|
---|
64 | if (range != null)
|
---|
65 | {
|
---|
66 | foreach (var cell in range)
|
---|
67 | {
|
---|
68 | if (ConvertUtil.IsNumeric(cell.Value))
|
---|
69 | {
|
---|
70 | var dateSerial = ConvertUtil.GetValueDouble(cell.Value);
|
---|
71 | var holidayDate = System.DateTime.FromOADate(dateSerial);
|
---|
72 | if (!IsHoliday(holidayDate))
|
---|
73 | {
|
---|
74 | resultDate = resultDate.AddDays(1);
|
---|
75 | }
|
---|
76 | }
|
---|
77 | }
|
---|
78 | }
|
---|
79 | }
|
---|
80 | return resultDate;
|
---|
81 | }
|
---|
82 |
|
---|
83 | private bool IsHoliday(System.DateTime date)
|
---|
84 | {
|
---|
85 | return date.DayOfWeek == DayOfWeek.Saturday || date.DayOfWeek == DayOfWeek.Sunday;
|
---|
86 | }
|
---|
87 | }
|
---|
88 | }
|
---|