/**
* Highlight cells based on their values
*/
static void SameCell(ISheet sheet)
{
sheet.CreateRow(0).CreateCell(0).SetCellValue(84);
sheet.CreateRow(1).CreateCell(0).SetCellValue(74);
sheet.CreateRow(2).CreateCell(0).SetCellValue(50);
sheet.CreateRow(3).CreateCell(0).SetCellValue(51);
sheet.CreateRow(4).CreateCell(0).SetCellValue(49);
sheet.CreateRow(5).CreateCell(0).SetCellValue(41);
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
// Condition 1: Cell Value Is greater than 70 (Blue Fill)
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.GreaterThan, "70");
IPatternFormatting fill1 = rule1.CreatePatternFormatting();
fill1.FillBackgroundColor = (IndexedColors.Blue.Index);
fill1.FillPattern = FillPattern.SolidForeground;
// Condition 2: Cell Value Is less than 50 (Green Fill)
IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "50");
IPatternFormatting fill2 = rule2.CreatePatternFormatting();
fill2.FillBackgroundColor = (IndexedColors.Green.Index);
fill2.FillPattern = FillPattern.SolidForeground;
CellRangeAddress[] regions =
{
CellRangeAddress.ValueOf("A1:A6")
};
sheetCF.AddConditionalFormatting(regions, rule1, rule2);
sheet.GetRow(0).CreateCell(2).SetCellValue("<== Condition 1: Cell Value is greater than 70 (Blue Fill)");
sheet.GetRow(4).CreateCell(2).SetCellValue("<== Condition 2: Cell Value is less than 50 (Green Fill)");
}
/**
* Use Excel conditional formatting to highlight items that are in a list on the worksheet.
*/
static void InList(ISheet sheet)
{
sheet.CreateRow(0).CreateCell(0).SetCellValue("Codes");
sheet.CreateRow(1).CreateCell(0).SetCellValue("AA");
sheet.CreateRow(2).CreateCell(0).SetCellValue("BB");
sheet.CreateRow(3).CreateCell(0).SetCellValue("GG");
sheet.CreateRow(4).CreateCell(0).SetCellValue("AA");
sheet.CreateRow(5).CreateCell(0).SetCellValue("FF");
sheet.CreateRow(6).CreateCell(0).SetCellValue("XX");
sheet.CreateRow(7).CreateCell(0).SetCellValue("CC");
sheet.GetRow(0).CreateCell(2).SetCellValue("Valid");
sheet.GetRow(1).CreateCell(2).SetCellValue("AA");
sheet.GetRow(2).CreateCell(2).SetCellValue("BB");
sheet.GetRow(3).CreateCell(2).SetCellValue("CC");
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
// Condition 1: Formula Is =A2=A1 (White Font)
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule("COUNTIF($C$2:$C$4,A2)");
IPatternFormatting fill1 = rule1.CreatePatternFormatting();
fill1.FillBackgroundColor = (IndexedColors.LightBlue.Index);
fill1.FillPattern = FillPattern.SolidForeground;
CellRangeAddress[] regions =
{
CellRangeAddress.ValueOf("A2:A8")
};
sheetCF.AddConditionalFormatting(regions, rule1);
sheet.GetRow(2).CreateCell(3).SetCellValue("<== Use Excel conditional formatting to highlight items that are in a list on the worksheet");
}
public XSSFDxfStyleProvider(CT_Dxf dxf, int stripeSize, IIndexedColorMap colorMap)
{
this.stripeSize = stripeSize;
this.colorMap = colorMap;
if (dxf == null)
{
border = null;
font = null;
number = null;
fill = null;
}
else
{
border = dxf.IsSetBorder() ? new XSSFBorderFormatting(dxf.border) : null;
font = dxf.IsSetFont() ? new XSSFFontFormatting(dxf.font) : null;
if (dxf.IsSetNumFmt())
{
CT_NumFmt numFmt = dxf.numFmt;
number = new ExcelNumberFormat((int)numFmt.numFmtId, numFmt.formatCode);
}
else
{
number = null;
}
fill = dxf.IsSetFill() ? new XSSFPatternFormatting(dxf.fill) : null;
}
}
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 rule2 = sheetCF.CreateConditionalFormattingRule(
ComparisonOperator.Between, "SUM(A10:A15)", "1+SUM(B16:B30)");
IBorderFormatting borderFmt = rule2.CreateBorderFormatting();
borderFmt.BorderDiagonal = BorderStyle.Medium;
CellRangeAddress[] regions =
{
new CellRangeAddress(2, 4, 0, 0), // A3:A5
};
sheetCF.AddConditionalFormatting(regions, rule1);
sheetCF.AddConditionalFormatting(regions, rule2);
// 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, rule1);
sheetCF.AddConditionalFormatting(regions, rule2);
// This row shift should only affect the formulas
sheet.ShiftRows(14, 17, 8);
IConditionalFormatting cf1 = sheetCF.GetConditionalFormattingAt(0);
Assert.AreEqual("SUM(A10:A23)", cf1.GetRule(0).Formula1);
Assert.AreEqual("1+SUM(B24:B30)", cf1.GetRule(0).Formula2);
IConditionalFormatting cf2 = sheetCF.GetConditionalFormattingAt(1);
Assert.AreEqual("SUM(A10:A23)", cf2.GetRule(0).Formula1);
Assert.AreEqual("1+SUM(B24:B30)", cf2.GetRule(0).Formula2);
sheet.ShiftRows(0, 8, 21);
cf1 = sheetCF.GetConditionalFormattingAt(0);
Assert.AreEqual("SUM(A10:A21)", cf1.GetRule(0).Formula1);
Assert.AreEqual("1+SUM(#REF!)", cf1.GetRule(0).Formula2);
cf2 = sheetCF.GetConditionalFormattingAt(1);
Assert.AreEqual("SUM(A10:A21)", cf2.GetRule(0).Formula1);
Assert.AreEqual("1+SUM(#REF!)", cf2.GetRule(0).Formula2);
}
void MakeFailedFormattingRules()
{
IConditionalFormattingRule rule1 = sheet.SheetConditionalFormatting.CreateConditionalFormattingRule(ComparisonOperator.NotEqual, "0");
IPatternFormatting fill1 = rule1.CreatePatternFormatting();
fill1.FillBackgroundColor = IndexedColors.Red.Index;
fill1.FillPattern = (short)FillPattern.SolidForeground;
failedFormattingRules = new IConditionalFormattingRule[] { rule1 };
}
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*/ HSSFColor.Red.Index);
Assert.AreEqual(HSSFColor.Red.Index, patternFmt.FillBackgroundColor);
Assert.AreEqual(0, patternFmt.FillForegroundColor);
patternFmt.FillForegroundColor = (/*setter*/ HSSFColor.Blue.Index);
Assert.AreEqual(HSSFColor.Blue.Index, patternFmt.FillForegroundColor);
Assert.AreEqual((short)FillPattern.NoFill, patternFmt.FillPattern);
patternFmt.FillPattern = (short)FillPattern.SolidForeground;
Assert.AreEqual((short)FillPattern.SolidForeground, patternFmt.FillPattern);
patternFmt.FillPattern = (short)FillPattern.NoFill;
Assert.AreEqual((short)FillPattern.NoFill, patternFmt.FillPattern);
if (this._testDataProvider.GetSpreadsheetVersion() == SpreadsheetVersion.EXCEL97)
{
patternFmt.FillPattern = (short)FillPattern.Bricks;
Assert.AreEqual((short)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(HSSFColor.Red.Index, r1fp.FillBackgroundColor);
Assert.AreEqual(HSSFColor.Blue.Index, r1fp.FillForegroundColor);
if (this._testDataProvider.GetSpreadsheetVersion() == SpreadsheetVersion.EXCEL97)
{
Assert.AreEqual((short)FillPattern.Bricks, r1fp.FillPattern);
}
}
/// <summary>
///
/// </summary>
/// <param name="path"></param>
/// <param name="maxRowIndex">Row index of max values</param>
/// <param name="minRowIndex">Row index of min values</param>
/// <param name="numFaiRows"></param>
/// <param name="numFaiColumns"></param>
/// <param name="numMiscRows">Count of rows other than fai value rows</param>
public static void FormatFaiExcel(string path, int maxRowIndex, int minRowIndex, int numFaiRows,
int numFaiColumns, int numMiscRows)
{
XSSFWorkbook wb;
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
wb = new XSSFWorkbook(file);
}
// Delete unformatted file
File.Delete(path);
var ws = wb.GetSheetAt(0);
for (int j = 0; j < numFaiColumns; j++)
{
var columnIndex = GetExcelColumnName(j + 1);
var maxFormula = $"${columnIndex}${maxRowIndex + 1}";
var minFormula = $"${columnIndex}${minRowIndex + 1}";
ISheetConditionalFormatting sheetCF = ws.SheetConditionalFormatting;
// Max rule
IConditionalFormattingRule maxRule =
sheetCF.CreateConditionalFormattingRule(ComparisonOperator.GreaterThan, maxFormula);
IPatternFormatting fill1 = maxRule.CreatePatternFormatting();
fill1.FillBackgroundColor = (IndexedColors.Red.Index);
fill1.FillPattern = FillPattern.SolidForeground;
// min rule
IConditionalFormattingRule minRule =
sheetCF.CreateConditionalFormattingRule(ComparisonOperator.LessThan, minFormula);
IPatternFormatting fill2 = minRule.CreatePatternFormatting();
fill2.FillBackgroundColor = (IndexedColors.Blue.Index);
fill2.FillPattern = FillPattern.SolidForeground;
CellRangeAddress[] regions =
{
CellRangeAddress.ValueOf($"{columnIndex}{numMiscRows+1}:{columnIndex}{numFaiRows+numMiscRows}")
};
sheetCF.AddConditionalFormatting(regions, maxRule, minRule);
}
using (var fs = File.Create(path))
{
wb.Write(fs);
}
}
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(PatternFormatting.NO_Fill, patternFmt.FillPattern);
patternFmt.FillPattern = (/*setter*/ PatternFormatting.SOLID_FOREGROUND);
Assert.AreEqual(PatternFormatting.SOLID_FOREGROUND, patternFmt.FillPattern);
patternFmt.FillPattern = (/*setter*/ PatternFormatting.NO_Fill);
Assert.AreEqual(PatternFormatting.NO_Fill, patternFmt.FillPattern);
patternFmt.FillPattern = (/*setter*/ PatternFormatting.BRICKS);
Assert.AreEqual(PatternFormatting.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(PatternFormatting.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)");
}
/**
* 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)");
}
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 };
}
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();
}
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.");
}
public void TestRead()
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook("WithConditionalFormatting.xls");
ISheet sh = wb.GetSheet("CF");
ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting;
Assert.AreEqual(3, sheetCF.NumConditionalFormattings);
IConditionalFormatting cf1 = sheetCF.GetConditionalFormattingAt(0);
Assert.AreEqual(2, cf1.NumberOfRules);
CellRangeAddress[] regions1 = cf1.GetFormattingRanges();
Assert.AreEqual(1, regions1.Length);
Assert.AreEqual("A1:A8", regions1[0].FormatAsString());
// CF1 has two rules: values less than -3 are bold-italic red, values greater than 3 are green
IConditionalFormattingRule rule1 = cf1.GetRule(0);
Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule1.ConditionType);
Assert.AreEqual(ComparisonOperator.GT, rule1.ComparisonOperation);
Assert.AreEqual("3", rule1.Formula1);
Assert.IsNull(rule1.Formula2);
// Fills and borders are not Set
Assert.IsNull(rule1.GetPatternFormatting());
Assert.IsNull(rule1.GetBorderFormatting());
IFontFormatting fmt1 = rule1.GetFontFormatting();
// Assert.AreEqual(IndexedColors.GREEN.index, fmt1.FontColorIndex);
Assert.IsTrue(fmt1.IsBold);
Assert.IsFalse(fmt1.IsItalic);
IConditionalFormattingRule rule2 = cf1.GetRule(1);
Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule2.ConditionType);
Assert.AreEqual(ComparisonOperator.LT, rule2.ComparisonOperation);
Assert.AreEqual("-3", rule2.Formula1);
Assert.IsNull(rule2.Formula2);
Assert.IsNull(rule2.GetPatternFormatting());
Assert.IsNull(rule2.GetBorderFormatting());
IFontFormatting fmt2 = rule2.GetFontFormatting();
// Assert.AreEqual(IndexedColors.RED.index, fmt2.FontColorIndex);
Assert.IsTrue(fmt2.IsBold);
Assert.IsTrue(fmt2.IsItalic);
IConditionalFormatting cf2 = sheetCF.GetConditionalFormattingAt(1);
Assert.AreEqual(1, cf2.NumberOfRules);
CellRangeAddress[] regions2 = cf2.GetFormattingRanges();
Assert.AreEqual(1, regions2.Length);
Assert.AreEqual("B9", regions2[0].FormatAsString());
IConditionalFormattingRule rule3 = cf2.GetRule(0);
Assert.AreEqual(ConditionType.FORMULA, rule3.ConditionType);
Assert.AreEqual(ComparisonOperator.NO_COMPARISON, rule3.ComparisonOperation);
Assert.AreEqual("$A$8>5", rule3.Formula1);
Assert.IsNull(rule3.Formula2);
IFontFormatting fmt3 = rule3.GetFontFormatting();
// Assert.AreEqual(IndexedColors.RED.index, fmt3.FontColorIndex);
Assert.IsTrue(fmt3.IsBold);
Assert.IsTrue(fmt3.IsItalic);
IPatternFormatting fmt4 = rule3.GetPatternFormatting();
// Assert.AreEqual(IndexedColors.LIGHT_CORNFLOWER_BLUE.index, fmt4.FillBackgroundColor);
// Assert.AreEqual(IndexedColors.AUTOMATIC.index, fmt4.FillForegroundColor);
Assert.AreEqual(PatternFormatting.NO_Fill, fmt4.FillPattern);
// borders are not Set
Assert.IsNull(rule3.GetBorderFormatting());
IConditionalFormatting cf3 = sheetCF.GetConditionalFormattingAt(2);
CellRangeAddress[] regions3 = cf3.GetFormattingRanges();
Assert.AreEqual(1, regions3.Length);
Assert.AreEqual("B1:B7", regions3[0].FormatAsString());
Assert.AreEqual(2, cf3.NumberOfRules);
IConditionalFormattingRule rule4 = cf3.GetRule(0);
Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule4.ConditionType);
Assert.AreEqual(ComparisonOperator.LE, rule4.ComparisonOperation);
Assert.AreEqual("\"AAA\"", rule4.Formula1);
Assert.IsNull(rule4.Formula2);
IConditionalFormattingRule rule5 = cf3.GetRule(1);
Assert.AreEqual(ConditionType.CELL_VALUE_IS, rule5.ConditionType);
Assert.AreEqual(ComparisonOperator.BETWEEN, rule5.ComparisonOperation);
Assert.AreEqual("\"A\"", rule5.Formula1);
Assert.AreEqual("\"AAA\"", rule5.Formula2);
}
//
public void TestRead(string sampleFile)
{
IWorkbook wb = _testDataProvider.OpenSampleWorkbook(sampleFile);
ISheet sh = wb.GetSheet("CF");
ISheetConditionalFormatting sheetCF = sh.SheetConditionalFormatting;
Assert.AreEqual(3, sheetCF.NumConditionalFormattings);
IConditionalFormatting cf1 = sheetCF.GetConditionalFormattingAt(0);
Assert.AreEqual(2, cf1.NumberOfRules);
CellRangeAddress[] regions1 = cf1.GetFormattingRanges();
Assert.AreEqual(1, regions1.Length);
Assert.AreEqual("A1:A8", regions1[0].FormatAsString());
// CF1 has two rules: values less than -3 are bold-italic red, values greater than 3 are green
IConditionalFormattingRule rule1 = cf1.GetRule(0);
Assert.AreEqual(ConditionType.CellValueIs, rule1.ConditionType);
Assert.AreEqual(ComparisonOperator.GreaterThan, rule1.ComparisonOperation);
Assert.AreEqual("3", rule1.Formula1);
Assert.IsNull(rule1.Formula2);
// Fills and borders are not Set
Assert.IsNull(rule1.GetPatternFormatting());
Assert.IsNull(rule1.GetBorderFormatting());
IFontFormatting fmt1 = rule1.GetFontFormatting();
// Assert.AreEqual(HSSFColor.GREEN.index, fmt1.FontColorIndex);
Assert.IsTrue(fmt1.IsBold);
Assert.IsFalse(fmt1.IsItalic);
IConditionalFormattingRule rule2 = cf1.GetRule(1);
Assert.AreEqual(ConditionType.CellValueIs, rule2.ConditionType);
Assert.AreEqual(ComparisonOperator.LessThan, rule2.ComparisonOperation);
Assert.AreEqual("-3", rule2.Formula1);
Assert.IsNull(rule2.Formula2);
Assert.IsNull(rule2.GetPatternFormatting());
Assert.IsNull(rule2.GetBorderFormatting());
IFontFormatting fmt2 = rule2.GetFontFormatting();
// Assert.AreEqual(HSSFColor.Red.index, fmt2.FontColorIndex);
Assert.IsTrue(fmt2.IsBold);
Assert.IsTrue(fmt2.IsItalic);
IConditionalFormatting cf2 = sheetCF.GetConditionalFormattingAt(1);
Assert.AreEqual(1, cf2.NumberOfRules);
CellRangeAddress[] regions2 = cf2.GetFormattingRanges();
Assert.AreEqual(1, regions2.Length);
Assert.AreEqual("B9", regions2[0].FormatAsString());
IConditionalFormattingRule rule3 = cf2.GetRule(0);
Assert.AreEqual(ConditionType.Formula, rule3.ConditionType);
Assert.AreEqual(ComparisonOperator.NoComparison, rule3.ComparisonOperation);
Assert.AreEqual("$A$8>5", rule3.Formula1);
Assert.IsNull(rule3.Formula2);
IFontFormatting fmt3 = rule3.GetFontFormatting();
// Assert.AreEqual(HSSFColor.Red.index, fmt3.FontColorIndex);
Assert.IsTrue(fmt3.IsBold);
Assert.IsTrue(fmt3.IsItalic);
IPatternFormatting fmt4 = rule3.GetPatternFormatting();
// Assert.AreEqual(HSSFColor.LIGHT_CORNFLOWER_BLUE.index, fmt4.FillBackgroundColor);
// Assert.AreEqual(HSSFColor.Automatic.index, fmt4.FillForegroundColor);
Assert.AreEqual((short)FillPattern.NoFill, fmt4.FillPattern);
// borders are not Set
Assert.IsNull(rule3.GetBorderFormatting());
IConditionalFormatting cf3 = sheetCF.GetConditionalFormattingAt(2);
CellRangeAddress[] regions3 = cf3.GetFormattingRanges();
Assert.AreEqual(1, regions3.Length);
Assert.AreEqual("B1:B7", regions3[0].FormatAsString());
Assert.AreEqual(2, cf3.NumberOfRules);
IConditionalFormattingRule rule4 = cf3.GetRule(0);
Assert.AreEqual(ConditionType.CellValueIs, rule4.ConditionType);
Assert.AreEqual(ComparisonOperator.LessThanOrEqual, rule4.ComparisonOperation);
Assert.AreEqual("\"AAA\"", rule4.Formula1);
Assert.IsNull(rule4.Formula2);
IConditionalFormattingRule rule5 = cf3.GetRule(1);
Assert.AreEqual(ConditionType.CellValueIs, rule5.ConditionType);
Assert.AreEqual(ComparisonOperator.Between, rule5.ComparisonOperation);
Assert.AreEqual("\"A\"", rule5.Formula1);
Assert.AreEqual("\"AAA\"", rule5.Formula2);
}
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);
}
