DEV Community

IronSoftware
IronSoftware

Posted on • Originally published at ironsoftware.com

C# Create Excel File Tutorial

This tutorial will guide you step-by-step on how to create an Excel Workbook file on any platform that supports .NET Framework 4.5 or .NET Core. Creating Excel files in C# can be simple, even without dependency on the legacy Microsoft.Office.Interop.Excel library. Use IronXL to set worksheet properties like freeze panes and protection, set print properties, and more.

Overview

How to Create an Excel File in C

  1. Download the C# Library to create Excel and CSV files
  2. Create an ASP.NET Project
  3. Generate an Excel Workbook with the C# Library
  4. Set cell values in an Excel worksheet
  5. Customize formats, add formulas, and passwords
  6. Save your Excel workbook using the C# Library

IronXL Creates C# Excel Files in .NET

IronXL is an intuitive C# & VB Excel API that allows you to read, edit & create Excel spreadsheet files in .NET with lightning fast performance. There is no need to install MS Office or even the Excel InterOp.

IronXL fully supports .NET Core, .NET Framework, Xamarin, Mobile, Linux, MacOS and Azure.

IronXL Features:

  • Human support directly from our .NET development team
  • Rapid installation with Microsoft Visual Studio
  • FREE for development. Licenses from $499.

**Create and Save an Excel File: Quick Code

https://www.nuget.org/packages/IronXL.Excel/

As an alternative, the IronXL.Dll can be downloaded and added to your project.

C#:

/**
Create & Save Excel File 
anchor-create-and-save-an-excel-file
**/
using IronXL;
//default file format is XLSX, we can override it using CreatingOptions
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
var sheet = workbook.CreateWorkSheet("example_sheet");
sheet["A1"].Value = "Example";
//set value to multiple cells
sheet["A2:A4"].Value = 5;
sheet["A5"].Style.SetBackgroundColor("#f0f0f0");
//set style to multiple cells
sheet["A5:A6"].Style.Font.Bold = true;
//set formula
sheet["A6"].Value = "=SUM(A2:A4)";
if (sheet["A6"].IntValue == sheet["A2:A4"].IntValue)
{
    Console.WriteLine("Basic test passed");
}
workbook.SaveAs("example_workbook.xlsx");
Enter fullscreen mode Exit fullscreen mode

VB:

'''
'''Create & Save Excel File 
'''anchor-create-and-save-an-excel-file
'''*
Imports IronXL
'default file format is XLSX, we can override it using CreatingOptions
Private workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Private sheet = workbook.CreateWorkSheet("example_sheet")
Private sheet("A1").Value = "Example"
'set value to multiple cells
Private sheet("A2:A4").Value = 5
sheet("A5").Style.SetBackgroundColor("#f0f0f0")
'set style to multiple cells
sheet("A5:A6").Style.Font.Bold = True
'set formula
sheet("A6").Value = "=SUM(A2:A4)"
If sheet("A6").IntValue = sheet("A2:A4").IntValue Then
    Console.WriteLine("Basic test passed")
End If
workbook.SaveAs("example_workbook.xlsx")
Enter fullscreen mode Exit fullscreen mode

Step 1

1. Download the FREE IronXL C# Library

Install by Using NuGet

There are three different ways to install the IronXL NuGet package:

  1. Visual Studio
  2. Developer Command Prompt
  3. Download the NuGet Package directly

Visual Studio

Visual Studio provides the NuGet Package Manager for you to use to install NuGet packages in your projects. You can access it via the Project Menu, or by right-clicking your project in the Solution Explorer. Both these options are shown below in Figures 3 and 4.

Image 1

Figure 3 – Project menu

Image 2

Figure 4 – Right-click Solution Explorer

After you have clicked Manage NuGet Packages from either option, Browse for the IronXL.Excel package and install it as shown in Figure 5.

Image 3

Figure 5 – Install IronXL.Excel NuGet Package

Developer Command Prompt

Open the Developer Command Prompt and follow these steps to install the IronXL.Excel NuGet package:

  1. Search for your Developer Command Prompt – it is usually under your Visual Studio folder
  2. Type in the following command:
  3. PM > Install-Package IronXL.Excel
  4. Press Enter
  5. The package will be installed.
  6. Reload your Visual Studio project

Download the NuGet Package directly

In order to download the NuGet package, make use of the next few steps:

  1. Navigate to the following URL: https://www.nuget.org/packages/ironxl.excel/
  2. Click on Download Package
  3. After the package has downloaded, double click it
  4. Reload your Visual Studio project

Install IronXL by Direct Download of the Library

The second way to install IronXL is by downloading it directly from the following URL: https://ironsoftware.com/csharp/excel/

Image 4

Figure 6 – Download IronXL library

Reference the Library in your project by using the next steps:

  1. Right-click the Solution in the Solution Explorer
  2. Select References
  3. Browse for the IronXL.dll library
  4. Click OK

Let's Go!

Now that you’re set-up, we can start playing with the awesome features in the IronXL library!

How to Tutorials

2. Create an ASP.NET

Make use of the following steps to create an ASP.NET Website

  1. Open Visual Studio
  2. Click File > New Project
  3. Select Web under Visual C# in the Project type listbox
  4. Select ASP.NET Web Application, as shown next

Image 5

Figure 1 - New Project

ironsoftware.com/csharp/excel/

  1. Click OK
  2. On the next screen, select Web Forms as shown in Figure 2 underneath

Image 6

Figure 2 – Web Forms

  1. Click OK

Now we have something to work with. Install IronXL to start customizing your file.

3. Create an Excel Workbook

It cannot be simpler to create a new Excel Workbook using IronXL! It is one line of code! Yes, really:

C#:

WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
Enter fullscreen mode Exit fullscreen mode

VB:

Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Enter fullscreen mode Exit fullscreen mode

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

3.1. Set a Default Worksheet

And, it’s even simpler to create a default Worksheet

C#:

var sheet = workbook.CreateWorkSheet("2020 Budget");
Enter fullscreen mode Exit fullscreen mode

VB:

Dim sheet = workbook.CreateWorkSheet("2020 Budget")
Enter fullscreen mode Exit fullscreen mode

"Sheet" in the above code snippet represents the worksheet and you can use it to set cell values and almost everything Excel can do.

In case 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. In a later article, I will explain how to do this. A Worksheet contains Rows and Columns. The intersection of a Row and a Column is called a Cell, and this is what you will manipulate whilst working with Excel.

4. Set Cell Values

4.1. Set Cells Values Manually

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

C#:

/**
Set Cell Value Manually
anchor-set-cell-values
**/
sheet["A1"].Value = "January";
sheet["B1"].Value = "February";
sheet["C1"].Value = "March";
sheet["D1"].Value = "April";
sheet["E1"].Value = "May";
sheet["F1"].Value = "June";
sheet["G1"].Value = "July";
sheet["H1"].Value = "August";
sheet["I1"].Value = "September";
sheet["J1"].Value = "October";
sheet["K1"].Value = "November";
sheet["L1"].Value = "December";
Enter fullscreen mode Exit fullscreen mode

VB:

'''
'''Set Cell Value Manually
'''anchor-set-cell-values
'''*
sheet("A1").Value = "January"
sheet("B1").Value = "February"
sheet("C1").Value = "March"
sheet("D1").Value = "April"
sheet("E1").Value = "May"
sheet("F1").Value = "June"
sheet("G1").Value = "July"
sheet("H1").Value = "August"
sheet("I1").Value = "September"
sheet("J1").Value = "October"
sheet("K1").Value = "November"
sheet("L1").Value = "December"
Enter fullscreen mode Exit fullscreen mode

Here, I have populated Columns A to L, and the first row of each to a name of a different month.

4.2. Set Cell Values Dynamically

Setting values dynamically is almost similar to the previous code segment. The nice thing about this 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 for loop to iterate through the range of cells you’d like to populate with values.

C#:

/**
Set Cell Value Dynamically
anchor-set-cell-values-dynamically
**/
Random r = new Random();
for (int i = 2; i <= 11; i++)
{
  sheet["A" + i].Value = r.Next(1, 1000);
  sheet["B" + i].Value = r.Next(1000, 2000);
  sheet["C" + i].Value = r.Next(2000, 3000);
  sheet["D" + i].Value = r.Next(3000, 4000);
  sheet["E" + i].Value = r.Next(4000, 5000);
  sheet["F" + i].Value = r.Next(5000, 6000);
  sheet["G" + i].Value = r.Next(6000, 7000);
  sheet["H" + i].Value = r.Next(7000, 8000);
  sheet["I" + i].Value = r.Next(8000, 9000);
  sheet["J" + i].Value = r.Next(9000, 10000);
  sheet["K" + i].Value = r.Next(10000, 11000);
  sheet["L" + i].Value = r.Next(11000, 12000);
}
Enter fullscreen mode Exit fullscreen mode

VB:

'''
'''Set Cell Value Dynamically
'''anchor-set-cell-values-dynamically
'''*
Dim r As New Random()
For i As Integer = 2 To 11
  sheet("A" & i).Value = r.Next(1, 1000)
  sheet("B" & i).Value = r.Next(1000, 2000)
  sheet("C" & i).Value = r.Next(2000, 3000)
  sheet("D" & i).Value = r.Next(3000, 4000)
  sheet("E" & i).Value = r.Next(4000, 5000)
  sheet("F" & i).Value = r.Next(5000, 6000)
  sheet("G" & i).Value = r.Next(6000, 7000)
  sheet("H" & i).Value = r.Next(7000, 8000)
  sheet("I" & i).Value = r.Next(8000, 9000)
  sheet("J" & i).Value = r.Next(9000, 10000)
  sheet("K" & i).Value = r.Next(10000, 11000)
  sheet("L" & i).Value = r.Next(11000, 12000)
Next i
Enter fullscreen mode Exit fullscreen mode

Every cell from A2 to L11 contains a unique value that was randomly generated.

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.

4.3. Add Directly from a Database

C#:

/**
Add Cells from Database
anchor-add-directly-from-a-database
**/
//Create database objects to populate data from database
string contring;
string sql;
DataSet ds = new DataSet("DataSetName");
SqlConnection con;
SqlDataAdapter da;
//Set Database Connection string
contring = @"Data Source=Server_Name;Initial Catalog=Database_Name;User ID=User_ID;Password=Password";
//SQL Query to obtain data
sql = "SELECT Field_Names FROM Table_Name";
//Open Connection & Fill DataSet
con = new SqlConnection(contring);
da = new SqlDataAdapter(sql, con);
con.Open();
da.Fill(ds);
//Loop through contents of dataset
foreach (DataTable table in ds.Tables)
{
     int Count = table.Rows.Count - 1;
     for (int j = 12; j <= 21; j++)
     {
       sheet["A" + j].Value = table.Rows[Count]["Field_Name_1"].ToString();
       sheet["B" + j].Value = table.Rows[Count]["Field_Name_2"].ToString();
       sheet["C" + j].Value = table.Rows[Count]["Field_Name_3"].ToString();
       sheet["D" + j].Value = table.Rows[Count]["Field_Name_4"].ToString();
       sheet["E" + j].Value = table.Rows[Count]["Field_Name_5"].ToString();
       sheet["F" + j].Value = table.Rows[Count]["Field_Name_6"].ToString();
       sheet["G" + j].Value = table.Rows[Count]["Field_Name_7"].ToString();
       sheet["H" + j].Value = table.Rows[Count]["Field_Name_8"].ToString();
       sheet["I" + j].Value = table.Rows[Count]["Field_Name_9"].ToString();
       sheet["J" + j].Value = table.Rows[Count]["Field_Name_10"].ToString();
       sheet["K" + j].Value = table.Rows[Count]["Field_Name_11"].ToString();
       sheet["L" + j].Value = table.Rows[Count]["Field_Name_12"].ToString();
     }
     Count++;
}
Enter fullscreen mode Exit fullscreen mode

VB:

'''
'''Add Cells from Database
'''anchor-add-directly-from-a-database
'''*
'Create database objects to populate data from database
Dim contring As String
Dim sql As String
Dim ds As New DataSet("DataSetName")
Dim con As SqlConnection
Dim da As SqlDataAdapter
'Set Database Connection string
contring = "Data Source=Server_Name;Initial Catalog=Database_Name;User ID=User_ID;Password=Password"
'SQL Query to obtain data
sql = "SELECT Field_Names FROM Table_Name"
'Open Connection & Fill DataSet
con = New SqlConnection(contring)
da = New SqlDataAdapter(sql, con)
con.Open()
da.Fill(ds)
'Loop through contents of dataset
For Each table As DataTable In ds.Tables
     Dim Count As Integer = table.Rows.Count - 1
     For j As Integer = 12 To 21
       sheet("A" & j).Value = table.Rows(Count)("Field_Name_1").ToString()
       sheet("B" & j).Value = table.Rows(Count)("Field_Name_2").ToString()
       sheet("C" & j).Value = table.Rows(Count)("Field_Name_3").ToString()
       sheet("D" & j).Value = table.Rows(Count)("Field_Name_4").ToString()
       sheet("E" & j).Value = table.Rows(Count)("Field_Name_5").ToString()
       sheet("F" & j).Value = table.Rows(Count)("Field_Name_6").ToString()
       sheet("G" & j).Value = table.Rows(Count)("Field_Name_7").ToString()
       sheet("H" & j).Value = table.Rows(Count)("Field_Name_8").ToString()
       sheet("I" & j).Value = table.Rows(Count)("Field_Name_9").ToString()
       sheet("J" & j).Value = table.Rows(Count)("Field_Name_10").ToString()
       sheet("K" & j).Value = table.Rows(Count)("Field_Name_11").ToString()
       sheet("L" & j).Value = table.Rows(Count)("Field_Name_12").ToString()
     Next j
     Count += 1
Next table
Enter fullscreen mode Exit fullscreen mode

You simply have to set the Value property of the particular cell to the Field name to be entered into the cell.

5. Apply Formatting

5.1. Set Background Colors of Cells

To set the background color of a cell or a range of cells, you simply need a line of code that looks like the following:

C#:

/**
Set Cell Background Color
anchor-set-background-colors-of-cells
**/
sheet["A1:L1"].Style.SetBackgroundColor("#d3d3d3");
Enter fullscreen mode Exit fullscreen mode

VB:

'''
'''Set Cell Background Color
'''anchor-set-background-colors-of-cells
'''*
sheet("A1:L1").Style.SetBackgroundColor("#d3d3d3")
Enter fullscreen mode Exit fullscreen mode

This sets the background color of the range of cells to gray. The color is in RGB (Red, Green, Blue) format where the first two characters represent Red, the next two, Green and the last two, Blue. The values range from 0 to 9, then A to F (Hexidecimal).

5.2. Create Borders

Creating borders with IronXL is very simple, as shown next:

C#:

/**
Create Borders
anchor-create-borders
**/
sheet["A1:L1"].Style.TopBorder.SetColor("#000000");
sheet["A1:L1"].Style.BottomBorder.SetColor("#000000");
sheet["L2:L11"].Style.RightBorder.SetColor("#000000");
sheet["L2:L11"].Style.RightBorder.Type = IronXL.Styles.BorderType.Medium;
sheet["A11:L11"].Style.BottomBorder.SetColor("#000000");
sheet["A11:L11"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Medium;
Enter fullscreen mode Exit fullscreen mode

VB:

'''
'''Create Borders
'''anchor-create-borders
'''*
sheet("A1:L1").Style.TopBorder.SetColor("#000000")
sheet("A1:L1").Style.BottomBorder.SetColor("#000000")
sheet("L2:L11").Style.RightBorder.SetColor("#000000")
sheet("L2:L11").Style.RightBorder.Type = IronXL.Styles.BorderType.Medium
sheet("A11:L11").Style.BottomBorder.SetColor("#000000")
sheet("A11:L11").Style.BottomBorder.Type = IronXL.Styles.BorderType.Medium
Enter fullscreen mode Exit fullscreen mode

In the above code I have set black Top and Bottom borders to Cells A1 to L1, then i have set the Right border to cells L2 to L11 and the style of the border is set to Medium. Lastly, I have set the Bottom border for cells A11 to L11

  1. Use Formulas in Cells

I keep saying that IronXL makes everything so easy, but it really does, and I can’t highlight it enough! The following code allows you to use formulas:

C#:

/**
Use Formulas in Cells
anchor-use-formulas-in-cells
**/
decimal sum = sheet["A2:A11"].Sum();
decimal avg = sheet["B2:B11"].Avg();
decimal max = sheet["C2:C11"].Max();
decimal min = sheet["D2:D11"].Min();
sheet["A12"].Value = sum;
sheet["B12"].Value = avg;
sheet["C12"].Value = max;
sheet["D12"].Value = min;
Enter fullscreen mode Exit fullscreen mode

VB:

'''
'''Use Formulas in Cells
'''anchor-use-formulas-in-cells
'''*
Dim sum As Decimal = sheet("A2:A11").Sum()
Dim avg As Decimal = sheet("B2:B11").Avg()
Dim max As Decimal = sheet("C2:C11").Max()
Dim min As Decimal = sheet("D2:D11").Min()
sheet("A12").Value = sum
sheet("B12").Value = avg
sheet("C12").Value = max
sheet("D12").Value = min
Enter fullscreen mode Exit fullscreen mode

What’s nice about this is the fact that you can set the data type of the cell thus the result of the formula. The above code shows how to use the SUM (sums values), AVG (averages values), MAX (gets the highest value) and MIN (gets the lowest value) formulas.

  1. Set Worksheet and Print Properties

7.1. Set Worksheet Properties

Worksheet properties include freezing rows and columns and protecting the worksheet with a password. This is shown next:

C#:

/**
Set Worksheet Properties
anchor-set-worksheet-and-print-properties
**/
sheet.ProtectSheet("Password");
sheet.CreateFreezePane(0, 1);
Enter fullscreen mode Exit fullscreen mode

VB:

'''
'''Set Worksheet Properties
'''anchor-set-worksheet-and-print-properties
'''*
sheet.ProtectSheet("Password")
sheet.CreateFreezePane(0, 1)
Enter fullscreen mode Exit fullscreen mode

The first row is frozen and will not scroll along with the rest of the Worksheet. The worksheet is also protected from any edits with a password. Figures 7 and 8 shows this in action.

Image 7

Figure 7 – Freeze Panes

Image description

Figure 8 – Protected Worksheet

7.2. Set Page and Print Properties

You can set Page properties such as the Orientation of the page, the size of the page as well as the PrintArea to name a few.

C#:

/**
Page & Print Properties
anchor-set-page-and-print-properties
**/
sheet.SetPrintArea("A1:L12");
sheet.PrintSetup.PrintOrientation = IronXL.Printing.PrintOrientation.Landscape;
sheet.PrintSetup.PaperSize = IronXL.Printing.PaperSize.A4;
Enter fullscreen mode Exit fullscreen mode

VB:

'''
'''Page & Print Properties
'''anchor-set-page-and-print-properties
'''*
sheet.SetPrintArea("A1:L12")
sheet.PrintSetup.PrintOrientation = IronXL.Printing.PrintOrientation.Landscape
sheet.PrintSetup.PaperSize = IronXL.Printing.PaperSize.A4
Enter fullscreen mode Exit fullscreen mode

The Print area gets set to A1 to L12. The Orientation gets set to Landscape and the paper size gets set to A4

Image 9

Figure 9 – Print Setup

8. Save Workbook

To save the workbook, use the following code:

C#:

/**
Save Workbook
anchor-save-workbook
**/
workbook.SaveAs("Budget.xlsx");
Enter fullscreen mode Exit fullscreen mode

VB:

'''
'''Save Workbook
'''anchor-save-workbook
'''*
workbook.SaveAs("Budget.xlsx")
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Read next

scofieldidehen profile image

Why No One Reads Your Rubbish Technical Content - (Hidden Secret)

Scofield Idehen -

rajatp profile image

Simplifying Database Optimization: A Beginner's Guide

Rajat Patel -

bybydev profile image

How to check if a variable is list in Python

byby -

momciloo profile image

Content Hub: Complete guide to a centralized source

Momcilo -