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:
- Text Color
- Border
- Text Style
- Text Alignment and Indentation
- Text Direction and Orientation (Angle)
- RichText Control
- Conditional Formatting
- Cell Style
Ready to Try It Out? Download Document Solutions for Excel Today!
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:
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.
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;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;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
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.
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;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.
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;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:
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 | Java
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.
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 | Java help.
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.
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"];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.
Ready to Try It Out? Download Document Solutions for Excel Today!
Tags:
Related Blogs
Try Our .NET Spreadsheet Document API
Paarisha Rana