Working with Cell or Range Formatting

14 Oct 202424 minutes to read

This section covers the various formatting options in a cell or a range.

Create a Style

The following code shows how to create and apply cell style.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet worksheet = workbook.Worksheets[0];

	#region Create Style
	//Creating a new style with cell back color, fill pattern and font attribute
	IStyle style = workbook.Styles.Add("NewStyle");
	style.Color = Syncfusion.Drawing.Color.LightGreen;
	style.FillPattern = ExcelPattern.DarkUpwardDiagonal;
	style.Font.Bold = true;
	worksheet.Range["B2"].CellStyle = style;
	#endregion

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/CreateStyle.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Creating a new style with cell back color, fill pattern and font attribute
  IStyle style = workbook.Styles.Add("NewStyle");
  style.Color = Color.LightGreen;
  style.FillPattern = ExcelPattern.DarkUpwardDiagonal;
  style.Font.Bold = true;
  worksheet.Range["B2"].CellStyle = style;

  workbook.SaveAs("Style.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Creating a new style with cell back color, fill pattern and font attribute
  Dim style As IStyle = workbook.Styles.Add("NewStyle")
  style.Color = Color.LightGreen
  style.FillPattern = ExcelPattern.DarkUpwardDiagonal
  style.Font.Bold = True
  worksheet.Range("B2").CellStyle = style

  workbook.SaveAs("Style.xlsx")
End Using

A complete working example to create style in C# is present on this GitHub page.

Set Default Style for row or column

It is the recommended and optimized approach to format entire row or column with same styles instead of formatting each and every cell individually. Use the following code to set default style.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet worksheet = workbook.Worksheets[0];

	#region Row and Column Style
	//Define new styles to apply in rows and columns
	IStyle rowStyle = workbook.Styles.Add("RowStyle");
	rowStyle.Color = Syncfusion.Drawing.Color.LightGreen;
	IStyle columnStyle = workbook.Styles.Add("ColumnStyle");
	columnStyle.Color = Syncfusion.Drawing.Color.Orange;

	//Set default row style for entire row
	worksheet.SetDefaultRowStyle(1, 2, rowStyle);
	//Set default column style for entire column
	worksheet.SetDefaultColumnStyle(1, 2, columnStyle);
	#endregion

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/RowColumnStyle.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Define new styles to apply in rows and columns
  IStyle rowStyle = workbook.Styles.Add("RowStyle");
  rowStyle.Color = Color.LightGreen;
  IStyle columnStyle = workbook.Styles.Add("ColumnStyle");
  columnStyle.Color = Color.Orange;

  //Set default row style for entire row
  worksheet.SetDefaultRowStyle(1, 2, rowStyle);
  //Set default column style for entire column
  worksheet.SetDefaultColumnStyle(1, 2, columnStyle);

  workbook.SaveAs("DefaultStyles.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Define new styles to apply in rows and columns
  Dim rowStyle As IStyle = workbook.Styles.Add("RowStyle")
  rowStyle.Color = Color.LightGreen
  Dim columnStyle As IStyle = workbook.Styles.Add("ColumnStyle")
  columnStyle.Color = Color.Orange

  'Set default row style for entire row
  worksheet.SetDefaultRowStyle(1, 2, rowStyle)
  'Set default column style for entire column
  worksheet.SetDefaultColumnStyle(1, 2, columnStyle)

  workbook.SaveAs("DefaultStyles.xlsx")
End Using

A complete working example to apply default style for rows and columns in C# is present on this GitHub page.

NOTE

Applying custom styles will override original styles.

TIPS

To apply styles for whole column instead of applying in each cell, use default styles.

Apply Global Style

The XlsIO adds styles globally that can be applied to one or more cells in a workbook. This is a recommended approach to apply single style in different rows and columns, which improves memory and performance considerably.

To learn more about performance, refer to the Improving Performing section for better performance in XlsIO.

The following code snippet illustrates how to set header style and body style to the cells.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet worksheet = workbook.Worksheets[0];

	//Adding values to a worksheet range
	worksheet.Range["A1"].Text = "CustomerID";
	worksheet.Range["B1"].Text = "CompanyName";
	worksheet.Range["C1"].Text = "ContactName";
	worksheet.Range["D1"].Text = "TotalSales (in USD)";
	worksheet.Range["A2"].Text = "ALFKI";
	worksheet.Range["A3"].Text = "ANATR";
	worksheet.Range["A4"].Text = "BONAP";
	worksheet.Range["A5"].Text = "BSBEV";
	worksheet.Range["B2"].Text = "Alfred Futterkiste";
	worksheet.Range["B3"].Text = "Ana Trujillo Emparedados y helados";
	worksheet.Range["B4"].Text = "Bon App";
	worksheet.Range["B5"].Text = "B's Beverages";
	worksheet.Range["C2"].Text = "Maria Anders";
	worksheet.Range["C3"].Text = "Ana Trujillo";
	worksheet.Range["C4"].Text = "Laurence Lebihan";
	worksheet.Range["C5"].Text = "Victoria Ashworth";
	worksheet.Range["D2"].Number = 15000.107;
	worksheet.Range["D3"].Number = 27000.208;
	worksheet.Range["D4"].Number = 18700.256;
	worksheet.Range["D5"].Number = 25000.450;

	#region Global Style
	//Formatting
	//Global styles should be used when the same style needs to be applied to more than one cell. This usage of a global style reduces memory usage.
	//Add custom colors to the palette
	workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33));

	//Defining header style
	IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
	headerStyle.BeginUpdate();
	headerStyle.Color = Color.FromArgb(255, 174, 33);
	headerStyle.Font.Bold = true;
	headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
	headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
	headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
	headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
	headerStyle.EndUpdate();

	//Add custom colors to the palette
	workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247));

	//Defining body style
	IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
	bodyStyle.BeginUpdate();
	bodyStyle.Color = Color.FromArgb(239, 243, 247);
	bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
	bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
	bodyStyle.EndUpdate();

	//Defining number format style
	IStyle numberformatStyle = workbook.Styles.Add("NumberFormatStyle");
	numberformatStyle.BeginUpdate();
	numberformatStyle.NumberFormat = "0.00";
	numberformatStyle.EndUpdate();

	//Apply Header style
	worksheet.Rows[0].CellStyle = headerStyle;
	//Apply Body Style
	worksheet.Range["A2:C5"].CellStyle = bodyStyle;
	//Apply Number Format style
	worksheet.Range["D2:D5"].CellStyle = numberformatStyle;
	#endregion

	//Auto-fit the columns
	worksheet.UsedRange.AutofitColumns();

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/GlobalStyle.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(2);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Adding values to a worksheet range
  worksheet.Range["A1"].Text = "CustomerID";
  worksheet.Range["B1"].Text = "CompanyName";
  worksheet.Range["C1"].Text = "ContactName";
  worksheet.Range["D1"].Text = "TotalSales (in USD)";
  worksheet.Range["A2"].Text = "ALFKI";
  worksheet.Range["A3"].Text = "ANATR";
  worksheet.Range["A4"].Text = "BONAP";
  worksheet.Range["A5"].Text = "BSBEV";
  worksheet.Range["B2"].Text = "Alfred Futterkiste";
  worksheet.Range["B3"].Text = "Ana Trujillo Emparedados y helados";
  worksheet.Range["B4"].Text = "Bon App";
  worksheet.Range["B5"].Text = "B's Beverages";
  worksheet.Range["C2"].Text = "Maria Anders";
  worksheet.Range["C3"].Text = "Ana Trujillo";
  worksheet.Range["C4"].Text = "Laurence Lebihan";
  worksheet.Range["C5"].Text = "Victoria Ashworth";
  worksheet.Range["D2"].Number = 15000.107;
  worksheet.Range["D3"].Number = 27000.208;
  worksheet.Range["D4"].Number = 18700.256;
  worksheet.Range["D5"].Number = 25000.450;

  //Formatting
  //Global styles should be used when the same style needs to be applied to more than one cell. This usage of a global style reduces memory usage.
  //Add custom colors to the palette
  workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33));

  //Defining header style
  IStyle headerStyle = workbook.Styles.Add("HeaderStyle");
  headerStyle.BeginUpdate();
  headerStyle.Color = Color.FromArgb(255, 174, 33);
  headerStyle.Font.Bold = true;
  headerStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
  headerStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
  headerStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
  headerStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
  headerStyle.EndUpdate();

  //Add custom colors to the palette
  workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247));

  //Defining body style
  IStyle bodyStyle = workbook.Styles.Add("BodyStyle");
  bodyStyle.BeginUpdate();
  bodyStyle.Color = Color.FromArgb(239, 243, 247);
  bodyStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
  bodyStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
  bodyStyle.EndUpdate();

  //Defining number format style
  IStyle numberformatStyle = workbook.Styles.Add("NumberFormatStyle");
  numberformatStyle.BeginUpdate();
  numberformatStyle.NumberFormat = "0.00";
  numberformatStyle.EndUpdate();

  //Apply Header style
  worksheet.Rows[0].CellStyle = headerStyle;
  //Apply Body Style
  worksheet.Range["A2:C5"].CellStyle = bodyStyle;
  //Apply Number Format style
  worksheet.Range["D2:D5"].CellStyle = numberformatStyle;
  //Auto-fit the columns
  worksheet.UsedRange.AutofitColumns();

  workbook.SaveAs("GlobalStyles.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(2)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Adding values to a worksheet range
  worksheet.Range("A1").Text = "CustomerID"
  worksheet.Range("B1").Text = "CompanyName"
  worksheet.Range("C1").Text = "ContactName"
  worksheet.Range("D1").Text = "TotalSales (in USD)"
  worksheet.Range("A2").Text = "ALFKI"
  worksheet.Range("A3").Text = "ANATR"
  worksheet.Range("A4").Text = "BONAP"
  worksheet.Range("A5").Text = "BSBEV"
  worksheet.Range("B2").Text = "Alfred Futterkiste"
  worksheet.Range("B3").Text = "Ana Trujillo Emparedados y helados"
  worksheet.Range("B4").Text = "Bon App"
  worksheet.Range("B5").Text = "B's Beverages"
  worksheet.Range("C2").Text = "Maria Anders"
  worksheet.Range("C3").Text = "Ana Trujillo"
  worksheet.Range("C4").Text = "Laurence Lebihan"
  worksheet.Range("C5").Text = "Victoria Ashworth"
  worksheet.Range["D2"].Number = 15000.107
  worksheet.Range["D3"].Number = 27000.208
  worksheet.Range["D4"].Number = 18700.256
  worksheet.Range["D5"].Number = 25000.450

  'Formatting
  'Global styles should be used when the same style needs to be applied to more than one cell. This usage of a global style reduces memory usage.
  'Add custom colors to the palette
  workbook.SetPaletteColor(8, Color.FromArgb(255, 174, 33))

  'Defining header style
  Dim headerStyle As IStyle = workbook.Styles.Add("HeaderStyle")
  headerStyle.BeginUpdate()
  headerStyle.Color = Color.FromArgb(255, 174, 33)
  headerStyle.Font.Bold = True
  headerStyle.Borders(ExcelBordersIndex.EdgeLeft).LineStyle = ExcelLineStyle.Thin
  headerStyle.Borders(ExcelBordersIndex.EdgeRight).LineStyle = ExcelLineStyle.Thin
  headerStyle.Borders(ExcelBordersIndex.EdgeTop).LineStyle = ExcelLineStyle.Thin
  headerStyle.Borders(ExcelBordersIndex.EdgeBottom).LineStyle = ExcelLineStyle.Thin
  headerStyle.EndUpdate()

  'Add custom colors to the palette
  workbook.SetPaletteColor(9, Color.FromArgb(239, 243, 247))

  'Defining body style
  Dim bodyStyle As IStyle = workbook.Styles.Add("BodyStyle")
  bodyStyle.BeginUpdate()
  bodyStyle.Color = Color.FromArgb(239, 243, 247)
  bodyStyle.Borders(ExcelBordersIndex.EdgeLeft).LineStyle = ExcelLineStyle.Thin
  bodyStyle.Borders(ExcelBordersIndex.EdgeRight).LineStyle = ExcelLineStyle.Thin
  bodyStyle.EndUpdate()

  'Defining number format style
  IStyle numberformatStyle = workbook.Styles.Add("NumberFormatStyle")
  numberformatStyle.BeginUpdate()
  numberformatStyle.NumberFormat = "0.00"
  numberformatStyle.EndUpdate()

  'Apply Header style
  worksheet.Rows(0).CellStyle = headerStyle
  'Apply Body Style
  worksheet.Range("A2:C5").CellStyle = bodyStyle
  'Apply Number Format style
  worksheet.Range("D2:D5").CellStyle = numberformatStyle
  'Auto-fit the columns
  worksheet.UsedRange.AutofitColumns()

  workbook.SaveAs("GlobalStyles.xlsx")
End Using

A complete working example to apply global style in C# is present on this GitHub page.

Excel document with global styles

Excel document with Global Styles

Apply Number Formats 

Number Formats are codes that helps to control the appearance of cell values especially numbers in an Excel document. Excel recognizes the numbers in various formats like:

  • Number
  • Currency
  • Percentage
  • DateTime
  • Accounting
  • Scientific
  • Fraction and
  • Text

This number format can be of maximum 4 parts, separated by semicolons. They are:

  • Positive Numbers
  • Negative Numbers
  • Zeros
  • Text

Each part is an individual number format. Default format is “General”, it means anything that will fit.

The following table shows various custom formatting codes:

Number Code

Description

General

General number format.

0 (zero)

Digit placeholder. This code pads the value with zeros to fill the format.

#

Digit placeholder. This code does not display extra zeros.

?

Digit placeholder. This code leaves a space for insignificant zeros but does not display them.

. (period)

Decimal placeholder. The decimal placeholder determines how many digits are displayed to the left and right of the decimal separator.

%

Percentage placeholder. Multiplies by 100 and adds the % character.

, (comma)

Thousands separator. A comma followed by a placeholder (0 or #) scales the number by a thousand.

E+ E- e+ e-

Scientific notation.

Text Code

Description

$ - + / ( ) : space

These characters are displayed in the number. To display any other character, enclose the character in quotation marks or precede it with a backslash.

\character

This code displays the succeeding character you specify.

Note Typing !, ^, &, ', ~, {, }, =, <, or > automatically places a backslash in front of the character.

"text"

This code displays the text.

*

This code repeats the next character in the format to fill the column width.

Note: Only one asterisk per section of a format is allowed.

_ (underscore)

This code skips the width of the next character. This code is commonly used as "_)" (without the quotation marks) to leave space for a closing parenthesis in a positive number format when the negative number format includes parentheses.

This allows the values to line up at the decimal point.

@

Text placeholder.

Date Code

Description

m

Month as a number without leading zeros (1-12).

mm

Month as a number with leading zeros (01-12).

mmm

Month as an abbreviation (Jan - Dec).

mmmm

Unabbreviated Month (January - December).

d

Day without leading zeros (1-31).

dd

Day with leading zeros (01-31).

ddd

Week day as an abbreviation (Sun - Sat).

dddd

Unabbreviated week day (Sunday - Saturday).

yy

Year as a two-digit number (for example, 96).

yyyy

Year as a four-digit number (for example, 1996).

Time Code

Description

h

Hours as a number without leading zeros (0-23).

hh

Hours as a number with leading zeros (00-23).

m

Minutes as a number without leading zeros (0-59).

mm

Minutes as a number with leading zeros (00-59).

s

Seconds as a number without leading zeros (0-59).

ss

Seconds as a number with leading zeros (00-59).

AM/PM am/pm

Time based on the twelve-hour clock.

Miscellaneous Code

Description

[BLACK], [BLUE], [CYAN], [GREEN], [MAGENTA], [RED], [WHITE], [YELLOW], [COLOR n]

These codes display the characters in the specified colors.

Note: n is a value from 1 to 56 and refers to the nth color in the color palette.

[Condition value]

Condition may be <, >, =, >=, <=, <> and value may be any number.

Note: A number format may contain up to two conditions.

XlsIO provides support for reading and writing various built-in and custom number formats in a cell by using the NumberFormat property of IRange interface.

The following code snippet illustrates how to set different number formats in a worksheet range.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet worksheet = workbook.Worksheets[0];

	worksheet.Range["A1"].Text = "DATA";
	worksheet.Range["B1"].Text = "NUMBER FORMAT APPLIED";
	worksheet.Range["C1"].Text = "RESULT";
	IStyle headingStyle = workbook.Styles.Add("HeadingStyle");
	headingStyle.Font.Bold = true;
	headingStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
	worksheet.Range["A1:C1"].CellStyle = headingStyle;

	#region Applying Number Format
	//Applying different number formats
	worksheet.Range["A2"].Text = "1000000.00075";
	worksheet.Range["B2"].Text = "0.00";
	worksheet.Range["C2"].NumberFormat = "0.00";
	worksheet.Range["C2"].Number = 1000000.00075;
	worksheet.Range["A3"].Text = "1000000.500";
	worksheet.Range["B3"].Text = "###,##";
	worksheet.Range["C3"].NumberFormat = "###,##";
	worksheet.Range["C3"].Number = 1000000.500;
	worksheet.Range["A5"].Text = "10000";
	worksheet.Range["B5"].Text = "0.00";
	worksheet.Range["C5"].NumberFormat = "0.00";
	worksheet.Range["C5"].Number = 10000;
	worksheet.Range["A6"].Text = "-500";
	worksheet.Range["B6"].Text = "[Blue]#,##0";
	worksheet.Range["C6"].NumberFormat = "[Blue]#,##0";
	worksheet.Range["C6"].Number = -500;
	worksheet.Range["A7"].Text = "0.000000000000000000001234567890";
	worksheet.Range["B7"].Text = "0.000000000000000000000000000000";
	worksheet.Range["C7"].NumberFormat = "0.000000000000000000000000000000";
	worksheet.Range["C7"].Number = 0.000000000000000000001234567890;
	worksheet.Range["A9"].Text = "1.20";
	worksheet.Range["B9"].Text = "0.00E+00";
	worksheet.Range["C9"].NumberFormat = "0.00E+00";
	worksheet.Range["C9"].Number = 1.20;

	//Applying percentage format
	worksheet.Range["A10"].Text = "1.20";
	worksheet.Range["B10"].Text = "0.00%";
	worksheet.Range["C10"].NumberFormat = "0.00%";
	worksheet.Range["C10"].Number = 1.20;

	//Applying date format
	worksheet.Range["A11"].Text = new DateTime(2005, 12, 25).ToString();
	worksheet.Range["B11"].Text = "m/d/yyyy";
	worksheet.Range["C11"].NumberFormat = "m/d/yyyy";
	worksheet.Range["C11"].DateTime = new DateTime(2005, 12, 25);

	//Applying currency format
	worksheet.Range["A12"].Text = "1.20";
	worksheet.Range["B12"].Text = "$#,##0.00";
	worksheet.Range["C12"].NumberFormat = "$#,##0.00";
	worksheet.Range["C12"].Number = 1.20;

	//Applying accounting format
	worksheet.Range["A12"].Text = "234";
	worksheet.Range["B12"].Text = "_($* #,##0_)";
	worksheet.Range["C12"].NumberFormat = "_($* #,##0_)";
	worksheet.Range["C12"].Number = 234;
	#endregion

	#region Accessing Value with Number Format
	//Get display text of the cell
	string text = worksheet.Range["C12"].DisplayText;
	#endregion

	//Fit column width to data
	worksheet.UsedRange.AutofitColumns();

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/NumberFormat.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  worksheet.Range["A1"].Text = "DATA";
  worksheet.Range["B1"].Text = "NUMBER FORMAT APPLIED";
  worksheet.Range["C1"].Text = "RESULT";
  IStyle headingStyle = workbook.Styles.Add("HeadingStyle");
  headingStyle.Font.Bold = true;
  headingStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
  worksheet.Range["A1:C1"].CellStyle = headingStyle;

  //Applying different number formats
  worksheet.Range["A2"].Text = "1000000.00075";
  worksheet.Range["B2"].Text = "0.00";
  worksheet.Range["C2"].NumberFormat = "0.00";
  worksheet.Range["C2"].Number = 1000000.00075;
  worksheet.Range["A3"].Text = "1000000.500";
  worksheet.Range["B3"].Text = "###,##";
  worksheet.Range["C3"].NumberFormat = "###,##";
  worksheet.Range["C3"].Number = 1000000.500;
  worksheet.Range["A5"].Text = "10000";
  worksheet.Range["B5"].Text = "0.00";
  worksheet.Range["C5"].NumberFormat = "0.00";
  worksheet.Range["C5"].Number = 10000;
  worksheet.Range["A6"].Text = "-500";
  worksheet.Range["B6"].Text = "[Blue]#,##0";
  worksheet.Range["C6"].NumberFormat = "[Blue]#,##0";
  worksheet.Range["C6"].Number = -500;
  worksheet.Range["A7"].Text = "0.000000000000000000001234567890";
  worksheet.Range["B7"].Text = "0.000000000000000000000000000000";
  worksheet.Range["C7"].NumberFormat = "0.000000000000000000000000000000";
  worksheet.Range["C7"].Number = 0.000000000000000000001234567890;
  worksheet.Range["A9"].Text = "1.20";
  worksheet.Range["B9"].Text = "0.00E+00";
  worksheet.Range["C9"].NumberFormat = "0.00E+00";
  worksheet.Range["C9"].Number = 1.20;

  //Applying percentage format
  worksheet.Range["A10"].Text = "1.20";
  worksheet.Range["B10"].Text = "0.00%";
  worksheet.Range["C10"].NumberFormat = "0.00%";
  worksheet.Range["C10"].Number = 1.20;

  //Applying date format
  worksheet.Range["A11"].Text = new DateTime(2005, 12, 25).ToString();
  worksheet.Range["B11"].Text = "m/d/yyyy";
  worksheet.Range["C11"].NumberFormat = "m/d/yyyy";
  worksheet.Range["C11"].DateTime = new DateTime(2005, 12, 25);

  //Applying currency format
  worksheet.Range["A12"].Text = "1.20";
  worksheet.Range["B12"].Text = "$#,##0.00";
  worksheet.Range["C12"].NumberFormat = "$#,##0.00";
  worksheet.Range["C12"].Number = 1.20;

  //Applying accounting format
  worksheet.Range["A12"].Text = "234";
  worksheet.Range["B12"].Text = "_($* #,##0_)";
  worksheet.Range["C12"].NumberFormat = "_($* #,##0_)";
  worksheet.Range["C12"].Number = 234;

  //Fit column width to data
  worksheet.UsedRange.AutofitColumns();

  workbook.SaveAs("NumberFormats.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)

  Dim worksheet As IWorksheet = workbook.Worksheets(0)
  worksheet.Range("A1").Text = "DATA"
  worksheet.Range("B1").Text = "NUMBER FORMAT APPLIED"
  worksheet.Range("C1").Text = "RESULT"
  Dim headingStyle As IStyle = workbook.Styles.Add("HeadingStyle")
  headingStyle.Font.Bold = True
  headingStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter
  worksheet.Range("A1:C1").CellStyle = headingStyle

  'Applying different number formats
  worksheet.Range("A2").Text = "1000000.00075"
  worksheet.Range("B2").Text = "0.00"
  worksheet.Range("C2").NumberFormat = "0.00"
  worksheet.Range("C2").Number = 1000000.00075
  worksheet.Range("A3").Text = "1000000.500"
  worksheet.Range("B3").Text = "###,##"
  worksheet.Range("C3").NumberFormat = "###,##"
  worksheet.Range("C3").Number = 1000000.5
  worksheet.Range("A5").Text = "10000"
  worksheet.Range("B5").Text = "0.00"
  worksheet.Range("C5").NumberFormat = "0.00"
  worksheet.Range("C5").Number = 10000
  worksheet.Range("A6").Text = "-500"
  worksheet.Range("B6").Text = "[Blue]#,##0"
  worksheet.Range("C6").NumberFormat = "[Blue]#,##0"
  worksheet.Range("C6").Number = -500
  worksheet.Range("A7").Text = "0.000000000000000000001234567890"
  worksheet.Range("B7").Text = "0.000000000000000000000000000000"
  worksheet.Range("C7").NumberFormat = "0.000000000000000000000000000000"
  worksheet.Range("C7").Number = 1.23456789E-21
  worksheet.Range("A9").Text = "1.20"
  worksheet.Range("B9").Text = "0.00E+00"
  worksheet.Range("C9").NumberFormat = "0.00E+00"
  worksheet.Range("C9").Number = 1.2

  'Applying percentage format
  worksheet.Range("A10").Text = "1.20"
  worksheet.Range("B10").Text = "0.00%"
  worksheet.Range("C10").NumberFormat = "0.00%"
  worksheet.Range("C10").Number = 1.2

  'Applying date format
  worksheet.Range("A11").Text = New DateTime(2005, 12, 25).ToString()
  worksheet.Range("B11").Text = "m/d/yyyy"
  worksheet.Range("C11").NumberFormat = "m/d/yyyy"
  worksheet.Range("C11").DateTime = New DateTime(2005, 12, 25)

  'Applying currency format
  worksheet.Range("A12").Text = "1.20"
  worksheet.Range("B12").Text = "$#,##0.00"
  worksheet.Range("C12").NumberFormat = "$#,##0.00"
  worksheet.Range("C12").Number = 1.2

  'Applying accounting format
  worksheet.Range("A12").Text = "234"
  worksheet.Range("B12").Text = "_($* #,##0_)"
  worksheet.Range("C12").NumberFormat = "_($* #,##0_)"
  worksheet.Range("C12").Number = 234

  'Fit column width to data
  worksheet.UsedRange.AutofitColumns()

  workbook.SaveAs("NumberFormats.xlsx")
End Using

The screenshot of the previous code is shown as follows:

A complete working example to apply number formats in C# is present on this GitHub page.

Excel document with number formats

Access number format applied results at runtime

Cell values can be accessed as Text, Number, DateTime and Formula of IRange interface. In addition to this, there is an another property DisplayText in IRange, which returns a resultant value of a cell with its number format applied.

The following code example illustrates how to display the text of a cell.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set value to the cell
  worksheet.Range["C4"].Number = 1.20;

  //Set value to a cell
  worksheet.Range["B4"].Text = "$#,##0.00";

  //Get display text of the cell
  string text = worksheet.Range["B4"].DisplayText;

  //Saving the workbook as stream
  FileStream stream = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set value to the cell
  worksheet.Range["C4"].Number = 1.20;

  //Set value to a cell
  worksheet.Range["B4"].Text = "$#,##0.00";

  //Get display text of the cell
  string text = worksheet.Range["B4"].DisplayText;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Set value to the cell
  worksheet.Range("C4").Number = 1.2

  'Set value to a cell
  worksheet.Range("B4").Text = "$#,##0.00"

  'Get display text of the cell
  Dim text As String = worksheet.Range("B4").DisplayText

  workbook.SaveAs("Output.xlsx")
End Using

You can set DetectDateTimeInValue property of IWorkbook as false with Value2 property, if you are sure that the given value is not of DateTime data type which improves time performance.

workbook.DetectDateTimeInValue = false;
workbook.DetectDateTimeInValue = false;
workbook.DetectDateTimeInValue = False

Hide Cell Content by setting Number Format

Essential XlsIO supports hiding rows or columns in a worksheet along with hiding specific range. You can also hide a particular cell content by setting a specific number format to that cell.

Refer to the following complete code snippets.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet worksheet = workbook.Worksheets[0];

	#region Hide Cell Content
	//Assign values to a range of cells in the worksheet
	worksheet.Range["A1:A10"].Text = "Hide Cell Content";

	//Apply number format for the cell to hide its content
	worksheet.Range["A5"].NumberFormat = ";;;";
	#endregion

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/HideCellContent.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Assign values to a range of cells in the worksheet
  worksheet.Range["A1:A10"].Text = "Hide Cell Content";

  //Apply number format for the cell to hide its content
  worksheet.Range["A5"].NumberFormat = ";;;";

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Assign values to a range of cells in the worksheet
  worksheet.Range("A1:A10").Text = "Hide Cell Content"

  'Apply number format for the cell to hide its content
  worksheet.Range("A5").NumberFormat = ";;;"

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to hide cell content by applying number format in C# is present on this GitHub page.

Apply Cell Text Alignment

The XlsIO supports the following alignment options:

  • Horizontal Alignment
  • Vertical Alignment
  • Indentation
  • Orientation
  • Text Direction

Horizontal Alignment

This code snippet aligns the cell content horizontally.

//Text Alignment Setting (Horizontal Alignment)
worksheet.Range["A2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
//Text Alignment Setting (Horizontal Alignment)
worksheet.Range["A2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
'Text Alignment Setting (Horizontal Alignment)
worksheet.Range("A2").CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter

Vertical Alignment

This code snippet aligns the cell content vertically.

//Text Alignment Setting (Vertical Alignment)
worksheet.Range["B2"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignBottom;
//Text Alignment Setting (Vertical Alignment)
worksheet.Range["B2"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignBottom;
'Text Alignment Setting (Vertical Alignment)
worksheet.Range("B2").CellStyle.VerticalAlignment = ExcelVAlign.VAlignBottom

Indentation

This allows to set the cell content either to move it closer to the cell border or to move it farther away from cell border.

//Text Indent Setting
worksheet.Range["C6"].CellStyle.IndentLevel = 6;
//Text Indent Setting
worksheet.Range["C6"].CellStyle.IndentLevel = 6;
'Text Indent Setting
worksheet.Range("C6").CellStyle.IndentLevel = 6

Orientation

This helps to rotate the cell text diagonally or vertically. The text orientation can be set by using the Rotation property as shown as follows.

//Text Orientation Settings
worksheet.Range["C2"].CellStyle.Rotation = 60;
//Text Orientation Settings
worksheet.Range["C2"].CellStyle.Rotation = 60;
'Text Orientation Settings
worksheet.Range("C2").CellStyle.Rotation = 60

Text Direction

You can specify the text direction by using the ReadingOrder property as shown as follows.

//Text Direction Setting
worksheet.Range["D2"].CellStyle.ReadingOrder = ExcelReadingOrderType.LeftToRight;
//Text Direction Setting
worksheet.Range["D2"].CellStyle.ReadingOrder = ExcelReadingOrderType.LeftToRight;
'Text Direction Setting
worksheet.Range("D2").CellStyle.ReadingOrder = ExcelReadingOrderType.LeftToRight

The following is the complete code snippet illustrating the previous options.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet worksheet = workbook.Worksheets[0];

	worksheet.Range["A2"].Text = "HAlignCenter";
	worksheet.Range["A4"].Text = "HAlignFill";
	worksheet.Range["A6"].Text = "HAlignRight";
	worksheet.Range["A8"].Text = "HAlignCenterAcrossSelection";
	worksheet.Range["B2"].Text = "VAlignCenter";
	worksheet.Range["B4"].Text = "VAlignFill";
	worksheet.Range["B6"].Text = "VAlignTop";
	worksheet.Range["B8"].Text = "VAlignCenterAcrossSelection";
	worksheet.Range["C2"].Text = "Text Rotation to 60 degree";
	worksheet.Range["C4"].Text = "Text Rotation to 90 degree";
	worksheet.Range["C6"].Text = "Indent level is 6";
	worksheet.Range["D2"].Text = "Text Direction(LeftToRight)";
	worksheet.Range["D3"].Text = "Text Direction(RightToLeft)";
	worksheet.Range["D4"].Text = "Text Direction(Context)";

	#region Alignment
	//Text Alignment Setting (Horizontal Alignment)
	worksheet.Range["A2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
	worksheet.Range["A4"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignFill;
	worksheet.Range["A6"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
	worksheet.Range["A8"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenterAcrossSelection;

	//Text Alignment Setting (Vertical Alignment)
	worksheet.Range["B2"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignBottom;
	worksheet.Range["B4"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
	worksheet.Range["B6"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignTop;
	worksheet.Range["B8"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignDistributed;

	//Text Orientation Settings
	worksheet.Range["C2"].CellStyle.Rotation = 60;
	worksheet.Range["C4"].CellStyle.Rotation = 90;

	//Text Indent Setting
	worksheet.Range["C6"].CellStyle.IndentLevel = 6;

	//Text Direction Setting
	worksheet.Range["D2"].CellStyle.ReadingOrder = ExcelReadingOrderType.LeftToRight;
	worksheet.Range["D3"].CellStyle.ReadingOrder = ExcelReadingOrderType.RightToLeft;
	worksheet.Range["D4"].CellStyle.ReadingOrder = ExcelReadingOrderType.Context;
	#endregion

	worksheet.UsedRange.AutofitColumns();
	worksheet.UsedRange.AutofitRows();

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/Alignment.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  worksheet.Range["A2"].Text = "HAlignCenter";
  worksheet.Range["A4"].Text = "HAlignFill";
  worksheet.Range["A6"].Text = "HAlignRight";
  worksheet.Range["A8"].Text = "HAlignCenterAcrossSelection";
  worksheet.Range["B2"].Text = "VAlignCenter";
  worksheet.Range["B4"].Text = "VAlignFill";
  worksheet.Range["B6"].Text = "VAlignTop";
  worksheet.Range["B8"].Text = "VAlignCenterAcrossSelection";
  worksheet.Range["C2"].Text = "Text Rotation to 60 degree";
  worksheet.Range["C4"].Text = "Text Rotation to 90 degree";
  worksheet.Range["C6"].Text = "Indent level is 6";
  worksheet.Range["D2"].Text = "Text Direction(LeftToRight)";
  worksheet.Range["D3"].Text = "Text Direction(RightToLeft)";
  worksheet.Range["D4"].Text = "Text Direction(Context)";

  //Text Alignment Setting (Horizontal Alignment)
  worksheet.Range["A2"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter;
  worksheet.Range["A4"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignFill;
  worksheet.Range["A6"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight;
  worksheet.Range["A8"].CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenterAcrossSelection;

  //Text Alignment Setting (Vertical Alignment)
  worksheet.Range["B2"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignBottom;
  worksheet.Range["B4"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter;
  worksheet.Range["B6"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignTop;
  worksheet.Range["B8"].CellStyle.VerticalAlignment = ExcelVAlign.VAlignDistributed;

  //Text Orientation Settings
  worksheet.Range["C2"].CellStyle.Rotation = 60;
  worksheet.Range["C4"].CellStyle.Rotation = 90;

  //Text Indent Setting
  worksheet.Range["C6"].CellStyle.IndentLevel = 6;

  //Text Direction Setting
  worksheet.Range["D2"].CellStyle.ReadingOrder = ExcelReadingOrderType.LeftToRight;
  worksheet.Range["D3"].CellStyle.ReadingOrder = ExcelReadingOrderType.RightToLeft;
  worksheet.Range["D4"].CellStyle.ReadingOrder = ExcelReadingOrderType.Context;
  worksheet.UsedRange.AutofitColumns();
  worksheet.UsedRange.AutofitRows();

  workbook.SaveAs("Book1.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  worksheet.Range("A2").Text = "HAlignCenter"
  worksheet.Range("A4").Text = "HAlignFill"
  worksheet.Range("A6").Text = "HAlignRight"
  worksheet.Range("A8").Text = "HAlignCenterAcrossSelection"
  worksheet.Range("B2").Text = "VAlignCenter"
  worksheet.Range("B4").Text = "VAlignFill"
  worksheet.Range("B6").Text = "VAlignTop"
  worksheet.Range("B8").Text = "VAlignCenterAcrossSelection"
  worksheet.Range("C2").Text = "Text Rotation to 60 degree"
  worksheet.Range("C4").Text = "Text Rotation to 90 degree"
  worksheet.Range("C6").Text = "Indent level is 6"
  worksheet.Range("D2").Text = "Text Direction(LeftToRight)"
  worksheet.Range("D3").Text = "Text Direction(RightToLeft)"
  worksheet.Range("D4").Text = "Text Direction(Context)"

  'Text Alignment Setting (Horizontal Alignment)
  worksheet.Range("A2").CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenter
  worksheet.Range("A4").CellStyle.HorizontalAlignment = ExcelHAlign.HAlignFill
  worksheet.Range("A6").CellStyle.HorizontalAlignment = ExcelHAlign.HAlignRight
  worksheet.Range("A8").CellStyle.HorizontalAlignment = ExcelHAlign.HAlignCenterAcrossSelection

  'Text Alignment Setting (Vertical Alignment)
  worksheet.Range("B2").CellStyle.VerticalAlignment = ExcelVAlign.VAlignBottom
  worksheet.Range("B4").CellStyle.VerticalAlignment = ExcelVAlign.VAlignCenter
  worksheet.Range("B6").CellStyle.VerticalAlignment = ExcelVAlign.VAlignTop
  worksheet.Range("B8").CellStyle.VerticalAlignment = ExcelVAlign.VAlignDistributed

  'Text Orientation Settings
  worksheet.Range("C2").CellStyle.Rotation = 60
  worksheet.Range("C4").CellStyle.Rotation = 90

  'Text Indent Setting
  worksheet.Range("C6").CellStyle.IndentLevel = 6

  'Text Direction Setting
  worksheet.Range("D2").CellStyle.ReadingOrder = ExcelReadingOrderType.LeftToRight
  worksheet.Range("D3").CellStyle.ReadingOrder = ExcelReadingOrderType.RightToLeft
  worksheet.Range("D4").CellStyle.ReadingOrder = ExcelReadingOrderType.Context
  worksheet.UsedRange.AutofitColumns()
  worksheet.UsedRange.AutofitRows()

  workbook.SaveAs("Book1.xlsx")
End Using

A complete working example to apply cell text alignment in C# is present on this GitHub page.

The following screenshot is the output of previous code:

Excel document with different alignment options

Merging and Un-Merging Cells

The cells can be merged using the Merge method in IRange as shown as follows.

//Merging Cells from A16 to C16
worksheet.Range["A16:C16"].Merge();
//Merging Cells from A16 to C16
worksheet.Range["A16:C16"].Merge();
'Merging Cells from A16 to C16
worksheet.Range("A16:C16").Merge()

Merged cells can be unmerged using the UnMerge method in IRange as shown below.

//Un-Merging merged cells from A16 to C16
worksheet.Range["A16:C16"].UnMerge();
//Un-Merging merged cells from A16 to C16
worksheet.Range["A16:C16"].UnMerge();
'Un-Merging merged cells from A16 to C16
worksheet.Range("A16:C16").UnMerge()

The below code shows merging and unmerging worksheet cells.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet worksheet = workbook.Worksheets[0];

	#region Merge
	//Merging cells
	worksheet.Range["A5:E10"].Merge();
	worksheet.Range["A15:E20"].Merge();
	#endregion

	#region UnMerge
	//Un-Merging merged cells
	worksheet.Range["A5:E10"].UnMerge();
	#endregion

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/MergeandUnMerge.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Merging cells
  worksheet.Range["A16:C16"].Merge();
  //Un-Merging merged cells
  worksheet.Range["A16:C16"].UnMerge();

  workbook.SaveAs("MergingUnMerging.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create()
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Merging cells
  worksheet.Range("A16:C16").Merge()
  'Un-Merging merged cells
  worksheet.Range("A16:C16").UnMerge()

  workbook.SaveAs("MergingUnMerging.xlsx")
End Using

A complete working example to merge and unmerge cells in C# is present on this GitHub page.

Apply Wrap Text

If a cell content is too wide to fit a column and do not want to split over into adjacent cells, you can use the WrapText property. This will set the content within the cell border. The following code snippet illustrates this behavior.

NOTE

Applying wrap-text will not auto-fit the rows by default. It is recommended to [auto-fit](#_AutoFit_Rows_or “”) manually.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet worksheet = workbook.Worksheets[0];

	worksheet.Range["A2"].Text = "First Sentence is wrapped";
	worksheet.Range["B2"].Text = "Second Sentence is wrapped";
	worksheet.Range["C2"].Text = "Third Sentence is wrapped";

	#region Wrap Text
	//Applying Wrap-text
	worksheet.Range["A2:C2"].WrapText = true;
	#endregion

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/WrapText.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  worksheet.Range["A2"].Text = "First Sentence is wrapped";
  worksheet.Range["B2"].Text = "Second Sentence is wrapped";
  worksheet.Range["C2"].Text = "Third Sentence is wrapped";

  //Applying Wrap-text
  worksheet.Range["A2:C2"].WrapText = true;

  workbook.SaveAs("WrapText.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  worksheet.Range("A2").Text = "First Sentence is wrapped"
  worksheet.Range("B2").Text = "Second Sentence is wrapped"
  worksheet.Range("C2").Text = "Third Sentence is wrapped"

  'Applying wrap-text
  worksheet.Range("A2:C2").WrapText = True

  workbook.SaveAs("WrapText.xlsx")
End Using

A complete working example to apply wrap text in C# is present on this GitHub page.

Unwrap Text

Similarly, the wrapped text can be unwrapped by disabling the WrapText property. The following code explains this.

worksheet.Range["A2:C2"].WrapText = false;
worksheet.Range["A2:C2"].WrapText = false;
worksheet.Range("A2:C2").WrapText = False

Auto-Fit Rows or Columns

Cell dimensions can be auto-sized to its content dynamically to make its content visible.

The following code shows how to auto-size row height and column width to its cell content.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet worksheet = workbook.Worksheets[0];

	#region AutoFit Row
	//Auto-fit rows
	worksheet.Range["A2"].Text = "Fit the content to row";
	worksheet.Range["A2"].WrapText = true;
	worksheet.Range["A2"].AutofitRows();
	#endregion

	#region AutoFit Column
	//Auto-fit columns
	worksheet.Range["B4"].Text = "Fit the content to column";
	worksheet.Range["B4"].AutofitColumns();
	#endregion

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/AutoFit.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Auto-fit rows
  worksheet.Range["A2"].Text = "Fit the content to row";
  worksheet.Range["A2"].WrapText = true;
  worksheet.Range["A2"].AutofitRows();

  //Auto-fit columns
  worksheet.Range["B4"].Text = "Fit the content to column";
  worksheet.Range["B4"].AutofitColumns();

  workbook.SaveAs("AutoFit.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Auto-fit rows
  worksheet.Range("A2").Text = "Fit the content to row"
  worksheet.Range("A2").WrapText = True
  worksheet.Range("A2").AutofitRows()

  'Auto-fit columns
  worksheet.Range("B4").Text = "Fit the content to column"
  worksheet.Range("B4").AutofitColumns()

  workbook.SaveAs("AutoFit.xlsx")
End Using

A complete working example to autofit rows and columns in C# is present on this GitHub page.

Apply Font Settings 

The appearance of a text can be controlled by font settings of a cell. These settings can be done by using the Font property in CellStyle. Refer to the following code.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet worksheet = workbook.Worksheets[0];

	//Adding text for a range
	worksheet.Range["A1:B6"].Text = "Hello World";

	#region Font Settings
	//Setting Font Type
	worksheet.Range["A1"].CellStyle.Font.FontName = "Arial Black";
	worksheet.Range["A3"].CellStyle.Font.FontName = "Castellar";

	//Setting Font Styles
	worksheet.Range["A2"].CellStyle.Font.Bold = true;
	worksheet.Range["A4"].CellStyle.Font.Italic = true;

	//Setting Font Size
	worksheet.Range["A5"].CellStyle.Font.Size = 18;

	//Setting Font Effects
	worksheet.Range["A6"].CellStyle.Font.Strikethrough = true;
	worksheet.Range["B3"].CellStyle.Font.Subscript = true;
	worksheet.Range["B5"].CellStyle.Font.Superscript = true;

	//Setting UnderLine Types
	worksheet.Range["B1"].CellStyle.Font.Underline = ExcelUnderline.Double;
	worksheet.Range["B2"].CellStyle.Font.Underline = ExcelUnderline.Single;
	worksheet.Range["B4"].CellStyle.Font.Underline = ExcelUnderline.DoubleAccounting;
	worksheet.Range["B6"].CellStyle.Font.Underline = ExcelUnderline.SingleAccounting;

	//Setting Font Color
	worksheet.Range["B6"].CellStyle.Font.Color = ExcelKnownColors.Green;
	#endregion

	worksheet.UsedRange.AutofitColumns();
	worksheet.UsedRange.AutofitRows();

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/FontSettings.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Adding text for a range
  worksheet.Range["A1:B6"].Text = "Hello World";

  //Setting Font Type
  worksheet.Range["A1"].CellStyle.Font.FontName = "Arial Black";
  worksheet.Range["A3"].CellStyle.Font.FontName = "Castellar";

  //Setting Font Styles
  worksheet.Range["A2"].CellStyle.Font.Bold = true;
  worksheet.Range["A4"].CellStyle.Font.Italic = true;

  //Setting Font Size
  worksheet.Range["A5"].CellStyle.Font.Size = 18;

  //Setting Font Effects
  worksheet.Range["A6"].CellStyle.Font.Strikethrough = true;
  worksheet.Range["B3"].CellStyle.Font.Subscript = true;
  worksheet.Range["B5"].CellStyle.Font.Superscript = true;

  //Setting UnderLine Types
  worksheet.Range["B1"].CellStyle.Font.Underline = ExcelUnderline.Double;
  worksheet.Range["B2"].CellStyle.Font.Underline = ExcelUnderline.Single;
  worksheet.Range["B4"].CellStyle.Font.Underline = ExcelUnderline.DoubleAccounting;
  worksheet.Range["B6"].CellStyle.Font.Underline = ExcelUnderline.SingleAccounting;

  //Setting Font Color
  worksheet.Range["B6"].CellStyle.Font.Color = ExcelKnownColors.Green;
  worksheet.UsedRange.AutofitColumns();
  worksheet.UsedRange.AutofitRows();

  workbook.SaveAs("FontSettings.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Adding text for a range
  worksheet.Range("A1:B6").Text = "Hello World"

  'Setting Font Type
  worksheet.Range("A1").CellStyle.Font.FontName = "Arial Black"
  worksheet.Range("A3").CellStyle.Font.FontName = "Castellar"

  'Setting Font Styles
  worksheet.Range("A2").CellStyle.Font.Bold = True
  worksheet.Range("A4").CellStyle.Font.Italic = True

  'Setting Font Size
  worksheet.Range("A5").CellStyle.Font.Size = 18

  'Setting Font Effects
  worksheet.Range("A6").CellStyle.Font.Strikethrough = True
  worksheet.Range("B3").CellStyle.Font.Subscript = True
  worksheet.Range("B5").CellStyle.Font.Superscript = True

  'Setting UnderLine Types
  worksheet.Range("B1").CellStyle.Font.Underline = ExcelUnderline.Double
  worksheet.Range("B2").CellStyle.Font.Underline = ExcelUnderline.Single
  worksheet.Range("B4").CellStyle.Font.Underline = ExcelUnderline.DoubleAccounting
  worksheet.Range("B6").CellStyle.Font.Underline = ExcelUnderline.SingleAccounting

  'Setting Font Color
  worksheet.Range("B6").CellStyle.Font.Color = ExcelKnownColors.Green
  worksheet.UsedRange.AutofitColumns()
  worksheet.UsedRange.AutofitRows()

  workbook.SaveAs("FontSettings.xlsx")
End Using

A complete working example to apply different font settings in C# is present on this GitHub page.

The output of the previous code is shown as follows.

Excel document with different font settings

Apply Color Settings

Colors give enhancement to cell values to highlight the data. These color settings in a cell are differentiated as BackColor, ForeColor, and PatternColor.

Back Color settings

Back color of a cell can be set using the ColorIndex property of CellStyle as shown as follows.

//Apply cell back color
worksheet.Range["A1"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
//Apply cell back color
worksheet.Range["A1"].CellStyle.ColorIndex = ExcelKnownColors.Aqua;
'Apply cell back color
worksheet.Range("A1").CellStyle.ColorIndex = ExcelKnownColors.Aqua

Fore Color Settings

Fore color of a cell can be set using the PatternColorIndex property of CellStyle as shown as follows.

//Apply cell fore color
worksheet.Range["A2"].CellStyle.PatternColorIndex = ExcelKnownColors.Green;
//Apply cell fore color
worksheet.Range["A2"].CellStyle.PatternColorIndex = ExcelKnownColors.Green;
'Apply cell fore color
worksheet.Range("A2").CellStyle.PatternColorIndex = ExcelKnownColors.Green

Pattern Settings

Excel provides various pattern styles for highlighting the cells. These patterns can be applied using the FillPattern property of CellStyle as shown as follows.

//Apply cell pattern
worksheet.Range["A2"].CellStyle.FillPattern = ExcelPattern.Angle;
//Apply cell pattern
worksheet.Range["A2"].CellStyle.FillPattern = ExcelPattern.Angle;
'Apply cell pattern
worksheet.Range("A2").CellStyle.FillPattern = ExcelPattern.Angle

A complete working example to apply different color settings in C# is present on this GitHub page.

Apply Border Settings

The XlsIO applies cell borders and format it through IBorderApplying HTML String interface as shown as follows.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet worksheet = workbook.Worksheets[0];

	#region Border Settings
	//Apply borders
	worksheet.Range["A2"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
	worksheet.Range["A4"].CellStyle.Borders.LineStyle = ExcelLineStyle.Double;
	worksheet.Range["A6"].CellStyle.Borders.LineStyle = ExcelLineStyle.Dash_dot;
	worksheet.Range["A8"].CellStyle.Borders.LineStyle = ExcelLineStyle.Thick;
	worksheet.Range["C2"].CellStyle.Borders.LineStyle = ExcelLineStyle.Slanted_dash_dot;
	worksheet.Range["C4"].CellStyle.Borders.LineStyle = ExcelLineStyle.Hair;
	worksheet.Range["C6"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium_dash_dot_dot;
	worksheet.Range["C8"].CellStyle.Borders.LineStyle = ExcelLineStyle.Thin;

	//Apply Border using Border Index
	//Top Border
	worksheet.Range["E2"].CellStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Medium;
	//Left Border
	worksheet.Range["E4"].CellStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Double;
	//Bottom Border
	worksheet.Range["E6"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Dashed;
	//Right Border
	worksheet.Range["E8"].CellStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thick;
	//DiagonalUp Border
	worksheet.Range["E10"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].LineStyle = ExcelLineStyle.Thin;
	//DiagonalDown Border
	worksheet.Range["E12"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].LineStyle = ExcelLineStyle.Dotted;

	//Apply border color
	worksheet.Range["A2"].CellStyle.Borders.Color = ExcelKnownColors.Blue;

	//Setting the Border as Range
	worksheet.Range["G2:I8"].BorderAround();
	worksheet.Range["G2:I8"].BorderInside(ExcelLineStyle.Dash_dot, ExcelKnownColors.Red);
	#endregion

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/BorderSettings.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Apply borders
  worksheet.Range["A2"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium;
  worksheet.Range["A4"].CellStyle.Borders.LineStyle = ExcelLineStyle.Double;
  worksheet.Range["A6"].CellStyle.Borders.LineStyle = ExcelLineStyle.Dash_dot;
  worksheet.Range["A8"].CellStyle.Borders.LineStyle = ExcelLineStyle.Thick;
  worksheet.Range["C2"].CellStyle.Borders.LineStyle = ExcelLineStyle.Slanted_dash_dot;
  worksheet.Range["C4"].CellStyle.Borders.LineStyle = ExcelLineStyle.Hair;
  worksheet.Range["C6"].CellStyle.Borders.LineStyle = ExcelLineStyle.Medium_dash_dot_dot;
  worksheet.Range["C8"].CellStyle.Borders.LineStyle = ExcelLineStyle.Thin;

  //Apply Border using Border Index
  //Top Border
  worksheet.Range["E2"].CellStyle.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Medium;
  //Left Border
  worksheet.Range["E4"].CellStyle.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Double;
  //Bottom Border
  worksheet.Range["E6"].CellStyle.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Dashed;
  //Right Border
  worksheet.Range["E8"].CellStyle.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thick;
  //DiagonalUp Border
  worksheet.Range["E10"].CellStyle.Borders[ExcelBordersIndex.DiagonalUp].LineStyle = ExcelLineStyle.Thin;
  //DiagonalDown Border
  worksheet.Range["E12"].CellStyle.Borders[ExcelBordersIndex.DiagonalDown].LineStyle = ExcelLineStyle.Dotted;
  //Inside Horizontal Border
  worksheet.Range["G2:I5"].CellStyle.Borders[ExcelBordersIndex.InsideHorizontal].LineStyle = ExcelLineStyle.Thick;
  //Inside Vertical Border
  worksheet.Range["G6:I8"].CellStyle.Borders[ExcelBordersIndex.InsideVertical].LineStyle = ExcelLineStyle.Thick;

  //Apply border color
  worksheet.Range["A2"].CellStyle.Borders.Color = ExcelKnownColors.Blue;

  //Setting the Border as Range
  worksheet.Range["G10:I16"].BorderAround();
  worksheet.Range["G10:I16"].BorderInside(ExcelLineStyle.Dash_dot, ExcelKnownColors.Red);

  workbook.SaveAs("BorderSettings.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Apply borders
  worksheet.Range("A2").CellStyle.Borders.LineStyle = ExcelLineStyle.Medium
  worksheet.Range("A4").CellStyle.Borders.LineStyle = ExcelLineStyle.Double
  worksheet.Range("A6").CellStyle.Borders.LineStyle = ExcelLineStyle.Dash_dot
  worksheet.Range("A8").CellStyle.Borders.LineStyle = ExcelLineStyle.Thick
  worksheet.Range("C2").CellStyle.Borders.LineStyle = ExcelLineStyle.Slanted_dash_dot
  worksheet.Range("C4").CellStyle.Borders.LineStyle = ExcelLineStyle.Hair
  worksheet.Range("C6").CellStyle.Borders.LineStyle = ExcelLineStyle.Medium_dash_dot_dot
  worksheet.Range("C8").CellStyle.Borders.LineStyle = ExcelLineStyle.Thin

  'Apply Border using Border Index
  'Top Border
  worksheet.Range("E2").CellStyle.Borders(ExcelBordersIndex.EdgeTop).LineStyle = ExcelLineStyle.Medium
  'Left Border
  worksheet.Range("E4").CellStyle.Borders(ExcelBordersIndex.EdgeLeft).LineStyle = ExcelLineStyle.Double
  'Bottom Border
  worksheet.Range("E6").CellStyle.Borders(ExcelBordersIndex.EdgeBottom).LineStyle = ExcelLineStyle.Dashed
  'Right Border
  worksheet.Range("E8").CellStyle.Borders(ExcelBordersIndex.EdgeRight).LineStyle = ExcelLineStyle.Thick
  'DiagonalUp Border
  worksheet.Range("E10").CellStyle.Borders(ExcelBordersIndex.DiagonalUp).LineStyle = ExcelLineStyle.Thin
  'DiagonalDown Border
  worksheet.Range("E12").CellStyle.Borders(ExcelBordersIndex.DiagonalDown).LineStyle = ExcelLineStyle.Dotted
  'Inside Horizontal Border
  worksheet.Range("G2:I5").CellStyle.Borders(ExcelBordersIndex.InsideHorizontal).LineStyle = ExcelLineStyle.Thick
  'Inside Vertical Border
  worksheet.Range("G6:I8").CellStyle.Borders(ExcelBordersIndex.InsideVertical).LineStyle = ExcelLineStyle.Thick
  
  'Apply border color
  worksheet.Range("A2").CellStyle.Borders.Color = ExcelKnownColors.Blue

  'Setting the Border as Range
  worksheet.Range("G10:I16").BorderAround()
  worksheet.Range("G10:I16").BorderInside(ExcelLineStyle.Dash_dot, ExcelKnownColors.Red)

  workbook.SaveAs("BorderSettings.xlsx")
End Using

A complete working example to apply different border settings in C# is present on this GitHub page.

The output of the previous code is shown in the following screenshot:

Excel document with different border settings

HTML String Formatting

HTML string generates a string of characters with different formatting styles using different HTML tags for each character. This makes it easy to manipulate the text or value in the worksheet range as each character is independent and doesn’t depend on hierarchical tag structure.

XlsIO supports adding HTML Rich-Text to a range of cells in worksheet.

Applying HTML String

The following code snippet illustrates how to read and write HTML Rich-Text using HtmlString property of IRange.

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Create(1);
    IWorksheet worksheet = workbook.Worksheets[0];

    //Add HTML string
    worksheet.Range["A1"].HtmlString = "<font style=\"color:red;font-family:Magneto;font-size:12px; \">Welcome Syncfusion</font>";

    //Assign HTML string as text to different range
    worksheet.Range["A2"].Text = worksheet.Range["A1"].HtmlString;

    #region Save
    //Saving the workbook
    FileStream outputStream = new FileStream(Path.GetFullPath("Output/HTMLString.xlsx"), FileMode.Create, FileAccess.Write);
    workbook.SaveAs(outputStream);
    #endregion

    //Dispose streams
    outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(3);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Add HTML string
  worksheet.Range["A1"].HtmlString = "<font style=\"color:red;font-family:Magneto;font-size:12px; \">Welcome Syncfusion</font>";

  //Assign HTML string as text to different range
  worksheet.Range["A2"].Text = worksheet.Range["A1"].HtmlString;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(3)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Add HTML string
  worksheet.Range("A1").HtmlString = "<font style=""color:red;font-family:Magneto;font-size:12px; "">Welcome Syncfusion</font>"

  'Assign HTML string as text to different range
  worksheet.Range("A2").Text = worksheet.Range("A1").HtmlString

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to apply HTML string formatting in C# is present on this GitHub page.

Supported Tags

The following are the list of tags supported in addition to HTML string.

  • <p></p> - Defines a paragraph
  • <font></font> - Defines font, color and size of text
  • <h1….h6></h1…h6> - Defines HTML headings
  • <a href></a> - Defines a hyperlink
  • <i></i> - Defines italic text
  • <u></u> - Underlines the text
  • <b></b> - Defines bold text
  • <sub></sub> - Defines subscript
  • <sup></sup> - Defines superscript
  • <br></br> - Inserts link break
  • <strike></strike> - Strikes out the text
  • <strong></strong> - Defines important text

These mentioned tags do support the following style attributes.

  • Color
  • Font-family
  • Text-decoration
  • Font-size
  • Size
  • Face

Rich-Text Formatting 

You can format each character in a cell with different font styles. XlsIO reads and writes rich-text by using the IRichTextString interface.

NOTE

Currently XlsIO cannot process and write RTF codes to cells.

using (ExcelEngine excelEngine = new ExcelEngine())
{
	IApplication application = excelEngine.Excel;
	application.DefaultVersion = ExcelVersion.Xlsx;
	IWorkbook workbook = application.Workbooks.Create(1);
	IWorksheet worksheet = workbook.Worksheets[0];

	//Add Text
	IRange range = worksheet.Range["A1"];
	range.Text = "RichText";
	IRichTextString richText = range.RichText;

	//Formatting first 4 characters.
	IFont redFont = workbook.CreateFont();
	redFont.Bold = true;
	redFont.Italic = true;
	redFont.RGBColor = Syncfusion.Drawing.Color.Red;
	richText.SetFont(0, 3, redFont);

	//Formatting last 4 characters.
	IFont blueFont = workbook.CreateFont();
	blueFont.Bold = true;
	blueFont.Italic = true;
	blueFont.RGBColor = Syncfusion.Drawing.Color.Blue;
	richText.SetFont(4, 7, blueFont);

	#region Save
	//Saving the workbook
	FileStream outputStream = new FileStream(Path.GetFullPath("Output/RichText.xlsx"), FileMode.Create, FileAccess.Write);
	workbook.SaveAs(outputStream);
	#endregion

	//Dispose streams
	outputStream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Add Text
  IRange range = worksheet.Range["A1"];
  range.Text = "RichText";
  IRichTextString richText = range.RichText;

  //Formatting first 4 characters
  IFont redFont = workbook.CreateFont();
  redFont.Bold = true;
  redFont.Italic = true;
  redFont.RGBColor = Color.Red;
  richText.SetFont(0, 3, redFont);

  //Formatting last 4 characters
  IFont blueFont = workbook.CreateFont();
  blueFont.Bold = true;
  blueFont.Italic = true;
  blueFont.RGBColor = Color.Blue;
  richText.SetFont(4, 7, blueFont);

  workbook.SaveAs("RichText.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Excel2013
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Add Text
  Dim range As IRange = worksheet.Range("A1")
  range.Text = "RichText"
  Dim richText As IRichTextString = range.RichText

  'Formatting first 4 characters
  Dim redFont As IFont = workbook.CreateFont()
  redFont.Bold = True
  redFont.Italic = True
  redFont.RGBColor = Color.Red
  richText.SetFont(0, 3, redFont)

  'Formatting last 4 characters
  Dim blueFont As IFont = workbook.CreateFont()
  blueFont.Bold = True
  blueFont.Italic = True
  blueFont.RGBColor = Color.Blue
  richText.SetFont(4, 7, blueFont)

  workbook.SaveAs("RichText.xlsx")
End Using

A complete working example to apply rich text formatting in C# is present on this GitHub page.

The output of the previous code is shown as follows:

Excel document with rich-text formatting