public void TestClone() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sheet = wb.CreateSheet(); String formula = "7"; ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(formula); IFontFormatting fontFmt = rule1.CreateFontFormatting(); fontFmt.SetFontStyle(true, false); IPatternFormatting patternFmt = rule1.CreatePatternFormatting(); patternFmt.FillBackgroundColor = (/*setter*/ HSSFColor.Yellow.Index); IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Between, "1", "2"); IConditionalFormattingRule[] cfRules = { rule1, rule2 }; short col = 1; CellRangeAddress[] regions = { new CellRangeAddress(0, 65535, col, col) }; sheetCF.AddConditionalFormatting(regions, cfRules); try { wb.CloneSheet(0); } catch (Exception e) { if (e.Message.IndexOf("needs to define a clone method") > 0) { Assert.Fail("Indentified bug 45682"); } throw e; } Assert.AreEqual(2, wb.NumberOfSheets); }
void MakeOutcomeFormattingRules() { IConditionalFormattingRule rule1 = sheet.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Equal, "\"" + KnownOutcomes.Passed + "\""); IPatternFormatting fill1 = rule1.CreatePatternFormatting(); fill1.FillBackgroundColor = IndexedColors.BrightGreen.Index; fill1.FillPattern = (short)FillPattern.SolidForeground; IConditionalFormattingRule rule2 = sheet.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Equal, "\"" + KnownOutcomes.Failed + "\""); IPatternFormatting fill2 = rule2.CreatePatternFormatting(); fill2.FillBackgroundColor = IndexedColors.Red.Index; fill2.FillPattern = (short)FillPattern.SolidForeground; outcomeFormattingRules = new IConditionalFormattingRule[] { rule1, rule2 }; }
public void TestCreatePatternFormatting() { IWorkbook workbook = _testDataProvider.CreateWorkbook(); ISheet sheet = workbook.CreateSheet(); ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Equal, "7"); IPatternFormatting patternFmt = rule1.CreatePatternFormatting(); Assert.AreEqual(0, patternFmt.FillBackgroundColor); patternFmt.FillBackgroundColor = (/*setter*/ IndexedColors.Red.Index); Assert.AreEqual(IndexedColors.Red.Index, patternFmt.FillBackgroundColor); Assert.AreEqual(0, patternFmt.FillForegroundColor); patternFmt.FillForegroundColor = (/*setter*/ IndexedColors.Blue.Index); Assert.AreEqual(IndexedColors.Blue.Index, patternFmt.FillForegroundColor); Assert.AreEqual(FillPattern.NoFill, patternFmt.FillPattern); patternFmt.FillPattern = (/*setter*/ FillPattern.SolidForeground); Assert.AreEqual(FillPattern.SolidForeground, patternFmt.FillPattern); patternFmt.FillPattern = (/*setter*/ FillPattern.NoFill); Assert.AreEqual(FillPattern.NoFill, patternFmt.FillPattern); patternFmt.FillPattern = (/*setter*/ FillPattern.Bricks); Assert.AreEqual(FillPattern.Bricks, patternFmt.FillPattern); IConditionalFormattingRule[] cfRules = { rule1 }; CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A1:A5") }; sheetCF.AddConditionalFormatting(regions, cfRules); // Verification IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0); Assert.IsNotNull(cf); Assert.AreEqual(1, cf.NumberOfRules); IPatternFormatting r1fp = cf.GetRule(0).GetPatternFormatting(); Assert.IsNotNull(r1fp); Assert.AreEqual(IndexedColors.Red.Index, r1fp.FillBackgroundColor); Assert.AreEqual(IndexedColors.Blue.Index, r1fp.FillForegroundColor); Assert.AreEqual(FillPattern.Bricks, r1fp.FillPattern); }
public static void ConditionFormat(ISheet sheet, int rowIndex) { ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; IConditionalFormattingRule rule = sheetCF.CreateConditionalFormattingRule("$B3<>\"Transferred\""); IPatternFormatting fill = rule.CreatePatternFormatting(); fill.FillBackgroundColor = (IndexedColors.Red.Index); fill.FillPattern = FillPattern.SolidForeground; CellRangeAddress[] regions = { CellRangeAddress.ValueOf($"B3:B{rowIndex}") }; sheetCF.AddConditionalFormatting(regions, rule); }
/** * Highlight multiple cells based on a formula */ static void MultiCell(ISheet sheet) { // header row IRow row0 = sheet.CreateRow(0); row0.CreateCell(0).SetCellValue("Units"); row0.CreateCell(1).SetCellValue("Cost"); row0.CreateCell(2).SetCellValue("Total"); IRow row1 = sheet.CreateRow(1); row1.CreateCell(0).SetCellValue(71); row1.CreateCell(1).SetCellValue(29); row1.CreateCell(2).SetCellValue(2059); IRow row2 = sheet.CreateRow(2); row2.CreateCell(0).SetCellValue(85); row2.CreateCell(1).SetCellValue(29); row2.CreateCell(2).SetCellValue(2059); IRow row3 = sheet.CreateRow(3); row3.CreateCell(0).SetCellValue(71); row3.CreateCell(1).SetCellValue(29); row3.CreateCell(2).SetCellValue(2059); ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; // Condition 1: Formula Is =$B2>75 (Blue Fill) IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("$A2>75"); IPatternFormatting fill1 = rule1.CreatePatternFormatting(); fill1.FillBackgroundColor = IndexedColors.Blue.Index; fill1.FillPattern = FillPattern.SolidForeground; CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A2:C4") }; sheetCF.AddConditionalFormatting(regions, rule1); sheet.GetRow(2).CreateCell(4).SetCellValue("<== Condition 1: Formula is =$B2>75 (Blue Fill)"); }
public void TestShiftRows() { IWorkbook wb = _testDataProvider.CreateWorkbook(); ISheet sheet = wb.CreateSheet(); ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule( ComparisonOperator.Between, "SUM(A10:A15)", "1+SUM(B16:B30)"); IFontFormatting fontFmt = rule1.CreateFontFormatting(); fontFmt.SetFontStyle(true, false); IPatternFormatting patternFmt = rule1.CreatePatternFormatting(); patternFmt.FillBackgroundColor = (/*setter*/ HSSFColor.Yellow.Index); IConditionalFormattingRule[] cfRules = { rule1, }; CellRangeAddress[] regions = { new CellRangeAddress(2, 4, 0, 0), // A3:A5 }; sheetCF.AddConditionalFormatting(regions, cfRules); // This row-shift should destroy the CF region sheet.ShiftRows(10, 20, -9); Assert.AreEqual(0, sheetCF.NumConditionalFormattings); // re-add the CF sheetCF.AddConditionalFormatting(regions, cfRules); // This row shift should only affect the formulas sheet.ShiftRows(14, 17, 8); IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0); Assert.AreEqual("SUM(A10:A23)", cf.GetRule(0).Formula1); Assert.AreEqual("1+SUM(B24:B30)", cf.GetRule(0).Formula2); sheet.ShiftRows(0, 8, 21); cf = sheetCF.GetConditionalFormattingAt(0); Assert.AreEqual("SUM(A10:A21)", cf.GetRule(0).Formula1); Assert.AreEqual("1+SUM(#REF!)", cf.GetRule(0).Formula2); }
/** * You can use Excel conditional formatting to shade bands of rows on the worksheet. * In this example, 3 rows are shaded light grey, and 3 are left with no shading. * In the MOD function, the total number of rows in the set of banded rows (6) is entered. */ static void ShadeBands(ISheet sheet) { ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("MOD(ROW(),6)<3"); IPatternFormatting fill1 = rule1.CreatePatternFormatting(); fill1.FillBackgroundColor = (IndexedColors.Grey25Percent.Index); fill1.FillPattern = FillPattern.SolidForeground; CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A1:Z100") }; sheetCF.AddConditionalFormatting(regions, rule1); sheet.CreateRow(0).CreateCell(1).SetCellValue("Shade Bands of Rows"); sheet.CreateRow(1).CreateCell(1).SetCellValue("Condition: Formula is =MOD(ROW(),6)<2 (Light Grey Fill)"); }
/** * Use Excel conditional formatting to shade alternating rows on the worksheet */ static void ShadeAlt(ISheet sheet) { ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; // Condition 1: Formula Is =A2=A1 (White Font) IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("MOD(ROW(),2)"); IPatternFormatting fill1 = rule1.CreatePatternFormatting(); fill1.FillBackgroundColor = (IndexedColors.LightGreen.Index); fill1.FillPattern = FillPattern.SolidForeground; CellRangeAddress[] regions = { CellRangeAddress.ValueOf("A1:Z100") }; sheetCF.AddConditionalFormatting(regions, rule1); sheet.CreateRow(0).CreateCell(1).SetCellValue("Shade Alternating Rows"); sheet.CreateRow(1).CreateCell(1).SetCellValue("Condition: Formula Is =MOD(ROW(),2) (Light Green Fill)"); }
static void Main(string[] args) { InitializeWorkbook(); HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.CreateSheet("Sheet1"); ISheetConditionalFormatting hscf = sheet1.SheetConditionalFormatting; // Define a Conditional Formatting rule, which triggers formatting // when cell's value is bigger than 55 and smaller than 500 // applies patternFormatting defined below. IConditionalFormattingRule rule = hscf.CreateConditionalFormattingRule( ComparisonOperator.Between, "55", // 1st formula "500" // 2nd formula ); // Create pattern with red background IPatternFormatting patternFmt = rule.CreatePatternFormatting(); patternFmt.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index; //// Define a region containing first column CellRangeAddress[] regions = { new CellRangeAddress(0, 65535, 0, 1) }; // Apply Conditional Formatting rule defined above to the regions hscf.AddConditionalFormatting(regions, rule); //fill cell with numeric values sheet1.CreateRow(0).CreateCell(0).SetCellValue(50); sheet1.CreateRow(0).CreateCell(1).SetCellValue(101); sheet1.CreateRow(1).CreateCell(1).SetCellValue(25); sheet1.CreateRow(1).CreateCell(0).SetCellValue(150); WriteToFile(); }
void MakePercentageConditionalFormattingRules() { IConditionalFormattingRule rule1 = sheet.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.GreaterThanOrEqual, GreenBandString); IPatternFormatting fill1 = rule1.CreatePatternFormatting(); fill1.FillBackgroundColor = IndexedColors.BrightGreen.Index; fill1.FillPattern = (short)FillPattern.SolidForeground; IConditionalFormattingRule rule2 = sheet.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.GreaterThanOrEqual, YellowBandString); IPatternFormatting fill2 = rule2.CreatePatternFormatting(); fill2.FillBackgroundColor = IndexedColors.Yellow.Index; fill2.FillPattern = (short)FillPattern.SolidForeground; IConditionalFormattingRule rule3 = sheet.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.LessThan, YellowBandString); IPatternFormatting fill3 = rule3.CreatePatternFormatting(); fill3.FillBackgroundColor = IndexedColors.Red.Index; fill3.FillPattern = (short)FillPattern.SolidForeground; PercentageFormattingRules = new IConditionalFormattingRule[] { rule1, rule2, rule3 }; }
public void test52122() { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Conditional Formatting Test"); sheet.SetColumnWidth(0, 256 * 10); sheet.SetColumnWidth(1, 256 * 10); sheet.SetColumnWidth(2, 256 * 10); // Create some content. // row 0 IRow row = sheet.CreateRow(0); ICell cell0 = row.CreateCell(0); cell0.SetCellType(CellType.Numeric); cell0.SetCellValue(100); ICell cell1 = row.CreateCell(1); cell1.SetCellType(CellType.Numeric); cell1.SetCellValue(120); ICell cell2 = row.CreateCell(2); cell2.SetCellType(CellType.Numeric); cell2.SetCellValue(130); // row 1 row = sheet.CreateRow(1); cell0 = row.CreateCell(0); cell0.SetCellType(CellType.Numeric); cell0.SetCellValue(200); cell1 = row.CreateCell(1); cell1.SetCellType(CellType.Numeric); cell1.SetCellValue(220); cell2 = row.CreateCell(2); cell2.SetCellType(CellType.Numeric); cell2.SetCellValue(230); // row 2 row = sheet.CreateRow(2); cell0 = row.CreateCell(0); cell0.SetCellType(CellType.Numeric); cell0.SetCellValue(300); cell1 = row.CreateCell(1); cell1.SetCellType(CellType.Numeric); cell1.SetCellValue(320); cell2 = row.CreateCell(2); cell2.SetCellType(CellType.Numeric); cell2.SetCellValue(330); // Create conditional formatting, CELL1 should be yellow if CELL0 is not blank. ISheetConditionalFormatting formatting = sheet.SheetConditionalFormatting; IConditionalFormattingRule rule = formatting.CreateConditionalFormattingRule("$A$1>75"); IPatternFormatting pattern = rule.CreatePatternFormatting(); pattern.FillBackgroundColor = IndexedColors.Blue.Index; pattern.FillPattern = FillPattern.SolidForeground; CellRangeAddress[] range = { CellRangeAddress.ValueOf("B2:C2") }; CellRangeAddress[] range2 = { CellRangeAddress.ValueOf("B1:C1") }; formatting.AddConditionalFormatting(range, rule); formatting.AddConditionalFormatting(range2, rule); // Write file. /*FileOutputStream fos = new FileOutputStream("c:\\temp\\52122_conditional-sheet.xls"); * try { * workbook.write(fos); * } finally { * fos.Close(); * }*/ IWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)workbook); ISheet sheetBack = wbBack.GetSheetAt(0); ISheetConditionalFormatting sheetConditionalFormattingBack = sheetBack.SheetConditionalFormatting; Assert.IsNotNull(sheetConditionalFormattingBack); IConditionalFormatting formattingBack = sheetConditionalFormattingBack.GetConditionalFormattingAt(0); Assert.IsNotNull(formattingBack); IConditionalFormattingRule ruleBack = formattingBack.GetRule(0); Assert.IsNotNull(ruleBack); IPatternFormatting patternFormattingBack1 = ruleBack.PatternFormatting; Assert.IsNotNull(patternFormattingBack1); Assert.AreEqual(IndexedColors.Blue.Index, patternFormattingBack1.FillBackgroundColor); Assert.AreEqual(FillPattern.SolidForeground, patternFormattingBack1.FillPattern); }
public void TestCreateCF() { IWorkbook workbook = _testDataProvider.CreateWorkbook(); ISheet sheet = workbook.CreateSheet(); String formula = "7"; ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting; IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(formula); IFontFormatting fontFmt = rule1.CreateFontFormatting(); fontFmt.SetFontStyle(true, false); IBorderFormatting bordFmt = rule1.CreateBorderFormatting(); bordFmt.BorderBottom = (/*setter*/ BorderStyle.Thin); bordFmt.BorderTop = (/*setter*/ BorderStyle.Thick); bordFmt.BorderLeft = (/*setter*/ BorderStyle.Dashed); bordFmt.BorderRight = (/*setter*/ BorderStyle.Dotted); IPatternFormatting patternFmt = rule1.CreatePatternFormatting(); patternFmt.FillBackgroundColor = (/*setter*/ HSSFColor.Yellow.Index); IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Between, "1", "2"); IConditionalFormattingRule[] cfRules = { rule1, rule2 }; short col = 1; CellRangeAddress[] regions = { new CellRangeAddress(0, 65535, col, col) }; sheetCF.AddConditionalFormatting(regions, cfRules); sheetCF.AddConditionalFormatting(regions, cfRules); // Verification Assert.AreEqual(2, sheetCF.NumConditionalFormattings); sheetCF.RemoveConditionalFormatting(1); Assert.AreEqual(1, sheetCF.NumConditionalFormattings); IConditionalFormatting cf = sheetCF.GetConditionalFormattingAt(0); Assert.IsNotNull(cf); regions = cf.GetFormattingRanges(); Assert.IsNotNull(regions); Assert.AreEqual(1, regions.Length); CellRangeAddress r = regions[0]; Assert.AreEqual(1, r.FirstColumn); Assert.AreEqual(1, r.LastColumn); Assert.AreEqual(0, r.FirstRow); Assert.AreEqual(65535, r.LastRow); Assert.AreEqual(2, cf.NumberOfRules); rule1 = cf.GetRule(0); Assert.AreEqual("7", rule1.Formula1); Assert.IsNull(rule1.Formula2); IFontFormatting r1fp = rule1.GetFontFormatting(); Assert.IsNotNull(r1fp); Assert.IsTrue(r1fp.IsItalic); Assert.IsFalse(r1fp.IsBold); IBorderFormatting r1bf = rule1.GetBorderFormatting(); Assert.IsNotNull(r1bf); Assert.AreEqual(BorderStyle.Thin, r1bf.BorderBottom); Assert.AreEqual(BorderStyle.Thick, r1bf.BorderTop); Assert.AreEqual(BorderStyle.Dashed, r1bf.BorderLeft); Assert.AreEqual(BorderStyle.Dotted, r1bf.BorderRight); IPatternFormatting r1pf = rule1.GetPatternFormatting(); Assert.IsNotNull(r1pf); // Assert.AreEqual(HSSFColor.Yellow.index,r1pf.FillBackgroundColor); rule2 = cf.GetRule(1); Assert.AreEqual("2", rule2.Formula2); Assert.AreEqual("1", rule2.Formula1); }
public void Write(string path, IProgress <string> progressHandler) { progressHandler.Report("Starting writing *.XLSX file..."); progressHandler.Report("Path: " + path); using (var stream = new FileStream(path, FileMode.Create, FileAccess.Write)) { IWorkbook workbook = new XSSFWorkbook(); ISheet entriesSheet = workbook.CreateSheet("Entries"); ISheet metaSheet = workbook.CreateSheet("MetaData"); int metaRowIndex = 0; foreach (var meta in MetaData) { IRow metaRow = metaSheet.CreateRow(metaRowIndex); ICell metaCell = metaRow.CreateCell(0); metaCell.SetCellValue(meta); metaRowIndex++; } metaSheet.SetColumnWidth(0, 100 * 256); IFont titleFont = workbook.CreateFont(); titleFont.IsBold = true; titleFont.FontHeightInPoints = 12; ICellStyle titleStyle = workbook.CreateCellStyle(); titleStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; titleStyle.FillPattern = FillPattern.SolidForeground; titleStyle.BorderTop = titleStyle.BorderLeft = titleStyle.BorderRight = titleStyle.BorderBottom = BorderStyle.Thin; titleStyle.SetFont(titleFont); ICellStyle valuesStyle = workbook.CreateCellStyle(); valuesStyle.BorderTop = valuesStyle.BorderLeft = valuesStyle.BorderRight = valuesStyle.BorderBottom = BorderStyle.Thin; valuesStyle.VerticalAlignment = VerticalAlignment.Center; valuesStyle.WrapText = true; entriesSheet.DefaultColumnWidth = 30; entriesSheet.DefaultRowHeightInPoints = 11 * 3; entriesSheet.SetColumnWidth(0, 50 * 256); entriesSheet.SetColumnWidth(1, 70 * 256); entriesSheet.SetColumnWidth(2, 70 * 256); entriesSheet.CreateFreezePane(0, 1); IRow titlesRow = entriesSheet.CreateRow(0); titlesRow.HeightInPoints = 20; ICell keyTitleCell = titlesRow.CreateCell(0); ICell nativeTitleCell = titlesRow.CreateCell(1); ICell translationTitleCell = titlesRow.CreateCell(2); ICell translatorCommentsTitleCell = titlesRow.CreateCell(3); ICell flagsTitleCell = titlesRow.CreateCell(4); ICell commentsTitleCell = titlesRow.CreateCell(5); ICell referencesTitleCell = titlesRow.CreateCell(6); foreach (var cell in titlesRow.Cells) { cell.CellStyle = titleStyle; } keyTitleCell.SetCellValue("Key"); nativeTitleCell.SetCellValue("Orginal"); translationTitleCell.SetCellValue("Translation"); translatorCommentsTitleCell.SetCellValue("Comments"); flagsTitleCell.SetCellValue("Flags"); commentsTitleCell.SetCellValue("Comments (generated)"); referencesTitleCell.SetCellValue("References"); int rowIndex = 1; foreach (var entry in Entries) { IRow entryRow = entriesSheet.CreateRow(rowIndex); ICell keyCell = entryRow.CreateCell(0); ICell nativeCell = entryRow.CreateCell(1); ICell translationCell = entryRow.CreateCell(2); ICell translatorCommentsCell = entryRow.CreateCell(3); ICell flagsCell = entryRow.CreateCell(4); ICell commentsCell = entryRow.CreateCell(5); ICell referencesCell = entryRow.CreateCell(6); keyCell.SetCellValue(entry.msgctxt); nativeCell.SetCellValue(entry.msgid); translationCell.SetCellValue(entry.msgstr); translatorCommentsCell.SetCellValue(GetMergedStringList(entry.translatorComments)); flagsCell.SetCellValue(GetMergedStringList(entry.flags)); commentsCell.SetCellValue(GetMergedStringList(entry.comments)); referencesCell.SetCellValue(GetMergedStringList(entry.references)); foreach (var cell in entryRow.Cells) { cell.CellStyle = valuesStyle; } rowIndex++; } IConditionalFormattingRule rule = entriesSheet.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.Equal, "\"\""); IPatternFormatting fill = rule.CreatePatternFormatting(); fill.FillBackgroundColor = IndexedColors.Red.Index; fill.FillPattern = FillPattern.SolidForeground; CellRangeAddress[] regions = { new CellRangeAddress(1, Entries.Count, 2, 2) }; entriesSheet.SheetConditionalFormatting.AddConditionalFormatting(regions, rule); workbook.Write(stream); } progressHandler.Report("File succesfuly created."); }