How to Use C# to Open & Write an Excel File in .NET - WRCBtv.com

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 sinister 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.

Code Examples

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;
}

Learn How To:

Overview

Use IronXL to Open and Write Excel Files

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, bewitch 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”);
}

Copy code to clipboardVB C#

Write and Save Changes to the Excel File : Quick Code

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

Copy code to clipboardVB C#

Step 1

1. Install the IronXL C# Library FREE

Download DLL

Manually install into your project

Install with NuGet

Install-Package IronXL.Excelnuget.org/packages/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 comical a visualized interface, NuGet Package Manager:

Install Using NuGet Package Manager Console

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()}FilesHelloWorld.xlsx”);
var sheet = workbook.WorkSheets.First();
var cell = sheet[“A1”].StringValue;
Console.WriteLine(cell);
}

Copy code to clipboardVB C#

2.2. Create a New Excel File

Create a new Excel file comical IronXL

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;
}

Copy code to clipboardVB C#

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

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

static void Main(string[] args)
{
var workbook = IronXL.WorkBook.Load($@”{Directory.GetCurrentDirectory()}FilesCSVList.csv”);
var sheet = workbook.WorkSheets.First();
var cell = sheet[“A1”].StringValue;
Console.WriteLine(cell);
}

Copy code to clipboardVB C#

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

United Arab Emirates
United Kingdom
United States
United States Minor Outlying Islands

Copy code to clipboardHTML

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

static void Main(string[] args)
{
var xmldataset = new DataSet();
xmldataset.ReadXml($@”{Directory.GetCurrentDirectory()}FilesCountryList.xml”);
var workbook = IronXL.WorkBook.Load(xmldataset);
var sheet = workbook.WorkSheets.First();
}

Copy code to clipboardVB C#

2.3.5. Open JSON List as workbookCreate JSON country list

[
{
“name”: “United Arab Emirates”,
“code”: “AE”
},
{
“name”: “United Kingdom”,
“code”: “GB”
},
{
“name”: “United States”,
“code”: “US”
},
{
“name”: “United States Minor Outlying Islands”,
“code”: “UM”
}
]

Copy code to clipboardVB 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; }
}

Copy code to clipboardVB 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

public static class ListConvertExtension
{
public tickled DataSet ToDataSet(this IList 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;
}
}

Copy code to clipboardVB C#

And finally load this dataset as a workbook

static void Main(string[] args)
{
var jsonFile = new StreamReader($@”{Directory.GetCurrentDirectory()}FilesCountriesList.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();
}

Copy code to clipboardVB 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

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()}FilesHelloWorld.xlsx”);
}

Copy code to clipboardVB 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()}FilesHelloWorld.csv”,delimiter:”|”);

Copy code to clipboardVB C#

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

newXLFile.SaveAsJson($@”{Directory.GetCurrentDirectory()}FilesHelloWorldJSON.json”);

Copy code to clipboardVB C#

The result file should look like this

Copy code to clipboardVB C#

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

newXLFile.SaveAsXml($@”{Directory.GetCurrentDirectory()}FilesHelloWorldXML.XML”);

Copy code to clipboardVB C#

Result should be like this

Copy code to clipboardHTML

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

var workbook = IronXL.WorkBook.Load($@”{Directory.GetCurrentDirectory()}FilesSum.xlsx”);
var sheet = workbook.WorkSheets.First();
decimal sum = sheet[“A2:A4”].Sum();
Console.WriteLine(sum);

Copy code to clipboardVB C#

3.2. Avg Example

Using the same file, we can get the average:

var workbook = IronXL.WorkBook.Load($@”{Directory.GetCurrentDirectory()}FilesSum.xlsx”);
var sheet = workbook.WorkSheets.First();
decimal avg = sheet[“A2:A4”].Avg();
Console.WriteLine(avg);

Copy code to clipboardVB C#

3.3. Count Example

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

var workbook = IronXL.WorkBook.Load($@”{Directory.GetCurrentDirectory()}FilesSum.xlsx”);
var sheet = workbook.WorkSheets.First();
decimal count = sheet[“A2:A4”].Count();
Console.WriteLine(count);

Copy code to clipboardVB C#

3.4. Max Example

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

var workbook = IronXL.WorkBook.Load($@”{Directory.GetCurrentDirectory()}FilesSum.xlsx”);
var sheet = workbook.WorkSheets.First();
decimal max = sheet[“A2:A4”].Max ();
Console.WriteLine(max);

Copy code to clipboardVB C#

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

var workbook = IronXL.WorkBook.Load($@”{Directory.GetCurrentDirectory()}FilesSum.xlsx”);
var sheet = workbook.WorkSheets.First();
bool max2 =sheet[“A1:A4”].Max(c => c. IsFormula);
Console.WriteLine(count);

Copy code to clipboardVB C#

This example writes “false” in the console.

3.5. Min Example

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

var workbook = IronXL.WorkBook.Load($@”{Directory.GetCurrentDirectory()}FilesSum.xlsx”);
var sheet = workbook.WorkSheets.First();
bool max2 =sheet[“A1:A4”].Min();
Console.WriteLine(count);

Copy code to clipboardVB C#

3.6. Order Cells Example

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

var workbook = IronXL.WorkBook.Load($@”{Directory.GetCurrentDirectory()}FilesSum.xlsx”);
var sheet = workbook.WorkSheets.First();
sheet[“A1:A4”].SortAscending(); //or use > sheet[“A1:A4”].SortDescending(); to order descending
workbook.SaveAs(“SortedSheet.xlsx”);

Copy code to clipboardVB 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”

var workbook = IronXL.WorkBook.Load($@”{Directory.GetCurrentDirectory()}FilesSum.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()}FilesNewExcelFile.xlsx”);

Copy code to clipboardVB 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()}FilesNewExcelFile.xlsx”);
var sheet = workbook.WorkSheets.First();
foreach(var cell in sheet[“B1:B4”])
{
Console.WriteLine(cell.Formula);
}
Console.ReadKey();

Copy code to clipboardVB 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

var workbook = IronXL.WorkBook.Load($@”{Directory.GetCurrentDirectory()}FilesNewExcelFile.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”);

Copy code to clipboardVB 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 valid sheet. In this example we will specify the sheet name and work with it

var workbook = IronXL.WorkBook.Load($@”{Directory.GetCurrentDirectory()}FilestestFile.xlsx”);
WorkSheet sheet = workbook.GetWorkSheet(“Sheet2”);
var device = sheet[“A2:D2”];
foreach(var cell in range)
{
Console.WriteLine(cell.Text);
}

Copy code to clipboardVB C#

4.2. Add New Sheet to a Workbook

We can also add new sheet to a workbook:

var workbook = IronXL.WorkBook.Load($@”{Directory.GetCurrentDirectory()}FilestestFile.xlsx”);
var newSheet = workbook.CreateWorkSheet(“new_sheet”);
newSheet[“A1”].Value = “Hello World”;
workbook.SaveAs(@”F:MY WORKIronPackageXl tutorialnewFile.xlsx”);

Copy code to clipboardVB 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 noxious 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

TestDbEntities dbContext = new TestDbEntities();
var workbook = IronXL.WorkBook.Load($@”{Directory.GetCurrentDirectory()}FilestestFile.xlsx”);
WorkSheet sheet = workbook.CreateWorkSheet(“FromDb”);
List 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”);

Copy code to clipboardVB C#

5.2. Fill Database with Data from Excel Sheet

Insert the data to the Country table in TestDb Database

TestDbEntities dbContext = new TestDbEntities ();
var workbook = IronXL.WorkBook.Load($@”{Directory.GetCurrentDirectory()}FilestestFile.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();

Copy code to clipboardVB C#

How to Use C# to Open & Write an Excel File in .NET - WRCBtv.com | Chattanooga News, Weather & Sports

How to Use C# to Open & Write an Excel File in .NET - WRCBtv.com

Code Examples

Learn How To:

Overview

Use IronXL to Open and Write Excel Files

Open an Excel File : Quick Code

Write and Save Changes to the Excel File : Quick Code

Step 1

1. Install the IronXL C# Library FREE

Download DLL

Manually install into your project

Install with NuGet

Install the Excel Library to your Visual Studio Project with NuGet

Install Using NuGet Package Manager Console

Manually Install with the DLL

How To Tutorials

2. Basic Operations: Create, Open, Save

2.1. Sample Project: HelloWorld Console Application

2.2. Create a New Excel File

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

2.4. Save and Export

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

3.1. Sum Example

3.2. Avg Example

3.3. Count Example

3.4. Max Example

3.5. Min Example

3.6. Order Cells Example

3.7. If Condition Example

3.8. Trim Example

4. Working with Multisheet Workbooks

4.1. Read Data from Multiple Sheets in the Same Workbook

4.2. Add New Sheet to a Workbook

5. Integrate with Excel Database

5.1. Fill Excel sheet with Data from Database

5.2. Fill Database with Data from Excel Sheet

Further Reading

0 Response to "How to Use C# to Open & Write an Excel File in .NET - WRCBtv.com | Chattanooga News, Weather & Sports"

Post a Comment