Set Sheet Styling

DsExcel Java enables users to set sheet styling to worksheets by performing actions like setting different fill styles for a cell, customizing the cell border and configuring the fonts for the spreadsheets etc.

Set Fill

You can set the fill style for a cell by using the getInterior method of the IRange interface. A cell interior can be of three types, namely, solid fill, pattern fill and gradient fill.

Solid Fill

You can specify the fill style for the cell as solid by setting the setPattern method of the IInterior interface.

Refer to the following example code to set solid fill.

// Solid fill for B5 
worksheet.getRange("B5").getInterior().setPattern(Pattern.Solid);
worksheet.getRange("B5").getInterior().setColor(Color.FromArgb(255, 0, 255));

Pattern Fill

You can integrate pattern fill in cells using the Pattern method of the IInterior interface to one of the valid pattern types.

Pattern fill also consists of two parts - background Color and foreground Color.

You can use the methods of the IInterior interface to set the background color and the foreground color as per your preferences.

Refer to the following example code to set pattern fill.

// Pattern fill for A1
worksheet.getRange("A1").getInterior().setPattern(Pattern.LightDown);
worksheet.getRange("A1").getInterior().setColor(Color.FromArgb(255, 0, 255));
worksheet.getRange("A1").getInterior().setPatternColorIndex(5);

Gradient Fill

You can integrate gradient fill in cells using the getGradient method of the IInterior interface.

Gradient fill can be of two types - Linear Gradient Fill and Rectangle Gradient Fill.

Linear Gradient Fill

You can set the linear gradient fill using the methods of the ILinearGradient interface.

Refer to the following example code to set linear gradient fill.

// Gradient fill for A1
worksheet.getRange("A1").getInterior().setPattern(Pattern.LinearGradient);
((ILinearGradient) worksheet.getRange("A1").getInterior().getGradient()).getColorStops().get(0)
        .setColor(Color.FromArgb(255, 0, 0));
((ILinearGradient) worksheet.getRange("A1").getInterior().getGradient()).getColorStops().get(1)
        .setColor(Color.FromArgb(255, 255, 0));
((ILinearGradient) worksheet.getRange("A1").getInterior().getGradient()).setDegree(90);

Rectangular Gradient Fill

You can also set the rectangular gradient fill using the methods of the IRectangularGradient interface.

Refer to the following example code to set rectangular gradient fill.

// Rectangular gradient fill for A1
worksheet.getRange("A1").getInterior().setPattern(Pattern.RectangularGradient);
((IRectangularGradient) worksheet.getRange("A1").getInterior().getGradient()).getColorStops().get(0)
        .setColor(Color.FromArgb(255, 0, 0));
((IRectangularGradient) worksheet.getRange("A1").getInterior().getGradient()).getColorStops().get(1)
        .setColor(Color.FromArgb(0, 255, 0));

((IRectangularGradient) worksheet.getRange("A1").getInterior().getGradient()).setBottom(0.2);
((IRectangularGradient) worksheet.getRange("A1").getInterior().getGradient()).setRight(0.3);
((IRectangularGradient) worksheet.getRange("A1").getInterior().getGradient()).setTop(0.4);
((IRectangularGradient) worksheet.getRange("A1").getInterior().getGradient()).setLeft(0.5);

Set Font

You can customize the font of a worksheet using the getFont method of IRange interface.

Refer to the following example code to set font style in your worksheet.

// Set font
worksheet.getRange("A1").setValue("aaa");
worksheet.getRange("A1").getFont().setThemeColor(ThemeColor.Accent1);
worksheet.getRange("A1").getFont().setTintAndShade(-0.5);
worksheet.getRange("A1").getFont().setThemeFont(ThemeFont.Major);
worksheet.getRange("A1").getFont().setBold(true);
worksheet.getRange("A1").getFont().setSize(20);
worksheet.getRange("A1").getFont().setStrikethrough(true);

Set Border

You can customize the border of a worksheet using the getBorders method of the IRange interface.

Refer to the following example code to set border in your worksheet.

// Set border
worksheet.getRange("A1:B5").getBorders().setLineStyle(BorderLineStyle.DashDot);
worksheet.getRange("A1:B5").getBorders().setThemeColor(ThemeColor.Accent1);
worksheet.getRange("A1:B5").getBorders().get(BordersIndex.EdgeRight).setLineStyle(BorderLineStyle.Double);
worksheet.getRange("A1:B5").getBorders().get(BordersIndex.EdgeRight).setThemeColor(ThemeColor.Accent2);
worksheet.getRange("A1:B5").getBorders().get(BordersIndex.DiagonalDown).setLineStyle(BorderLineStyle.Double);
worksheet.getRange("A1:B5").getBorders().get(BordersIndex.DiagonalDown).setThemeColor(ThemeColor.Accent5);

Set Number Format

You can set the number format in a worksheet using the setNumberFormat method of the IRange interface.

Refer to the following example code to set number format in your worksheet.

// Set number format
worksheet.getRange("A1").setValue(12);
worksheet.getRange("A1").setNumberFormat("$#,##0.00");

Set Alignment

You can customize the alignment of cells using the following methods: setHorizontalAlignment, setVerticalAlignment, setAddIndent, and setReadingOrder of the IRange interface. setHorizontalAlignment method sets the horizontal alignment of a cell, whereas setVerticalAlignment method sets the vertical alignment of a cell. Furthermore, setAddIndent method sets the indent of text when the horizontal or vertical alignment is set to Distributed. setReadingOrder method sets the direction in which the content within a cell or range of cells will be read.

The following table lists the alignment options in setHorizontalAlignment and setVerticalAlignment methods:

Method	Option	Example
setHorizontalAlignment	Center
	CenterContinuous
	Distributed
	Fill
	General
	Justify
	Left
	Right
setVerticalAlignment	Bottom
	Center
	Distributed
	Justify
	Top

Refer to the following example code to set alignment in your worksheet:

// Set alignment
worksheet.getRange("A1").setHorizontalAlignment(HorizontalAlignment.Distributed);
worksheet.getRange("A1").setAddIndent(true);
worksheet.getRange("A1").setVerticalAlignment(VerticalAlignment.Top);
worksheet.getRange("A1").setReadingOrder(ReadingOrder.RightToLeft);

!type=note
Note:
AddIndent will work in East Asian languages, as different languages have different split word strategies and the effects will be different.
Distributed layout will only take effect if the text orientation is set to 0, 90, -90, and 255. Any value less than 0 will display as the ＾right＾ horizontal alignment with wrap text, and any value above 0 will display as the ＾left＾ horizontal alignment with wrap text.

Set Fill
Solid Fill
Pattern Fill
Gradient Fill
Set Font
Set Border
Set Number Format
Set Alignment