Hur man lägger till tidsperioders villkorsstyrd formatering
Möjliga användningsscenario
Att använda villkorsstyrd formatering för tidsperioder i Excel är mycket användbart när du arbetar med datum – det hjälper dig att visuellt spåra och hantera tidsbaserad data snabbt.
-
Omedelbara insikter om tidsbaserad data: Markera snabbt saker som dagens uppgifter, förra månadens försäljning, kommande deadlines, nästa veckas möten.
-
Bättre tidshantering: Hjälper dig att hålla koll på förfallodagar, händelser eller utgångna objekt. Bra för projektplaner, fakturor eller scheman.
-
Automatiska uppdateringar: Den uppdateras dynamiskt. Om dagens datum ändras, kommer Excel att uppdatera formateringen utan att du gör något.
-
Visuell tydlighet: Gör tidskänslig information framträdande med färger eller fet stil — så att den inte missas.
Hur man lägger till villkorsstyrd formatering för tidsperioder med Excel
Så här kan du lägga till villkorsstyrd formatering för tidsperioder i Excel — mycket användbart för att markera datum som idag, förra veckan, nästa månad, etc.
Steg för att lägga till villkorsstyrd formatering för tidsperioder:
- Markera det område av datumceller du vill formatera. Exempel: A2:A50.
- Gå till fliken Hem i menyfliksområdet.
- Klicka på Villkorsstyrd formatering i Stilar-gruppen.
- Håll muspekaren över Markera cellregler.
- Klicka på En datum som inträffar…
- I dialogrutan som visas: Använd rullgardinsmenyn för att välja en tidsperiod (Idag, Igår, Imorgon, Senaste 7 dagar, Förra veckan, Nästa månad osv.).
- Välj format (standard är ljusröd fyllning med mörkröd text, eller klicka på Anpassad formatering för att välja egna stilar).
- Klicka på OK.
Hur man lägger till villkorsstyrd formatering för tidsperioder med Aspose.Cells for .NET
Aspose.Cells stöder fullt ut den villkorsstyrda formateringen som Microsoft Excel 2007 och senare versioner tillhandahåller i XLSX-format på celler vid körning. Det här exemplet visar ett övningsexempel för villkorsstyrd formatering för tidsperioder med olika attributsets.
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; | |
} |