Data Formatting

Approaches to Format Data in Cells

It’s a common fact that if the worksheet cells are formatted properly then it becomes easier for the users to read the contents (data) of the cell. There are many ways to format cells and their contents. The simplest way is to format cells using Microsoft Excel in a WYSIWYG environment while creating a Designer Spreadsheet. After the designer spreadsheet is created, you can open the spreadsheet using Aspose.Cells keeping all format settings saved with the spreadsheet. Another way to format cells and their contents is to use Aspose.Cells API. In this topic, we’ll describe two approaches to format cells and their contents with the use of Aspose.Cells API.

Formatting Cells

Developers can format cells and their contents using the flexible API of Aspose.Cells. Aspose.Cells provides a class, Workbook, that represents a Microsoft Excel file. The Workbook class contains a WorksheetCollection that allows access to each worksheet in an Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a Cells collection. Each item in the Cells collection represents an object of Cell class.

Aspose.Cells provides the Style property in the Cell class, used to set the formatting style of a cell. Moreover, Aspose.Cells also provides a Style class that is used to serve the same purpose. Apply different kinds of formatting styles on the cells to set their background or foreground colors, borders, fonts, horizontal and vertical alignments, indentation level, text direction, rotation angle and much more.

Using the setStyle Method

When applying different formatting styles to different cells then it’s better to use the setStyle method of the Cell class. An example is given below to demonstrate the use of setStyle method to apply various formatting settings on a cell.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getDataDir(FormattingCellsUsingsetStyleMethod.class);
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();

// Accessing the "A1" cell from the worksheet
Cell cell = cells.get("A1");

// Adding some value to the "A1" cell
cell.setValue("Hello Aspose!");

Style style = cell.getStyle();

// Setting the vertical alignment of the text in the "A1" cell
style.setVerticalAlignment(TextAlignmentType.CENTER);

// Setting the horizontal alignment of the text in the "A1" cell
style.setHorizontalAlignment(TextAlignmentType.CENTER);

// Setting the font color of the text in the "A1" cell
Font font = style.getFont();
font.setColor(Color.getGreen());

// Setting the cell to shrink according to the text contained in it
style.setShrinkToFit(true);

// Setting the bottom border
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.MEDIUM, Color.getRed());

// Saved style
cell.setStyle(style);

// Saving the modified Excel file in default (that is Excel 2003) format
workbook.save(dataDir + "output.xls");

Using the Style Object

When applying the same formatting style to different cells, use the Style object.

1. Add a Style object to the Styles collection of the Workbook class by calling the createStyle method of the Workbook class.
2. Access the newly added Style object from the Styles collection.
3. Set the desired properties of the Style object to apply desired formatting settings.
4. Assign the configured Style object to the Style property of any desired cell.

This approach can greatly improve the efficiency of your applications and save memory too.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(FormattingCellsUsingStyleObject.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook(dataDir + "book1.xls");

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();

// Accessing the "A1" cell from the worksheet
Cell cell = cells.get("A1");

// Adding some value to the "A1" cell
cell.setValue("Hello Aspose!");

// Adding a new Style to the styles collection of the Excel object
Style style = workbook.createStyle();

// Setting the vertical alignment of the text in the "A1" cell
style.setVerticalAlignment(TextAlignmentType.CENTER);

// Setting the horizontal alignment of the text in the "A1" cell
style.setHorizontalAlignment(TextAlignmentType.CENTER);

// Setting the font color of the text in the "A1" cell
Font font = style.getFont();
font.setColor(Color.getGreen());

// Setting the cell to shrink according to the text contained in it
style.setShrinkToFit(true);

// Setting the bottom border
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.MEDIUM, Color.getRed());

// Saved style
cell.setStyle(style);

// Saving the modified Excel file in default format
workbook.save(dataDir + "FCUsingStyleObject_out.xls");

Applying Gradient Fill Effects

To apply your desired Gradient Fill Effects to the cell, use the Style object’s setTwoColorGradient method accordingly.

Code Example

The following output is achieved by executing the code below.

Applying Gradient Fill Effects

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ApplyGradientFillEffects.class) + "data/";
// Instantiate a new Workbook
Workbook workbook = new Workbook();
// Get the first worksheet (default) in the workbook
Worksheet worksheet = workbook.getWorksheets().get(0);
// Input a value into B3 cell
worksheet.getCells().get(2, 1).putValue("test");

// Get the Style of the cell
Style style = worksheet.getCells().get("B3").getStyle();
// Set Gradient pattern on
style.setGradient(true);
// Specify two color gradient fill effects
style.setTwoColorGradient(Color.fromArgb(255, 255, 255), Color.fromArgb(79, 129, 189),
		GradientStyleType.HORIZONTAL, 1);
// Set the color of the text in the cell
style.getFont().setColor(Color.getRed());
// Specify horizontal and vertical alignment settings
style.setHorizontalAlignment(TextAlignmentType.CENTER);
style.setVerticalAlignment(TextAlignmentType.CENTER);

// Apply the style to the cell
worksheet.getCells().get("B3").setStyle(style);

// Set the third row height in pixels
worksheet.getCells().setRowHeightPixel(2, 53);

// Merge the range of cells (B3:C3)
worksheet.getCells().merge(2, 1, 1, 2);

// Save the Excel file
workbook.save(dataDir + "ApplyGradientFillEffects_out.xlsx");

Configuring Alignment Settings

Anyone who has used Microsoft Excel to format cells will be familiar with the alignment settings in Microsoft Excel.

Alignment settings in Microsoft Excel

As you can see from the above figure, there are different kinds of alignment options:

• Text alignment (horizontal & vertical)
• Indentation.
• Orientation.
• Text control.
• Text direction.

All of these alignment settings are fully supported by Aspose.Cells and are discussed in more detail below.

Configuring Alignment Settings

Aspose.Cells provides a class, Workbook, that represents an Excel file. The Workbook class contains a WorksheetCollection that allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet class.

The Worksheet class provides a Cells collection. Each item in the Cells collection represents an object of the Cell class.

Aspose.Cells provides the setStyle method in the Cell class that is used a cell’s formatting. The Style class provides useful properties for configuring font settings.

Select any text alignment type using the TextAlignmentType enumeration. The pre-defined text alignment types in the TextAlignmentType enumeration are:

Text Alignment Types	Description
Bottom	Represents bottom text alignment
Center	Represents center text alignment
CenterAcross	Represents center across text alignment
Distributed	Represents distributed text alignment
Fill	Represents fill text alignment
General	Represents general text alignment
Justify	Represents justify text alignment
Left	Represents left text alignment
Right	Represents right text alignment
Top	Represents top text alignment
You can also apply justify distributed setting using the Style.setJustifyDistributed() method.

Horizontal Alignment

Use the Style object’s setHorizontalAlignment method to align the text horizontally.

The following output is achieved by executing the example code below:

Aligning the text horizontally

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(TextAlignmentHorizontal.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Accessing the added worksheet in the Excel file
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = worksheet.getCells();

// Adding the current system date to "A1" cell
Cell cell = cells.get("A1");
Style style = cell.getStyle();

// Adding some value to the "A1" cell
cell.setValue("Visit Aspose!");

// Setting the horizontal alignment of the text in the "A1" cell
style.setHorizontalAlignment(TextAlignmentType.CENTER);

// Saved style
cell.setStyle(style);

// Saving the modified Excel file in default format
workbook.save(dataDir + "TAHorizontal_out.xls");

Vertical Alignment

Use the Style object’s setVerticalAlignment mehod to align the text vertically.

The following output is achieved when VerticalAlignment is set to center.

Aligning the text vertically

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(TextAlignmentVertical.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Accessing the added worksheet in the Excel file
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = worksheet.getCells();

// Adding the current system date to "A1" cell
Cell cell = cells.get("A1");
Style style = cell.getStyle();

// Adding some value to the "A1" cell
cell.setValue("Visit Aspose!");

// Setting the vertical alignment of the text in a cell
Style style1 = cell.getStyle();
style1.setVerticalAlignment(TextAlignmentType.CENTER);
cell.setStyle(style1);

// Saved style
cell.setStyle(style1);

// Saving the modified Excel file in default format
workbook.save(dataDir + "TAVertical_out.xls");

Indentation

It is possible to set the indentation level of the text in a cell by using the Style object’s setIndentLevel method.

The following output is achieved when IndentLevel is set to 2.

Indentation level adjusted to 2

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(Indentation.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Accessing the added worksheet in the Excel file
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = worksheet.getCells();

// Adding the current system date to "A1" cell
Cell cell = cells.get("A1");
Style style = cell.getStyle();

// Adding some value to the "A1" cell
cell.setValue("Visit Aspose!");

// Setting the vertical alignment of the text in a cell
Style style1 = cell.getStyle();
style1.setIndentLevel(2);
cell.setStyle(style1);

// Saved style
cell.setStyle(style1);

// Saving the modified Excel file in default format
workbook.save(dataDir + "Indentation_out.xls");

Orientation

Set the orientation (rotation) of the text in a cell with the Style object’s setRotationAngle method.

The following output is achieved when the rotation angle is set to 25.

Rotation angle set to 25

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(Orientation.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Accessing the added worksheet in the Excel file
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = worksheet.getCells();

// Adding the current system date to "A1" cell
Cell cell = cells.get("A1");
Style style = cell.getStyle();

// Adding some value to the "A1" cell
cell.setValue("Visit Aspose!");

// Setting the rotation of the text (inside the cell) to 25
Style style1 = cell.getStyle();
style1.setRotationAngle(25);
cell.setStyle(style1);

// Saved style
cell.setStyle(style1);

// Saving the modified Excel file in default format
workbook.save(dataDir + "Orientation_out.xls");

Text Control

The following section discusses how to control text by setting text wrapping, shrink to fit and other formatting options.

Wrapping Text

Wrapping text in a cell makes it easier to read: the height of the cell adjusts to fit all the text, instead of cutting it off or spilling over into adjacent cells.

Set text wrapping on or off with the Style object’s setTextWrapped method.

The following output is achieved when text wrapping is enabled.

Text wrapped inside the cell

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(WrapText.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Accessing the added worksheet in the Excel file
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = worksheet.getCells();

// Adding the current system date to "A1" cell
Cell cell = cells.get("A1");
Style style = cell.getStyle();

// Adding some value to the "A1" cell
cell.setValue("Visit Aspose!");

// Enabling the text to be wrapped within the cell
Style style1 = cell.getStyle();
style1.setTextWrapped(true);
cell.setStyle(style1);

// Saved style
cell.setStyle(style1);

// Saving the modified Excel file in default format
workbook.save(dataDir + "WrapText_out.xls");

Shrinking to Fit

An option to wrapping text in a field is to shrink the text size to fit a cell’s dimensions. This is done by setting the Style object’s IsTextWrapped property to true.

The following output is achieved when text is shrunk to fit the cell.

Text shrinked to fit inside the boundaries of the cell

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ShrinkingToFit.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Accessing the added worksheet in the Excel file
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = worksheet.getCells();

// Adding the current system date to "A1" cell
Cell cell = cells.get("A1");
Style style = cell.getStyle();

// Adding some value to the "A1" cell
cell.setValue("Visit Aspose!");

// Shrinking the text to fit according to the dimensions of the cell
Style style1 = cell.getStyle();
style1.setShrinkToFit(true);
cell.setStyle(style1);

// Saved style
cell.setStyle(style1);

// Saving the modified Excel file in default format
workbook.save(dataDir + "ShrinkingToFit_out.xls");

Merging Cells

Like Microsoft Excel, Aspose.Cells supports merging several cells into one.

The following output is achieved if the three cells in the first row are merged to create a big single cell.

Three cells merged to create a big cell

Use the Cells collection’s Merge method to merge cells. The Merge method takes the following parameters:

• First row, the first row from where to start merging.
• First column, the first column from where to start merging.
• Number of rows, the number of rows to merge.
• Number of columns, the number of columns to merge.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(MergingCellsInWorksheet.class) + "data/";

// Create a Workbook.
Workbook wbk = new Workbook();

// Create a Worksheet and get the first sheet.
Worksheet worksheet = wbk.getWorksheets().get(0);

// Create a Cells object to fetch all the cells.
Cells cells = worksheet.getCells();

// Merge some Cells (C6:E7) into a single C6 Cell.
cells.merge(5, 2, 2, 3);

// Input data into C6 Cell.
worksheet.getCells().get(5, 2).setValue("This is my value");

// Create a Style object to fetch the Style of C6 Cell.
Style style = worksheet.getCells().get(5, 2).getStyle();

// Create a Font object
Font font = style.getFont();

// Set the name.
font.setName("Times New Roman");

// Set the font size.
font.setSize(18);

// Set the font color
font.setColor(Color.getBlue());

// Bold the text
font.setBold(true);

// Make it italic
font.setItalic(true);

// Set the backgrond color of C6 Cell to Red
style.setForegroundColor(Color.getRed());
style.setPattern(BackgroundType.SOLID);

// Apply the Style to C6 Cell.
cells.get(5, 2).setStyle(style);

// Save the Workbook.
wbk.save(dataDir + "mergingcells_out.xls");
wbk.save(dataDir + "mergingcells_out.xlsx");
wbk.save(dataDir + "mergingcells_out.ods");

// Print message
System.out.println("Process completed successfully");

Text Direction

It is possible to set the reading order of text in cells. The reading order is the visual order in which characters, words etc. are displayed. For example, English is a left to right language while Arabic is a right to left language.

The reading order is set with the Style object’s TextDirection property. Aspose.Cells provides pre-defined text direction types in the TextDirectionType enumeration.

Text Direction Types	Description
Context	The reading order consistent with the language of the first entered character
LeftToRight	Left to right reading order
RightToLeft	Right to left reading order

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ChangeTextDirection.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Accessing the added worksheet in the Excel file
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = worksheet.getCells();

// Adding the current system date to "A1" cell
Cell cell = cells.get("A1");
Style style = cell.getStyle();

// Adding some value to the "A1" cell
cell.setValue("Visit Aspose!");

// Setting the text direction from right to left
Style style1 = cell.getStyle();
style1.setTextDirection(TextDirectionType.RIGHT_TO_LEFT);
cell.setStyle(style1);
// Saved style
cell.setStyle(style1);

// Saving the modified Excel file in default format
workbook.save(dataDir + "ChangeTextDirection_out.xls");

The following output is achieved if the reading order of the text is set to right to left.

Setting text reading order to right to left

Formatting Selected Characters in a Cell

Dealing with Font Settings explained how to format cells but only how to format the content of the wntire cells. What if you want to format only selected characters?

Aspose.Cells supports this feature. This topic explains how to use this feature.

Formatting Selected Characters

Aspose.Cells provides a class, Workbook, that represents a Microsoft Excel file. The Workbook class contains a Worksheets collection that allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides a Cells collection. Each item in the Cells collection represents an object of the Cell class.

The Cell class provides characters method that takes the following parameters to select a range of characters in a cell:

• Start Index, the index of the character to start the selection from.
• Number of Characters, the number of characters to select.

In the output file, in the A1" cell, the word ‘Visit’ is formatted with the default font but ‘Aspose!’ is bold and blue.

Formatting selected characters

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Path to source file
String dataDir = Utils.getSharedDataDir(FormattingSelectedCharacters.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Accessing the added worksheet in the Excel file
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet = workbook.getWorksheets().get(sheetIndex);
Cells cells = worksheet.getCells();

// Adding some value to the "A1" cell
Cell cell = cells.get("A1");
cell.setValue("Visit Aspose!");

Font font = cell.characters(6, 7).getFont();

// Setting the font of selected characters to bold
font.setBold(true);

// Setting the font color of selected characters to blue
font.setColor(Color.getBlue());

// Saving the Excel file
workbook.save(dataDir + "FSCharacters_out.xls");

If you are interested in [formatting a portion of Rich Text in a cell, consider using the Cell.getCharacters & Cell.setCharacters methods. The Cell.getCharacters method is to be used to access the portions of the text and then amendments can be done using the Cell.setCharacters method whereas the get method returns an array of FontSetting objects which can be manipulated to set various properties font name, font color, boldness etc and set method can be used to apply the changes.

Activating Sheets and Making an Active Cell or Select a Range of Cells in the Worksheet

Sometimes, you may need to activate a specific worksheet so that it is the first that is displayed when someone opens the file in Microsoft Excel. You may also need to activate a specific cell in such a way that the scrollbars scrolls to the active cell so that it is clearly visible. Aspose.Cells is capable of doing all the above mentioned tasks.

An active sheet is the sheet that you’re working on in a workbook. The name on the tab of the active sheet is bold by default. An active cell, meanwhile, is the cell that’s selected and into which data is entered when you begin typing. Only one cell is active at a time. The active cell is surrounded by a heavy border to make it show up against the other cells. Aspose.Cells also allows you to select a range of cells in the worksheet.

Activating a Sheet and Making a Cell Active

Aspose.Cells provides a specific API for these tasks. For Example, the WorksheetCollection.setActiveSheetIndex method is useful for setting an active sheet. Similarly, the Worksheet.setActiveCell method is used to set and get an active cell in a worksheet.

If you want that the horizontal and vertical scrollbars to be scrolled to the row and column index position to give a good view of the selected data when the file is opened in Microsoft Excel, use the Worksheet.setFirstVisibleRow and Worksheet.setFirstVisibleColumn properties.

The following example shows how to activate a worksheet and make a cell in it active. The scrollbars are scrolled to make the 2nd row and 2nd column as their first visible row and column.

Setting B2 cell as an active cell

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(MakeCellActive.class) + "data/";
// Instantiate a new Workbook.
Workbook workbook = new Workbook();

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

// Get the cells in the worksheet.
Cells cells = worksheet1.getCells();

// Input data into B2 cell.
cells.get(1, 1).setValue("Hello World!");

// Set the first sheet as an active sheet.
workbook.getWorksheets().setActiveSheetIndex(0);

// Set B2 cell as an active cell in the worksheet.
worksheet1.setActiveCell("B2");

// Set the B column as the first visible column in the worksheet.
worksheet1.setFirstVisibleColumn(1);

// Set the 2nd row as the first visible row in the worksheet.
worksheet1.setFirstVisibleRow(1);

// Save the Excel file.
workbook.save(dataDir + "MakeCellActive_out.xls");

Selecting a Range of Cells in the Worksheet

Aspose.Cells provides the method Worksheet.selectRange(int startRow, int startColumn, int totalRows, int totalColumns, bool removeOthers). Using the last parameter - removeOthers - to true, other cell or cell range selections in the sheet are removed.

The following example shows how to select a range of cells in the active worksheet.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SelectRangeofCellsinWorksheet.class) + "data/";
// Instantiate a new Workbook.
Workbook workbook = new Workbook();

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

// Get the cells in the worksheet.
Cells cells = worksheet1.getCells();

// Input data into B2 cell.
cells.get(1, 1).setValue("Hello World!");

// Set the first sheet as an active sheet.
workbook.getWorksheets().setActiveSheetIndex(0);

// Select range of cells(A1:E10) in the worksheet.
worksheet1.selectRange(0, 0, 10, 5, true);

// Save the Excel file.
workbook.save(dataDir + "SROfCInWorksheet_out.xlsx");

All the above classes and methods are available with the licensed version of Aspose.Cells.

Formatting Rows and Columns

Formatting the rows and columns in a spreadsheet to give the report a look is possibly the most widely used feature of Excel application. Aspose.Cells APIs also provide this functionality through its data model by exposing Style class which mainly handles all the styling related features such as font & its attributes, alignment of text, background/foreground colors, borders, display format for numbers & date literals and so on. Another useful class that Aspose.Cells APIs provide is the StyleFlag which allows the re-usability of the Style object. 

In this article, we will try to explain how to use Aspose.Cells for Java API to apply formatting to rows and columns. 

Formatting Rows & Columns

Aspose.Cells provides a class, Workbook that represents a Microsoft Excel file. The Workbook class contains a WorksheetCollection that allows access to each worksheet in the Excel file. A worksheet is represented by the Worksheet class. The Worksheet class provides the Cells collection. The Cells collection provides a Rows collection.

Formatting a Row

Each item in the Rows collection represents a Row object. The Row object offers the applyStyle method used to apply formatting to a row.

To apply the same formatting to a row, use the Style object:

1. Add a Style object to the Workbook class by calling its createStyle method.
2. Set the Style object properties to apply the formatting settings.
3. Assign the configured Style object to the applyStyle method of a Row object.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(FormattingARow.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Accessing the added worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();

// Adding a new Style to the styles collection of the Excel object Accessing the newly added Style to the Excel object
Style style = workbook.createStyle();

// Setting the vertical alignment of the text in the cell
style.setVerticalAlignment(TextAlignmentType.CENTER);

// Setting the horizontal alignment of the text in the cell
style.setHorizontalAlignment(TextAlignmentType.CENTER);

// Setting the font color of the text in the cell
Font font = style.getFont();
font.setColor(Color.getGreen());

// Shrinking the text to fit in the cell
style.setShrinkToFit(true);

// Setting the bottom border of the cell
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.MEDIUM, Color.getRed());

// Creating StyleFlag
StyleFlag styleFlag = new StyleFlag();
styleFlag.setHorizontalAlignment(true);
styleFlag.setVerticalAlignment(true);
styleFlag.setShrinkToFit(true);
styleFlag.setBottomBorder(true);
styleFlag.setFontColor(true);

// Accessing a row from the Rows collection
Row row = cells.getRows().get(0);

// Assigning the Style object to the Style property of the row
row.applyStyle(style, styleFlag);

// Saving the Excel file
workbook.save(dataDir + "FormattingARow_out.xls");

Formatting a Column

The Cells collection provides a Columns collection. Each item in the Columns collection represents a Column object. Similar to the Row object, the Column object offers the applyStyle method used to set the column formatting. Use the applyStyle method of the Column object to format a column in the same way a row.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(FormattingAColumn.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Accessing the added worksheet in the Excel file
Worksheet worksheet = workbook.getWorksheets().get(0);
Cells cells = worksheet.getCells();

// Adding a new Style to the styles collection of the Excel object Accessing the newly added Style to the Excel object
Style style = workbook.createStyle();

// Setting the vertical alignment of the text in the cell
style.setVerticalAlignment(TextAlignmentType.CENTER);

// Setting the horizontal alignment of the text in the cell
style.setHorizontalAlignment(TextAlignmentType.CENTER);

// Setting the font color of the text in the cell
Font font = style.getFont();
font.setColor(Color.getGreen());

// Shrinking the text to fit in the cell
style.setShrinkToFit(true);

// Setting the bottom border of the cell
style.setBorder(BorderType.BOTTOM_BORDER, CellBorderType.MEDIUM, Color.getRed());

// Creating StyleFlag
StyleFlag styleFlag = new StyleFlag();
styleFlag.setHorizontalAlignment(true);
styleFlag.setVerticalAlignment(true);
styleFlag.setShrinkToFit(true);
styleFlag.setBottomBorder(true);
styleFlag.setFontColor(true);

// Accessing a column from the Columns collection
Column column = cells.getColumns().get(0);

// Applying the style to the column
column.applyStyle(style, styleFlag);

// Saving the Excel file
workbook.save(dataDir + "FormattingAColumn_out.xls");

Setting Display Format of Numbers & Dates for Rows & Columns

If the requirement is to set the display format of numbers & dates for a complete row or column then the process is more or less same as discussed above, however, instead of setting parameters for the textual contents, you will be setting the formatting for numbers and dates using the Style.Number or Style.Custom. Please note, the Style.Number property is of type integer and refers to the built-in number and date formats, whereas the Style.Custom property is of type string and accepts the valid patterns.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(SettingDisplayFormat.class) + "data/";
// Instantiating a Workbook object
Workbook workbook = new Workbook();

// Obtaining the reference of the first (default) worksheet by passing its sheet index
Worksheet worksheet = workbook.getWorksheets().get(0);

// Adding a new Style to the styles collection of the Workbook object
Style style = workbook.createStyle();

// Setting the Number property to 4 which corresponds to the pattern #,##0.00
style.setNumber(4);

// Creating an object of StyleFlag
StyleFlag flag = new StyleFlag();

// Setting NumberFormat property to true so that only this aspect takes effect from Style object
flag.setNumberFormat(true);

// Applying style to the first row of the worksheet
worksheet.getCells().getRows().get(0).applyStyle(style, flag);

// Re-initializing the Style object
style = workbook.createStyle();

// Setting the Custom property to the pattern d-mmm-yy
style.setCustom("d-mmm-yy");

// Applying style to the first column of the worksheet
worksheet.getCells().getColumns().get(0).applyStyle(style, flag);

// Saving spreadsheet on disc
workbook.save(dataDir + "SDisplayFormat_out.xlsx");

Please check the detailed article on [Setting Display Formats of Numbers and Dates.