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 a worksheet using the setHorizontalAlignment method, setVerticalAlignment method, setAddIndent method and setReadingOrder methods of the IRange interface.

    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);

    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);