C# Open Excel File and Write to Excel Tutorial

by Elijah Williams

Follow step-by-step examples of how to create, open, and save Excel files with C#, and apply basic operations like getting sum, average, count, and more. IronXL.Excel is a stand alone .NET software library for reading a wide range of spreadsheet formats. It does not require Microsoft Excel to be installed, nor depend on Interop.


Overview

Use IronXL to Open and Write Excel Files

Open, write, save, and customize Excel files with the easy to use IronXL C# library.

Download a sample project from GitHub or use your own, and follow the tutorial.

  1. Install the IronXL Excel Library from NuGet or the DLL download
  2. Use the WorkBook.Load method to read any XLS, XLSX or CSV document.
  3. Get Cell values using intuitive syntax: sheet["A11"].DecimalValue

In this tutorial, we will walk you through:

  • Installing IronXL.Excel: how to install IronXL.Excel to an existing project.
  • Basic Operations: basic operation steps with Excel to Create or Open workbook, select sheet, select cell, and save the workbook
  • Advanced Sheet Operations: how to utilize different manipulation capabilities like adding headers or footers, mathematical operations files, and other features.

Open an Excel File : Quick Code

using IronXL;
using System;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

Range range = sheet["A2:A8"];

decimal total = 0;

//iterate over range of cells
foreach (var cell in range)
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.RowIndex, cell.Value);

    if (cell.IsNumeric)
    {
        //Get decimal value to avoid floating numbers precision issue
        total += cell.DecimalValue;
    }
}

//check formula evaluation
if (sheet["A11"].DecimalValue == total)
{
    Console.WriteLine("Basic Test Passed");
}
using IronXL;
using System;

WorkBook workbook = WorkBook.Load("test.xlsx");
WorkSheet sheet = workbook.DefaultWorkSheet;

Range range = sheet["A2:A8"];

decimal total = 0;

//iterate over range of cells
foreach (var cell in range)
{
    Console.WriteLine("Cell {0} has value '{1}'", cell.RowIndex, cell.Value);

    if (cell.IsNumeric)
    {
        //Get decimal value to avoid floating numbers precision issue
        total += cell.DecimalValue;
    }
}

//check formula evaluation
if (sheet["A11"].DecimalValue == total)
{
    Console.WriteLine("Basic Test Passed");
}
Imports IronXL
Imports System

Private workbook As WorkBook = WorkBook.Load("test.xlsx")
Private sheet As WorkSheet = workbook.DefaultWorkSheet

Private range As Range = sheet("A2:A8")

Private total As Decimal = 0

'iterate over range of cells
For Each cell In range
	Console.WriteLine("Cell {0} has value '{1}'", cell.RowIndex, cell.Value)

	If cell.IsNumeric Then
		'Get decimal value to avoid floating numbers precision issue
		total += cell.DecimalValue
	End If
Next cell

'check formula evaluation
If sheet("A11").DecimalValue = total Then
	Console.WriteLine("Basic Test Passed")
End If
VB   C#

Write and Save Changes to the Excel File : Quick Code

sheet["B1"].Value = 11.54;

//Save Changes
workbook.SaveAs("test.xlsx");
sheet["B1"].Value = 11.54;

//Save Changes
workbook.SaveAs("test.xlsx");
sheet("B1").Value = 11.54

'Save Changes
workbook.SaveAs("test.xlsx")
VB   C#


Step 1

1. Install the IronXL C# Library FREE

C# PDF DLL

Download DLL

Download DLL

Manually install into your project

or
C# Nuget Library for PDF

Install with NuGet

Install-Package IronXL.Excel

IronXL.Excel provides a flexible and powerful library for opening, reading, editing and saving Excel files in .NET. It can be installed and used on all of the .NET project types, like Windows applications, ASP.NET MVC and .NET Core Application.

Install the Excel Library to your Visual Studio Project with NuGet

The first step will be to install IronXL.Excel. To add IronXL.Excel library to the project, we have two ways : NuGet Package Manager or NuGet Package Manager Console.

To add IronXL.Excel library to our project using NuGet, we can do it using a visualized interface, NuGet Package Manager:

  1. Using mouse -> right click on project name -> Select manage NuGet Package

  2. From browse tab -> search for IronXL.Excel -> Install

  3. And we are done

Install Using NuGet Package Manager Console

  1. From tools -> NuGet Package Manager -> Package Manager Console

  2. Run command -> Install-Package IronXL.Excel -Version 2019.5.2

Manually Install with the DLL

You may also choose to manually install the DLL to your project or to your global assembly cache.

 PM > Install-Package  IronXL.Excel


How To Tutorials

2. Basic Operations: Create, Open, Save

2.1. Sample Project: HelloWorld Console Application

Create a HelloWorld Project

2.1.1. Open Visual Studio

2.1.2. Choose Create New Project

2.1.3. Choose Console App (.NET framework)

2.1.4. Give our sample the name “HelloWorld” and click create

2.1.5. Now we have console application created

2.1.6. Add IronXL.Excel => click install

2.1.7. Add our first few lines that reads 1st cell in 1st sheet in the Excel file, and print

static void Main(string[] args)
{
    var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx");
    var sheet = workbook.WorkSheets.First();
    var cell = sheet["A1"].StringValue;
    Console.WriteLine(cell);
}
static void Main(string[] args)
{
    var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx");
    var sheet = workbook.WorkSheets.First();
    var cell = sheet["A1"].StringValue;
    Console.WriteLine(cell);
}
Shared Sub Main(ByVal args() As String)
	Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx")
	Dim sheet = workbook.WorkSheets.First()
	Dim cell = sheet("A1").StringValue
	Console.WriteLine(cell)
End Sub
VB   C#

2.2. Create a New Excel File

Create a new Excel file using IronXL

/**
Create Excel File
anchor-create-a-new-excel-file
**/
static void Main(string[] args)
{
    var newXLFile = WorkBook.Create(ExcelFileFormat.XLSX);
    newXLFile.Metadata.Title = "IronXL New File";
    var newWorkSheet = newXLFile.CreateWorkSheet("1stWorkSheet");
    newWorkSheet["A1"].Value = "Hello World";
    newWorkSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
    newWorkSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;
}
/**
Create Excel File
anchor-create-a-new-excel-file
**/
static void Main(string[] args)
{
    var newXLFile = WorkBook.Create(ExcelFileFormat.XLSX);
    newXLFile.Metadata.Title = "IronXL New File";
    var newWorkSheet = newXLFile.CreateWorkSheet("1stWorkSheet");
    newWorkSheet["A1"].Value = "Hello World";
    newWorkSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
    newWorkSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;
}
'''
'''Create Excel File
'''anchor-create-a-new-excel-file
'''*
Shared Sub Main(ByVal args() As String)
	Dim newXLFile = WorkBook.Create(ExcelFileFormat.XLSX)
	newXLFile.Metadata.Title = "IronXL New File"
	Dim newWorkSheet = newXLFile.CreateWorkSheet("1stWorkSheet")
	newWorkSheet("A1").Value = "Hello World"
	newWorkSheet("A2").Style.BottomBorder.SetColor("#ff6600")
	newWorkSheet("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed
End Sub
VB   C#

2.3. Open (CSV, XML, JSON List) as Workbook

2.3.1. Open CSV file

2.3.2 Create a new text file and add to it a list of names and ages (see example) then save it as CSVList.csv

Your code snippet should look like this

/**
Open CSV as Workbook
anchor-open-csv-xml-json-list-as-workbook
**/
static void Main(string[] args)
{
    var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\CSVList.csv");
    var sheet = workbook.WorkSheets.First();
    var cell = sheet["A1"].StringValue; 
    Console.WriteLine(cell);
}
/**
Open CSV as Workbook
anchor-open-csv-xml-json-list-as-workbook
**/
static void Main(string[] args)
{
    var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\CSVList.csv");
    var sheet = workbook.WorkSheets.First();
    var cell = sheet["A1"].StringValue; 
    Console.WriteLine(cell);
}
'''
'''Open CSV as Workbook
'''anchor-open-csv-xml-json-list-as-workbook
'''*
Shared Sub Main(ByVal args() As String)
	Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\CSVList.csv")
	Dim sheet = workbook.WorkSheets.First()
	Dim cell = sheet("A1").StringValue
	Console.WriteLine(cell)
End Sub
VB   C#

2.3.3. Open XML File Create an XML file that contains a countries list: the root element “countries”, with children elements “country”, and each country has properties that define the country like code, continent, etc.

<?xml version="1.0" encoding="utf-8"?>
<countries xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
        <country code="ae" handle="united-arab-emirates" continent="asia" iso="784">United Arab Emirates</country>
        <country code="gb" handle="united-kingdom" continent="europe" alt="England Scotland Wales GB UK Great Britain Britain Northern" boost="3" iso="826">United Kingdom</country>
        <country code="us" handle="united-states" continent="north america" alt="US America USA" boost="2" iso="840">United States</country>
        <country code="um" handle="united-states-minor-outlying-islands" continent="north america" iso="581">United States Minor Outlying Islands</country>
</countries>
HTML

2.3.4. Copy the following code snippet to open XML as a workbook

/**
Open XML as Workbook
anchor-open-csv-xml-json-list-as-workbook
**/
static void Main(string[] args)
{
    var xmldataset = new DataSet();
    xmldataset.ReadXml($@"{Directory.GetCurrentDirectory()}\Files\CountryList.xml");
    var workbook = IronXL.WorkBook.Load(xmldataset);
    var sheet = workbook.WorkSheets.First();
}
/**
Open XML as Workbook
anchor-open-csv-xml-json-list-as-workbook
**/
static void Main(string[] args)
{
    var xmldataset = new DataSet();
    xmldataset.ReadXml($@"{Directory.GetCurrentDirectory()}\Files\CountryList.xml");
    var workbook = IronXL.WorkBook.Load(xmldataset);
    var sheet = workbook.WorkSheets.First();
}
'''
'''Open XML as Workbook
'''anchor-open-csv-xml-json-list-as-workbook
'''*
Shared Sub Main(ByVal args() As String)
	Dim xmldataset = New DataSet()
	xmldataset.ReadXml($"{Directory.GetCurrentDirectory()}\Files\CountryList.xml")
	Dim workbook = IronXL.WorkBook.Load(xmldataset)
	Dim sheet = workbook.WorkSheets.First()
End Sub
VB   C#

2.3.5. Open JSON List as workbook Create JSON country list

/**
Open JSON as Workbook
anchor-open-csv-xml-json-list-as-workbook
**/
[
    {
        "name": "United Arab Emirates",
        "code": "AE"
    },
    {
        "name": "United Kingdom",
        "code": "GB"
    },
    {
        "name": "United States",
        "code": "US"
    },
    {
        "name": "United States Minor Outlying Islands",
        "code": "UM"
    }
]
/**
Open JSON as Workbook
anchor-open-csv-xml-json-list-as-workbook
**/
[
    {
        "name": "United Arab Emirates",
        "code": "AE"
    },
    {
        "name": "United Kingdom",
        "code": "GB"
    },
    {
        "name": "United States",
        "code": "US"
    },
    {
        "name": "United States Minor Outlying Islands",
        "code": "UM"
    }
]
'''
'''Open JSON as Workbook
'''anchor-open-csv-xml-json-list-as-workbook
'''*
'INSTANT VB TODO TASK: The following line uses invalid syntax:
'[{ "name": "United Arab Emirates", "code": "AE" }, { "name": "United Kingdom", "code": "GB" }, { "name": "United States", "code": "US" }, { "name": "United States Minor Outlying Islands", "code": "UM" }]
VB   C#

2.3.6. Create a country model that will map to JSON

Here is the class code snippet

public class CountryModel
{
    public string name { get; set; }
    public string code { get; set; }
}
public class CountryModel
{
    public string name { get; set; }
    public string code { get; set; }
}
Public Class CountryModel
	Public Property name() As String
	Public Property code() As String
End Class
VB   C#

2.3.8. Add Newtonsoft library to convert JSON to the list of country models

2.3.9 To convert the list to dataset, we have to create a new extension for the list. Add extension class with the name “ListConvertExtension”

Then add this code snippet

/**
Convert to Data Set
anchor-open-csv-xml-json-list-as-workbook
**/
public static class ListConvertExtension
{
    public static DataSet ToDataSet<T>(this IList<T> list)
    {
        Type elementType = typeof(T);
        DataSet ds = new DataSet();
        DataTable t = new DataTable();
        ds.Tables.Add(t);

        //add a column to table for each public property on T
        foreach (var propInfo in elementType.GetProperties())
        {
            Type ColType = Nullable.GetUnderlyingType(propInfo.PropertyType) ?? propInfo.PropertyType;

            t.Columns.Add(propInfo.Name, ColType);
        }

        //go through each property on T and add each value to the table
        foreach (T item in list)
        {
            DataRow row = t.NewRow();

            foreach (var propInfo in elementType.GetProperties())
            {
                row[propInfo.Name] = propInfo.GetValue(item, null) ?? DBNull.Value;
            }

            t.Rows.Add(row);
        }

        return ds;
    }
}
/**
Convert to Data Set
anchor-open-csv-xml-json-list-as-workbook
**/
public static class ListConvertExtension
{
    public static DataSet ToDataSet<T>(this IList<T> list)
    {
        Type elementType = typeof(T);
        DataSet ds = new DataSet();
        DataTable t = new DataTable();
        ds.Tables.Add(t);

        //add a column to table for each public property on T
        foreach (var propInfo in elementType.GetProperties())
        {
            Type ColType = Nullable.GetUnderlyingType(propInfo.PropertyType) ?? propInfo.PropertyType;

            t.Columns.Add(propInfo.Name, ColType);
        }

        //go through each property on T and add each value to the table
        foreach (T item in list)
        {
            DataRow row = t.NewRow();

            foreach (var propInfo in elementType.GetProperties())
            {
                row[propInfo.Name] = propInfo.GetValue(item, null) ?? DBNull.Value;
            }

            t.Rows.Add(row);
        }

        return ds;
    }
}
'''
'''Convert to Data Set
'''anchor-open-csv-xml-json-list-as-workbook
'''*
Public Module ListConvertExtension
	<System.Runtime.CompilerServices.Extension> _
	Public Function ToDataSet(Of T)(ByVal list As IList(Of T)) As DataSet
		Dim elementType As Type = GetType(T)
		Dim ds As New DataSet()
'INSTANT VB NOTE: The variable t was renamed since Visual Basic does not allow local variables with the same name as method-level generic type parameters:
		Dim t_Conflict As New DataTable()
		ds.Tables.Add(t_Conflict)

		'add a column to table for each public property on T
		For Each propInfo In elementType.GetProperties()
			Dim ColType As Type = If(Nullable.GetUnderlyingType(propInfo.PropertyType), propInfo.PropertyType)

			t_Conflict.Columns.Add(propInfo.Name, ColType)
		Next propInfo

		'go through each property on T and add each value to the table
		For Each item As T In list
			Dim row As DataRow = t_Conflict.NewRow()

			For Each propInfo In elementType.GetProperties()
				row(propInfo.Name) = If(propInfo.GetValue(item, Nothing), DBNull.Value)
			Next propInfo

			t_Conflict.Rows.Add(row)
		Next item

		Return ds
	End Function
End Module
VB   C#

And finally load this dataset as a workbook

static void Main(string[] args)
{
    var jsonFile = new StreamReader($@"{Directory.GetCurrentDirectory()}\Files\CountriesList.json");
    var countryList =  Newtonsoft.Json.JsonConvert.DeserializeObject<CountryModel[]>(jsonFile.ReadToEnd());
    var xmldataset = countryList.ToDataSet();
    var workbook = IronXL.WorkBook.Load(xmldataset);
    var sheet = workbook.WorkSheets.First();
}
static void Main(string[] args)
{
    var jsonFile = new StreamReader($@"{Directory.GetCurrentDirectory()}\Files\CountriesList.json");
    var countryList =  Newtonsoft.Json.JsonConvert.DeserializeObject<CountryModel[]>(jsonFile.ReadToEnd());
    var xmldataset = countryList.ToDataSet();
    var workbook = IronXL.WorkBook.Load(xmldataset);
    var sheet = workbook.WorkSheets.First();
}
Shared Sub Main(ByVal args() As String)
	Dim jsonFile = New StreamReader($"{Directory.GetCurrentDirectory()}\Files\CountriesList.json")
	Dim countryList = Newtonsoft.Json.JsonConvert.DeserializeObject(Of CountryModel())(jsonFile.ReadToEnd())
	Dim xmldataset = countryList.ToDataSet()
	Dim workbook = IronXL.WorkBook.Load(xmldataset)
	Dim sheet = workbook.WorkSheets.First()
End Sub
VB   C#

2.4. Save and Export

We can save or export the Excel file to multiple file formats like (“.xlsx”,”.csv”,”.html”) using one of the following commands.

2.4.1. Save to “.xlsx” To Save to “.xlsx” use saveAs function

/**
Save and Export
anchor-save-and-export
**/
static void Main(string[] args)
{
    var newXLFile = WorkBook.Create(ExcelFileFormat.XLSX);
    newXLFile.Metadata.Title = "IronXL New File";
    var newWorkSheet = newXLFile.CreateWorkSheet("1stWorkSheet");
    newWorkSheet["A1"].Value = "Hello World";
    newWorkSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
    newWorkSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;

    newXLFile.SaveAs($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx");
}
/**
Save and Export
anchor-save-and-export
**/
static void Main(string[] args)
{
    var newXLFile = WorkBook.Create(ExcelFileFormat.XLSX);
    newXLFile.Metadata.Title = "IronXL New File";
    var newWorkSheet = newXLFile.CreateWorkSheet("1stWorkSheet");
    newWorkSheet["A1"].Value = "Hello World";
    newWorkSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
    newWorkSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;

    newXLFile.SaveAs($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx");
}
'''
'''Save and Export
'''anchor-save-and-export
'''*
Shared Sub Main(ByVal args() As String)
	Dim newXLFile = WorkBook.Create(ExcelFileFormat.XLSX)
	newXLFile.Metadata.Title = "IronXL New File"
	Dim newWorkSheet = newXLFile.CreateWorkSheet("1stWorkSheet")
	newWorkSheet("A1").Value = "Hello World"
	newWorkSheet("A2").Style.BottomBorder.SetColor("#ff6600")
	newWorkSheet("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed

	newXLFile.SaveAs($"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx")
End Sub
VB   C#

2.4.2. Save to csv “.csv” To save to “.csv” we can use SaveAsCsv and pass to it 2 parameters 1st parameter the file name and path the 2nd parameter is the delimiter like (“,” or “|” or “:”)

newXLFile.SaveAsCsv($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.csv",delimiter:"|");
newXLFile.SaveAsCsv($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.csv",delimiter:"|");
newXLFile.SaveAsCsv($"{Directory.GetCurrentDirectory()}\Files\HelloWorld.csv",delimiter:="|")
VB   C#

2.4.3. Save to JSON “.json” To save to Json “.json” use SaveAsJson as follow

newXLFile.SaveAsJson($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldJSON.json");
newXLFile.SaveAsJson($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldJSON.json");
newXLFile.SaveAsJson($"{Directory.GetCurrentDirectory()}\Files\HelloWorldJSON.json")
VB   C#

The result file should look like this

[
    [
        "Hello World"
    ],
    [
        ""
    ]
]
[
    [
        "Hello World"
    ],
    [
        ""
    ]
]
'INSTANT VB TODO TASK: The following line uses invalid syntax:
'[["Hello World"], [""]]
VB   C#

2.4.4. Save to XML “.xml” To save to xml use SaveAsXml as follow

newXLFile.SaveAsXml($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML");
newXLFile.SaveAsXml($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML");
newXLFile.SaveAsXml($"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML")
VB   C#

Result should be like this

<?xml version="1.0" standalone="yes"?>
<_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">Hello World</Column1>
  </_x0031_stWorkSheet>
  <_x0031_stWorkSheet>
    <Column1 xsi:type="xs:string" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" />
  </_x0031_stWorkSheet>
</_x0031_stWorkSheet>
HTML


3. Advanced Operations: Sum, Avg, Count, etc.

Let's apply common Excel functions like SUM, AVG, Count and see each code snippet.

3.1. Sum Example

Let’s find the sum for this list. I created an Excel file and named it “Sum.xlsx” and added this list of numbers manually

/**
Function SUM
anchor-sum-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
decimal sum = sheet["A2:A4"].Sum();
Console.WriteLine(sum);
/**
Function SUM
anchor-sum-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
decimal sum = sheet["A2:A4"].Sum();
Console.WriteLine(sum);
'''
'''Function SUM
'''anchor-sum-example
'''*
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim sheet = workbook.WorkSheets.First()
Dim sum As Decimal = sheet("A2:A4").Sum()
Console.WriteLine(sum)
VB   C#

3.2. Avg Example

Using the same file, we can get the average:

/**
Function AVG
anchor-avg-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
decimal avg = sheet["A2:A4"].Avg();
Console.WriteLine(avg);
/**
Function AVG
anchor-avg-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
decimal avg = sheet["A2:A4"].Avg();
Console.WriteLine(avg);
'''
'''Function AVG
'''anchor-avg-example
'''*
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim sheet = workbook.WorkSheets.First()
Dim avg As Decimal = sheet("A2:A4").Avg()
Console.WriteLine(avg)
VB   C#

3.3. Count Example

Using the same file, we can also get the number of elements in a sequence:

/**
Function Count
anchor-count-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
decimal count = sheet["A2:A4"].Count();
Console.WriteLine(count);
/**
Function Count
anchor-count-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
decimal count = sheet["A2:A4"].Count();
Console.WriteLine(count);
'''
'''Function Count
'''anchor-count-example
'''*
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim sheet = workbook.WorkSheets.First()
Dim count As Decimal = sheet("A2:A4").Count()
Console.WriteLine(count)
VB   C#

3.4. Max Example

Using the same file, we can get the max value of range of cells:

/**
Function MAX
anchor-max-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
decimal max = sheet["A2:A4"].Max ();
Console.WriteLine(max);
/**
Function MAX
anchor-max-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
decimal max = sheet["A2:A4"].Max ();
Console.WriteLine(max);
'''
'''Function MAX
'''anchor-max-example
'''*
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim sheet = workbook.WorkSheets.First()
Dim max As Decimal = sheet("A2:A4").Max()
Console.WriteLine(max)
VB   C#

– We can apply the transform function to the result of max function:

var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
bool max2 =sheet["A1:A4"].Max(c => c. IsFormula);
Console.WriteLine(count);
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
bool max2 =sheet["A1:A4"].Max(c => c. IsFormula);
Console.WriteLine(count);
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim sheet = workbook.WorkSheets.First()
Dim max2 As Boolean =sheet("A1:A4").Max(Function(c) c.IsFormula)
Console.WriteLine(count)
VB   C#

This example writes “false” in the console.

3.5. Min Example

Using the same file, we can get the min value of range of cells:

/**
Function MIN
anchor-min-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
bool max2 =sheet["A1:A4"].Min();
Console.WriteLine(count);
/**
Function MIN
anchor-min-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
bool max2 =sheet["A1:A4"].Min();
Console.WriteLine(count);
'''
'''Function MIN
'''anchor-min-example
'''*
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim sheet = workbook.WorkSheets.First()
Dim max2 As Boolean =sheet("A1:A4").Min()
Console.WriteLine(count)
VB   C#

3.6. Order Cells Example

Using the same file, we can order cells by ascending or descending:

/**
Function Order Cells
anchor-order-cells-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
sheet["A1:A4"].SortAscending(); //or use > sheet["A1:A4"].SortDescending(); to order descending
workbook.SaveAs("SortedSheet.xlsx");
/**
Function Order Cells
anchor-order-cells-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
sheet["A1:A4"].SortAscending(); //or use > sheet["A1:A4"].SortDescending(); to order descending
workbook.SaveAs("SortedSheet.xlsx");
'''
'''Function Order Cells
'''anchor-order-cells-example
'''*
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim sheet = workbook.WorkSheets.First()
sheet("A1:A4").SortAscending() 'or use > sheet["A1:A4"].SortDescending(); to order descending
workbook.SaveAs("SortedSheet.xlsx")
VB   C#

3.7. If Condition Example

Using the same file, we can use the Formula property to set or get a cell’s formula:

3.7.1. Save to XML “.xml”

/**
Condition IF
anchor-if-condition-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
int i = 1;
foreach(var cell in sheet["B1:B4"])
{
    cell.Formula = "=IF(A" +i+ ">=20,\" Pass\" ,\" Fail\" )";
    i++;
}
workbook.SaveAs($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");
/**
Condition IF
anchor-if-condition-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
var sheet = workbook.WorkSheets.First();
int i = 1;
foreach(var cell in sheet["B1:B4"])
{
    cell.Formula = "=IF(A" +i+ ">=20,\" Pass\" ,\" Fail\" )";
    i++;
}
workbook.SaveAs($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");
'''
'''Condition IF
'''anchor-if-condition-example
'''*
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim sheet = workbook.WorkSheets.First()
Dim i As Integer = 1
For Each cell In sheet("B1:B4")
	cell.Formula = "=IF(A" & i & ">=20,"" Pass"" ,"" Fail"" )"
	i += 1
Next cell
workbook.SaveAs($"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx")
VB   C#

7.2. Using the generated file from the previous example, we can get the Cell’s Formula:

var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");
var sheet = workbook.WorkSheets.First();
foreach(var cell in sheet["B1:B4"])
{
    Console.WriteLine(cell.Formula); 
}
Console.ReadKey();
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");
var sheet = workbook.WorkSheets.First();
foreach(var cell in sheet["B1:B4"])
{
    Console.WriteLine(cell.Formula); 
}
Console.ReadKey();
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx")
Dim sheet = workbook.WorkSheets.First()
For Each cell In sheet("B1:B4")
	Console.WriteLine(cell.Formula)
Next cell
Console.ReadKey()
VB   C#

3.8. Trim Example

To apply trim function (to eliminate all extra spaces in cells), I added this column to the sum.xlsx file

And use this code

/**
Function Trim
anchor-trim-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");
var sheet = workbook.WorkSheets.First();
int i = 1;
foreach (var cell in sheet["f1:f4"])
{
    cell.Formula = "=trim(D"+i+")";
    i++;
}
workbook.SaveAs("editedFile.xlsx");
/**
Function Trim
anchor-trim-example
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");
var sheet = workbook.WorkSheets.First();
int i = 1;
foreach (var cell in sheet["f1:f4"])
{
    cell.Formula = "=trim(D"+i+")";
    i++;
}
workbook.SaveAs("editedFile.xlsx");
'''
'''Function Trim
'''anchor-trim-example
'''*
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx")
Dim sheet = workbook.WorkSheets.First()
Dim i As Integer = 1
For Each cell In sheet("f1:f4")
	cell.Formula = "=trim(D" & i & ")"
	i += 1
Next cell
workbook.SaveAs("editedFile.xlsx")
VB   C#

Thus, you can apply formulas in the same way.


4. Working with Multisheet Workbooks

We will go through how to work with workbook that have more than one sheet.

4.1. Read Data from Multiple Sheets in the Same Workbook

I created an xlsx file that contains two sheets: “Sheet1”,” Sheet2”

Until now we used WorkSheets.First() to work with the first sheet. In this example we will specify the sheet name and work with it

/**
Name Multiple Sheets
anchor-read-data-from-multiple-sheets-in-the-same-workbook
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet sheet = workbook.GetWorkSheet("Sheet2");
var range = sheet["A2:D2"];
foreach(var cell in range)
{
    Console.WriteLine(cell.Text);
}
/**
Name Multiple Sheets
anchor-read-data-from-multiple-sheets-in-the-same-workbook
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet sheet = workbook.GetWorkSheet("Sheet2");
var range = sheet["A2:D2"];
foreach(var cell in range)
{
    Console.WriteLine(cell.Text);
}
'''
'''Name Multiple Sheets
'''anchor-read-data-from-multiple-sheets-in-the-same-workbook
'''*
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx")
Dim sheet As WorkSheet = workbook.GetWorkSheet("Sheet2")
Dim range = sheet("A2:D2")
For Each cell In range
	Console.WriteLine(cell.Text)
Next cell
VB   C#

4.2. Add New Sheet to a Workbook

We can also add new sheet to a workbook:

/**
Add New Sheet
anchor-add-new-sheet-to-a-workbook
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
var newSheet = workbook.CreateWorkSheet("new_sheet");
newSheet["A1"].Value = "Hello World";
workbook.SaveAs(@"F:\MY WORK\IronPackage\Xl tutorial\newFile.xlsx"); 
/**
Add New Sheet
anchor-add-new-sheet-to-a-workbook
**/
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
var newSheet = workbook.CreateWorkSheet("new_sheet");
newSheet["A1"].Value = "Hello World";
workbook.SaveAs(@"F:\MY WORK\IronPackage\Xl tutorial\newFile.xlsx"); 
'''
'''Add New Sheet
'''anchor-add-new-sheet-to-a-workbook
'''*
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx")
Dim newSheet = workbook.CreateWorkSheet("new_sheet")
newSheet("A1").Value = "Hello World"
workbook.SaveAs("F:\MY WORK\IronPackage\Xl tutorial\newFile.xlsx")
VB   C#


5. Integrate with Excel Database

Let’s see how to export/import data to/from Database.

I created the "TestDb" database containing a Country table with two columns: Id (int, identity), CountryName(string)

5.1. Fill Excel sheet with Data from Database

Here we will create a new sheet and fill it with data from the Country Table

/**
Import Data to Sheet
anchor-fill-excel-sheet-with-data-from-database
**/
TestDbEntities dbContext = new TestDbEntities();
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet sheet = workbook.CreateWorkSheet("FromDb");
List<Country> countryList = dbContext.Countries.ToList();
sheet.SetCellValue(0, 0, "Id");
sheet.SetCellValue(0, 1, "Country Name");
int row = 1;
foreach (var item in countryList)
{
    sheet.SetCellValue(row, 0, item.id);
    sheet.SetCellValue(row, 1, item.CountryName);
    row++;
}
workbook.SaveAs("FilledFile.xlsx");
/**
Import Data to Sheet
anchor-fill-excel-sheet-with-data-from-database
**/
TestDbEntities dbContext = new TestDbEntities();
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet sheet = workbook.CreateWorkSheet("FromDb");
List<Country> countryList = dbContext.Countries.ToList();
sheet.SetCellValue(0, 0, "Id");
sheet.SetCellValue(0, 1, "Country Name");
int row = 1;
foreach (var item in countryList)
{
    sheet.SetCellValue(row, 0, item.id);
    sheet.SetCellValue(row, 1, item.CountryName);
    row++;
}
workbook.SaveAs("FilledFile.xlsx");
'''
'''Import Data to Sheet
'''anchor-fill-excel-sheet-with-data-from-database
'''*
Dim dbContext As New TestDbEntities()
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx")
Dim sheet As WorkSheet = workbook.CreateWorkSheet("FromDb")
Dim countryList As List(Of Country) = dbContext.Countries.ToList()
sheet.SetCellValue(0, 0, "Id")
sheet.SetCellValue(0, 1, "Country Name")
Dim row As Integer = 1
For Each item In countryList
	sheet.SetCellValue(row, 0, item.id)
	sheet.SetCellValue(row, 1, item.CountryName)
	row += 1
Next item
workbook.SaveAs("FilledFile.xlsx")
VB   C#

5.2. Fill Database with Data from Excel Sheet

Insert the data to the Country table in TestDb Database

/**
Import Data to Database
anchor-fill-database-with-data-from-excel-sheet
**/
TestDbEntities dbContext = new TestDbEntities ();
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet sheet = workbook.GetWorkSheet("Sheet3");
System.Data.DataTable dataTable = sheet.ToDataTable(true);
foreach (DataRow row in dataTable.Rows)
{
    Country c = new Country();
    c.CountryName = row[1].ToString();
    dbContext.Countries.Add(c);
}
dbContext.SaveChanges();
/**
Import Data to Database
anchor-fill-database-with-data-from-excel-sheet
**/
TestDbEntities dbContext = new TestDbEntities ();
var workbook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet sheet = workbook.GetWorkSheet("Sheet3");
System.Data.DataTable dataTable = sheet.ToDataTable(true);
foreach (DataRow row in dataTable.Rows)
{
    Country c = new Country();
    c.CountryName = row[1].ToString();
    dbContext.Countries.Add(c);
}
dbContext.SaveChanges();
'''
'''Import Data to Database
'''anchor-fill-database-with-data-from-excel-sheet
'''*
Dim dbContext As New TestDbEntities()
Dim workbook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx")
Dim sheet As WorkSheet = workbook.GetWorkSheet("Sheet3")
Dim dataTable As System.Data.DataTable = sheet.ToDataTable(True)
For Each row As DataRow In dataTable.Rows
	Dim c As New Country()
	c.CountryName = row(1).ToString()
	dbContext.Countries.Add(c)
Next row
dbContext.SaveChanges()
VB   C#


Further Reading

To learn more about working with IronXL, you may wish to look at the other tutorials within this section, and also the examples on our homepage, which most developers find enough to get them started.

Our Object Reference contains specific references to the WorkBook class.


Tutorial Quick Access

Download this Tutorial as C# Source Code

The full free C# for Excel Source Code for this tutorial is available to download as a zipped Visual Studio 2017 project file.

Download

Explore this Tutorial on GitHub

The source code for this project is available in C# and VB.NET on GitHub.

Use this code as an easy way to get up and running in just a few minutes. The project is saved as a Microsoft Visual Studio 2017 project, but is compatible with any .NET IDE.

How to Open and Write Excel File in C# on GitHub

Object Reference for IronXL

Explore the Object Reference for IronXL, outlining the details of all of IronXL’s features, namespaces, classes, methods fields and enums.

View the Object Reference
The .Net Excel library can revolutionise bring data into and out of  web applications and business systems.

Elijah Williams

Product Development Engineer

Elijah is an engineer on a Development and Testing team responsible for a large corporate financial reporting system. Elijah has been an early adopter of the IronXL Library, featuring it in the core of their Excel reporting interface.