Features / Styles / Create and Set Custom Named Style
Create and Set Custom Named Style

A custom cell style that is applied to the worksheet with a unique name is called Named style. Named styles are typically different from the built-in style names defined for a spreadsheet.

You can create and set custom named styles based on specific requirements. You can also modify an existing style and save it as a new workbook style. In DsExcel Java, Styles refers to the named style collection that stores both the built-in and custom named styles.

While working with styles in the spreadsheets, you can use the following ways -

Create and Set a Custom Named Style

DsExcel Java enables you to define custom named styles for your worksheet, configure it as per your preferences and store them in the collection so that they can be accessed later.

You can add a custom named style to your worksheet using the methods of IStyleCollection interface. This method can also be used to return an IStyle instance. If you want to configure the named style settings in your spreadsheet, you can use the methods of the IStyle interface.

Refer to the following example code to create a custom named style and configure its settings.

Java
Copy Code
// Add custom name style.
IStyle style = workbook.getStyles().add("testStyle");

// Configure custom name style settings begin.
// Border
style.getBorders().get(BordersIndex.EdgeLeft).setLineStyle(BorderLineStyle.Thin);
style.getBorders().get(BordersIndex.EdgeTop).setLineStyle(BorderLineStyle.Thick);
style.getBorders().get(BordersIndex.EdgeRight).setLineStyle(BorderLineStyle.Double);
style.getBorders().get(BordersIndex.EdgeBottom).setLineStyle(BorderLineStyle.Double);
style.getBorders().setColor(Color.FromArgb(0, 255, 0));

// Font
style.getFont().setThemeColor(ThemeColor.Accent1);
style.getFont().setTintAndShade(0.8);
style.getFont().setItalic(true);
style.getFont().setBold(true);
style.getFont().setName("LiSu");
style.getFont().setSize(28);
style.getFont().setStrikethrough(true);
style.getFont().setSubscript(true);
style.getFont().setSuperscript(false);
style.getFont().setUnderline(UnderlineType.Double);

// Protection
style.setFormulaHidden(true);
style.setLocked(false);

// Number
style.setNumberFormat("#,##0_);[Red](#,##0)");

// Alignment
style.setHorizontalAlignment(HorizontalAlignment.Right);
style.setVerticalAlignment(VerticalAlignment.Bottom);
style.setWrapText(true);
style.setIndentLevel(5);
style.setOrientation(45);

// Fill
style.getInterior().setColorIndex(5);
style.getInterior().setPattern(Pattern.Down);
style.getInterior().setPatternColor(Color.FromArgb(0, 0, 255));
style.setIncludeAlignment(false);
style.setIncludeBorder(true);
style.setIncludeFont(false);
style.setIncludeNumber(true);
style.setIncludePatterns(false);
style.setIncludeProtection(true);

You can get or set named style in a worksheet using the setStyle method of the IRange interface.

Refer to the following example code to get or set named style in your worksheet.

Java
Copy Code
// Set range's style to custom name style.
worksheet.getRange("A1").setStyle(worksheet.getWorkbook().getStyles().get("testStyle"));
worksheet.getRange("A1").setValue(123);

Modify an Existing Style and Save it as a New Workbook Style

With DsExcel Java, it is not necessary to create a custom named style right from the scratch. Instead, you can tweak an existing style (via getting the existing style from the Styles collection) as per your custom requirements and save the new style as another workbook style that can be used as and when required.

Users can use the add method of the IStyleCollection interface in order to add the new style. The custom style will be based on the existing workbook style and will be stored in the IStyleCollection interface so that it can be used as another workbook style in the future.

Refer to the following example code to modify an existing style and save it as another workbook style in the Styles collection.

Java
Copy Code
        
// Create a new workbook
Workbook workbook = new Workbook();
        
// Fetch default worksheet
IWorksheet worksheet = workbook.getWorksheets().get(0);

// Fetch existing Style "Good" and set to Range A1's Style
worksheet.getRange("A1").setStyle(workbook.getStyles().get("Good"));
        
// Setting Cell Text
worksheet.getRange("A1").setValue("Good");

// Create and modify a style based on current existing style "Good" and name it as "MyGood"
IStyle myGood = workbook.getStyles().add("MyGood", workbook.getStyles().get("Good"));
myGood.getFont().setBold(true);
myGood.getFont().setItalic(true);

// Set new style "MyGood" to Range B1's Style
worksheet.getRange("B1").setStyle(workbook.getStyles().get("MyGood"));
        
// Setting Cell Text
worksheet.getRange("B1").setValue("MyGood");

// Saving workbook
workbook.save("2-CreateModifyStyleBasedOnAStyle.xlsx");
        

Note: The following limitations must be kept in mind while exporting Excel files with vertical text to PDF -
  • The orientation can only be set to 0, 90, -90 and 255. Other values will be treated as 0 while rendering the PDF file.
  • If the font name starts with "@" and the orientation is 255, DsExcel will ignore the "@".