時間範囲の条件付き書式の追加方法

可能な使用シナリオ

Excelで時間範囲の条件付き書式を使用すると、日付を扱う際に非常に便利です。視覚的に時間に基づくデータを追跡し管理できます。

1. 時間に基づくデータの即時把握：今日のタスク、先月の販売、締切、来週の予定などを素早くハイライトします。

2. より良い時間管理：期限、イベント、期限切れ間近のアイテムを把握できます。プロジェクトのタイムライン、請求書、予定表に最適です。

3. 自動更新：動的に更新されます。今日の日付が変わると、Excelが書式を自動的に更新します。

4. 視覚的な明確さ：時間に敏感な情報を色や太字のスタイルで目立たせ、見逃さないようにします。

Excelで時間範囲の条件付き書式を追加する方法

Excelで期間条件付き書式を追加する方法はこちらです — 今日、先週、来月などの日付をハイライト表示するのに非常に役立ちます。

期間条件付き書式の追加手順：

1. 書式設定したい日付セルの範囲を選択します。例：A2:A50。
2. リボンのホームタブに移動します。
3. スタイルグループの条件付き書式をクリックします。
4. ハイライトセルルールにカーソルを合わせます。
5. A Date Occurring…をクリックします。
6. 表示されるダイアログボックスで：ドロップダウンを使用して期間（今日、昨日、明日、過去7日間、先週、来月など）を選択します。
7. 書式（デフォルトは薄い赤の塗りつぶしと濃い赤のテキスト、またはカスタムフォーマットをクリックして自分のスタイルを選んでください）を選択します。
8. OKをクリックします。

Aspose.Cells for .NETを使った期間条件付き書式の追加方法

Aspose.Cellsは、XLSX形式のセルに対し、Microsoft Excel 2007以降の条件付き書式を実行時に完全にサポートします。この例では、さまざまな属性の期間条件付き書式の演習を示しています。

private void TestTimePeriod()
{
    // Instantiate a workbook object
    Workbook book = new Workbook();
    // Create a worksheet object and get the first worksheet
    Worksheet _sheet = book.Worksheets[0];

    AddTimePeriod_1(_sheet);
    AddTimePeriod_2(_sheet);
    AddTimePeriod_3(_sheet);
    AddTimePeriod_4(_sheet);
    AddTimePeriod_5(_sheet);
    AddTimePeriod_6(_sheet);
    AddTimePeriod_7(_sheet);
    AddTimePeriod_8(_sheet);
    AddTimePeriod_9(_sheet);
    AddTimePeriod_10(_sheet);

    book.Save(filePath + "TimePeriodType.xlsx");
}

// This method implements the TimePeriod conditional formatting type with Yesterday attribute.
private void AddTimePeriod_10(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I19:K20", Color.MediumSeaGreen, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.Yesterday;
    Cell c = _sheet.Cells["I19"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/07/30"));
    c = _sheet.Cells["K20"];
    c.PutValue(DateTime.Parse("2008/08/03"));

    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I20"];
    c.PutValue("Yesterday");
}
// This method implements the TimePeriod conditional formatting type with Tomorrow attribute.
private void AddTimePeriod_9(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I17:K18", Color.MediumPurple, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.Tomorrow;
    Cell c = _sheet.Cells["I17"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/08/01"));
    c = _sheet.Cells["K18"];
    c.PutValue(DateTime.Parse("2008/08/03"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I18"];
    c.PutValue("Tomorrow");

}
// This method implements the TimePeriod conditional formatting type with ThisWeek attribute.
private void AddTimePeriod_8(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I15:K16", Color.MediumOrchid, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.ThisWeek;
    Cell c = _sheet.Cells["I15"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/07/28"));
    c = _sheet.Cells["K16"];
    c.PutValue(DateTime.Parse("2008/08/03"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I16"];
    c.PutValue("ThisWeek");
}
// This method implements the TimePeriod conditional formatting type with ThisMonth attribute.
private void AddTimePeriod_7(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I13:K14", Color.MediumBlue, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.ThisMonth;
    Cell c = _sheet.Cells["I13"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/07/5"));
    c = _sheet.Cells["K14"];
    c.PutValue(DateTime.Parse("2008/05/30"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I14"];
    c.PutValue("ThisMonth");
}

// This method implements the TimePeriod conditional formatting type with NextWeek attribute.
private void AddTimePeriod_6(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I11:K12", Color.MediumAquamarine, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.NextWeek;
    Cell c = _sheet.Cells["I11"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/08/5"));
    c = _sheet.Cells["K12"];
    c.PutValue(DateTime.Parse("2008/07/30"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I12"];
    c.PutValue("NextWeek");
}

// This method implements the TimePeriod conditional formatting type with NextMonth attribute.
private void AddTimePeriod_5(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I9:K10", Color.Maroon, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.NextMonth;
    Cell c = _sheet.Cells["I9"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/08/25"));
    c = _sheet.Cells["K10"];
    c.PutValue(DateTime.Parse("2008/07/30"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c = _sheet.Cells["I10"];
    c.PutValue("NextMonth");
}
// This method implements the TimePeriod conditional formatting type with LastWeek attribute.
private void AddTimePeriod_4(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I7:K8", Color.Linen, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.LastWeek;
    Cell c = _sheet.Cells["I7"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/07/25"));
    c = _sheet.Cells["K8"];
    c.PutValue(DateTime.Parse("2008/07/30"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I8"];
    c.PutValue("LastWeek");
}
// This method implements the TimePeriod conditional formatting type with LastMonth attribute.
private void AddTimePeriod_3(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I5:K6", Color.Linen, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.LastMonth;
    Cell c = _sheet.Cells["I5"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/06/26"));
    c = _sheet.Cells["K6"];
    c.PutValue(DateTime.Parse("2008/07/30"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I6"];
    c.PutValue("LastMonth");
}
// This method implements the TimePeriod conditional formatting type with Last7Days attribute.
private void AddTimePeriod_2(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I3:K4", Color.LightSteelBlue, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.Last7Days;
    Cell c = _sheet.Cells["I3"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Parse("2008/07/26"));
    c = _sheet.Cells["K4"];
    c.PutValue(DateTime.Parse("2008/08/30"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);

    c = _sheet.Cells["I4"];
    c.PutValue("Last7Days");
}
// This method implements the TimePeriod conditional formatting type with Today attribute.
private void AddTimePeriod_1(Worksheet _sheet)
{
    FormatConditionCollection conds = GetFormatCondition("I1:K2", Color.LightSlateGray, _sheet);
    int idx = conds.AddCondition(FormatConditionType.TimePeriod);
    FormatCondition cond = conds[idx];
    cond.Style.BackgroundColor = Color.Pink;
    cond.Style.Pattern = BackgroundType.Solid;
    cond.TimePeriod = TimePeriodType.Today;
    Cell c = _sheet.Cells["I1"];
    Style style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c.PutValue(DateTime.Today);
    c = _sheet.Cells["K2"];
    c.PutValue(DateTime.Parse("2008/07/30"));
    style = c.GetStyle();
    style.Number = 30;
    c.SetStyle(style);
    c = _sheet.Cells["I2"];
    c.PutValue("Today");
}

// This method adds formatted conditions.
private FormatConditionCollection GetFormatCondition(string cellAreaName, Color color, Worksheet _sheet)
{
    // Adds an empty conditional formattings
    int index = _sheet.ConditionalFormattings.Add();
    // Get the formatted conditions
    FormatConditionCollection formatConditions = _sheet.ConditionalFormattings[index];
    // Get the cell area calling the custom GetCellAreaByName method
    CellArea area = GetCellAreaByName(cellAreaName);
    // Add the formatted conditions cell area.
    formatConditions.AddArea(area);
    // Call the custom FillCell method
    FillCell(cellAreaName, color, _sheet);
    // Return the formatted conditions
    return formatConditions;
}

// This method specifies the cell shading color for the conditional formattings cellarea range.
private void FillCell(string cellAreaName, Color color, Worksheet _sheet)
{
    CellArea area = GetCellAreaByName(cellAreaName);
    int k = 0;
    for (int i = area.StartColumn; i <= area.EndColumn; i++)
    {
        for (int j = area.StartRow; j <= area.EndRow; j++)
        {
            Cell c = _sheet.Cells[j, i];
            if (!color.IsEmpty)
            {
                Style s = c.GetStyle();
                s.ForegroundColor = color;
                s.Pattern = BackgroundType.Solid;
                c.SetStyle(s);
            }
            // Set some random values to the cells in the cellarea range
            int value = j + i + k;
            c.PutValue(value);
            k++;
        }
    }

}
// This method specifies the CellArea range (start row, start col, end row, end col etc.)
// For the conditional formatting
internal static CellArea GetCellAreaByName(string s)
{
    CellArea area = new CellArea();
    string[] strCellRange = s.Replace("$", "").Split(':');
    int column;
    CellsHelper.CellNameToIndex(strCellRange[0], out area.StartRow, out column);
    area.StartColumn = column;
    if (strCellRange.Length == 1)
    {
        area.EndRow = area.StartRow;
        area.EndColumn = area.StartColumn;
    }
    else
    {
        CellsHelper.CellNameToIndex(strCellRange[1], out area.EndRow, out column);
        area.EndColumn = column;
    }
    return area;
}