Introduction

Sometimes, developers may need to apply same formatting settings on rows or columns of the worksheets. If developers apply formatting settings on the cells one by one then it may take longer time and would not be a good solution too. To address this issue, Aspose.Cells provides a simplest yet faster way that will be discussed in detail in the below discussion.

Formatting Rows & Columns

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.

Formatting a Row

Cells collection provides a Rows collection. Each item in the Rows collection represents a Row object. Row object offers a Style property that is used to set the formatting style of a row. Style property is infact an object of Style class that encapsulates all formatting settings to apply on the rows.

If developers need to apply the Same formatting style to a row 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 a Row object

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];

 

 

//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;

 

 

//Accessing a row from the Rows collection

Row row = cells.Rows[0];

 

 

//Assigning the Style object to the Style property of the row

row.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)

 

 

'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

style.VerticalAlignment=TextAlignmentType.Center

 

 

'Setting the horizontal alignment of the text

style.HorizontalAlignment=TextAlignmentType.Center

 

 

'Setting the font color of the text

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

 

 

'Accessing a row from the Rows collection

Dim row As Row =  cells.Rows(0)

 

 

'Assigning the Style object to the Style property of the row

row.Style = style

 

 

'Saving the Excel file

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

 

 

[JAVA]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Accessing the added worksheet in the Excel file

Worksheet worksheet = workbook.getWorksheets().addSheet();

Cells cells = worksheet.getCells();

 

 

//Accessing the newly added Style to the Excel object

Style style = workbook.createStyle();

 

 

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

style.setVAlignment(TextAlignmentType.CENTER);

 

 

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

style.setHAlignment(TextAlignmentType.CENTER);

 

 

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

Font font = style.getFont();

font.setColor(Color.GREEN);

 

 

//Shrinking the text to fit in the cell

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);

 

 

//Accessing a row from the Rows collection

Row row = cells.getRow(0);

 

 

//Assigning the Style object to the Style property of the row

row.setStyle(style);

 

//Saving the Excel file

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

Formatting a Column

Cells collection also provides a Columns collection. Each item in the Columns collection represents a Column object. Similar to Row object, Column object also offers a Style property that is used to set the formatting style of a column. Developers can use this Style property of the Column object to format a column in the same way as that of a row (discussed in above section).

Example:

[C#]

 

 

//Accessing a column from the Columns collection

Column column = cells.Columns[0];

 

 

//Assigning the Style object to the Style property of the column

column.Style = style;

 

 

[VB.NET]

 

 

'Accessing a column from the Columns collection

Dim column As Column =  cells.Columns(0)

 

 

'Assigning the Style object to the Style property of the column

column.Style = style

 

 

[JAVA]

//Accessing a column from the Columns collection

Column column = cells.getColumns().getColumn(0);

 

 

//Assigning the Style object to the Style property of the column

column.setStyle (style);