How to Open Excel File in ASP .NET Core

This tutorial will demonstrate how to open an Excel file using IronXL in an ASP.NET Core application using the IronXL library. It will cover the basic steps to read Excel files and then display data from them and save modified data back to the file.

What is IronXL?

IronXL is a .NET Excel library that provides functionality for working with Excel documents without requiring Microsoft Excel or Office Interop dependencies. It supports both .NET Framework and .NET Core, providing capabilities to write, manipulate, and read Excel files with just a few lines of code. For a detailed tutorial on reading Excel files, please refer to this Excel tutorial.

IronXL library can read and write Excel documents, extract data from them, manipulate the data, and even create new Excel files from scratch. IronXL supports not just .XLS format but is also compatible with .XLSX, which is an XML-based file format developed by Microsoft for representing spreadsheets.

Pre-requisites

Before dive into the code, make sure you have the following:

1. .NET Core 3.1 or higher installed.
2. A suitable IDE for .NET development (Microsoft Visual Studio is recommended).
3. Basic understanding of ASP.NET Core and Razor Pages.

Create a new .NET Core Project

1. Open Microsoft Visual Studio and select "Create a new project".
2. Choose "ASP.NET Core Web App" from the list of project templates and click "Next".
3. Provide a name for your project, for example, "Open_ExcelFile.NET_Core", select a location, and click "Next".
4. Choose ".NET Core" as the target framework and "ASP.NET Core 3.1 or higher" as the version. In the "Project template" section, select "Web Application" and click "Create".

Install IronXL Package

The next step is to install the IronXL package in this newly created project:

1. Right-click on the project in the Solution Explorer and select "Manage NuGet Packages".

2. In the "Browse" tab, search for "IronXL" and click "Install" for the IronXL.Excel package.

   Search and install the IronXL package in NuGet Package Manager UI

3. Wait for the package to be installed.

Your ASP.NET Core project is now set up, and the IronXL library is installed and ready to use.

Create a Razor Page

Now, let's create a Razor Page to write the code to open the Excel file.

1. Right-click on the "Pages" folder in the Solution Explorer and select "Add" > "Razor Page".

2. Enter "ExcelOpener" as the Page name and click "Add".

   Create a new Razor Page

This will create two files: ExcelOpener.cshtml and ExcelOpener.cshtml.cs.

The ExcelOpener.cshtml file contains the HTML markup for the page, and the ExcelOpener.cshtml.cs file contains the C# code-behind for the page.

Reading Excel Files

Let's dive into the code. The provided ExcelOpenerModel class is a Razor Page Model. This class has a List<List<string>> property Data, which will be used to store the data read from an Excel file.

public List<List<string>> Data { get; set; }

public ExcelOpenerModel()
{
    Data = new List<List<string>>();
}

public List<List<string>> Data { get; set; }

public ExcelOpenerModel()
{
    Data = new List<List<string>>();
}

Public Property Data() As List(Of List(Of String))

'INSTANT VB WARNING: The following constructor is declared outside of its associated class:
'ORIGINAL LINE: public ExcelOpenerModel()
Public Sub New()
	Data = New List(Of List(Of String))()
End Sub

In the OnGet method, IronXL is used to load an Excel file and read the data from the default worksheet (the first sheet in the workbook). Each row is represented as a List<string>, and all these lists are then added to the Data property.

public void OnGet()
{
    try
    {
        Data = new List<List<string>>();
        // Load your Excel file
        var workbook = WorkBook.Load(@"C:\Users\Administrator\Documents\Loan.xlsx");

        // Select your Worksheet
        WorkSheet sheet = workbook.DefaultWorkSheet;

        for (int i = 0; i < sheet.RowCount; i++)
        {
            var row = new List<string>();
            for (int j = 0; j < sheet.ColumnCount; j++)
            {
                row.Add(sheet.GetCellAt(i, j).Value.ToString());
            }
            Data.Add(row);
        }
    }
    catch (Exception ex)
    {
        // Handle exceptions here
        Console.WriteLine(ex.Message);
    }
}

public void OnGet()
{
    try
    {
        Data = new List<List<string>>();
        // Load your Excel file
        var workbook = WorkBook.Load(@"C:\Users\Administrator\Documents\Loan.xlsx");

        // Select your Worksheet
        WorkSheet sheet = workbook.DefaultWorkSheet;

        for (int i = 0; i < sheet.RowCount; i++)
        {
            var row = new List<string>();
            for (int j = 0; j < sheet.ColumnCount; j++)
            {
                row.Add(sheet.GetCellAt(i, j).Value.ToString());
            }
            Data.Add(row);
        }
    }
    catch (Exception ex)
    {
        // Handle exceptions here
        Console.WriteLine(ex.Message);
    }
}

Public Sub OnGet()
	Try
		Data = New List(Of List(Of String))()
		' Load your Excel file
		Dim workbook = WorkBook.Load("C:\Users\Administrator\Documents\Loan.xlsx")

		' Select your Worksheet
		Dim sheet As WorkSheet = workbook.DefaultWorkSheet

		For i As Integer = 0 To sheet.RowCount - 1
			Dim row = New List(Of String)()
			For j As Integer = 0 To sheet.ColumnCount - 1
				row.Add(sheet.GetCellAt(i, j).Value.ToString())
			Next j
			Data.Add(row)
		Next i
	Catch ex As Exception
		' Handle exceptions here
		Console.WriteLine(ex.Message)
	End Try
End Sub

Key methods explained

• WorkBook.Load(filepath): This method loads the Excel document from the specified file path. It helps to load and read the Excel sheet.
• workbook.DefaultWorkSheet: This property gets the default (first) Excel worksheet in the Excel spreadsheet.
• sheet.GetCellAt(i, j).Value.ToString(): This gets the cell at the specified row and column indexes and converts its value to a string. This is an example of an index method that can access specific cells or ranges within an Excel file based on row and column indexes.

Short Explanation

In the ExcelOpener.cshtml.cs file, a class named ExcelOpenerModel represents the Razor Page model. It contains a property named Data of type List<List<string>>, which will hold the data from the Excel file.

The OnGet method is called when the page is requested and is responsible for reading the data from the Excel file. It uses IronXL to load the Excel file, selects the default worksheet, and iterates over the rows and columns to populate the Data property.

Writing Excel Files

The OnPost method in ExcelOpenerModel class is used for saving modifications to the Excel file. It first loads the Excel file and retrieves the default worksheet. It then writes the values from Data to the cells in the worksheet and saves the workbook to a new file.

public void OnPost()
{
    try
    {
        // Load your Excel file
        var workbook = WorkBook.Load(@"C:\Users\Administrator\Documents\Loan.xlsx");

        // Select your Worksheet
        var sheet = workbook.DefaultWorkSheet;

        for (int i = 0; i < Data.Count; i++)
        {
            for (int j = 0; j < Data[i].Count; j++)
            {
                sheet.SetCellValue(i, j, Data[i][j]);
            }
        }

        // Save the Excel file
        var filename = @"C:\ModifiedData.xlsx";
        workbook.SaveAs(filename);
    }
    catch (Exception ex)
    {
        // Handle exceptions here
        Console.WriteLine(ex.Message);
    }
}

public void OnPost()
{
    try
    {
        // Load your Excel file
        var workbook = WorkBook.Load(@"C:\Users\Administrator\Documents\Loan.xlsx");

        // Select your Worksheet
        var sheet = workbook.DefaultWorkSheet;

        for (int i = 0; i < Data.Count; i++)
        {
            for (int j = 0; j < Data[i].Count; j++)
            {
                sheet.SetCellValue(i, j, Data[i][j]);
            }
        }

        // Save the Excel file
        var filename = @"C:\ModifiedData.xlsx";
        workbook.SaveAs(filename);
    }
    catch (Exception ex)
    {
        // Handle exceptions here
        Console.WriteLine(ex.Message);
    }
}

Public Sub OnPost()
	Try
		' Load your Excel file
		Dim workbook = WorkBook.Load("C:\Users\Administrator\Documents\Loan.xlsx")

		' Select your Worksheet
		Dim sheet = workbook.DefaultWorkSheet

		For i As Integer = 0 To Data.Count - 1
			Dim j As Integer = 0
			Do While j < Data(i).Count
				sheet.SetCellValue(i, j, Data(i)(j))
				j += 1
			Loop
		Next i

		' Save the Excel file
		Dim filename = "C:\ModifiedData.xlsx"
		workbook.SaveAs(filename)
	Catch ex As Exception
		' Handle exceptions here
		Console.WriteLine(ex.Message)
	End Try
End Sub

Key Methods Explained

• sheet.SetCellValue(i, j, Data[i][j]): This method sets the value of the cell at the specified row and column indexes.
• workbook.SaveAs(filepath): This method saves the Excel workbook to the specified file path.

Short Explanation

The OnPost method is called when the form is submitted, and it saves the modified data back to the Excel file. It loads the Excel file, selects the default worksheet, and updates the cell values based on the modified data received from the form.

HTML Form for Editing Excel Data

In ExcelOpener.cshtml, a form is defined for displaying and editing the data from the Excel file. Each cell value is displayed in a text input field. After editing, clicking the "Save" button will submit the form, triggering the OnPost method and saving the changes to the Excel file.

@page
@model Open_Excel_File_.NET_Core.Pages.ExcelOpenerModel

<form method="post">
    <table class="table table-bordered table-striped">
        <!-- Bootstrap classes for tables -->
        @for (int i = 0; i < Model.Data.Count; i++)
        {
            <tr>
                @for (int j = 0; j < Model.Data[i].Count; j++)
                {
                    <td>
                        <input type="text" class="form-control" name="Data[@i][@j]" value="@Model.Data[i][j]" /> <!-- Bootstrap class for form controls -->
                    </td>
                }
            </tr>
        }
    </table>

    <input type="submit" class="btn btn-primary" value="Save" /> <!-- Bootstrap classes for buttons -->
</form>

@page
@model Open_Excel_File_.NET_Core.Pages.ExcelOpenerModel

<form method="post">
    <table class="table table-bordered table-striped">
        <!-- Bootstrap classes for tables -->
        @for (int i = 0; i < Model.Data.Count; i++)
        {
            <tr>
                @for (int j = 0; j < Model.Data[i].Count; j++)
                {
                    <td>
                        <input type="text" class="form-control" name="Data[@i][@j]" value="@Model.Data[i][j]" /> <!-- Bootstrap class for form controls -->
                    </td>
                }
            </tr>
        }
    </table>

    <input type="submit" class="btn btn-primary" value="Save" /> <!-- Bootstrap classes for buttons -->
</form>

Navigating to the Page

In _Layout.cshtml, a link to the page is added to the navigation menu. By clicking this link, you will navigate to the Excel opener page.

<li class="nav-item">
    <a class="nav-link text-dark" asp-area="" asp-page="/ExcelOpener">ExcelOpener</a>
</li>

<li class="nav-item">
    <a class="nav-link text-dark" asp-area="" asp-page="/ExcelOpener">ExcelOpener</a>
</li>

Running the Application and Explaining the Output

Build the application by clicking on the "Build" button in Visual Studio or using the Ctrl + Shift + B shortcut. Run the application by pressing F5 or clicking on the "Start" button in Visual Studio.

Once the application is running, navigate to the "ExcelOpener" page by clicking on the corresponding link in the navigation bar. The page will display the data from Excel in a tabular format.

You can modify the data in the table and click the "Save" button to save the changes back to the Excel file.

Display Excel data in the Web Application

This tutorial demonstrated how to open an Excel file using IronXL in an ASP.NET Core application. IronXL provides a simple and convenient way to read and manipulate Excel files. You can explore the IronXL documentation for more advanced features and functionality.

IronXL offers a free trial, allowing you to evaluate its capabilities. If you find it useful for your projects, you can purchase a license starting from $749.