Format Excel cell, column and row in C#, VB.NET, Java

Formatting cells, columns and rows

EasyXLS™ library enables you to format Excel cells, rows, columns and cell ranges. The supported formatted options are:

- Cell text format: number format, currency format, date format, accounting format, percentage format, fraction format, text format and other custom formats
- Cell alignment: horizontal and vertical alignment, text indentation, wrap text and shrink options, merge cells, text rotation and text direction
- Cell font settings: font name, font size, foreground, bold ant italic options, underline style, strikethrough, superscript and subscript effects
- Cell border: border line style and color
- Cell fill settings: background color, pattern style, pattern color, gradient fill
- Cell protection: locked cells and hidden formulas

The Excel file can be formatted inclusive using themes for MS Excel workbook.

Format using styles
Straight format for a cell
Format rows
Format columns
Format cell ranges
Autoformat for cell ranges
Multiple formatting in cell
Format using themes

Format using styles

EasyXLS provides a specific class, ExcelStyle, which stores all formatting settings. The ExcelStyle format can be set for a specific cell, for a row, for a column or for a range of cells. Setting a format for a cell overrides the format of a row or a column.

For better performances, EasyXLS recommends using as less as possible instances of ExcelStyle classes.

Setting the cell format can be accomplished from ExcelCell class using setStyle() method.

If you want to format cells, consider the code sample and the screenshot below:


C++

// Create the formatting style for header
EasyXLS::IExcelStylePtr xlsStyleHeader;
hr = CoCreateInstance(__uuidof(EasyXLS::ExcelStyle),
		      NULL,
		      CLSCTX_ALL,
																	__uuidof(EasyXLS::IExcelStyle),
		      (void**) &xlsStyleHeader) ;

xlsStyleHeader->setFont("Verdana");
xlsStyleHeader->setFontSize(8);
xlsStyleHeader->setItalic(true);
xlsStyleHeader->setBold(true);
xlsStyleHeader->setForeground(COLOR_YELLOW);
xlsStyleHeader->setBackground(COLOR_BLACK);
xlsStyleHeader->setBorderColors (COLOR_GRAY, COLOR_GRAY, COLOR_GRAY, COLOR_GRAY);
xlsStyleHeader->setBorderStyles (BORDER_BORDER_MEDIUM, BORDER_BORDER_MEDIUM,
				 BORDER_BORDER_MEDIUM, BORDER_BORDER_MEDIUM);
xlsStyleHeader->setHorizontalAlignment(ALIGNMENT_ALIGNMENT_CENTER);
xlsStyleHeader->setVerticalAlignment(ALIGNMENT_ALIGNMENT_BOTTOM);
xlsStyleHeader->setWrap(true);

// Add data in cells for report header
char* cellValue = (char*)malloc(11*sizeof(char));
char*  columnNumber = (char*)malloc(sizeof(char));
for (int column=0; column<5; column++)
{
	strcpy_s(cellValue, 8, "Column ");
	_itoa_s(column+ 1, columnNumber, 2, 10);
	strcat_s(cellValue, 10, columnNumber);
	xlsFirstTable->easy_getCell(0,column)->setValue(cellValue);
	xlsFirstTable->easy_getCell(0,column)->setStyle(xlsStyleHeader);
}

Click here to see Continuous Code Listing


C++.NET

// Create the formatting style for header
ExcelStyle ^xlsStyleHeader = gcnew ExcelStyle("Verdana", 8, true, true, Color::Yellow);
xlsStyleHeader->setBackground(Color::Black);
xlsStyleHeader->setBorderColors(Color::Gray, Color::Gray, Color::Gray, Color::Gray);
xlsStyleHeader->setBorderStyles(Border::BORDER_MEDIUM, Border::BORDER_MEDIUM,
				Border::BORDER_MEDIUM, Border::BORDER_MEDIUM);
xlsStyleHeader->setHorizontalAlignment(Alignment::ALIGNMENT_CENTER);
xlsStyleHeader->setVerticalAlignment(Alignment::ALIGNMENT_BOTTOM);
xlsStyleHeader->setWrap(true);

// Add data in cells for report header
for (int column=0; column<5; column++)
{
	xlsFirstTable->easy_getCell(0,column)->setValue(String::Concat(
																	"Column ",(column + 1).ToString()));
	xlsFirstTable->easy_getCell(0,column)->setStyle(xlsStyleHeader);
}

Click here to see Continuous Code Listing


.NET:

// Create the formatting style for header
$xlsStyleHeader = new COM("EasyXLS.ExcelStyle");
$xlsStyleHeader->setFont("Verdana");
$xlsStyleHeader->setFontSize(8);
$xlsStyleHeader->setItalic(True);
$xlsStyleHeader->setBold(True);
$xlsStyleHeader->setForeground((int)$COLOR_YELLOW);
$xlsStyleHeader->setBackground((int)$COLOR_BLACK);
$xlsStyleHeader->setBorderColors ((int)$COLOR_GRAY, (int)$COLOR_GRAY,
				  (int)$COLOR_GRAY, (int)$COLOR_GRAY);
$xlsStyleHeader->setBorderStyles ($BORDER_BORDER_MEDIUM, $BORDER_BORDER_MEDIUM,
				  $BORDER_BORDER_MEDIUM, $BORDER_BORDER_MEDIUM);
$xlsStyleHeader->setHorizontalAlignment($ALIGNMENT_ALIGNMENT_CENTER);
$xlsStyleHeader->setVerticalAlignment($ALIGNMENT_ALIGNMENT_BOTTOM);
$xlsStyleHeader->setWrap(True);

// Add data in cells for report header
for ($column=0; $column<5; $column++)
{
	$xlsFirstTable->easy_getCell(0,$column)->setValue("Column " . ($column + 1));
	$xlsFirstTable->easy_getCell(0,$column)->setStyle($xlsStyleHeader);
}

Click here to see Continuous Code Listing


Java:

// Create the formatting style for the header
$xlsStyleHeader = new java("EasyXLS.ExcelStyle");
$xlsStyleHeader->setFont("Verdana");
$xlsStyleHeader->setFontSize(8);
$xlsStyleHeader->setItalic(True);
$xlsStyleHeader->setBold(True);
$xlsStyleHeader->setForeground(java("java.awt.Color")->YELLOW);
$xlsStyleHeader->setBackground(java("java.awt.Color")->BLACK);
$xlsStyleHeader->setBorderColors (java("java.awt.Color")->GRAY,
				  java("java.awt.Color")->GRAY,
				  java("java.awt.Color")->GRAY,
				  java("java.awt.Color")->GRAY);
$xlsStyleHeader->setBorderStyles ($BORDER_BORDER_MEDIUM, $BORDER_BORDER_MEDIUM,
				  $BORDER_BORDER_MEDIUM, $BORDER_BORDER_MEDIUM);
$xlsStyleHeader->setHorizontalAlignment($ALIGNMENT_ALIGNMENT_CENTER);
$xlsStyleHeader->setVerticalAlignment($ALIGNMENT_ALIGNMENT_BOTTOM);
$xlsStyleHeader->setWrap(True);

// Add data in cells for report header
for ($column=0; $column<5; $column++)
{
	$xlsFirstTable->easy_getCell(0,$column)->setValue("Column " . ($column + 1));
	$xlsFirstTable->easy_getCell(0,$column)->setStyle($xlsStyleHeader);
}

Click here to see Continuous Code Listing

The screen shot below represents the result of the above code that formats the cells. It shows an Excel file with two worksheets ('First Tab' and 'Second Tab'). The first worksheet has the following format for the cells. The column headers are formatted with font size (8), font name (Verdana), foreground (yellow), background (black), borders color (gray), border size (medium), horizontal alignment (center), vertical alignment (bottom), and wrap option. The table cells are formatted with default font size (10), default font name (Arial), borders color (gray), foreground color (dark gray).

Straight format for a cell

The format attributes can be set directly to the cell. Use this scenario if there are only a few cells that require different formatting than the rest of the cells.

For better performances, EasyXLS recommends avoiding setting the format attributes for each ExcelCell class and use styles instead. For more details check Export large Excel files chapter.

Format rows

Setting the row format can be accomplished from ExcelRow class using setStyle() method. Also, the row can be directly formatted using the specific methods from ExcelRow class.

If a cell is defined in the ExcelTable, the cell format will override the format of the row. The empty cells from the row will keep the row formatting.

Format columns

Setting the column format can be accomplished from ExcelColumn class using setStyle() method. Also, the column can be directly formatted using the specific methods from ExcelColumn class.

If a cell is defined in the ExcelTable, the cell format will override the format of the row. The empty cells from the row will keep the row formatting.

Format cell ranges

EasyXLS provides methods for applying a formatting style to a specific range of cells. Using ExcelTable.easy_setRangeStyle method, the same formatting is applied for all cells in the range.

Autoformat for cell ranges

EasyXLS provides methods to apply a complex layout format for a range of cells. The autoformat includes formatting for header, inner data, footer and margins.

Multiple formatting in cell

Using EasyXLS you can apply different font formatting in the same cell, so that the texts inside the cell have different look including bold, italic, colors, underline and other settings.

Format using themes

EasyXLS allows you to format the cells according to the theme set for the Excel file. The look of the spreadsheet changes according to the applied theme.

Available for: Professional, Excel Writer, Excel Reader

EasyXLS Excel libraries:
.NET Excel Library
full .NET version to import, export or convert Excel files
COM+ Excel Library
full COM+ version to import, export or convert Excel files


Java Excel Library
full Java version to import, export or convert Excel files