c# Excel library

Formatting(C#)

 
top
Number Formatting

 formatting cell value with the custom format pattern string

workBook.setSelection("A2:B4");
RangeStyle rangeStyle = workBook.getRangeStyle();
rangeStyle.CustomFormat = "$#,##0.00;[Red]$#,##0.00";
//rangeStyle.CustomFormat = "0.00%";
//rangeStyle.CustomFormat = "dd-mmm-yyyy";
workBook.setRangeStyle(rangeStyle);

 
top
Alignment Setting

formatting cell with alignment setting

RangeStyle rangeStyle = workBook.getRangeStyle(1, 1, 2, 2);//get format from range B2:C3
rangeStyle.HorizontalAlignment = RangeStyle.HorizontalAlignmentRight;
//rangeStyle.Indent = 5;
rangeStyle.VerticalAlignment = RangeStyle.VerticalAlignmentCenter;
workBook.setRangeStyle(rangeStyle, 1, 1, 2, 2);//set format for range B2:C3

 
top
Font Setting

formatting cell with font setting

RangeStyle rangeStyle = workBook.getRangeStyle(1, 1, 2, 2);//get format from range B2:C3
rangeStyle.FontName = "Arial";
rangeStyle.FontSize = 14 * 20;
rangeStyle.FontColor = Color.Blue.ToArgb();
rangeStyle.FontItalic = true;
rangeStyle.FontBold = true;
workBook.setRangeStyle(rangeStyle, 1, 1, 2, 2);//set format for range B2:C3

 
top
Border Setting

formatting cell with border setting

RangeStyle rangeStyle = workBook.getRangeStyle(1, 1, 1, 1);//get format from cell B2
rangeStyle.LeftBorder = RangeStyle.BorderThin;
rangeStyle.LeftBorderColor = Color.DarkOrange.ToArgb();
rangeStyle.TopBorder = RangeStyle.BorderMedium;
rangeStyle.RightBorder = RangeStyle.BorderHair;
rangeStyle.BottomBorder = RangeStyle.BorderMediumDashDotDot;
//apply to the horizon border of the whole range
rangeStyle.HorizontalInsideBorder = RangeStyle.BorderThin;
//apply to the vertical border of the whole range
rangeStyle.VerticalInsideBorder = RangeStyle.BorderThin;
workBook.setRangeStyle(rangeStyle, 1, 1, 1, 1);//set format for cell B2

 
top
Pattern Setting.

formatting cell with fill/pattern setting

RangeStyle rangeStyle = workBook.getRangeStyle(1, 1, 2, 2);//get format from range B2:C3
rangeStyle.Pattern = RangeStyle.PatternSolid;
rangeStyle.PatternFG = Color.Red.ToArgb();
workBook.setRangeStyle(rangeStyle, 1, 1, 2, 2);//set format for range B2:C3

 
top
Conditional Formatting.

apply conditional formatting to cell

WorkBook workBook = new WorkBook();

ConditionFormat[] condfmt = new ConditionFormat[3];
condfmt[0] = workBook.CreateConditionFormat();
condfmt[1] = workBook.CreateConditionFormat();
condfmt[2] = workBook.CreateConditionFormat();

// Condition #1
RangeStyle cf = condfmt[0].RangeStyle;
condfmt[0].Type = ConditionFormat.eTypeFormula;
condfmt[0].setFormula1("and(iseven(row()), $D1 > 1000)", 0, 0);
cf.FontColor = 0x00ff00;
cf.Pattern = RangeStyle.PatternSolid;
cf.PatternFG = 0xcc99ff;
condfmt[0].RangeStyle = cf;

// Condition #2
condfmt[1].Type = ConditionFormat.eTypeFormula;
condfmt[1].setFormula1("iseven($A1)", 0, 0);
cf.FontColor = 0xffffff;
condfmt[1].RangeStyle = cf;

// Condition #3
condfmt[2].Type = ConditionFormat.eTypeCell;
condfmt[2].setFormula1("500", 0, 0);
condfmt[2].Operator = ConditionFormat.eOperatorGreaterThan;
cf = condfmt[2].RangeStyle;
cf.FontColor = 0xff0000;
condfmt[2].RangeStyle = cf;

// Select the range and apply conditional formatting
workBook.setSelection(0, 0, 39, 3);
workBook.ConditionalFormats = condfmt;

 
top
Merge/Unmerge Range.

merge/unmerge range of cells

//range contain merged area can not be merged
RangeStyle rangeStyle = workBook.getRangeStyle(1, 1, 2, 2);//get format from range B2:C3
rangeStyle.MergeCells = true;//merge range
//rangeStyle.MergeCells = false;//unmerge range
workBook.setRangeStyle(rangeStyle, 1, 1, 2, 2);//set format for range B2:C3

 
top
Rich Text Formatting.

apply multi-format-text formatting to cell

WorkBook workBook = new WorkBook();

//set data
workBook.setText(0, 0, "Hello, you are welcome!");
workBook.setActiveCell(0,0);
//text orientation
RangeStyle rangeStyle = workBook.getRangeStyle(0,0,0,0);
rangeStyle.Orientation = (short) 45;
workBook.setRangeStyle(rangeStyle);

//multi text selection format
workBook.setTextSelection(0, 6);
rangeStyle = workBook.getRangeStyle(0, 0, 0, 0);
rangeStyle.FontBold = true;
workBook.setRangeStyle(rangeStyle);

workBook.setTextSelection(7, 10);
rangeStyle = workBook.getRangeStyle(0, 0, 0, 0);
rangeStyle.FontItalic = true;
workBook.setRangeStyle(rangeStyle);

workBook.setTextSelection(11, 14);
rangeStyle = workBook.getRangeStyle(0, 0, 0, 0);
rangeStyle.FontUnderline = RangeStyle.UnderlineSingle;
workBook.setRangeStyle(rangeStyle);

workBook.setTextSelection(15, 22);
rangeStyle = workBook.getRangeStyle(0, 0, 0, 0);
rangeStyle.FontSize = 14 * 20;
workBook.setRangeStyle(rangeStyle);

workBook.write("TextFormatting.xls");

 
top
Text Wrapping.

formatting cell with text wrapped

RangeStyle rangeStyle = workBook.getRangeStyle(1, 1, 2, 2);//get format from range B2:C3
rangeStyle.WordWrap = true;//text wrapped
workBook.setRangeStyle(rangeStyle, 1, 1, 2, 2);//set format for range B2:C3