Introduction

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 fomat 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 an Excel file. Workbook class contains a Worksheets collection that allows to access each worksheet in the Excel file. A worksheet is represented by the Worksheet class. Worksheet class provides a Cells collection. Each item in the Cells collection represents an object of Cell class.

Aspose.Cells provides Style property in the Cell class that is 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. Developers can apply different kinds of formatting styles on the cells to set their background or foreground colors, borders, fonts, horizontal and vertical alignments, identation level, text direction, rotation angle and much more.

Using Style Property

If developers need to apply different formatting styles to different cells then it's better to use the Style property of the Cell class. An example is given below to demonstrate the use of Style property to apply various formatting settings on a cell.

Example:

[C#]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Adding a new worksheet to the Workbook object

workbook.Worksheets.Add();

 

 

//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = workbook.Worksheets[0];

 

 

//Accessing the "A1" cell from the worksheet

Cell cell = worksheet.Cells["A1"];

 

 

//Adding some value to the "A1" cell

cell.PutValue("Hello Aspose!");

 

 

//Setting the vertical alignment of the text in the "A1" cell

cell.Style.VerticalAlignment=TextAlignmentType.Center;

 

 

//Setting the horizontal alignment of the text in the "A1" cell

cell.Style.HorizontalAlignment=TextAlignmentType.Center;

 

 

//Setting the font color of the text in the "A1" cell

cell.Style.Font.Color = Color.Green;

 

 

//Setting the cell to shrink according to the text contained in it

cell.Style.ShrinkToFit=true;

 

 

//Setting the bottom border color of the cell to red

cell.Style.Borders[BorderType.BottomBorder].Color = Color.Red;

 

 

//Setting the bottom border type of the cell to medium

cell.Style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;

 

 

//Saving the Excel file

workbook.Save(saveFileDialog1.FileName,FileFormatType.Default);

 

 

[VB.NET]

 

 

'Instantiating a Workbook object

Dim workbook As Workbook = New Workbook()

 

 

'Adding a new worksheet to the Workbook object

workbook.Worksheets.Add()

 

 

'Obtaining the reference of the newly added worksheet by passing its sheet index

Dim worksheet As Worksheet = workbook.Worksheets(0)

 

 

'Accessing the "A1" cell from the worksheet

Dim cell As Cell = worksheet.Cells("A1")

 

 

'Adding some value to the "A1" cell

cell.PutValue("Hello Aspose!")

 

 

'Setting the vertical alignment of the text in the "A1" cell

cell.Style.VerticalAlignment=TextAlignmentType.Center

 

 

'Setting the horizontal alignment of the text in the "A1" cell

cell.Style.HorizontalAlignment=TextAlignmentType.Center

 

 

'Setting the font color of the text in the "A1" cell

cell.Style.Font.Color = Color.Green

 

 

'Setting the cell to shrink according to the text contained in it

cell.Style.ShrinkToFit=True

 

 

'Setting the bottom border color of the cell to red

cell.Style.Borders(BorderType.BottomBorder).Color = Color.Red

 

 

'Setting the bottom border type of the cell to medium

cell.Style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Medium

 

 

'Saving the Excel file

workbook.Save(saveFileDialog1.FileName,FileFormatType.Default)

 

 

[JAVA]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook;

 

 

//Opening the Excel file

workbook.open("C:\\book1.xls");

 

 

//Accessing the first worksheet in the Excel file

Worksheet worksheet = workbook.getWorksheets().getSheet(0);

Cells cells = worksheet.getCells();

 

 

//Accessing the "A1" cell from the worksheet

Cell cell=cells.getCell("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.setVAlignment(TextAlignmentType.CENTER);

 

 

//Setting the horizontal alignment of the text in the "A1" cell

style.setHAlignment(TextAlignmentType.CENTER);

 

 

//Setting the font color of the text in the "A1" cell

Font font = style.getFont();

font.setColor (Color.GREEN);

 

 

//Setting the cell to shrink according to the text contained in it

style.setShrinked(true);

 

 

//Setting the bottom border color of the cell to red

style.setBorderColor(BorderType.BOTTOM,Color.RED);

 

 

//Setting the bottom border type of the cell to medium

style.setBorderLine(BorderType.BOTTOM,BorderLineType.MEDIUM);

 

 

//Saved style

cell.setStyle(style);

 

 

//Saving the modified Excel file in default (that is Excel 2000) format

workbook.save("C:\\output.xls",FileFormatType.DEFAULT);

 

Using Style Object

If developers need to apply the Same formatting style to different cells then they should use Style object. Please follow the steps below to use the Style object:

• Add a Style object to the Styles collection of the Excel class by calling the Add method of the Styles collection
• Access the newly added Style object from the Styles collection
• Set the desired properties of the Style object to apply desired formatting settings
• 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.

Example:

[C#]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Adding a new worksheet to the Excel object

workbook.Worksheets.Add();

 

 

//Obtaining the reference of the newly added worksheet by passing its sheet index

Worksheet worksheet = workbook.Worksheets[0];

 

 

//Accessing the "A1" cell from the worksheet

Dim cell As Cell = worksheet.Cells("A1")

 

 

//Adding some value to the "A1" cell

cell.PutValue("Hello Aspose!")

 

 

//Adding a new Style to the styles collection of the Excel object

workbook.Styles.Add();

 

 

//Accessing the newly added Style to the Excel object

Style style = workbook.Styles[0];

 

 

//Setting the vertical alignment of the text in the "A1" cell

style.VerticalAlignment = TextAlignmentType.Center;

 

 

//Setting the horizontal alignment of the text in the "A1" cell

style.HorizontalAlignment = TextAlignmentType.Center;

 

 

//Setting the font color of the text in the "A1" cell

style.Font.Color = Color.Green;

 

 

//Shrinking the text to fit in the cell

style.ShrinkToFit = true;

 

 

//Setting the bottom border color of the cell to red

style.Borders[BorderType.BottomBorder].Color = Color.Red;

 

 

//Setting the bottom border type of the cell to medium

style.Borders[BorderType.BottomBorder].LineStyle = CellBorderType.Medium;

 

 

//Assigning the Style object to the Style property of the "A1" cell

cell.Style=style;

 

 

//Saving the Excel file

workbook.Save(saveFileDialog1.FileName,FileFormatType.Default);

 

 

[VB.NET]

 

'Instantiating a Workbook object

Dim workbook As Workbook = New Workbook()

 

 

'Adding a new worksheet to the Workbook object

workbook.Worksheets.Add()

 

 

'Obtaining the reference of the newly added worksheet by passing its sheet index

Dim worksheet As Worksheet = workbook.Worksheets(0)

 

 

'Accessing the "A1" cell from the worksheet

Dim cell As Cell = worksheet.Cells("A1")

 

 

'Adding some value to the "A1" cell

cell.PutValue("Hello Aspose!")

 

 

'Adding a new Style to the styles collection of the Excel object

workbook.Styles.Add()

 

 

'Accessing the newly added Style to the Excel object

Dim style As Style = workbook.Styles(0)

 

 

'Setting the vertical alignment of the text in the "A1" cell

style.VerticalAlignment = TextAlignmentType.Center

 

 

'Setting the horizontal alignment of the text in the "A1" cell

style.HorizontalAlignment = TextAlignmentType.Center

 

 

'Setting the font color of the text in the "A1" cell

style.Font.Color = Color.Green

 

'Shrinking the text to fit in the cell

style.ShrinkToFit = True

 

 

'Setting the bottom border color of the cell to red

style.Borders(BorderType.BottomBorder).Color = Color.Red

 

 

'Setting the bottom border type of the cell to medium

style.Borders(BorderType.BottomBorder).LineStyle = CellBorderType.Medium

 

 

'Assigning the Style object to the Style property of the "A1" cell

cell.Style = style

 

 

'Saving the Excel file

workbook.Save(saveFileDialog1.FileName,FileFormatType.Default)

 

 

[JAVA]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Opening the Excel file

workbook.open("C:\\book1.xls");

 

 

//Accessing the first worksheet in the Excel file

Worksheet worksheet = workbook.getWorksheets().getSheet(0);

Cells cells = worksheet.getCells();

 

 

//Accessing the "A1" cell from the worksheet

Cell cell = cells.getCell("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.setVAlignment(TextAlignmentType.CENTER);

 

 

//Setting the horizontal alignment of the text in the "A1" cell

style.setHAlignment(TextAlignmentType.CENTER);

 

 

//Setting the font color of the text in the "A1" cell

Font font = style.getFont();

font.setColor (Color.GREEN);

 

 

//Setting the cell to shrink according to the text contained in it

style.setShrinked(true);

 

 

//Setting the bottom border color of the cell to red

style.setBorderColor(BorderType.BOTTOM,Color.RED);

 

 

//Setting the bottom border type of the cell to medium

style.setBorderLine(BorderType.BOTTOM,BorderLineType.MEDIUM);