C# Excel Automation (Developer Tutorial)

Introduction

Microsoft Excel worksheet automation in C# allows developers to generate, edit, and manage Microsoft Excel files automatically by utilizing the power of programming to interface with the Microsoft Excel object model. C# developers may do a wide range of activities, including reading and writing/adding data, formatting cells, making charts, producing reports, and automating Microsoft Office Excel-related actions, by using libraries like Microsoft.Office.Interop.Excel or third-party solutions for automating any unattended, non-interactive client program to run Microsoft Office applications.

Automating Microsoft Excel's new workbook object in C#, Visual Basic, or Microsoft Visual C++ offers a stable and adaptable framework for managing and processing Excel worksheet data with simplicity and precision. It allows developers to design Microsoft Office applications that smoothly incorporate Excel object model functions, carry out data-driven processes, and generate dynamic reports. In this article, we are going to see more about Excel automation in C#.

How to use C# Excel Automation

1. Create a Visual Studio project.
2. Install the library IronXL.
3. Initialize Excel application object interface to automate Microsoft Excel.
4. Access the worksheet and manipulate data using the object.
5. Save and close the workbook. Also, release the resource object.

IronXL

IronXL is a potent .NET library intended to make working with Excel files in C#, VB.NET, and other .NET languages easier. It supports both the XLS and XLSX formats. This library allows developers to create, read, write, and manipulate Excel spreadsheets more quickly and easily. It also offers a wide range of tools and features.

IronXL's salient characteristics and functions comprise:

• Data handling: With IronXL, reading, writing, and manipulating data in Excel workbooks is simple. You can access cell values using a two-dimensional array, add formulae, format data, and do calculations.
• Excel File Creation and Editing: Developers can add, remove, and manage worksheets in addition to creating new Excel files and editing ones that already exist. They can also deal with different Excel parts.
• IronXL has cross-platform compatibility, meaning it can be used in a variety of application contexts and is compatible with several .NET platforms, such as Xamarin, .NET Core, and .NET Framework.
• Versatility and Compatibility: It is compatible with several Excel versions and supports both the older XLS and the more recent XLSX Excel formats.
• Support for Legacy and Modern Excel Formats: It can accommodate a variety of file format needs by supporting both classic Excel file formats (XLS, which dates back to Excel 97–2003) and more recent XML-based forms (XLSX, which dates back to Excel 2007).
• Usefulness: The library makes Excel-related tasks easier to understand and accessible to developers with different levels of expertise by providing a simple API with properties and methods that are easy to understand.
• Data Export and Extraction: IronXL makes it easier to export Excel data to a variety of formats and extract data from Excel files, allowing for easy interaction with databases or other systems.
• Documentation and Support: To help developers use IronXL's library for their Excel-related activities, the company offers extensive documentation, tutorials, and support.
• Automation and Efficiency: IronXL helps users be more productive, spends less time on manual labor, and creates data-driven, effective applications by automating Excel activities.
• Integration & Customisation: It offers options for exporting data from Excel to numerous formats and facilitates the production of customized reports or data-driven solutions. It also interfaces nicely with databases and other systems.

IronXL is used in many different areas, including finance, data analysis, reporting, business intelligence, and software development. It gives developers the ability to manipulate Excel files programmatically and create reliable solutions that incorporate data manipulation and Excel integration. Refer here to know more.

Creating a New Project in Visual Studio

Click File from the File menu to launch the Visual Studio application. Click "New project," then choose "Window Forms App."

After choosing the file location, type the project file name in the designated text field. Subsequently, select the required .NET Framework by clicking the Create button, as seen in the sample below.

The structure of the Visual Studio project will then depend on the selected application. You may build or execute the application using the Console, Windows, or online application to add code. You can add input logic to the program.cs file.

The library may then be added, and the code tested.

Installing IronXL Library

Install the IronXL library since the upcoming patch depends on it. Lastly, to finish this, open the NuGet Package Manager Console and type the following command:

Install-Package IronXL.Excel

Another option is to look for the package "IronXL" using the NuGet Package Manager. We may select from this list of all the NuGet packages linked to IronXL which one has to be downloaded.

Excel Automation Using IronXL

An example illustrating the use of IronXL in C# for simple Excel automation can be seen below. Here's an example of how to make a new Excel file, fill in the cells, then save the workbook.

using IronXL;
class Program
{
    static void Main(string [] args)
    {
        // Create a new Excel workbook
        WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
        // Access the first worksheet in the workbook
        WorkSheet sheet = workbook.CreateWorkSheet("output");
        // Set cell values, var row
        sheet ["A1"].Value = "Hello";
        sheet ["B1"].Value = "World";
        // Save the workbook to a specific file path
        string filePath = @"output.xlsx";
        workbook.SaveAs(filePath);
        Console.WriteLine("File created and saved successfully.");
    }
}

using IronXL;
class Program
{
    static void Main(string [] args)
    {
        // Create a new Excel workbook
        WorkBook workbook = WorkBook.Create(ExcelFileFormat.XLSX);
        // Access the first worksheet in the workbook
        WorkSheet sheet = workbook.CreateWorkSheet("output");
        // Set cell values, var row
        sheet ["A1"].Value = "Hello";
        sheet ["B1"].Value = "World";
        // Save the workbook to a specific file path
        string filePath = @"output.xlsx";
        workbook.SaveAs(filePath);
        Console.WriteLine("File created and saved successfully.");
    }
}

Imports IronXL
Friend Class Program
	Shared Sub Main(ByVal args() As String)
		' Create a new Excel workbook
		Dim workbook As WorkBook = WorkBook.Create(ExcelFileFormat.XLSX)
		' Access the first worksheet in the workbook
		Dim sheet As WorkSheet = workbook.CreateWorkSheet("output")
		' Set cell values, var row
		sheet ("A1").Value = "Hello"
		sheet ("B1").Value = "World"
		' Save the workbook to a specific file path
		Dim filePath As String = "output.xlsx"
		workbook.SaveAs(filePath)
		Console.WriteLine("File created and saved successfully.")
	End Sub
End Class

This code shows how to automate Excel with IronXL in a simple example:

A new Excel workbook in XLSX format is created by using WorkBook.Create(ExcelFileFormat.XLSX). Getting to a Worksheet: CreateWorkSheet("Sheet1") appends "Sheet1" as a worksheet to the workbook. Sheet ["A1"] and Sheet ["B1"] are where cell values are set value = "Hello", value = "World". Enter values in A1 and B1 correspondingly. Workbook: Preserving its contents. Workbook saves to a given file path when SaveAs(filePath) is used.

Additional Operation

• Data manipulation: Use different data manipulation techniques, such as filtering, sorting, and cell merging.
• Graphics and Charts: Utilising Excel data, create graphics and charts.
• Excel data may be integrated with databases, and it can also be exported to various formats.

Developers may automate Excel processes, modify data, format cells, and carry out a variety of other actions programmatically in C# using IronXL's broad collection of functions for working with Excel files. Modify these actions to the demands of your particular use case. IronXL enables dependable Excel automation by handling exceptions and ensuring appropriate resource cleaning. To learn more about the code refer here.

Conclusion

Many businesses, including banking, data research, reporting, and software development, use the IronXL, Excel object library extensively. It is a vital tool for companies and developers looking to optimize Excel-related activities since it makes it possible to create dynamic, data-centric apps that handle Excel files effectively and programmatically.

In summary, IronXL streamlines Excel automation in C# and provides a dependable, feature-rich solution for programmatically managing Excel files, enabling effective data manipulation, and boosting productivity in .NET applications. A free Community Edition with restrictions for non-commercial usage was made available by IronXL. Better features, support, and full capability were provided by paid versions which start at 749$, which could be obtained through subscription- or perpetual-based licensing models. For up-to-date and comprehensive licensing details, visit IronXL's official website. Go here for further details on Iron Software products.