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");

USING IRONXL

C# Open Excel File Programmatically (Code Example Tutorial)

Updated January 8, 2024

We are all aware that the Excel spreadsheet has been around for a long time. It is primarily used for calculations and graphing data, making it perfect for creating financial models, analyzing the cost-effectiveness of various advertising campaigns, and many other types of analysis.

While it should be a straightforward process, creating an Excel file programmatically can be difficult because of the number of rows and columns involved. Many tasks are quickly done with Excel, such as selecting or sorting data. However, there is no easy way to do it when you need to create a new spreadsheet from scratch. Creating XLSX files programmatically requires many lines of code, something which can get complicated very quickly.

Excel files are harder to create programmatically because they need to be set up with data and formulas before they can be used. The different kinds of data you store in the spreadsheet can significantly impact how difficult it is to create an Excel file. If you need to create an Excel file with a lot of data, you will probably find it more complicated than if there are fewer columns and rows. Secondly, the formatting of your spreadsheet could also make creating an Excel file difficult. For example, suppose you want the columns in your spreadsheet to be aligned at all times. In that case, this will make generating a new spreadsheet more complicated than usual, because you will need to calculate where each column should be, based on other columns in the sheet, and how many rows there should be.

So, we have come up with a solution. We will show how easy it is to create and read Excel files with the IronXL C# library.

How to Open Excel File Programmatically in C#

Install Open Excel File C# Library
Utilize WorkBook C# class to create or open Excel file
Add data to Excel sheet programmatically in C#
Set cell styling and number formatting with intuitive APIs
Export Excel file as CSV or other formats

IronXL: C# Excel Library

IronXL is a C# Excel library that helps you make more robust spreadsheets. With IronXL, there are no limitations on the number of rows and columns in a spreadsheet. You can have as many rows and columns as you need without adding complex formulas for calculating column widths.

IronXL makes it possible to create workbooks with millions of rows and thousands of columns, which would not be possible without complex calculations or using any other spreadsheet software. With IronXL, there is no limit to the number of rows or columns in your spreadsheet, allowing you to design very high-dimensional models on a single sheet. IronXL is a complete library that will complete all Excel processes. Its fully kitted features will create and read Excel files in C#.

IronXL simplifies the number crunching and data analysis process by providing its users with powerful functions that they can use to work on the data without too much effort. We also do not need to install Microsoft Office on our machine.

The following section will describe how the library can be used to read and write Excel files in C#.

Create a C# .NET Project

Create a new C# Console project in Microsoft Visual Studio. I am using Visual Studio's 2022 version. You can use any version, but the latest version is recommended. When creating a project, choose .NET Framework > 3.0 because it is recommended. You can give any name to your project. You can create a GUI project, too, according to your needs. IronXL supports every template format of the .NET Framework. After completing the project, the next step will be to install the IronXL library.

Install the IronXL Library

Now it's time to install the IronXL library. You must follow the steps below to install it. We will install the library using the NuGet Package Manager.

Go to the Tools option from the main menu bar. Hover on the NuGet Package Manager and select the Manage NuGet Packages for Solution... option from the dropdown menu.

C# Open Excel File Programmatically (Code Example Tutorial), Figure 1: Navigate to NuGet Package Manager in Visual Studio Navigate to NuGet Package Manager in Visual Studio

This will open the NuGet Package Manager tab. Go to the browse tab and search for IronXL. Select IronXL from the search results.

C# Open Excel File Programmatically (Code Example Tutorial), Figure 2: Search for IronXL in NuGet Package Manager Search for IronXL in NuGet Package Manager

Install the selected library. You will see the IronXL library dependencies in the solution explorer.

C# Open Excel File Programmatically (Code Example Tutorial), Figure 3: IronXL package is added to the project IronXL package is added to the project

Now we can use the library in our project. Let's move to the program.cs file and write the code for creating an Excel file.

Code for creating Excel Files

Here is the code for creating an Excel file programmatically using the IronXL C# library.

/**
Create & Save Excel File
anchor-create-and-save-an-excel-file
**/
using IronXL;

//default file format is XLSX, we can override it using CreatingOptions
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
var sheet = workbook.CreateWorkSheet("example_sheet");
sheet ["A1"].Value = "Example";
//set value to multiple cells
sheet ["A2:A4"].Value = 5;
sheet ["A5"].Style.SetBackgroundColor("#f0f0f0");
//set style to multiple cells
sheet ["A5:A6"].Style.Font.Bold = true;
//set formula
sheet ["A6"].Value = "=SUM(A2:A4)";
if (sheet ["A6"].IntValue == sheet ["A2:A4"].IntValue)
{
    Console.WriteLine("Basic test passed");
}
workbook.SaveAs("example_workbook.xlsx");

/**
Create & Save Excel File
anchor-create-and-save-an-excel-file
**/
using IronXL;

//default file format is XLSX, we can override it using CreatingOptions
WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
var sheet = workbook.CreateWorkSheet("example_sheet");
sheet ["A1"].Value = "Example";
//set value to multiple cells
sheet ["A2:A4"].Value = 5;
sheet ["A5"].Style.SetBackgroundColor("#f0f0f0");
//set style to multiple cells
sheet ["A5:A6"].Style.Font.Bold = true;
//set formula
sheet ["A6"].Value = "=SUM(A2:A4)";
if (sheet ["A6"].IntValue == sheet ["A2:A4"].IntValue)
{
    Console.WriteLine("Basic test passed");
}
workbook.SaveAs("example_workbook.xlsx");

'''
'''Create & Save Excel File
'''anchor-create-and-save-an-excel-file
'''*
Imports IronXL

'default file format is XLSX, we can override it using CreatingOptions
Private workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
Private sheet = workbook.CreateWorkSheet("example_sheet")
Private sheet ("A1").Value = "Example"
'set value to multiple cells
Private sheet ("A2:A4").Value = 5
sheet ("A5").Style.SetBackgroundColor("#f0f0f0")
'set style to multiple cells
sheet ("A5:A6").Style.Font.Bold = True
'set formula
sheet ("A6").Value = "=SUM(A2:A4)"
If sheet ("A6").IntValue = sheet ("A2:A4").IntValue Then
	Console.WriteLine("Basic test passed")
End If
workbook.SaveAs("example_workbook.xlsx")

VB C#

We import the IronXL library at the program's top in the following code. After that, we initiated a WorkBook variable and created an Excel workbook in XLSX format. After that, we created a WorkSheet in the WorkBook using the CreateWorkSheet function, and in the parameter, we provided the worksheet's name. Similarly, we can create multiple worksheets. So, now our Excel book and sheet are ready to use.

Next, we enter data into the cells of an Excel document. We can access the cells by their name, and we can access the values of the cells using the "value" property. We can set the font size and style of any specific cell. We can write the formula for any particular cell or a group of cells. After that, we save the Excel file with "example_workbook.xlsx". It will be saved to the debug folder of our project.

Output

Here you can see the output of the created program.

C# Open Excel File Programmatically (Code Example Tutorial), Figure 4: Result file displayed in Microsoft Excel Result file displayed in Microsoft Excel

Conclusion

We can create, read and modify existing Microsoft Excel files or an XLSX file in C#. IronXL provides many other features for Microsoft Excel files. You can explore these features from our tutorial page. IronXL is the complete package for working with Excel. IronXL also offers a wide range of features to interact with Excel WorkBook, WorkSheet and Cells level such as converting between popular formats, cell data formatting, inserting math functions, and even managing charts.

IronXL is entirely free for development. You can use it for free in the development phase with the IronXL watermark. IronXL also offers a free free trial key for production. You can test it for 30 days, completely free. IronXL has a reasonable pricing plan. You can purchase it at a level that matches your requirements.

C# Open Excel File Programmatically (Code Example Tutorial), Figure 5: IronXL pricing plan IronXL pricing plan