using IronXL; using System; using System.Linq; // Supported for XLSX, XLS, XLSM, XLTX, CSV and TSV WorkBook workBook = WorkBook.Load("sample.xlsx"); // Select worksheet at index 0 WorkSheet workSheet = workBook.WorkSheets[0]; // Get any existing worksheet WorkSheet firstSheet = workBook.DefaultWorkSheet; // Select a cell and return the converted value int cellValue = workSheet["A2"].IntValue; // Read from ranges of cells elegantly. foreach (var cell in workSheet["A2:A10"]) { Console.WriteLine("Cell {0} has value '{1}'", cell.AddressString, cell.Text); } // Calculate aggregate values such as Min, Max and Sum decimal sum = workSheet["A2:A10"].Sum(); // Linq compatible decimal max = workSheet["A2:A10"].Max(c => c.DecimalValue);

using IronSoftware.Drawing; using IronXL; using IronXL.Styles; using System.Linq; WorkBook workBook = WorkBook.Load("sample.xls"); WorkSheet workSheet = workBook.WorkSheets.First(); var range = workSheet["A1:H10"]; var cell = range.First(); // Set background color of the cell with an rgb string cell.Style.SetBackgroundColor("#428D65"); // Apply styling to the whole range. // Set underline property to the font // FontUnderlineType is enum that stands for different types of font underlying range.Style.Font.Underline = FontUnderlineType.SingleAccounting; // Define whether to use horizontal line through the text or not range.Style.Font.Strikeout = false; // Define whether the font is bold or not range.Style.Font.Bold = true; // Define whether the font is italic or not range.Style.Font.Italic = false; // Get or set script property of a font // Font script enum stands for available options range.Style.Font.FontScript = FontScript.Super; // Set the type of the border line // There are also TopBorder,LeftBorder,RightBorder,DiagonalBorder properties // BorderType enum indicates the line style of a border in a cell range.Style.BottomBorder.Type = BorderType.MediumDashed; // Indicate whether the cell should be auto-sized range.Style.ShrinkToFit = true; // Set alignment of the cell range.Style.VerticalAlignment = VerticalAlignment.Bottom; // Set border color range.Style.DiagonalBorder.SetColor("#20C96F"); // Define border type and border direction as well range.Style.DiagonalBorder.Type = BorderType.Thick; // DiagonalBorderDirection enum stands for direction of diagonal border inside cell range.Style.DiagonalBorderDirection = DiagonalBorderDirection.Forward; // Set background color of cells range.Style.SetBackgroundColor(Color.Aquamarine); // Set fill pattern of the cell // FillPattern enum indicates the style of fill pattern range.Style.FillPattern = FillPattern.Diamonds; // Set the number of spaces to intend the text range.Style.Indention = 5; // Indicate if the text is wrapped range.Style.WrapText = true; workBook.SaveAs("stylingOptions.xls");

Published October 6, 2020

Work with Excel in .NET Core

.NET Core Excel Overview

In this modern era, we need a better way to work with Excel Spreadsheets in our .NET Core applications. In the following tutorial, we will learn how to access spreadsheets in dotnet Core Excel projects and modify their values using C#.

Step 1

1. Download IronXL Library

For an easy way to work with Excel files in .NET Core, try IronXL. Download IronXL DLL or install with NuGet for free use in development projects.

 PM > Install-Package IronXL.Excel

How to Tutorial

2. .NET Core Excel Editing Project

Now that you've downloaded IronXL, let's get started. Load an Excel file in the project and access the WorkSheet where data needs to be edited and have changes made.

3. Edit Specific Cell Value

For editing Excel files, add the reference IronXL to your project and import the library by Using IronXL.

3.1. Load a Sample File

In the following case, our Excel file name is sample.xlsx and it exists in the bin> Debug> netcoreapp3.1 folder of the project. We will use this code to edit the value new value in cell A1 of sample.xlsx.

/**
Load WorkSheet
anchor-load-a-sample-file
**/
using IronXL;
static void Main(string[] args)
{          
    WorkBook wb = WorkBook.Load("sample.xlsx"); //load Excel file 
    WorkSheet ws = wb.GetWorkSheet("Sheet1"); //Get sheet1 of sample.xlsx
    ws["A1"].Value = "new value"; //access A1 cell and edit the value
    wb.SaveAs("sample.xlsx");   //save changes        
}

/**
Load WorkSheet
anchor-load-a-sample-file
**/
using IronXL;
static void Main(string[] args)
{          
    WorkBook wb = WorkBook.Load("sample.xlsx"); //load Excel file 
    WorkSheet ws = wb.GetWorkSheet("Sheet1"); //Get sheet1 of sample.xlsx
    ws["A1"].Value = "new value"; //access A1 cell and edit the value
    wb.SaveAs("sample.xlsx");   //save changes        
}

'''
'''Load WorkSheet
'''anchor-load-a-sample-file
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx") 'load Excel file
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1") 'Get sheet1 of sample.xlsx
	ws("A1").Value = "new value" 'access A1 cell and edit the value
	wb.SaveAs("sample.xlsx") 'save changes
End Sub

VB C#

4. Assign Value to Multiple Cells

It is very easy to edit multiple cells and assign static value at a time by using (colon :). Its left side indicates starting cell, and the right side indicates the last cell of a specific column.

sheet[From:To]

This will edit new value from cell A1 to A9 of column A.

/**
Assign Value Multi Cells
anchor-assign-value-to-multiple-cells
**/
using IronXL;
static void Main(string[] args)
{          
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    ws["A1:A9"].Value = "new value";
    wb.SaveAs("sample.xlsx");  
}

/**
Assign Value Multi Cells
anchor-assign-value-to-multiple-cells
**/
using IronXL;
static void Main(string[] args)
{          
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    ws["A1:A9"].Value = "new value";
    wb.SaveAs("sample.xlsx");  
}

'''
'''Assign Value Multi Cells
'''anchor-assign-value-to-multiple-cells
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	ws("A1:A9").Value = "new value"
	wb.SaveAs("sample.xlsx")
End Sub

VB C#

5. Edit Cells with User Inputs

Here is an alternative case where we can take the values from the users and edit the Excel file.

/**
Edit Cells User Input
anchor-edit-cells-with-user-inputs
**/
using IronXL;
static void Main(string[] args)
{
  string _from, _to,NewValue ;

  Console.Write("Enter Starting Cell :");
  _from = Console.ReadLine();

  Console.Write("Enter Last Cell :");
  _to = Console.ReadLine();

  Console.Write("Enter value:");
  NewValue = Console.ReadLine();

  WorkBook wb = WorkBook.Load("sample.xlsx");
  WorkSheet ws = wb.GetWorkSheet("Sheet1");
  ws[_from + ":" + _to].Value = NewValue;
  wb.SaveAs("sample.xlsx"); 
  Console.WriteLine("Successfully Changed...!");
  Console.ReadKey();
}

/**
Edit Cells User Input
anchor-edit-cells-with-user-inputs
**/
using IronXL;
static void Main(string[] args)
{
  string _from, _to,NewValue ;

  Console.Write("Enter Starting Cell :");
  _from = Console.ReadLine();

  Console.Write("Enter Last Cell :");
  _to = Console.ReadLine();

  Console.Write("Enter value:");
  NewValue = Console.ReadLine();

  WorkBook wb = WorkBook.Load("sample.xlsx");
  WorkSheet ws = wb.GetWorkSheet("Sheet1");
  ws[_from + ":" + _to].Value = NewValue;
  wb.SaveAs("sample.xlsx"); 
  Console.WriteLine("Successfully Changed...!");
  Console.ReadKey();
}

'''
'''Edit Cells User Input
'''anchor-edit-cells-with-user-inputs
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
  Dim _from, _to, NewValue As String

  Console.Write("Enter Starting Cell :")
  _from = Console.ReadLine()

  Console.Write("Enter Last Cell :")
  _to = Console.ReadLine()

  Console.Write("Enter value:")
  NewValue = Console.ReadLine()

  Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
  Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
  ws(_from & ":" & _to).Value = NewValue
  wb.SaveAs("sample.xlsx")
  Console.WriteLine("Successfully Changed...!")
  Console.ReadKey()
End Sub

VB C#

The above code will display the following output and take inputs from user:

Values changed from B4 to B9 in ExcelSheet, we can see:

6. Edit Multiple Cells with Static Value

It is very easy to edit multiple cells and assign dynamic values. Let's see the following example:

/**
Edit Multi Cells Static Value
anchor-edit-multiple-cells-with-static-value
**/
using IronXL;
static void Main(string[] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    for (int i = From; i <= To; i++) //Set cell range of column A to be edit.
    {
        ws["A" + i].Value = "Value"+i;
    }
    wb.SaveAs("sample.xlsx");  
}

/**
Edit Multi Cells Static Value
anchor-edit-multiple-cells-with-static-value
**/
using IronXL;
static void Main(string[] args)
{
    WorkBook wb = WorkBook.Load("sample.xlsx");
    WorkSheet ws = wb.GetWorkSheet("Sheet1");
    for (int i = From; i <= To; i++) //Set cell range of column A to be edit.
    {
        ws["A" + i].Value = "Value"+i;
    }
    wb.SaveAs("sample.xlsx");  
}

'''
'''Edit Multi Cells Static Value
'''anchor-edit-multiple-cells-with-static-value
'''*
Imports IronXL
Shared Sub Main(ByVal args() As String)
	Dim wb As WorkBook = WorkBook.Load("sample.xlsx")
	Dim ws As WorkSheet = wb.GetWorkSheet("Sheet1")
	For i As Integer = From To [To] 'Set cell range of column A to be edit.
		ws("A" & i).Value = "Value" & i
	Next i
	wb.SaveAs("sample.xlsx")
End Sub

VB C#