Formatting(VB.Net)

 
top
Number Formatting

 formatting cell value with the custom format pattern string

workBook.setSelection("A2:B4")
Dim rangeStyle As 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

Dim rangeStyle As 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

Dim rangeStyle As 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

Dim rangeStyle As 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

Dim rangeStyle As 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

Dim workBook As New WorkBook

Dim condfmt(3) As ConditionFormat
condfmt(0) = workBook.CreateConditionFormat()
condfmt(1) = workBook.CreateConditionFormat()
condfmt(2) = workBook.CreateConditionFormat()

' Condition #1
Dim cf As RangeStyle
cf = condfmt(0).RangeStyle
condfmt(0).Type = ConditionFormat.eTypeFormula
condfmt(0).setFormula1("and(iseven(row()), $D1 > 1000)", 0, 0)
cf.FontColor = &HFF00
cf.Pattern = RangeStyle.PatternSolid
cf.PatternFG = &HCC99FF
condfmt(0).RangeStyle = cf

' Condition #2
condfmt(1).Type = ConditionFormat.eTypeFormula
condfmt(1).setFormula1("iseven($A1)", 0, 0)
cf.FontColor = &HFFFFFF
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 = &HFF0000
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

Dim rangeStyle As RangeStyle
'range contain merged area can not be merged
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

Dim workBook As New WorkBook

'set data
workBook.setText(0, 0, "Hello, you are welcome!")
workBook.setActiveCell(0, 0)
'text orientation
Dim RangeStyle As RangeStyle
RangeStyle = workBook.getRangeStyle(0, 0, 0, 0)
RangeStyle.Orientation = 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

Dim rangeStyle As 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