Spire.Xls_4.12.2.zip

import java.awt.Color; import com.spire.ms.System.DateTime; import com.spire.xls.*; import com.spire.xls.core.*; import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats; import com.spire.xls.core.spreadsheet.conditionalformatting.TimePeriodType; public class variousConditionalFormatting { public static void main(String[] args) { //Load the document from disk Workbook workbook = new Workbook(); //Create a blank sheet workbook.createEmptySheets(1); //Get the first sheet Worksheet sheet = workbook.getWorksheets().get(0); AddConditionalFormattingForNewSheet(sheet); String result = "output/variousConditionalFormatting_result.xlsx"; //Save and Launch workbook.saveToFile(result, ExcelVersion.Version2010); } private static void AddConditionalFormattingForNewSheet(Worksheet sheet) { AddDefaultIconSet(sheet); AddIconSet2(sheet); AddIconSet3(sheet); AddIconSet4(sheet); AddIconSet5(sheet); AddIconSet6(sheet); AddIconSet7(sheet); AddIconSet8(sheet); AddIconSet9(sheet); AddIconSet10(sheet); AddIconSet11(sheet); AddIconSet12(sheet); AddIconSet13(sheet); AddIconSet14(sheet); AddIconSet15(sheet); AddIconSet16(sheet); AddIconSet17(sheet); AddIconSet18(sheet); AddDefaultColorScale(sheet); Add3ColorScale(sheet); Add2ColorScale(sheet); AddEboveEverage(sheet); AddEboveEverage2(sheet); AddEboveEverage3(sheet); AddTop10_1(sheet); AddTop10_2(sheet); AddTop10_3(sheet); AddTop10_4(sheet); AddDataBar1(sheet); AddDataBar2(sheet); AddContainsText(sheet); AddNotContainsText(sheet); AddContainsBlank(sheet); AddNotContainsBlank(sheet); AddBeginWith(sheet); AddEndWith(sheet); AddContainsError(sheet); AddNotContainsError(sheet); AddDuplicate(sheet); AddUnique(sheet); 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); sheet.getAllocatedRange().setColumnWidth(15); sheet.getAllocatedRange().autoFitRows(); } //This method implements the TimePeriod conditional formatting type with Yesterday attribute. private static void AddTimePeriod_10(Worksheet sheet) { XlsConditionalFormats conds = sheet.getConditionalFormats().add(); conds.addRange(sheet.getCellRange("I19:K20")); sheet.getCellRange("I19:K20").getStyle().setFillPattern(ExcelPatternType.Solid); sheet.getCellRange("I19:K20").getStyle().setColor(Color.green); IConditionalFormat cf = conds.addTimePeriodCondition(TimePeriodType.Yesterday); cf.setFillPattern(ExcelPatternType.Solid); cf.setBackColor(Color.pink); sheet.getCellRange("I19").setValue(DateTime.getNow().addDays(-2).getDate().toString()); sheet.getCellRange("J19").setValue(DateTime.getNow().addDays(-1).getDate().toString()); sheet.getCellRange("K19").setValue(DateTime.getNow().getDate().toString()); sheet.getCellRange("I20").setText("Yesterday"); sheet.getCellRange("J20").setValue(DateTime.getNow().addDays(1).getDate().toString()); sheet.getCellRange("K20").setValue(DateTime.getNow().addDays(2).getDate().toString()); } //This method implements the TimePeriod conditional formatting type with Tomorrow attribute. private static void AddTimePeriod_9(Worksheet sheet) { XlsConditionalFormats conds = sheet.getConditionalFormats().add(); conds.addRange(sheet.getCellRange("I17:K18")); sheet.getCellRange("I17:K18").getStyle().setFillPattern(ExcelPatternType.Solid); sheet.getCellRange("I17:K18").getStyle().setColor(Color.blue); IConditionalFormat cf = conds.addTimePeriodCondition(TimePeriodType.Tomorrow); cf.setFillPattern(ExcelPatternType.Solid); cf.setBackColor(Color.pink); sheet.getCellRange("I17").setValue(DateTime.getNow().addDays(-2).getDate().toString()); sheet.getCellRange("J17").setValue(DateTime.getNow().addDays(-1).getDate().toString()); sheet.getCellRange("K17").setValue(DateTime.getNow().getDate().toString()); sheet.getCellRange("I18").setText("Tomorrow"); sheet.getCellRange("J18").setValue(DateTime.getNow().addDays(1).getDate().toString()); sheet.getCellRange("K18").setValue(DateTime.getNow().addDays(2).getDate().toString()); } //This method implements the TimePeriod conditional formatting type with ThisWeek attribute. private static void AddTimePeriod_8(Worksheet sheet) { XlsConditionalFormats conds = sheet.getConditionalFormats().add(); conds.addRange(sheet.getCellRange("I15:K16")); sheet.getCellRange("I15:K16").getStyle().setFillPattern(ExcelPatternType.Solid); sheet.getCellRange("I15:K16").getStyle().setColor(Color.blue); IConditionalFormat cf = conds.addTimePeriodCondition(TimePeriodType.ThisWeek); cf.setFillPattern(ExcelPatternType.Solid); cf.setBackColor(Color.pink); sheet.getCellRange("I15").setValue(DateTime.getNow().addDays(-2).getDate().toString()); sheet.getCellRange("J15").setValue(DateTime.getNow().addDays(-1).getDate().toString()); sheet.getCellRange("K15").setValue(DateTime.getNow().getDate().toString()); sheet.getCellRange("I16").setText("ThisWeek"); sheet.getCellRange("J16").setValue(DateTime.getNow().addDays(2).getDate().toString()); sheet.getCellRange("K16").setValue(DateTime.getNow().addDays(3).getDate().toString()); } //This method implements the TimePeriod conditional formatting type with ThisMonth attribute. private static void AddTimePeriod_7(Worksheet sheet) { XlsConditionalFormats conds = sheet.getConditionalFormats().add(); conds.addRange(sheet.getCellRange("I13:K14")); sheet.getCellRange("I13:K14").getStyle().setFillPattern(ExcelPatternType.Solid); sheet.getCellRange("I13:K14").getStyle().setColor(Color.blue); IConditionalFormat cf = conds.addTimePeriodCondition(TimePeriodType.ThisMonth); cf.setFillPattern(ExcelPatternType.Solid); cf.setBackColor(Color.pink); sheet.getCellRange("I13").setValue(DateTime.getNow().addMonths(-1).getDate().toString()); sheet.getCellRange("J13").setValue(DateTime.getNow().addDays(-1).getDate().toString()); sheet.getCellRange("K13").setValue(DateTime.getNow().getDate().toString()); sheet.getCellRange("I14").setText("ThisMonth"); sheet.getCellRange("J14").setValue(DateTime.getNow().addMonths(1).getDate().toString()); sheet.getCellRange("K14").setValue(DateTime.getNow().addMonths(2).getDate().toString()); } //This method implements the TimePeriod conditional formatting type with NextWeek attribute. private static void AddTimePeriod_6(Worksheet sheet) { XlsConditionalFormats conds = sheet.getConditionalFormats().add(); conds.addRange(sheet.getCellRange("I11:K12")); sheet.getCellRange("I11:K12").getStyle().setFillPattern(ExcelPatternType.Solid); sheet.getCellRange("I11:K12").getStyle().setColor(Color.blue); IConditionalFormat cf = conds.addTimePeriodCondition(TimePeriodType.NextWeek); cf.setFillPattern(ExcelPatternType.Solid); cf.setBackColor(Color.pink); sheet.getCellRange("I11").setValue(DateTi