using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

USING IRONXL

How to Export to Excel With Formatting in C#

Updated March 24, 2024

This article will explore various methods and techniques for exporting data to Excel while incorporating formatting elements that can help you create professional, visually engaging spreadsheets using IronXL.

How to Export to Excel with Formatting

Install the C# library required for exporting to Excel with formatting.
Utilize the WorkBook.Load method to load an already existing CSV file.
Set the background color of the cell using SetBackgroundColor function.
Add the border to the file using BottomBorder and BorderType methods
Save the XLSX file using SaveAs method.

IronXL

IronXL is a powerful and versatile Excel library for working with Excel files in the .NET Framework. It provides developers with a comprehensive set of tools to create, manipulate, and manage Excel documents programmatically. Whether you're building a desktop application, a web-based system, or working on data-driven projects in C# or VB.NET, IronXL simplifies the process of interacting with Excel files.

This library is designed to streamline tasks such as reading, writing, and formatting Excel spreadsheets, making it an indispensable resource for those seeking efficient and reliable solutions for Excel integration in their .NET applications.

This introduction will explore the key features and capabilities of IronXL, demonstrating how it can empower developers to work seamlessly with Excel data, unlocking new possibilities for data processing and reporting within the .NET ecosystem.

Creating a New Project in C#

To leverage the capabilities of the IronXL library for Excel-related tasks, the first step involves creating a .NET project in Visual Studio. Although any version of Visual Studio is compatible, it is advisable to use the most recent one.

In this tutorial, the Console Application project is recommended to illustrate how to work with IronXL.
Create a new project in Visual Studio
After selecting the project type, proceed to specify a name and location for the project.
Configure new project
You also have the flexibility to choose the preferred framework for the project, such as .NET Core 6.
Target framework selection
Once the solution is generated, access the Program.cs file. In this file, you can input your code and execute the application.

Installing IronXL

The IronXL library offers various methods for downloading and installation, and this article will discuss two of them.

Using Visual Studio NuGet Packages

To install the IronXL library using NuGet Package Manager in Visual Studio, open the NuGet Package Manager and search for "IronXL" in the Browse tab.

Once you've located IronXL in the search results, select it and proceed with the installation. After the installation is completed, you can utilize the IronXL library in your project.

How to Export to Excel With Formatting in C#, Figure 4: Search and install IronXL package in NuGet Package Manager UI Search and install the IronXL package in NuGet Package Manager UI

Using the Visual Studio Command Line

Many developers prefer to install packages using the command line interface. To install IronXL via the command line interface, follow these steps:

Go to Tools > NuGet Package Manager > Package Manager Console in Visual Studio.
Navigate to Package Manager Console
In the Package Manager Console tab, enter the following line:
```
Install-Package IronXL.Excel
```
Now the package will download and install into the current project, making it ready for use.
Installation of IronXL in Package Manager Console

Export `DataTable` from CSV File to Excel File With formatting

This section will see how to convert the CSV file data table to a worksheet and then add formatting to it before saving it as an Excel file using IronXL. Here is the complete source code of the conversion method or export function that converts CSV files and DataTable table to Excel files. In the below code example, the CSV file is converted to an XLSX file and add formatting to it.

using IronXL;
using IronXL.Styles;
using IronSoftware.Drawing;
using System.Linq;

WorkBook workBook = WorkBook.Load("table.csv");
WorkSheet ws = workBook.DefaultWorkSheet;

var range = ws["A1:E11"];
var cell = ws["B4:B4"];
cell.Style.SetBackgroundColor("#428D65");

var range1 = ws["A2:E6"];
range1.Style.Font.Underline = FontUnderlineType.SingleAccounting;

var range2 = ws["A7:E11"];
range2.Style.Font.Bold = true;
range2.Style.Font.Italic = true;
range.Style.BottomBorder.Type = BorderType.Medium;

workBook.SaveAs("sample.xlsx");

using IronXL;
using IronXL.Styles;
using IronSoftware.Drawing;
using System.Linq;

WorkBook workBook = WorkBook.Load("table.csv");
WorkSheet ws = workBook.DefaultWorkSheet;

var range = ws["A1:E11"];
var cell = ws["B4:B4"];
cell.Style.SetBackgroundColor("#428D65");

var range1 = ws["A2:E6"];
range1.Style.Font.Underline = FontUnderlineType.SingleAccounting;

var range2 = ws["A7:E11"];
range2.Style.Font.Bold = true;
range2.Style.Font.Italic = true;
range.Style.BottomBorder.Type = BorderType.Medium;

workBook.SaveAs("sample.xlsx");

Imports IronXL
Imports IronXL.Styles
Imports IronSoftware.Drawing
Imports System.Linq

Private workBook As WorkBook = WorkBook.Load("table.csv")
Private ws As WorkSheet = workBook.DefaultWorkSheet

Private range = ws("A1:E11")
Private cell = ws("B4:B4")
cell.Style.SetBackgroundColor("#428D65")

Dim range1 = ws("A2:E6")
range1.Style.Font.Underline = FontUnderlineType.SingleAccounting

Dim range2 = ws("A7:E11")
range2.Style.Font.Bold = True
range2.Style.Font.Italic = True
range.Style.BottomBorder.Type = BorderType.Medium

workBook.SaveAs("sample.xlsx")

VB C#

The provided code is written in C# and utilizes the IronXL library, which is designed for working with Excel files in the .NET Framework. Here's an explanation of the code's functionality in plain language

The code begins by importing the necessary libraries, which include IronXL and other related libraries, to access the required functionalities.
It then loads an Excel workbook from a CSV file named "table.csv" using the WorkBook.Load method. This CSV file is essentially converted into an Excel workbook for further processing.
The default worksheet of the loaded Excel workbook is accessed and assigned to the ws variable that converts the DataTable to an Excel worksheet.
The code proceeds to apply various styles and formatting to different parts of the worksheet. For instance, it selects a specific cell (B4) and sets its background color to a shade of green. Additionally, it underlines the font in a particular cell range (A2 to E6) with a specific style (Single Accounting underline). In a different cell range (A7 to E11), it applies both bold and italic font styles to the text.
Furthermore, the code adds a medium-thickness bottom border to a larger range of cells (A1 to E11). These styling and formatting instructions enhance the visual representation of the data within the Excel worksheet.
Finally, the modified workbook is saved as an XLSX file named "sample.xlsx" using the SaveAs method. This ensures that all the styling and formatting changes are persisted in the resulting Excel document.

How to Export to Excel With Formatting in C#, Figure 7: The result Excel file The result Excel file

Conclusion

Exporting data to Excel with formatting is a crucial aspect of data management and reporting, enabling professionals from various fields to present information in a visually appealing and organized manner. The provided C# code leverages the IronXL library to streamline this process, allowing users to create a new project, install IronXL, and transform data from a CSV file into an XLSX file with applied formatting you can also convert it into an XLS file.

IronXL simplifies the interaction between C# applications and Excel, making it easier to apply various formatting styles, such as background colors, font underlining, bold and italic text, and border additions. This capability enhances the visual appeal and clarity of the resulting Excel documents, making it a valuable resource for data analysts, business professionals, and developers seeking to create polished and professional reports.

The complete source code example of export to Excel can be found in the following how-to page. For more detailed instructions, please visit the example of formatting Excel files and customizing background cells.

If you want to try out IronXL today, be sure to check out the free trial, a risk-free opportunity to explore its capabilities. You can purchase the license after exploring all the functionalities IronXL has to offer.

< PREVIOUS
How to Write A CSV File in C#

NEXT >
How to Export File to CSV in C#