C# Write to Excel [Without Using Interop] Code Example Tutorial
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.
How to Write an Excel File in C#
- Download the Write Excel C# Library
- Create and open new CSV or XML Excel file as Excel workbook
- Save and export your Excel workbook
- Apply Advanced Operations in your multiple Excel worksheets
- Integrate with Excel Database
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.
- Install the IronXL Excel Library from NuGet or the DLL download
- Use the WorkBook.Load method to read any XLS, XLSX or CSV document.
- 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
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-1.csusing IronXL;
WorkBook workBook = WorkBook.Load("test.xlsx");
WorkSheet workSheet = workBook.DefaultWorkSheet;
IronXL.Range range = workSheet["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 (workSheet["A11"].DecimalValue == total)
{
Console.WriteLine("Basic Test Passed");
}Imports IronXL
Private workBook As WorkBook = WorkBook.Load("test.xlsx")
Private workSheet As WorkSheet = workBook.DefaultWorkSheet
Private range As IronXL.Range = workSheet("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 workSheet("A11").DecimalValue = total Then
Console.WriteLine("Basic Test Passed")
End IfWrite and Save Changes to the Excel File : Quick Code
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-2.csworkSheet["B1"].Value = 11.54;
// Save Changes
workBook.SaveAs("test.xlsx");workSheet("B1").Value = 11.54
' Save Changes
workBook.SaveAs("test.xlsx")Step 1
1. Install the IronXL C# Library FREE
Install with NuGet
Install-Package IronXL.Excel
Install with NuGet
Install-Package IronXL.Excel
Start using IronPDF in your project today with a free trial.
Check out IronXL on Nuget for quick installation and deployment. With over 8 million downloads, it's transforming Excel with C#.
Consider installing the IronXL DLL directly. Download and manually install it for your project or GAC form: IronXL.zip
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:
Using mouse -> right click on project name -> Select manage NuGet Package
From browse tab -> search for IronXL.Excel -> Install
- And we are done
Install Using NuGet Package Manager Console
From tools -> NuGet Package Manager -> Package Manager Console
- 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.ExcelHow 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
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-3.csWorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
string cell = workSheet["A1"].StringValue;
Console.WriteLine(cell);Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim cell As String = workSheet("A1").StringValue
Console.WriteLine(cell)2.2. Create a New Excel File
Create a new Excel file using IronXL
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-4.csWorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
workBook.Metadata.Title = "IronXL New File";
WorkSheet workSheet = workBook.CreateWorkSheet("1stWorkSheet");
workSheet["A1"].Value = "Hello World";
workSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
workSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
workBook.Metadata.Title = "IronXL New File"
Dim workSheet As WorkSheet = workBook.CreateWorkSheet("1stWorkSheet")
workSheet("A1").Value = "Hello World"
workSheet("A2").Style.BottomBorder.SetColor("#ff6600")
workSheet("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed2.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
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-5.csWorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\CSVList.csv");
WorkSheet workSheet = workBook.WorkSheets.First();
string cell = workSheet["A1"].StringValue;
Console.WriteLine(cell);Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\CSVList.csv")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim cell As String = workSheet("A1").StringValue
Console.WriteLine(cell)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><?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>2.3.4. Copy the following code snippet to open XML as a workbook
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-7.csDataSet xmldataset = new DataSet();
xmldataset.ReadXml($@"{Directory.GetCurrentDirectory()}\Files\CountryList.xml");
WorkBook workBook = IronXL.WorkBook.Load(xmldataset);
WorkSheet workSheet = workBook.WorkSheets.First();Dim xmldataset As New DataSet()
xmldataset.ReadXml($"{Directory.GetCurrentDirectory()}\Files\CountryList.xml")
Dim workBook As WorkBook = IronXL.WorkBook.Load(xmldataset)
Dim workSheet As WorkSheet = workBook.WorkSheets.First()2.3.5. Open JSON List as workbook Create JSON country list
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-8.cs[
{
"name": "United Arab Emirates",
"code": "AE"
},
{
"name": "United Kingdom",
"code": "GB"
},
{
"name": "United States",
"code": "US"
},
{
"name": "United States Minor Outlying Islands",
"code": "UM"
}
]'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" }]2.3.6. Create a country model that will map to JSON
Here is the class code snippet
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-9.cspublic 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 Class2.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
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-10.cspublic 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;
}
}
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 ModuleAnd finally load this dataset as a workbook
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-11.csStreamReader jsonFile = new StreamReader($@"{Directory.GetCurrentDirectory()}\Files\CountriesList.json");
var countryList = Newtonsoft.Json.JsonConvert.DeserializeObject<CountryModel[]>(jsonFile.ReadToEnd());
var xmldataset = countryList.ToDataSet();
WorkBook workBook = IronXL.WorkBook.Load(xmldataset);
WorkSheet workSheet = workBook.WorkSheets.First();Dim jsonFile As New StreamReader($"{Directory.GetCurrentDirectory()}\Files\CountriesList.json")
Dim countryList = Newtonsoft.Json.JsonConvert.DeserializeObject(Of CountryModel())(jsonFile.ReadToEnd())
Dim xmldataset = countryList.ToDataSet()
Dim workBook As WorkBook = IronXL.WorkBook.Load(xmldataset)
Dim workSheet As WorkSheet = workBook.WorkSheets.First()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
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-12.csWorkBook workBook = WorkBook.Create(ExcelFileFormat.XLSX);
workBook.Metadata.Title = "IronXL New File";
WorkSheet workSheet = workBook.CreateWorkSheet("1stWorkSheet");
workSheet["A1"].Value = "Hello World";
workSheet["A2"].Style.BottomBorder.SetColor("#ff6600");
workSheet["A2"].Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed;
workBook.SaveAs($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx");Dim workBook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
workBook.Metadata.Title = "IronXL New File"
Dim workSheet As WorkSheet = workBook.CreateWorkSheet("1stWorkSheet")
workSheet("A1").Value = "Hello World"
workSheet("A2").Style.BottomBorder.SetColor("#ff6600")
workSheet("A2").Style.BottomBorder.Type = IronXL.Styles.BorderType.Dashed
workBook.SaveAs($"{Directory.GetCurrentDirectory()}\Files\HelloWorld.xlsx")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 “:”)
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-13.csworkBook.SaveAsCsv($@"{Directory.GetCurrentDirectory()}\Files\HelloWorld.csv", delimiter: "|");workBook.SaveAsCsv($"{Directory.GetCurrentDirectory()}\Files\HelloWorld.csv", delimiter:= "|")2.4.3. Save to JSON “.json” To save to Json “.json” use SaveAsJson as follow
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-14.csworkBook.SaveAsJson($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldJSON.json");workBook.SaveAsJson($"{Directory.GetCurrentDirectory()}\Files\HelloWorldJSON.json")The result file should look like this
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-15.cs[
[
"Hello World"
],
[
""
]
]'INSTANT VB TODO TASK: The following line uses invalid syntax:
'[["Hello World"], [""]]2.4.4. Save to XML “.xml” To save to xml use SaveAsXml as follow
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-16.csworkBook.SaveAsXml($@"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML");workBook.SaveAsXml($"{Directory.GetCurrentDirectory()}\Files\HelloWorldXML.XML")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><?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>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
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-18.csWorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal sum = workSheet["A2:A4"].Sum();
Console.WriteLine(sum);Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim sum As Decimal = workSheet("A2:A4").Sum()
Console.WriteLine(sum)3.2. Avg Example
Using the same file, we can get the average:
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-19.csWorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal avg = workSheet["A2:A4"].Avg();
Console.WriteLine(avg);Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim avg As Decimal = workSheet("A2:A4").Avg()
Console.WriteLine(avg)3.3. Count Example
Using the same file, we can also get the number of elements in a sequence:
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-20.csWorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal count = workSheet["A2:A4"].Count();
Console.WriteLine(count);Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim count As Decimal = workSheet("A2:A4").Count()
Console.WriteLine(count)3.4. Max Example
Using the same file, we can get the max value of range of cells:
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-21.csWorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal max = workSheet["A2:A4"].Max();
Console.WriteLine(max);Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim max As Decimal = workSheet("A2:A4").Max()
Console.WriteLine(max)– We can apply the transform function to the result of max function:
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-22.csWorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
bool max2 = workSheet["A1:A4"].Max(c => c.IsFormula);
Console.WriteLine(max2);Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim max2 As Boolean = workSheet("A1:A4").Max(Function(c) c.IsFormula)
Console.WriteLine(max2)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:
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-23.csWorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
decimal min = workSheet["A1:A4"].Min();
Console.WriteLine(min);Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim min As Decimal = workSheet("A1:A4").Min()
Console.WriteLine(min)3.6. Order Cells Example
Using the same file, we can order cells by ascending or descending:
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-24.csWorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
workSheet["A1:A4"].SortAscending();
// workSheet["A1:A4"].SortDescending(); to order descending
workBook.SaveAs("SortedSheet.xlsx");Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
workSheet("A1:A4").SortAscending()
' workSheet["A1:A4"].SortDescending(); to order descending
workBook.SaveAs("SortedSheet.xlsx")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”
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-25.csWorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
int i = 1;
foreach (var cell in workSheet["B1:B4"])
{
cell.Formula = "=IF(A" + i + ">=20,\" Pass\" ,\" Fail\" )";
i++;
}
workBook.SaveAs($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\Sum.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim i As Integer = 1
For Each cell In workSheet("B1:B4")
cell.Formula = "=IF(A" & i & ">=20,"" Pass"" ,"" Fail"" )"
i += 1
Next cell
workBook.SaveAs($"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx")7.2. Using the generated file from the previous example, we can get the Cell’s Formula:
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-26.csWorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
foreach (var cell in workSheet["B1:B4"])
{
Console.WriteLine(cell.Formula);
}
Console.ReadKey();Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
For Each cell In workSheet("B1:B4")
Console.WriteLine(cell.Formula)
Next cell
Console.ReadKey()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
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-27.csWorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx");
WorkSheet workSheet = workBook.WorkSheets.First();
int i = 1;
foreach (var cell in workSheet["f1:f4"])
{
cell.Formula = "=trim(D" + i + ")";
i++;
}
workBook.SaveAs("editedFile.xlsx");Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\NewExcelFile.xlsx")
Dim workSheet As WorkSheet = workBook.WorkSheets.First()
Dim i As Integer = 1
For Each cell In workSheet("f1:f4")
cell.Formula = "=trim(D" & i & ")"
i += 1
Next cell
workBook.SaveAs("editedFile.xlsx")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
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-28.csWorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet workSheet = workBook.GetWorkSheet("Sheet2");
var range = workSheet["A2:D2"];
foreach (var cell in range)
{
Console.WriteLine(cell.Text);
}Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx")
Dim workSheet As WorkSheet = workBook.GetWorkSheet("Sheet2")
Dim range = workSheet("A2:D2")
For Each cell In range
Console.WriteLine(cell.Text)
Next cell4.2. Add New Sheet to a Workbook
We can also add new sheet to a workbook:
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-29.csWorkBook workBook = IronXL.WorkBook.Load($@"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx");
WorkSheet workSheet = workBook.CreateWorkSheet("new_sheet");
workSheet["A1"].Value = "Hello World";
workBook.SaveAs(@"F:\MY WORK\IronPackage\Xl tutorial\newFile.xlsx");Dim workBook As WorkBook = IronXL.WorkBook.Load($"{Directory.GetCurrentDirectory()}\Files\testFile.xlsx")
Dim workSheet As WorkSheet = workBook.CreateWorkSheet("new_sheet")
workSheet("A1").Value = "Hello World"
workBook.SaveAs("F:\MY WORK\IronPackage\Xl tutorial\newFile.xlsx")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
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-30.csTestDbEntities 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");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")5.2. Fill Database with Data from Excel Sheet
Insert the data to the Country table in TestDb Database
:path=/static-assets/excel/content-code-examples/tutorials/csharp-open-write-excel-file-31.csTestDbEntities 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();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()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 API 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.
DownloadExplore 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
API Reference for IronXL
Explore the API Reference for IronXL, outlining the details of all of IronXL’s features, namespaces, classes, methods fields and enums.
View the API Reference
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.
