Document Solutions for Excel, Java Edition | Document Solutions
Features / Styles / Set Sheet Styling
In This Topic
    Set Sheet Styling
    In This Topic

    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.

    Java
    Copy Code 
    // 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.

    Java
    Copy Code 
    // 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.

    Java
    Copy Code 
    // 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.

    Java
    Copy Code 
    // 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.

    Java
    Copy Code 
    // 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.

    Java
    Copy Code 
    // 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.

    Java
    Copy Code 
    // 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:

    Java
    Copy Code 
    // 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);

    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 Protection

    You can set protection for your worksheet using the setFormulaHidden method and setLocked method of the IRange interface.

    Refer to the following example code to set protection for your worksheet.

    Java
    Copy Code 
    // Set protection
worksheet.getRange("A1").setLocked(true);
worksheet.getRange("A1").setFormulaHidden(true);