Published July 23, 2023
How to Open Excel File in ASP .NET Core
In this tutorial, we will learn how to open an Excel file using IronXL in an ASP.NET Core application using the IronXL library. We will cover the basic steps to read Excel files and 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 file with just a few lines of code. For a detailed tutorial on reading Excel file please refer here.
We 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 also .xlsx, which is an XML-based file format developed by Microsoft for representing spreadsheets.
Pre-requisites
Before we dive into the code, make sure you have the following:
- .NET Core 3.1 or higher installed.
- A suitable IDE for .NET development (Microsoft Visual Studio is recommended).
- Basic understanding of ASP.NET Core and Razor Pages.
Create a new .NET Core Project
- Open Microsoft Visual Studio, and select "Create a new project".
- Choose "ASP.NET Core Web App" from the list of project templates and click "Next".
- Provide a name for your project, for example, "Open_Excel_File_.NET_Core", select a location, and click "Next".
- 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
Next, we need to install the IronXL package in our project:
- Right-click on the project in the Solution Explorer and select "Manage NuGet Packages".
In the "Browse" tab, search for "IronXL" and click "Install" for the IronXL.Excel package.
- 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 where we will write the code to open the Excel file.
- Right-click on the "Pages" folder in the Solution Explorer and select "Add" > "Razor Page".
Enter "ExcelOpener" as the Page name and click "Add".
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>>();
}IRON VB CONVERTER ERROR developers@ironsoftware.comIn the OnGet method, we 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);
}
}IRON VB CONVERTER ERROR developers@ironsoftware.comKey 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 which allows us access to specific cells or ranges within an Excel file based on row and column indexes.
Short Explanation
In the ExcelOpener.cshtml.cs file, we have a class named ExcelOpenerModel that 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);
}
}IRON VB CONVERTER ERROR developers@ironsoftware.comKey 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>IRON VB CONVERTER ERROR developers@ironsoftware.comNavigating to the Page
In _Layout.cshtml, a link to the page is added in 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>IRON VB CONVERTER ERROR developers@ironsoftware.comRunning 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.
In this tutorial, we learned 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 $599.
