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 the 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 command:

  Install-Package IronXL.Excel

  Install-Package IronXL.Excel

  SHELL

• 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 explain 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 to Excel files. In the below code example, the CSV file is converted to an XLSX file and formatted.

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

// Load an existing CSV file as a workbook
WorkBook workBook = WorkBook.Load("table.csv");

// Access the default worksheet within the workbook
WorkSheet ws = workBook.DefaultWorkSheet;

// Select a specific cell range and apply a background color
var cell = ws["B4:B4"];
cell.Style.SetBackgroundColor("#428D65"); // Set background color to green

// Apply an underline style to a range of cells
var range1 = ws["A2:E6"];
range1.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Single accounting underline

// Apply bold and italic font styles to another range of cells
var range2 = ws["A7:E11"];
range2.Style.Font.Bold = true; // Set font to bold
range2.Style.Font.Italic = true; // Set font to italic

// Add a medium-thickness bottom border to a range of cells
var range = ws["A1:E11"];
range.Style.BottomBorder.Type = BorderType.Medium; // Medium border

// Save the modified workbook as an XLSX file
workBook.SaveAs("sample.xlsx");

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

// Load an existing CSV file as a workbook
WorkBook workBook = WorkBook.Load("table.csv");

// Access the default worksheet within the workbook
WorkSheet ws = workBook.DefaultWorkSheet;

// Select a specific cell range and apply a background color
var cell = ws["B4:B4"];
cell.Style.SetBackgroundColor("#428D65"); // Set background color to green

// Apply an underline style to a range of cells
var range1 = ws["A2:E6"];
range1.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Single accounting underline

// Apply bold and italic font styles to another range of cells
var range2 = ws["A7:E11"];
range2.Style.Font.Bold = true; // Set font to bold
range2.Style.Font.Italic = true; // Set font to italic

// Add a medium-thickness bottom border to a range of cells
var range = ws["A1:E11"];
range.Style.BottomBorder.Type = BorderType.Medium; // Medium border

// Save the modified workbook as an XLSX file
workBook.SaveAs("sample.xlsx");

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

' Load an existing CSV file as a workbook
Private workBook As WorkBook = WorkBook.Load("table.csv")

' Access the default worksheet within the workbook
Private ws As WorkSheet = workBook.DefaultWorkSheet

' Select a specific cell range and apply a background color
Private cell = ws("B4:B4")
cell.Style.SetBackgroundColor("#428D65") ' Set background color to green

' Apply an underline style to a range of cells
Dim range1 = ws("A2:E6")
range1.Style.Font.Underline = FontUnderlineType.SingleAccounting ' Single accounting underline

' Apply bold and italic font styles to another range of cells
Dim range2 = ws("A7:E11")
range2.Style.Font.Bold = True ' Set font to bold
range2.Style.Font.Italic = True ' Set font to italic

' Add a medium-thickness bottom border to a range of cells
Dim range = ws("A1:E11")
range.Style.BottomBorder.Type = BorderType.Medium ' Medium border

' Save the modified workbook as an XLSX file
workBook.SaveAs("sample.xlsx")

The provided C# code uses the IronXL library for working with Excel files in the .NET Framework. Here's how it functions:

1. Namespace Importing: The code imports necessary IronXL libraries for Excel functionalities, styling, and coloring.
2. Workbook Loading: It loads an Excel workbook from "table.csv" using WorkBook.Load, preparing for conversion from CSV to Excel format.
3. Accessing Worksheet: The default worksheet in the workbook is accessed and assigned to the variable ws.
4. Applying Styles:
   • Cell B4 is set to have a green background.
   • The range A2 to E6 is underlined using a single accounting style.
   • The range A7 to E11 is formatted to have bold and italic fonts.
   • A medium-thickness bottom border is added to the range A1 to E11.
5. Saving the Workbook: The workbook, with all applied styles, is saved as "sample.xlsx", ensuring that the formatting is preserved.

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