SmartXLS Software

• Number Formatting.
• Alignment Setting.
• Font Setting.
• Border Setting.
• Pattern Setting.
• Conditional Formatting.
• Merge/Unmerge Range.
• Rich Text Formatting.
• Text Wrapping.

Number Formatting

 formatting cell value with the custom format pattern string

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

Alignment Setting

formatting cell with alignment setting

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

Font Setting

formatting cell with font setting

rangeStyle = workBook.getRangeStyle(1, 1, 2, 2);//get format from range B2:C3
rangeStyle.setFontName("Arial");
rangeStyle.setFontSize(14 * 20);
rangeStyle.setFontColor(Color.blue.getRGB());
rangeStyle.setFontItalic(true);
rangeStyle.setFontBold(true);
workBook.setRangeStyle(rangeStyle, 1, 1, 2, 2);//set format for range B2:C3

Border Setting

formatting cell with border setting

rangeStyle = workBook.getRangeStyle(1, 1, 1, 1);//get format from cell B2
rangeStyle.setLeftBorder(RangeStyle.BorderThin);
rangeStyle.setLeftBorderColor(Color.darkGray.getRGB());
rangeStyle.setTopBorder(RangeStyle.BorderMedium);
rangeStyle.setRightBorder(RangeStyle.BorderHair);
rangeStyle.setBottomBorder(RangeStyle.BorderMediumDashDotDot);
//apply to the horizon border of the whole range
rangeStyle.setHorizontalInsideBorder(RangeStyle.BorderThin);
//apply to the vertical border of the whole range
rangeStyle.setVerticalInsideBorder(RangeStyle.BorderThin);
workBook.setRangeStyle(rangeStyle, 1, 1, 1, 1);//set format for cell B2

Pattern Setting.

formatting cell with fill/pattern setting

rangeStyle = workBook.getRangeStyle(1, 1, 2, 2);//get format from range B2:C3
rangeStyle.setPattern(RangeStyle.PatternSolid);
rangeStyle.setPatternFG(Color.red.getRGB());
workBook.setRangeStyle(rangeStyle, 1, 1, 2, 2);//set format for range B2:C3

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].getRangeStyle();
condfmt[0].setType(ConditionFormat.eTypeFormula);
condfmt[0].setFormula1("and(iseven(row()), $D1 > 1000)", 0, 0);
cf.setFontColor(0x00ff00);
cf.setPattern(RangeStyle.PatternSolid);
cf.setPatternFG(0xcc99ff);
cf.setCustomFormat("yyyy/mm/dd h:mm tt");
condfmt[0].setRangeStyle(cf);

// Condition #2
condfmt[1].setType(ConditionFormat.eTypeFormula);
condfmt[1].setFormula1("iseven($A1)", 0, 0);
cf.setFontColor(0xffffff);
condfmt[1].setRangeStyle(cf);

// Condition #3
condfmt[2].setType(ConditionFormat.eTypeCell);
condfmt[2].setFormula1("500", 0, 0);
condfmt[2].setOperator(ConditionFormat.eOperatorGreaterThan);
cf=condfmt[2].getRangeStyle();
cf.setFontColor(0xff0000);
condfmt[2].setRangeStyle(cf);
// Select the range and apply conditional formatting
workBook.setSelection(0, 0, 39, 3);
workBook.setConditionalFormats(condfmt);

Merge/Unmerge Range.

merge/unmerge range of cells

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

Rich Text Formatting.

apply multi-format-text formatting to cell

WorkBook workBook = new WorkBook();
//set data
workBook.setText(0,0,"Hello, you are welcome!");

//text orientation
RangeStyle rangeStyle = workBook.getRangeStyle();
rangeStyle.setOrientation((short)45);
workBook.setRangeStyle(rangeStyle);

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

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

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

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

workBook.write("TextFormatting.xls");

Text Wrapping.

formatting cell with text wrapped

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