How to Create a new Excel Workbook in C#

How Do I Get Started Creating Excel Workbooks in C#?

In this tutorial, we'll learn how to create a new workbook using IronXL in C#. We'll walk through the necessary steps, from setting up your development environment to writing code that generates a new Excel workbook. You'll discover how to create spreadsheets programmatically without any Excel installation on your machine.

By the end of this tutorial, you'll have a solid understanding of how to leverage IronXL to create Excel workbooks programmatically, empowering you to integrate Excel functionality into your C# applications with ease. Let's get started!

What is IronXL?

IronXL is a versatile C# library that allows you to work with Excel documents without needing Microsoft Office Excel Interop or any Excel Application. It lets you easily read, create, and modify workbooks, format cells, add formulas, and work with both modern and older Excel file formats.

You can validate data, insert images, apply conditional formatting, and create charts without needing Microsoft Office. Developers can build Excel solutions for tasks like financial reports, data dashboards, and inventory management with ease using IronXL. The library supports converting between different spreadsheet formats including XLSX, XLS, CSV, TSV, JSON, and XML.

Let's begin creating an Excel file in C#.

How Do I Create a New C# Project for Excel Development?

Open Visual Studio and create a new C# Console Application project. Name it whatever you prefer. You can also create other types of projects such as ASP.NET MVC, Blazor, MAUI, Web Forms, Windows Forms, Web API, etc. This code will work with all project types. I'm using a console application for simplicity and to make it relevant for all project types. The library even works in Docker containers and Azure Functions.

How Do I Install IronXL NuGet Package?

To install the IronXL package in your C# project, you can use any of the following ways. Proper license key configuration ensures your production deployments work without restrictions.

1. To install IronXL, right-click on your project in Solution Explorer, choose "Manage NuGet Packages," search for IronXL, and then proceed with the installation.

2. Alternatively, you can install IronXL via the Package Manager Console using the following command:

Install-Package IronXL.Excel

This command will download, install, and add an assembly reference to your project. Wait for the package to be downloaded and installed. Once the installation is complete, you can start using IronXL in your project to work with Excel files programmatically.

How Do I Import Necessary Namespaces?

At the top of your C# file, add the following namespace:

using IronXL;

using IronXL;

How Do I Create a New Excel File Programmatically?

Now, let's write the code to create an Excel file:

internal class Program
{
    static void Main(string[] args)
    {
        // Create a new workbook in the XLSX format
        WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
        workBook.Metadata.Author = "Mr. Author"; // Set the author (optional)

        // Add a blank worksheet named "Sheet1"
        WorkSheet workSheet = workBook.CreateWorkSheet("Sheet1");

        // Add data to the new worksheet
        workSheet["A1"].Value = "Developer Name";
        workSheet["A2"].Value = "John Grahm";
        workSheet["A3"].Value = "David Smith";
        workSheet["A4"].Value = "Rishi Kelkar";

        // Save the Excel file as "Developers.xlsx"
        workBook.SaveAs("Developers.xlsx");
    }
}

internal class Program
{
    static void Main(string[] args)
    {
        // Create a new workbook in the XLSX format
        WorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
        workBook.Metadata.Author = "Mr. Author"; // Set the author (optional)

        // Add a blank worksheet named "Sheet1"
        WorkSheet workSheet = workBook.CreateWorkSheet("Sheet1");

        // Add data to the new worksheet
        workSheet["A1"].Value = "Developer Name";
        workSheet["A2"].Value = "John Grahm";
        workSheet["A3"].Value = "David Smith";
        workSheet["A4"].Value = "Rishi Kelkar";

        // Save the Excel file as "Developers.xlsx"
        workBook.SaveAs("Developers.xlsx");
    }
}

The above code demonstrates how to create an Excel file programmatically using IronXL in C#. It starts by creating a new Excel workbook (a new instance of an Excel file) in the XLSX format, sets author metadata using the workbook metadata properties, adds a blank Excel worksheet named "Sheet1" using the CreateWorkSheet() method, populates data in cells A1 to A4, and finally saves the workbook as "Developers.xlsx".

IronXL offers a range of functionalities for working with Excel files, including creation, manipulation, and saving in various formats like XLSX, CSV, TSV, JSON, XML, and HTML. You can also load existing spreadsheets to modify them or manage multiple worksheets within a single workbook.

The output is as:

How Do I Apply Formatting and Styling to Excel Files?

IronXL allows you to format cells, apply font styles, set background colors, and adjust alignment. You can create professional-looking spreadsheets by customizing cell appearance. The library supports comprehensive cell styling including borders, fonts, and alignment options.

// Set style of heading for cell A1
workSheet["A1"].Style.BackgroundColor = "#FFFF66"; // Light yellow background
workSheet["A1"].Style.Font.Bold = true; // Bold font

// Set border style for a specific range (A1:A4)
var range = workSheet["A1:A4"];
range.Style.BottomBorder.Type = BorderType.Medium; // Medium bottom border
range.Style.LeftBorder.Type = BorderType.Medium; // Medium left border
range.Style.RightBorder.Type = BorderType.Medium; // Medium right border
range.Style.TopBorder.Type = BorderType.Medium; // Medium top border

// Set style of heading for cell A1
workSheet["A1"].Style.BackgroundColor = "#FFFF66"; // Light yellow background
workSheet["A1"].Style.Font.Bold = true; // Bold font

// Set border style for a specific range (A1:A4)
var range = workSheet["A1:A4"];
range.Style.BottomBorder.Type = BorderType.Medium; // Medium bottom border
range.Style.LeftBorder.Type = BorderType.Medium; // Medium left border
range.Style.RightBorder.Type = BorderType.Medium; // Medium right border
range.Style.TopBorder.Type = BorderType.Medium; // Medium top border

The above code demonstrates how to customize the appearance of specific cells in an Excel worksheet using IronXL in C#. It first sets the background color and pattern of cell A1 to a light yellow shade and makes the font bold, effectively styling it as a heading.

Next, it defines a range spanning cells A1 to A4 and sets medium-weight borders along the bottom, left, right, and top edges of this range, enhancing its visual distinction within the worksheet. These styling options allow developers to create visually appealing and organized Excel documents tailored to their specific needs. You can also set cell data formats for numbers, dates, and currencies.

The output is as:

How Do I Add Formulas and Calculations?

You can add formulas to cells programmatically. IronXL supports a wide range of Excel functions and mathematical operations. The library can both set formulas and evaluate their results.

// Add a new column to display the length of developer names
workSheet["B1"].Value = "Name Length";
workSheet["B1"].Style.BackgroundColor = "#FFFF66"; // Styled as heading
workSheet["B1"].Style.Font.Bold = true; // Bold font

// Formula to calculate the length of names in column B
workSheet["B2"].Value = "=LEN(A2)";
workSheet["B3"].Value = "=LEN(A3)";
workSheet["B4"].Value = "=LEN(A4)";

// Add a total count of the length of names in cell A5
workSheet["A5"].Value = "Sum of Length";
workSheet["B5"].Formula = "=SUM(B2:B4)";

// Add a new column to display the length of developer names
workSheet["B1"].Value = "Name Length";
workSheet["B1"].Style.BackgroundColor = "#FFFF66"; // Styled as heading
workSheet["B1"].Style.Font.Bold = true; // Bold font

// Formula to calculate the length of names in column B
workSheet["B2"].Value = "=LEN(A2)";
workSheet["B3"].Value = "=LEN(A3)";
workSheet["B4"].Value = "=LEN(A4)";

// Add a total count of the length of names in cell A5
workSheet["A5"].Value = "Sum of Length";
workSheet["B5"].Formula = "=SUM(B2:B4)";

The above code illustrates how to use formulas and functions in IronXL for calculating the length of developer names and computing the sum of these lengths within an Excel worksheet. Through this demonstration, developers can understand how to integrate formulas and functions within IronXL to perform dynamic calculations and manipulations within Excel worksheets programmatically, offering flexibility and automation in data processing tasks.

First, a header titled "Name Length" is added to cell B1, with styling to highlight its significance. Next, formulas are applied to cells B2, B3, and B4 to calculate the length of each developer's name using the LEN function, referencing the corresponding cell in column A. This enables automatic calculation of name lengths as the developer names change.

Additionally, a total count of name lengths is computed in cell B5 using the SUM function, which adds up the values from cells B2 to B4. You can also use other aggregate functions like AVERAGE, MIN, and MAX.

By incorporating these formulas, the worksheet becomes dynamically updated. While this specific use case might not be practical, it serves as an example of using Excel formulas in code.

What Are My Next Steps with IronXL?

In summary, this tutorial has demonstrated how to create a new Excel workbook in C# using IronXL, a robust library facilitating Excel file manipulation within the C# ecosystem. With IronXL, developers can seamlessly integrate Excel functionality into their applications, from setting up the development environment to generating Excel workbooks programmatically. Alongside its ability to perform tasks like formatting, styling, and applying formulas, IronXL offers a comprehensive feature set for efficient data management and analysis.

For references on how to use IronXL, visit the documentation page. IronXL also offers a collection of code examples that are helpful to get started. Explore advanced scenarios like creating charts, working with named ranges, or protecting worksheets.

Developers can explore IronXL through its free trial and purchase, ensuring a seamless transition from evaluation to full-scale implementation. For more details on perpetual licenses, visit the license page. Consider exploring license extensions for additional features.