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 May 9, 2023

How to Export File to Excel in .NET 6

1.0 Introduction

Excel, a spreadsheet program from Microsoft, is available for Windows, macOS, Android, iOS, and iPadOS. The program includes the Visual Basic for Applications (VBA) macro programming language, graphing tools, pivot tables, and calculation skills. Excel is part of the Microsoft 365 software collection. Spreadsheets like Excel use a grid of cells grouped in numbered rows and letter-named columns to organize data manipulations, such as arithmetic operations. To meet the needs of the financial, engineering, and statistical fields, it offers integrated features. Additionally, it can display data as line graphs, histograms, and charts and has a limited three-dimensional graphical presentation. Data can be segmented to show how various factors impact it from multiple perspectives.

In this article, one of the most popular libraries, IronXL, will be used to compare and contrast how to interact with Microsoft Excel documents programmatically in .NET technologies, whether in CSV format or the standard Excel format.

2.0 IronXL Library Features

Microsoft Excel documents can be read and converted to CSV files using the C# IronXL .NET Excel library. IronXL is a stand-alone .NET software library that can read multiple spreadsheet formats and does not require Microsoft Office or Microsoft.Office.Interop.Excel to be installed.

With the simple C# API of IronXL, you can quickly read, modify, and create Excel spreadsheets in the .NET environment. IronXL fully supports .NET Core, .NET Framework, Xamarin, Mobile, Linux, macOS, and Azure.

IronXL is one of the best Excel spreadsheet libraries for C#, supporting both the .NET Framework and .NET Core.
IronXL supports nearly all .NET Frameworks, including console applications, Windows Forms, and web applications.
IronXL is compatible with the Windows, Linux, and macOS operating systems.
IronXL can quickly and effortlessly read Excel files.
IronXL can read a variety of Excel file types, including XLSX, CSV, XLS, XLST, TSV, XLSM, and others. We have a lot of options, including the ability to import, update, and export data tables and datasets.
IronXL is capable of generating calculations for Excel.
IronXL supports a number of Excel column data formats, including text, numbers, formulas, dates, currencies, and percentages.
IronXL supports the following Excel column data types: text, integers, formulas, dates, currencies, and percentages.

To learn more, click here.

3.0 Creating a .NET Core 6 Project

The following sections of this newsletter provide an example to illustrate how easily the IronXL library generates QR codes.

Open Visual Studio and select "New Project" from the "File" menu option.

In the resulting dialog box, select the "Console App" template and click "Next."

How to Export File to Excel in .NET 6: Figure 1

Enter any project name you choose for the Project name, and then enter the location of the new project in the Location field. Click the Next button to proceed.

How to Export File to Excel in .NET 6: Figure 2

Select a .NET Framework from the Framework drop-down option. Here, we're using .NET 6.0, which has long-term support. Then click Create.

How to Export File to Excel in .NET 6: Figure 3

Next, download the required IronXL library for the solution. To do so, use the following code on the package manager:

Install-Package IronXL.Excel

How to Export File to Excel in .NET 6: Figure 4

Alternatively, we can search for the package 'IronXL' using the NuGet Package Manager. This will list all the NuGet packages related to IronXL, and we can select the required package to download.

How to Export File to Excel in .NET 6: Figure 5

For our form, we have added a 'Save As' dialog box which allows us to save the generated IronXL images to the selected location.

4.0 Export Data using IronXL

Exporting data to Excel format can be easily done using IronXL. We can export data into various file types, such as XLS, XLSX, CSV, JSON, and XML, using IronXL.

4.1 Export Data to an Excel File

It is very simple to export data to .xlsx or .xls formats with just a few lines of code. Below is a sample source code to export data from an Excel file into a simple tabular format:

var workbook = IronXL.WorkBook.LoadExcel("Demo file.xlsx");
workbook.SaveAs("export.xlsx");
//or
workbook.SaveAs("export.xls");
//or
workbook.WorkSheets[0].SaveAs("export.xls");

var workbook = IronXL.WorkBook.LoadExcel("Demo file.xlsx");
workbook.SaveAs("export.xlsx");
//or
workbook.SaveAs("export.xls");
//or
workbook.WorkSheets[0].SaveAs("export.xls");

Dim workbook = IronXL.WorkBook.LoadExcel("Demo file.xlsx")
workbook.SaveAs("export.xlsx")
'or
workbook.SaveAs("export.xls")
'or
workbook.WorkSheets(0).SaveAs("export.xls")

VB C#

In the above example, we first load an existing Excel file using the LoadExcel method, which allows us to pass the file name with file path as a parameter. This loads the file into the 'Workbook' object. The 'Workbook' object provides a method called SaveAs, which allows us to save the Excel document as an XLSX or XLS file. This method saves the entire file into the chosen format.

We can also select a specific Excel worksheet by using the index value of the worksheet or by specifying the name of the sheet. Then, we can use the SaveAs function to export the data of the Excel worksheet into a separate file.

4.2 Export Data to a CSV File

We can also export data into CSV using IronXL. Below is an example code to export the data source into CSV format:

var excelDoc = IronXL.WorkBook.LoadExcel("Demo file.xlsx");
excelDoc.SaveAsCsv("export.csv");

var excelDoc = IronXL.WorkBook.LoadExcel("Demo file.xlsx");
excelDoc.SaveAsCsv("export.csv");

Dim excelDoc = IronXL.WorkBook.LoadExcel("Demo file.xlsx")
excelDoc.SaveAsCsv("export.csv")

VB C#

The above code is similar to exporting to XLS. IronXL provides a separate method called SaveAsCsv, which helps to export XLSX files into CSV files. It is easy and simple to use. In the above code, we are loading an XLSX file, converting it, and saving it into a CSV file with just a few lines of code. We can also pass user input such as a password as a parameter on the LoadExcel method.

4.3 Export Data to a JSON File

We can also save XLSX files as JSON by using the following code:

var excelDoc = IronXL.WorkBook.LoadExcel("Demo file.xlsx");
excelDoc.SaveAsJson("export.json");
//or
excelDoc.SaveAsXml("export.xml");

var excelDoc = IronXL.WorkBook.LoadExcel("Demo file.xlsx");
excelDoc.SaveAsJson("export.json");
//or
excelDoc.SaveAsXml("export.xml");

Dim excelDoc = IronXL.WorkBook.LoadExcel("Demo file.xlsx")
excelDoc.SaveAsJson("export.json")
'or
excelDoc.SaveAsXml("export.xml")

VB C#

The above sample code demonstrates that IronXL provides separate methods to save files as XML and JSON, which can be easily implemented.

With the help of IronXL, we can add features such as header rows, font sizes, formulas, colors, and more. Click here for more IronXL tutorials.

Conclusion

IronXL is one of the most widely used Excel utilities that can operate independently of any other external libraries. It doesn't require Microsoft Excel to be installed and uses a variety of channels to function.

For all tasks related to MS Excel documents that need to be implemented programmatically, IronXL offers an all-in-one solution. You can perform formula calculations, sort strings or numbers, cut and append data, find and replace, merge and unmerge cells, and save files. Additionally, it allows you to set cell data formats and perform spreadsheet data validation. You can also read and write CSV files, enabling interaction with Excel data.

IronXL is available for $749 at launch and provides the option for customers to pay a one-year membership charge for updates and product support. For an additional charge, IronXL provides security for unrestricted redistribution. Click here to learn more details about the pricing information.