How to Create an Excel File in C#

In this tutorial I will show you how to create an Excel file using the C# programming language. This tutorial consists of a step-by-step guide specially designed for beginners and intermediate developers. Experts can skip the “project creation” section and jump directly to the “code” section. I will use the IronXL Library to create Excel files.

We will cover the following topics:

• What is an Excel File?
• What is IronXL?
• A Step-by-Step Guide to Creating Excel Files
• Step 1: Create a Visual Studio Project
• Step 2: Install the NuGet Package
• Step 3: Create an Excel Workbook
• Step 4: Set a Default Worksheet
• Step 5: Save the Workbook
• Step 6: Set Cell Values
• Set Cell Values Manually
• Set Cell Values Dynamically
• Set Cell Values from the Database
• Summary

What is an Excel File?

Excel is a spreadsheet application developed and published by Microsoft. It is part of the Microsoft Office suite of productivity software.
Excel is a tool for both organizing data and making calculations with it. The software can analyze data, calculate statistics, generate pivot tables, and represent data as charts or graphs.
For example, you could create an Excel spreadsheet that calculates a monthly budget, tracks associated expenses, and interactively sorts the data by various criteria.
Unlike a word processor, such as Microsoft Word, Excel organizes data in columns and rows. Rows and columns intersect at a space called a cell. Each cell contains data, such as text, numerical values, or formulas.

What is IronXL?

IronXL is an intuitive C# & VB Excel API that allows you to read, edit and create Excel spreadsheet files in .NET with lightning-fast performance. There is no need to install MS Office or even Excel Interop. This library can also be used for manipulating Excel documents.
IronXL fully supports .NET Core, .NET Framework, Xamarin, Mobile, Linux, MacOS and Azure.

A Step-by-Step Guide to Creating Excel Files

Firstly, we have to create a new Visual Studio Project. I will use the Console Application template for the demonstration. You can use any template that best suits your requirements.
You may also use a current project that would benefit from the capacity to create Excel files.

Step1: Create a Visual Studio Project

Open Microsoft Visual Studio 2019 or any other version. However, the latest version is recommended. Click on “Create New Project”. Select the C# Console Application for the application template. Click on the “Next” button.
Assign a name to your project. I have named mine "Excel Tutorial". You can select any name you wish. Click the “Next” button, and set the target framework. I have set my target framework to ".Net 5.0", the latest and most stable version. Click the “Create” button, and a new project will be created for you.

(adsbygoogle = window.adsbygoogle || []).push({});

Step 2: Install the NuGet Package in Visual Studio

Our next step is to install the IronXL NuGet Package for our project. Click on “Tools” on the menu bar. A new drop-down menu will appear. Click on "NuGet Package Manager" and then "Manage Nuget Packages for Solution", as shown below.

Click on “Browse” and search for IronXL in the search bar.

Click on “IronXL.Excel” and press the “Installed” button. This will install IronXL in your project. Now you are free to use any of its functions in your project.
Now that the IronXL Library has been installed, let's go to the next step.

Step 3: Create an Excel Workbook

Firstly, add the namespace of IronXL into your project.

using IronXL;

It could not be easier to create a new Excel Workbook using IronXL! We need just one line of code. Yes, really!

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);

Both the XLS (older Excel file versions) and XLSX (current and newer file versions) file formats can be created with IronXL.

Step 4: Set a Default Worksheet

Let's create an Excel spreadsheet. I have just created one. You can create as many Excel spreadsheets as you need.

var sheet = workbook.CreateWorkSheet("Result Sheet");

"Sheet" in the above code snippet represents the worksheet, and you can use it to set cell values and almost everything else Excel can do.
If you are confused about the difference between a Workbook and a Worksheet, let me explain: a Workbook contains Worksheets. This means that you can add as many Worksheets as you like into one Workbook. I will explain how to do this in a later article. A Worksheet contains rows and columns. The intersection of a row and a column is called a cell, and these cells are what we manipulate whilst working with Excel.

Step 5: Save the Workbook

To save the xlsx format Workbook, use the following code:

workbook.SaveAs("Budget.xlsx");

Run the program to see the output.

Output File

This program will create a new workbook. You can find it in your project bin folder. You can also specify the path with the “Save As” function. Open your Excel document with Microsoft Excel. Below is the Excel worksheet we have just created.

Step 6: Setting Cell Values

Now that our workbook is created and saved, let's add some data to the cell.

Setting Cell Values Manually:

To set cell values manually, you simply indicate which cell you are working with and set its value, as in the following example:

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
            var sheet = workbook.CreateWorkSheet("Result Sheet");
// Set Cell Values Manually
            sheet["A1"].Value = "Object Oriented Programming";
            sheet["B1"].Value = "Data Structure";
            sheet["C1"].Value = "Database Management System";
            sheet["D1"].Value = "Agile Development";
            sheet["E1"].Value = "Software Design and Architecture";
            sheet["F1"].Value = "Software Requirement Engineering";
            sheet["G1"].Value = "Computer Programming";
            sheet["H1"].Value = "Software Project Management";
            sheet["I1"].Value = "Software Construction";
            sheet["J1"].Value = "Software Quality Engineering";
            sheet["K1"].Value = "Software ReEngineering";
            sheet["L1"].Value = "Advance Database Management System";
// Save Workbook
            workbook.SaveAs("Result Sheet.xlsx");

Here, I have populated Columns A to L, and the first row of each to the names of various courses related to software engineering.
Run the program to see the output:

Output File

Setting Cell Values Dynamically

To set values dynamically we employ instructions that are very similar to those under the previous heading. The advantage here is that you do not have to hard-code the cell location. In the next code example, you will create a new random object to create random numbers, and then make use of a loop to iterate through the range of cells you’d like to populate with values.

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
            var sheet = workbook.CreateWorkSheet("Result Sheet");
            /**
            Set Cell Value Dynamically
            **/
            Random r = new Random();
            for (int i = 2; i <= 11; i++)
            {
                sheet["A" + i].Value = r.Next(1, 100);
                sheet["B" + i].Value = r.Next(1, 100);
                sheet["C" + i].Value = r.Next(1, 100);
                sheet["D" + i].Value = r.Next(1, 100);
                sheet["E" + i].Value = r.Next(1, 100);
                sheet["F" + i].Value = r.Next(1, 100);
                sheet["G" + i].Value = r.Next(1, 100);
                sheet["H" + i].Value = r.Next(1, 100);
                sheet["I" + i].Value = r.Next(1, 100);
                sheet["J" + i].Value = r.Next(1, 100);
                sheet["K" + i].Value = r.Next(1, 100);
                sheet["L" + i].Value = r.Next(1, 100);
            }
            // Save Workbook
            workbook.SaveAs("Result Sheet.xlsx");
        }

Every cell from A2 to L11 contains a unique value that was randomly generated.
Let's run the program. It will create an excel file for us. Open this with Microsoft Excel to see the output.

Output

Talking about dynamic values, how about learning how to dynamically add data into cells directly from a database? The next code snippet quickly shows how this is done, assuming you have set up your database connections correctly.

Adding Data Directly from a Database

I will get the data from the table and assign those values to the cells of my spreadsheet. Here is a snapshot of my “Marks” table:

I will get the data from that tables and assign those values to the cells of my spreadsheet. Here is the snapshot of my Marks Table.

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
            var sheet = workbook.CreateWorkSheet("Result Sheet");
            //Create database objects to populate data from database
            string contring;
            string sql;
            DataSet ds = new DataSet("ResultSet");
            SqlConnection con;
            SqlDataAdapter da;
            //Set Database Connection string
            contring = @"data source = DESKTOP-FEP5MVS\SQLEXPRESS; Initial Catalog = Result; Integrated Security = True; ";
            //SQL Query to obtain data
            sql = "SELECT [Data Structure],[Computer Programming] , [Agile Development] , [Software ReEngineering] , [Software Construction]  FROM Marks";
            //Open Connection & Fill DataSet
            con = new SqlConnection(contring);
            da = new SqlDataAdapter(sql, con);
            con.Open();
            da.Fill(ds);
            //Loop through Column
            foreach (DataTable table in ds.Tables)
            {
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    sheet["A1"].Value = table.Columns[j].ToString();
                    sheet["B1"].Value = table.Columns[j].ToString();
                    sheet["C1"].Value = table.Columns[j].ToString();
                    sheet["D1"].Value = table.Columns[j].ToString();
                    sheet["E1"].Value = table.Columns[j].ToString();
                }
            }

            //Loop through contents of dataset
            foreach (DataTable table in ds.Tables)
            {
                for (int j = 0; j < table.Rows.Count; j++)
                {
                    sheet["A" + (j + 2)].Value = table.Rows[j]["Data Structure"].ToString();
                    sheet["B" + (j + 2)].Value = table.Rows[j]["Computer Programming"].ToString();
                    sheet["C" + (j + 2)].Value = table.Rows[j]["Agile Development"].ToString();
                    sheet["D" + (j + 2)].Value = table.Rows[j]["Software ReEngineering"].ToString();
                    sheet["E" + (j + 2)].Value = table.Rows[j]["Software Construction"].ToString();
                }
            }
            // Save Workbook
            workbook.SaveAs("Result Sheet.xlsx");
        }

In the above code, I first set the connection string of my database, and then I read the data from the table. Next, I assigned the column name to the Excel spreadsheet in the first loop, and values of the table in the second loop.
You simply have to set the value property of the particular cell to the field name, so that it can be entered into the cell.

Output File

Let's run the program. It will create an Excel file for us. Open this with Microsoft Excel to see the output.

Summary

IronXL allow us to generate new Excel files in C#, create new worksheets inside them, set font sizes, use formulas and much more. There are too many functions and details to describe here. For more detailed information, please click here.

Iron software provides other libraries such as IronPdf for creating Pdf documents, Iron Barcode for generating, reading and manipulating barcode, and so forth. If you purchase the complete Iron suite, you will be eligible to receive all 5 products for the price of just two. For more details, please click here.

I hope this article was helpful and easy to read. Feel free to comment with your queries and feedback.
You can download a file project from this link.