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

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 Office 2007-2013 spreadsheets.

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.

Setting the cell format can be accomplished from ExcelCell class using setStyle() method or other specific methods for formatting.

Setting the row format can be accomplished from ExcelRow class using setStyle() method or other specific methods for formatting.

Setting the column format can be accomplished from ExcelColumn class using setStyle() method or other specific methods for formatting.

Setting the format for a range of cells can be accomplished from ExcelTable class using easy_setRangeStyle() method.

For better performances, EasyXLS recommends using as less as possible instances of ExcelStyle classes and avoid setting the format attributes for each ExcelCell class. For more details check Export large Excel files chapter.

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); xlsStyleHeader->setDataType(DATATYPE_STRING); // Create the formatting style for cells EasyXLS::IExcelStylePtr xlsStyleData; hr = CoCreateInstance(__uuidof(EasyXLS::ExcelStyle), NULL, CLSCTX_ALL, __uuidof(EasyXLS::IExcelStyle), (void**) &xlsStyleData) ; xlsStyleData->setHorizontalAlignment(ALIGNMENT_ALIGNMENT_LEFT); xlsStyleData->setForeground(COLOR_DARKGRAY); xlsStyleData->setWrap(false); xlsStyleData->setDataType(DATATYPE_STRING); 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); xlsStyleHeader->setDataType(DataType::STRING); // Create the formatting style for cells ExcelStyle ^xlsStyleData = gcnew ExcelStyle(); xlsStyleData->setHorizontalAlignment(Alignment::ALIGNMENT_LEFT); xlsStyleData->setForeground(Color::DarkGray); xlsStyleData->setWrap(false); xlsStyleData->setDataType(DataType::STRING); xlsFirstTable->easy_setRangeStyle("A2:E101", xlsStyleData); 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); $xlsStyleHeader->setDataType($DATATYPE_STRING); // Create the formatting style for cells $xlsStyleData = new COM("EasyXLS.ExcelStyle"); $xlsStyleData->setHorizontalAlignment($ALIGNMENT_ALIGNMENT_LEFT); $xlsStyleData->setForeground((int)$COLOR_DARKGRAY); $xlsStyleData->setWrap(false); $xlsStyleData->setDataType($DATATYPE_STRING); 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); $xlsStyleHeader->setDataType($DATATYPE_STRING); // Create a formatting style for cells $xlsStyleData = new java("EasyXLS.ExcelStyle"); $xlsStyleData->setHorizontalAlignment($ALIGNMENT_ALIGNMENT_LEFT); $xlsStyleData->setForeground(java("java.awt.Color")->LIGHT_GRAY); $xlsStyleData->setWrap(false); $xlsStyleData->setDataType($DATATYPE_STRING); Click here to see Continuous Code Listing

.NET: # Create the formatting style for the header xlsStyleHeader = 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) xlsStyleHeader.setDataType(DataType.STRING) # Create a formatting style for cells xlsStyleData = ExcelStyle() xlsStyleData.setHorizontalAlignment(Alignment.ALIGNMENT_LEFT) xlsStyleData.setForeground(Color.DarkGray) xlsStyleData.setWrap(False) xlsStyleData.setDataType(DataType.STRING) xlsFirstTable.easy_setRangeStyle("A2:E101", xlsStyleData) Click here to see Continuous Code Listing Java: # Create the formatting style for the header xlsStyleHeader = gateway.jvm.ExcelStyle("Verdana", 8, True, True, gateway.jvm.Color.YELLOW) xlsStyleHeader.setBackground(gateway.jvm.Color.BLACK) xlsStyleHeader.setBorderColors(gateway.jvm.Color.GRAY, gateway.jvm.Color.GRAY, gateway.jvm.Color.GRAY, gateway.jvm.Color.GRAY) xlsStyleHeader.setBorderStyles(gateway.jvm.Border.BORDER_MEDIUM, gateway.jvm.Border.BORDER_MEDIUM, gateway.jvm.Border.BORDER_MEDIUM, gateway.jvm.Border.BORDER_MEDIUM) xlsStyleHeader.setHorizontalAlignment(gateway.jvm.Alignment.ALIGNMENT_CENTER) xlsStyleHeader.setVerticalAlignment(gateway.jvm.Alignment.ALIGNMENT_BOTTOM) xlsStyleHeader.setWrap(True) xlsStyleHeader.setDataType(gateway.jvm.DataType.STRING) # Create a formatting style for cells xlsStyleData = gateway.jvm.ExcelStyle() xlsStyleData.setHorizontalAlignment(gateway.jvm.Alignment.ALIGNMENT_LEFT) xlsStyleData.setForeground(gateway.jvm.Color.LIGHT_GRAY) xlsStyleData.setWrap(False) xlsStyleData.setDataType(gateway.jvm.DataType.STRING) xlsFirstTable.easy_setRangeStyle("A2:E101", xlsStyleData) Click here to see Continuous Code Listing

Formatting cells, columns and rows

Source code sample

EasyXLS Excel libraries:

.NET Excel Library

COM+ Excel Library

Java Excel Library

File formats:

Formatting cells, columns and rows

Source code sample

Related methods:

Related constants:

See also:

EasyXLS Excel libraries:

File formats: