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");

Published April 20, 2023

How to Export to Excel with Formatting in C#

As a C# developer, you will often encounter scenarios where you need to store data in Excel files with specific formatting. This data can come in various formats such as text, JSON, data tables, HTML, or XML files. Exporting this data to Excel can be a daunting task if you are starting from scratch. Fortunately, with the emergence of new technologies, there are several Excel libraries available in C#.

In this article, we will be using IronXL to export data to Excel with formatting.

IronXL

IronXL is a powerful Excel library that enables C# developers to create, load, read, format, and edit Excel files with XLS or XLSX extensions. It is designed specifically for .NET and prioritizes speed, accuracy, and ease of use. IronXL enables users to save Excel files in various formats and load different spreadsheet formats into Excel for easier data reading.

With IronXL, users can take advantage of all the formatting features available for a cell. It also supports easy import and export of data to and from Excel files. IronXL is compatible with the latest version of .NET Framework as well as previous versions up to .NET Core 2.0. Additionally, IronXL can be used on a variety of platforms, including Linux, MacOS, Azure, Docker, and AWS.

How to Export to Excel in C# using IronPDF

Prerequisites

To export data to Excel with formatting in C#, we require the following components:

Visual Studio - To work on C# .NET applications, an IDE is required. Visual Studio is the recommended IDE for C# apps and is available for download and installation from the Microsoft website. You can also use any other IDE that supports C# envirnoment.
Create Console App: Creating a console project is the primary task. Follow these steps to create a simple console application.
- Open Visual Studio and click Create a Project.
- Choose Console App from the list of options available. Make sure the language selected is C#.
- Next, name your project whatever you want. I have named mine "Demo App".
- Next, select .NET as the project configuration; the latest version is .NET 7.0 and it has standard-term support. To avoid any errors, I have used the more stable version, .NET 6.0, for which Microsoft provides long-term support. Choose the appropriate version according to your needs.
IronXL - IronXL is a .NET Excel library that helps to work with Excel spreadsheets. You can download and install the IronXL package from multiple sources:
1. NuGet Package Manager - the package manager of the .NET Framework. You can directly download and install IronXL from the NuGet website.
2. NuGet Packages in Visual Studio: You can also install IronXL by using the NuGet package manager within Visual Studio. Simply right-click on the solution explorer and select "Manage NuGet packages for solution".
3. Download IronXL .NET Excel DLL Zip File: You can download the IronXL .NET Excel DLL zip file directly from the IronExcel website.

Adding Namespaces

Once all the prerequisites are fulfilled, we need to add an assembly reference to use IronXL in our program. To do this, add the following namespace at the top of the source code in the main.cs file:


    using IronXL;
    using IronXL.Styles;
    using IronSoftware.Drawing;


    using IronXL;
    using IronXL.Styles;
    using IronSoftware.Drawing;

Imports IronXL
	Imports IronXL.Styles
	Imports IronSoftware.Drawing

VB C#

Create or Load an Existing Data Source

To export data to Excel, we first need a data source. IronXL is a flexible library, and to export to Excel, we can add data from almost all structured document formats. Some of the data sources other than .xls or .xlsx files include CSV, TSV, DataSet, and DataTable. Here, we will load a simple CSV data file to export it to Excel. The following code loads a CSV into a new WorkBook class:


    WorkBook workBook = WorkBook.Load("color_srgb.csv");


    WorkBook workBook = WorkBook.Load("color_srgb.csv");

Dim workBook As WorkBook = WorkBook.Load("color_srgb.csv")

VB C#

The above code opens the CSV file for editing. The following file is now opened:

How to Export to Excel with Formatting in C#: Figure 5

Get Data as an Excel WorkSheet

In the previous step, a CSV file is opened as an Excel WorkBook. Now, we will access the data as part of the default worksheet using the WorkSheet class.


    WorkSheet ws = workbook.DefaultWorkSheet;


    WorkSheet ws = workbook.DefaultWorkSheet;

Dim ws As WorkSheet = workbook.DefaultWorkSheet

VB C#

For more detail on working with Excel worksheets, you can visit the following code examples page. Note that you can also export data to other formats.

Apply Formatting to a Range of Cells

Once the CSV file is loaded successfully and the data is available as a worksheet, we can then format the specified rows and columns. IronXL provides a complete range of Excel formatting, including cell styles, borders, fonts, number formatting, conditional formatting, and more.

Get a range of cells

It is straightforward to select a range of cells from a worksheet using IronXL. The following source code selects a range of cells vertically, starting from the first column, and applies settings to the first Excel cell:


    var range = workSheet["A1:C10"];
    var cell = range.First();


    var range = workSheet["A1:C10"];
    var cell = range.First();

Dim range = workSheet("A1:C10")
	Dim cell = range.First()

VB C#

Apply Formatting to Cells

Background Color

Now, we can start applying different formatting options to the first row selected in the previous step. The first row is the header row and we will set its background color:


    // Set background color of the cell with an rgb string
    cell.Style.SetBackgroundColor("#428D65");


    // Set background color of the cell with an rgb string
    cell.Style.SetBackgroundColor("#428D65");

' Set background color of the cell with an rgb string
	cell.Style.SetBackgroundColor("#428D65")

VB C#

The above method will only set the background color of the first cell among the other two column headers.

Underline Font


    // Set underline property to the font
    range.Style.Font.Underline = FontUnderlineType.SingleAccounting;


    // Set underline property to the font
    range.Style.Font.Underline = FontUnderlineType.SingleAccounting;

' Set underline property to the font
	range.Style.Font.Underline = FontUnderlineType.SingleAccounting

VB C#

In the above line of code, the font underline property is set to single using the FontUnderlineType enum.

Bold and Italic

Here, we will set the headers to bold and prevent the font from being italics.


    // 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;


    // 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;

' 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

VB C#

Cell Border

The cell border style property lets you set the Excel cell TopBorder, BottomBorder, RightBorder, LeftBorder and DiagonalBorder. You can also set the border type.


    // Set the type of the border line
    range.Style.BottomBorder.Type = BorderType.MediumDashed;


    // Set the type of the border line
    range.Style.BottomBorder.Type = BorderType.MediumDashed;

' Set the type of the border line
	range.Style.BottomBorder.Type = BorderType.MediumDashed

VB C#

There are a number of formatting options available using the IronXL library. For more information on cell ranges style, please visit this code example.

Save to an XLS File or an XLSX File

How to Export to Excel with Formatting in C#: Figure 6

IronXL also provides the ability to load data from a DataTable into an Excel format. For more information on how to convert a C# DataTable to Excel or CSV file format, you can refer to the following code example.

Summary

In this article, we learned how to export data to an Excel file and apply formatting to it in C# using IronXL. In addition to the formatting options mentioned above, IronXL also offers the ability to apply conditional formatting.

IronXL facilitates the conversion between file formats and enables the creation of Excel files from scratch, even without Interop and Microsoft Excel installed. It is compatible with C# DataSet and DataTable, giving developers an advantage in easily interconverting data without relying on any third-party applications.

IronXL is free for development but must be licensed for commercial use. Try its 30-day free trial to test its compatibility with your project in production.