How to Export to Excel With Formatting in C#

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

1. Install the C# library required for exporting to Excel with formatting.
2. Utilize the WorkBook.Load method to load an already existing CSV file.
3. Set the background color of the cell using SetBackgroundColor function.
4. Add the border to the file using BottomBorder and BorderType methods
5. 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.

1. In this tutorial, the Console Application project is recommended to illustrate how to work with IronXL.

   Create a new project in Visual Studio

2. After selecting the project type, proceed to specify a name and location for the project.

   Configure new project

3. You also have the flexibility to choose the preferred framework for the project, such as .NET Core 6.

   Target framework selection

4. 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.

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

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

1. The code begins by importing the necessary libraries, which include IronXL and other related libraries, to access the required functionalities.
2. 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.
3. The default worksheet of the loaded Excel workbook is accessed and assigned to the ws variable that converts the DataTable to an Excel worksheet.
4. 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.
5. 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.
6. 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.

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.