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 IronXL; using IronXL.Styles; using IronSoftware.Drawing; 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 November 15, 2022

How to Generate an Excel File on Razor Pages

Introduction

Razor Pages are a new way to build web pages. With Razor Page, the source code that renders the web page is built in C# instead of being generated by a server-side page. Razor Pages are an attempt to make web pages more dynamic, along with the basic functionalities that we see with traditional ASP.NET Web Pages (.ASPX file extension). This is done by using Razor syntax and some properties.

Excel files are difficult to create programmatically. Some programming languages help generate, import and export Excel data (with or without data formulas) but can't handle many of the complicated features or file functions in Excel input file, such as font colors, formatting, etc. There are multiple libraries or free tool to generate or export Excel in ASP.NET Core, like POI Java project NPOI package (which can also be used with PowerPoint files) and Office Interop. I personally choose to use a feature-rich library that is very simple, easy-to-use, and that doesn't require Microsoft Office. This library should also support multiple versions of .NET.

In this article, I'll teach you the very easy method using the IronXL C# library for generating and exporting Excel spreadsheets with dummy data and row headers in Razor Pages (Page model).

How to Generate Excel Files in Razor Pages

Install C# library generate Excel file in Razor
Access or create new Excel file in a method
Add data and style to the WorkBook object in Razor
Use File method to automatically download Excel file when the button is pressed
Inspect the generated Excel file

IronXL: C# Excel Library

IronXL is a C# Excel library that provides methods and functions for operating on large data sets. It makes use of parallel processing to obtain more computing power. It doesn't require Microsoft Office to be installed. It's easy for programmers to use because it doesn't require any knowledge about how the process works behind the scenes. It supports XLS as well as XLSX files.

We can import and export Excel files using IronXL. We can also use IronXL for creating Excel formulas and office documents. It is the perfect solution for those who don't want to install Microsoft Office on their machines.

Let's see how we can use IronXL in Razor pages to import and export Excel files.

Generate Excel File in Razor Pages

For creating Excel sheets in Razor Pages, we'll need to open Visual Studio, create an ASP.NET Core Web Application and install IronXL.

Prerequisites

For creating Excel files in Razor Pages, there are some prerequisites:

Visual Studio 2022 (Latest Version)
.NET Framework 6 or 7
.NET Core Web Application in Visual Studio

Install IronXL Library

You can install it using the NuGet Package Manager Console. Open the Console and write the following command to install the IronXL library in a .NET Core Web Application.

PM> Install-Package IronXL.Excel

Code for Generating Excel file

Now, it's time to write code. Open the "Index.cs" file from the Pages folder and add the following method:

public FileResult OnPostExport()
{
    WorkBook workBook = new WorkBook(IronXL.ExcelFileFormat.XLSX);
    WorkSheet workSheet = workBook.CreateWorkSheet("data");

    //Add data and styles to the new worksheet

    workSheet["A1"].Value = "Product EN";
    workSheet["B1"].Value = "SKU";
    workSheet["C1"].Value = "Customer";

    workSheet["A1:C1"].Style.Font.Bold = true;

    workSheet["A2"].Value = "Iron Rods";
    workSheet["A3"].Value = "Mobile Phones";
    workSheet["A4"].Value = "Chargers";

    workSheet["B2"].Value = "105";
    workSheet["B3"].Value = "285";
    workSheet["B4"].Value = "301";

    workSheet["C2"].Value = "Adam";
    workSheet["C3"].Value = "Ellen";
    workSheet["C4"].Value = "Tom";

    return File(workBook.ToStream().ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Grid.xlsx");
}

public FileResult OnPostExport()
{
    WorkBook workBook = new WorkBook(IronXL.ExcelFileFormat.XLSX);
    WorkSheet workSheet = workBook.CreateWorkSheet("data");

    //Add data and styles to the new worksheet

    workSheet["A1"].Value = "Product EN";
    workSheet["B1"].Value = "SKU";
    workSheet["C1"].Value = "Customer";

    workSheet["A1:C1"].Style.Font.Bold = true;

    workSheet["A2"].Value = "Iron Rods";
    workSheet["A3"].Value = "Mobile Phones";
    workSheet["A4"].Value = "Chargers";

    workSheet["B2"].Value = "105";
    workSheet["B3"].Value = "285";
    workSheet["B4"].Value = "301";

    workSheet["C2"].Value = "Adam";
    workSheet["C3"].Value = "Ellen";
    workSheet["C4"].Value = "Tom";

    return File(workBook.ToStream().ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Grid.xlsx");
}

Public Function OnPostExport() As FileResult
	Dim workBook As New WorkBook(IronXL.ExcelFileFormat.XLSX)
	Dim workSheet As WorkSheet = workBook.CreateWorkSheet("data")

	'Add data and styles to the new worksheet

	workSheet("A1").Value = "Product EN"
	workSheet("B1").Value = "SKU"
	workSheet("C1").Value = "Customer"

	workSheet("A1:C1").Style.Font.Bold = True

	workSheet("A2").Value = "Iron Rods"
	workSheet("A3").Value = "Mobile Phones"
	workSheet("A4").Value = "Chargers"

	workSheet("B2").Value = "105"
	workSheet("B3").Value = "285"
	workSheet("B4").Value = "301"

	workSheet("C2").Value = "Adam"
	workSheet("C3").Value = "Ellen"
	workSheet("C4").Value = "Tom"

	Return File(workBook.ToStream().ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Grid.xlsx")
End Function

VB C#

In the above code, I create a new Excel Workbook containing one worksheet. After that, I populate the cell's value of the sheet with dummy data. Afterward, I returne the generated file in a server response. In the first parameter, I convert the workbook to stream (because it is important to convert the file into a stream before downloading it). I set the data type in the second parameter, and specify the name of the file download in the third parameterI passed the file name.

Create a button to download Excel file

Replace the existing code in "Index.cshtml" with the code below:

@page
@model IndexModel
@{
    ViewData["Title"] = "Home page";
}

<div class="text-center">
    <h1 class="display-4">IronXL Generate Excel File</h1>
    <p class="m-5">IronXL is an Excel Library for C# and allows developers to read and edit Excel data from XLS and XLSX documents without using Microsoft.Office.Interop.Excel</p>
    <form method="post" asp-page-handler="Export">
        <button class="btn btn-success p-3">Generate Excel File!</button>
    </form>
</div>

@page
@model IndexModel
@{
    ViewData["Title"] = "Home page";
}

<div class="text-center">
    <h1 class="display-4">IronXL Generate Excel File</h1>
    <p class="m-5">IronXL is an Excel Library for C# and allows developers to read and edit Excel data from XLS and XLSX documents without using Microsoft.Office.Interop.Excel</p>
    <form method="post" asp-page-handler="Export">
        <button class="btn btn-success p-3">Generate Excel File!</button>
    </form>
</div>

HTML

In the HTML code above, I create a form with the property "asp-page-handler" set to "Export". This will enable the application to export an Excel file to our PC. I also create a button with the text "Generate Excel File!". When the user clicks on this button, it'll trigger the browser to download the generated Excel file.

Run the Project

Now run the project and you'll see the following screen in the browser. Click on the green button to download the Excel file.

How to Generate an Excel File in Razor Pages, Figure 1: IronXL Generate Excel Sheets

You'll see the downloaded file at the bottom when you click on the button "Generate Excel File!".

How to Generate an Excel File in Razor Pages, Figure 2: IronXL Downloaded File

Open the Generated Excel File

The content in the generated Excel file is consisten with what written in the code, with perfect formatting. You can see the snapshot of generated Excel file below.

How to Generate an Excel File in Razor Pages, Figure 3: IronXL Generated File

IronXL better than Competitors

The IronXL library is a more powerful tool to work with Excel that supports XLS as well as XLSX and does not have the limitations of NPOI. NPOI runs much slower compared to IronXL, as it is written in Java instead of in C#. Therefore, you will want to go for IronXL instead of for POI when you want to generate large/multiple spreadsheets in ASP.NET Core applications.

IronXL is a great library for creating and editing Excel files without using Microsoft Office. Unlike Microsoft Interop, IronXL doesn't need to call Microsoft Excel's ActiveX in the background to perform Excel operations and to export to Excel. IronXL can work without the Microsoft Office suite. IronXL can also export CSV files to the Excel format.

Conclusion

In this article, we learnt how we can use IronXL to generate an Excel file in Razor Pages, in a manner that does not require calling Microsoft Excel ActiveX and downloading the file. We can also read Excel files in Razor by uploading files using file upload control and filtering only Excel files.

I suggest you visit the following link for more tutorials.

IronXL is free to use for non-commerical development purposes. A 30-day free trial is available for testing in production. Look at the pricing plans for more details about prices and licensing.