How to Programmatically Format Excel XLSX Cells Using a C# .NET API
Tags: webdev, devops, csharp, tutorial
Published: 2023-12-07 16:37:31

---
canonical_url: https://developer.mescius.com/blogs/how-to-progammatically-format-excel-xlsx-cells-using-a-c-sharp-net-api
description: Learn how to programmatically format Excel XLSX cells using a C# .NET API. See more and build your application today.
---
Microsoft Excel is powerful spreadsheet software that can handle vast amounts of data and organize it in a structured tabular layout. However, it isn't easy to find relevant insight from this large pool of data in its raw format. MS Excel helps to distinguish the data and present its intended meaning by using colors, text sizes, alignment, and several other appearance settings.

In this blog, we’ll see how you can use Document Solutions for Excel (DsExcel .NET Edition) to programmatically set the various formatting and appearance settings provided by Excel for data recorded within a cell. It includes:

*   <span>[Text Color](#color)</span>
*   [<span>Border</span>](#border)
*   [<span>Text Style</span>](#textstyle)
*   [<span>Text Alignment and Indentation</span>](#alignment)
*   [<span>Text Direction and Orientation (Angle)</span>](#direction)
*   [<span>RichText Control</span>](#richtext)
*   [<span>Conditional Formatting</span>](#conditional)
*   [<span>Cell Style</span>](#cellstyle)

## <a id="color"></a>Text Color

Text Color is one of the fundamental appearance settings that helps with several data situations, such as

*   highlight what is important in your data, for example, rising temperatures
*   distinguish data such as departmental or regional sales
*   promote information recall by increasing attention level, and many others

In Excel, you add text color using the Color palette in the Toolbar or Format cell dialog as shown below:

<span>![Format Excel C#](//cdn.mescius.io/umb/media/5hvdss03/01-format.png?rmode=max&width=747&height=528)</span>

To add text color using DsExcel .NET, use the _Color_ or __ThemeColor__ property of the **Font** setting with **IRange **interface as depicted in the code below:

```
    //Standard, System color
    worksheet.Range["A1"].Font.Color = Color.Red;
    or
    //Theme color
    worksheet.Range["A1"].Font.ThemeColor = ThemeColor.Light1; 
```

While Color is used to set the standard (system) color palette, ThemeColor is used to set the colors from the theme palette. You can set other colors using System.Drawing.Color.FromArgb method.

## <a id="border"></a>Border

The border is another commonly used formatting option that helps to create sections of data that may be related but separate from each other, such as Billing and Shipping Details in an Invoice, Totals in a list, and so on.

Like Text Color, Border can be applied from the Toolbar and Format Cell dialog in MsExcel. To apply borders using DsExcel API for .NET, it can be applied using the **Border** setting with **IRange** interface. This setting offers to configure the border color and line style using the Color or ThemeColor and LineStyle properties as depicted below:

```
    worksheet.Range["A1"].Borders.LineStyle = BorderLineStyle.Dotted;
    or 
    worksheet.Range["A1"].Cells.Borders.LineStyle = BorderLineStyle.Dotted;
```

The above code applies borders to all the sides of a cell or range of cells. To apply a border to individual sides using the BordersIndex enum as shown in the code below:

```
worksheet.Range["A1"].Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Dotted;
    worksheet.Range["A1"].Cells.Borders[BordersIndex.EdgeBottom].LineStyle = BorderLineStyle.Dotted;
```

## <a id="textstyle"></a>Text Style

Excel allows you to style text for Bold, Italics, and Underline and make the content more scannable to readers. They can easily identify words and phrases that carry more weight in the content.

DsExcel allows you to play with text font and text styles using the **Font** setting with the **IRange** interface, as shown below:

```
    worksheet.Range["A1"].Font.Bold = true;
    worksheet.Range["A1"].Font.Italic = true;
    worksheet.Range["A1"].Font.Underline = UnderlineType.Single;

    //OR

    worksheet.Range["A1"].Cells.Font.Bold = true;
    worksheet.Range["A1"].Cells.Font.Italic = true;
    worksheet.Range["A1"].Cells.Font.Underline = UnderlineType.Single;
```

## <a id="alignment"></a>Text Alignment and Indentation

Text alignment and Indentation is a paragraph-formatting attribute that makes it easier to process and comprehend the document content. It is generally used to format the appearance of the text in a whole paragraph or numbers in a table.

There are two types of text alignment in MS Excel:

*   Horizontal alignment with options: left, center, right, and justify
*   Vertical alignment with options: top, middle, and bottom

![Format Excel C#](//cdn.mescius.io/umb/media/0tylajg5/02-format.png)

Using DsExcel, you can programmatically align text using the **HorizontalAlignment** and **VerticalAlignment** properties with the **IRange** interface as shown below:

```
     worksheet.Range["A1"].HorizontalAlignment = HorizontalAlignment.Center;
     worksheet.Range["A1"].VerticalAlignment = VerticalAlignment.Top;
```

There are two types of indentation: left and right.

![Format Excel C#](//cdn.mescius.io/umb/media/k3onkblb/03-format.png)

You can apply text indentation by enabling the **AddIndent** setting with the **IRange** interface and configuring the **IndentLevel**, which accepts an integer indicating the indent level, as shown in the code below:

```
    worksheet.Range["A1:H7"].AddIndent = true;
    worksheet.Range["A1:A7"].IndentLevel = 0;
    worksheet.Range["B1:B7"].IndentLevel = 1;
    worksheet.Range["C1:C7"].IndentLevel = 2;
```

## <a id="direction"></a>Text Direction and Orientation (Angle)

Text Direction and Orientation settings in Excel help with language-specific styling. Text direction configures the writing direction - left-to-right (LTR) or right-to-left (RTL) and can be used for languages like Arabic. Text Orientation sets the angle for text and is especially useful with Vertical texts like CJK.

![Format Excel C#](//cdn.mescius.io/umb/media/opjbpf0o/04-format.png?rmode=max&width=759&height=638)

DsExcel allows setting the Text Direction using the **ReadingOrder** property with the **IRange** interface. It accepts the values from the **ReadingOrder** enum as shown below:

```
    worksheet.Range["A1"].ReadingOrder = ReadingOrder.RightToLeft; 
```

You can use the Orientation property with the **IRange** interface to add the angled text. It accepts integer values ranging from -90 to 90 or 255 (for vertical text), as shown below: 

```
    worksheet.Range["A1"].Orientation = 45;
```

Try the demos - [.NET](https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/formatting "https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/formatting") | [Java](https://developer.mescius.com/document-solutions/java-excel-api/demos/formatting "https://developer.mescius.com/document-solutions/java-excel-api/demos/formatting")

## <a id="richtext"></a>RichText Control

DsExcel provides support for applying rich text formatting in the cells of the worksheet. RichText allows text styling in a cell using different colors, font, effects (bold, underline, double underline, strikethrough, subscript, superscript), and much more. It eventually helps in text highlighting and emphasizing within a single cell.

In Excel, to include RichText in a cell, you enter the cell in edit mode and select a part of the text to apply individual formatting, as shown below:

![Format Excel C#](//cdn.mescius.io/umb/media/nxkg4ech/05-richtext.gif)

Using DsExcel, you can configure RichText using **IRichText** and **ITextRun** objects as depicted below:

```
    IRichText richText = worksheet.Range["A1"].RichText;
    // Add string "Documents " to IRichText object and apply formatting
    ITextRun run1 = richText.Add("Document");
    run1.Font.Color = Color.Red;
    run1.Font.Bold = true;
    run1.Font.Size = 20;

    // Append string "Solutions" to IRichText object and apply formatting
    ITextRun run2 = richText.Add("Solutions");
    run2.Font.ThemeFont = ThemeFont.Major;
    run2.Font.ThemeColor = ThemeColor.Accent1;
    run2.Font.Size = 30;
    run2.Font.Underline = UnderlineType.Single;
```

For more details, check out the documentation [.NET](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/SetRichTextinaCell.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/SetRichTextinaCell.html") | [Java](https://developer.mescius.com/document-solutions/java-excel-api/docs/online/SetRichTextinaCell.html "https://developer.mescius.com/document-solutions/java-excel-api/docs/online/SetRichTextinaCell.html")

Try out the demos [.NET](https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/cellrichtext "https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/cellrichtext") | [Java](https://developer.mescius.com/document-solutions/java-excel-api/demos/cellrichtext "https://developer.mescius.com/document-solutions/java-excel-api/demos/cellrichtext")

## <a id="conditional"></a>Conditional Formatting

In a worksheet, Excel allows users to create conditional formatting rules on cell values for individual cells and a range of cells to highlight information that matches certain criteria or rows and columns. When the cell value meets the data condition, the cell is formatted as per the specified rule.

Excel offers several conditional rules that can be applied to a cell or a range of cells.

![Format Excel C#](//cdn.mescius.io/umb/media/wwqbe424/06-format.png) 

DsExcel supports all the conditional formatting rules that Excel offers. The set of conditional formatting rules for a range is applied using the **FormatConditions** collection with the **IRange** interface. For example, to apply a conditional format for unique values in a range, you need to add a rule for AddUniqueValue to the **FormatConditions** collection as depicted in the code below:

```
    IUniqueValues condition = worksheet.Range["A1:E7"].FormatConditions.AddUniqueValues();
    condition.DupeUnique = DupeUnique.Unique;
    condition.Font.Name = "Arial";
    condition.Interior.Color = Color.Pink;
```

Check out more details for each of the conditional formatting rules from [.NET](https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/ApplyConditionalFormatting.html "https://developer.mescius.com/document-solutions/dot-net-excel-api/docs/online/ApplyConditionalFormatting.html") | [Java](https://developer.mescius.com/document-solutions/java-excel-api/docs/online/ApplyConditionalFormatting.html "https://developer.mescius.com/document-solutions/java-excel-api/docs/online/ApplyConditionalFormatting.html") help.

Try the demos - [.NET](https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/conditionalformatting "https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/conditionalformatting") | [Java](https://developer.mescius.com/document-solutions/java-excel-api/demos/conditionalformatting "https://developer.mescius.com/document-solutions/java-excel-api/demos/conditionalformatting")

## <a id="cellstyle"></a>Cell Style

Excel provides several built-in cell styles, such as Good, Bad, Heading, Title, and so on, to quickly style your cell for specific data needs.

![Format Excel C#](//cdn.mescius.io/umb/media/wq3negme/07-format.png)

With DsExcel, you can programmatically apply these quick styles to a cell or range of cells using the workbook's **Styles** collections and providing it as a value to the **IRange.Style** property as shown below:

```
    worksheet.Range["A1"].Style = workbook.Styles["Bad"];
```

Try the demos - [.NET](https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/applystyle "https://developer.mescius.com/document-solutions/dot-net-excel-api/demos/applystyle") | [Java](https://developer.mescius.com/document-solutions/java-excel-api/demos/applystyle "https://developer.mescius.com/document-solutions/java-excel-api/demos/applystyle")

## Conclusion

In essence, Document Solutions for Excel not only complements Excel's powerful data management capabilities but also adds a layer of programmability and control, enabling users to elevate the visual representation of their data for more effective communication and analysis. You can explore the provided demos to experience firsthand the versatility and functionality that DsExcel offers in enhancing your Excel workflow.