dev-resources.site

for different kinds of informations.

Set Cell Styles and Formatting in Excel with Java

Published at

9/10/2021

Prerequisite: Add Dependencies

In order to set cell styles and formatting, I use Free Spire.XLS for Java library. To begin, you need to add dependencies to include Free Spire.XLS for Java into your Java project. For maven project, add the following configuration to the project’s pom.xml file.

<repositories>    
    <repository>    
        <id>com.e-iceblue</id>    
        <name>e-iceblue</name>    
        <url>http://repo.e-iceblue.com/nexus/content/groups/public/</url>    
    </repository>    
</repositories>    
<dependencies>    
    <dependency>    
        <groupId> e-iceblue </groupId>    
        <artifactId>spire.xls.free</artifactId>    
        <version>3.9.1</version>    
    </dependency>    
</dependencies>

For non-maven project, download Free Spire.XLS for Java pack from this website, extract the zip file and add Spire.Xls.jar in the lib folder into the project as a dependency.

Set Cell Styles and Formatting

Using Free Spire.XLS for Java, you can set cell styles (borders, patterns, gradients, alignments, text orientation, direction, wrapping, shrinking, indentation etc.), number formatting and font formatting (font name, size, style, color, superscript, subscript etc.), as shown in the following example.

import com.spire.xls.*;

import java.awt.*;

public class CellStyles {
    public static void main(String []args){
        //Create an Excel file
        Workbook workbook = new Workbook();
        workbook.setVersion(ExcelVersion.Version2016);
        //If you want to load an Excel file, use loadFromFile method
        //workbook.loadFromFile("test.xlsx");

        //Get the first worksheet
        Worksheet sheet = workbook.getWorksheets().get(0);

        int row = 2;

        //Set borders
        sheet.getCellRange(row, 1).setText("Borders");
        sheet.getCellRange(row, 2).getBorders().setLineStyle(LineStyleType.Thin);
        sheet.getCellRange(row, 2).getBorders().getByBordersLineType(BordersLineType.DiagonalUp).setLineStyle(LineStyleType.None);
        sheet.getCellRange(row, 2).getBorders().getByBordersLineType(BordersLineType.DiagonalDown).setLineStyle(LineStyleType.None);
        sheet.getCellRange(row, 2).getBorders().setColor(Color.RED);       

        //Set pattern
        sheet.getCellRange(row += 2, 1).setText("Pattern");
        sheet.getCellRange(row, 2).getCellStyle().setFillPattern(ExcelPatternType.Angle);
        sheet.getCellRange(row, 2).getCellStyle().setPatternColor(Color.GREEN);

        //Set gradient effect
        sheet.getCellRange(row += 2, 1).setText("Gradient");
        sheet.getCellRange(row , 2).getStyle().getInterior().setFillPattern( ExcelPatternType.Gradient);//Not applicable for Excel 97-2003
        sheet.getCellRange(row, 2).getStyle().getInterior().getGradient().setForeColor(Color.CYAN);
        sheet.getCellRange(row, 2).getStyle().getInterior().getGradient().setBackColor( Color.BLUE);
        sheet.getCellRange(row, 2).getStyle().getInterior().getGradient().twoColorGradient(GradientStyleType.Horizontal, GradientVariantsType.ShadingVariants1);

        //Set number formatting
        sheet.getCellRange(row += 2, 1).setText("Number Formatting");
        sheet.getCellRange(row, 2).setNumberValue(1234.5678);
        sheet.getCellRange(row, 2).setNumberFormat("$#,##0.00");

        //Set font formatting
        sheet.getCellRange(row += 2, 1).setText("Font Formatting");
        sheet.getCellRange(row, 2).setText("Hello World");
        sheet.getCellRange(row, 2).getStyle().getFont().setFontName("Consolas");
        sheet.getCellRange(row, 2).getStyle().getFont().setSize(14);
        sheet.getCellRange(row, 2).getStyle().getFont().isItalic(true) ;
        sheet.getCellRange(row, 2).getStyle().getFont().setUnderline(FontUnderlineType.Single);
        sheet.getCellRange(row, 2).getStyle().getFont().setColor(Color.BLUE);

        //Set superscript (the code to set subscript is very similar)
        sheet.getCellRange(row += 2, 1).setText("Superscript");
        sheet.getCellRange(row, 2).getRichText().setText("a2 + b2 = c2");
        ExcelFont font = workbook.createFont();
        font.isSuperscript(true);
        //Set font for specific characters
        sheet.getCellRange(row, 2).getRichText().setFont(1, 1, font);
        sheet.getCellRange(row, 2).getRichText().setFont(6, 6, font);
        sheet.getCellRange(row, 2).getRichText().setFont(11, 11, font);

        //Set text alignment
        sheet.getCellRange(row += 2, 1).setText("Text Alignment");
        sheet.getCellRange(row, 2).setText("Center Aligned");
        sheet.getCellRange(row, 2).getStyle().setHorizontalAlignment(HorizontalAlignType.Center);
        sheet.getCellRange(row, 2).getStyle().setVerticalAlignment(VerticalAlignType.Center);

        //Set text orientation
        sheet.getCellRange(row += 2, 1).setText("Text Orientation");
        sheet.getCellRange(row, 2).setText("25 degree");
        sheet.getCellRange(row, 2).getStyle().setRotation(25);

        //Set text direction
        sheet.getCellRange(row += 2, 1).setText("Text Direction");
        sheet.getCellRange(row, 2).setText("Direction");
        sheet.getCellRange(row, 2).getStyle().setReadingOrder(ReadingOrderType.LeftToRight);

        //Set text wrapping
        sheet.getCellRange(row += 2, 1).setText("Text Wrapping");
        sheet.getCellRange(row, 2).setText("Wrap Extra-long Text into Multiple Lines");
        sheet.getCellRange(row, 2).getStyle().setWrapText(true);

        //Set text shrinking
        sheet.getCellRange(row += 2, 1).setText("Text Shrinking");
        sheet.getCellRange(row, 2).setText("Shrink Text to Fit in the Cell");
        sheet.getCellRange(row, 2).getStyle().setShrinkToFit(true);

        //Set indentation
        sheet.getCellRange(row += 2, 1).setText("Indentation");
        sheet.getCellRange(row, 2).setText("Two");
        sheet.getCellRange(row, 2).getStyle().setIndentLevel(2);

        //Set row height
        for(int rowCount = 1; rowCount <= sheet.getLastRow(); rowCount++) {
            sheet.setRowHeight(rowCount, 25);
        }
        //Set column width
        sheet.setColumnWidth(1, 20);
        sheet.setColumnWidth(2, 20);

        //Save the result file
        workbook.saveToFile("StylesAndFormatting.xlsx", ExcelVersion.Version2016);
    }
}

The following is the output Excel file after setting cell styles and formatting:

formatting Article's

30 articles in total

How to Configure VSCode for Auto Formatting and Linting in Python