A cell style is a defined set of formatting characteristics, like fonts and font sizes, number formatting, and cell borders etc. In Microsoft Excel, you can set cell styles to make some data standout from others or make your spreadsheets more eye-catching. In this article, I am going to describe how to achieve the same function programmatically using Java.
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:
Top comments (0)