C# Excel Automation (Developer Tutorial)

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 perform a wide range of activities, including reading and writing/adding data, formatting cells, creating charts, producing reports, and automating Microsoft Office Excel-related tasks, 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 seamlessly incorporate Excel object model functions, carry out data-driven processes, and generate dynamic reports. In this article, we are going to explore 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 formulas, 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 existing ones.
• Cross-Platform Compatibility: IronXL 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 simplifies Excel-related tasks, making it accessible to developers with different levels of expertise by providing an easy-to-understand API with clear properties and methods.
• Data Export and Extraction: IronXL facilitates exporting Excel data to a variety of formats and extracting data from Excel files, allowing seamless interaction with databases or other systems.
• Documentation and Support: To help developers use IronXL's library for their Excel-related activities, the company provides extensive documentation, tutorials, and support.
• Automation and Efficiency: IronXL improves productivity, reduces manual labor, and helps create data-driven applications by automating Excel tasks.
• Integration & Customization: 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 across various domains, including finance, data analysis, reporting, business intelligence, and software development. It enables developers to manipulate Excel files programmatically and create reliable solutions that incorporate data manipulation and Excel integration. Refer here for more information.

Creating a New Project in Visual Studio

Click on "File" in the Visual Studio menu to launch the application. Choose "New project," then select "Window Forms App."

After choosing the file location, type the project file name in the designated text field. Subsequently, select the required .NET Framework and click the "Create" button as shown in the example below.

The structure of the Visual Studio project will then depend on the selected application type. 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 can then be added, and the code tested.

Installing IronXL Library

To use the IronXL library, open the NuGet Package Manager Console and type the following command to install it:

Install-Package IronXL.Excel

Another option is to search for the package "IronXL" using the NuGet Package Manager. You may choose from a list of all the NuGet packages related to IronXL which one needs 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 create 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);

        // Create a new worksheet in the workbook
        WorkSheet sheet = workbook.CreateWorkSheet("output");

        // Set values in cells
        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);

        // Create a new worksheet in the workbook
        WorkSheet sheet = workbook.CreateWorkSheet("output");

        // Set values in cells
        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)

		' Create a new worksheet in the workbook
		Dim sheet As WorkSheet = workbook.CreateWorkSheet("output")

		' Set values in cells
		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 demonstrates the automation of Excel with IronXL in a straightforward example:

1. Creating a New Excel Workbook: A new Excel workbook in XLSX format is created using WorkBook.Create(ExcelFileFormat.XLSX).
2. Accessing a Worksheet: CreateWorkSheet("output") adds a worksheet named "output" to the workbook.
3. Setting Cell Values: Cells A1 and B1 are assigned the values "Hello" and "World", respectively.
4. Saving the Workbook: The workbook is saved to a specified file path using SaveAs(filePath).

Additional Operations

• Data Manipulation: Utilize different data manipulation techniques such as filtering, sorting, and merging cells.
• Graphics and Charts: Create graphics and charts using Excel data.
• Data Integration and Export: Integrate Excel data with databases and export it to various formats.

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

Conclusion

Many industries, including finance, data research, reporting, and software development, use the IronXL, Excel object library extensively. It is a vital tool for companies and developers seeking to optimize Excel-related activities, facilitating the creation of dynamic, data-centric applications that handle Excel files programmatically and efficiently.

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. IronXL offers a free Community Edition with restrictions for non-commercial usage. Paid versions provide better features, support, and full capability starting at $749, available 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.