Wie man eine Excel-Datei in ASP.NET Core öffnet

Was ist 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".

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

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

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 $799.