Skip to footer content
USING IRONXL

How to Edit a Spreadsheet in C#

This article will explore the art of spreadsheet manipulation in C#, with the help of the most advanced C# data manipulating library named IronXL without interacting with the user interface.

How to Edit a Spreadsheet in C#

The ability to edit and manipulate the data of Excel files using C# can be extremely helpful to businesses and developers. The following is a brief overview of how this can be done with ease in five simple steps:

  1. Install the C# library for editing spreadsheets.
  2. Utilize the WorkBook.Load method to open an Excel file.
  3. Use the Worksheet.Value method to set the value of the cell.
  4. Apply the Range.Sum method to sum the values of the cells.
  5. Save the newly generated spreadsheet in .XLSX format using the SaveAs method.

IronXL

IronXL is a powerful tool that has become a game-changer in the realm of C# spreadsheet processing. As a comprehensive library designed specifically for C#.

Whether you're working on a financial application, data-driven dashboard, or any project that involves handling tabular data, IronXL provides a robust and user-friendly solution that simplifies complex spreadsheet operations in C# development that can easily be implemented in your Web Application or your .NET applications.

This article will explore the key features and benefits of IronXL, shedding light on how it enhances the efficiency and functionality of C# applications that deal with Excel spreadsheet processing. Using IronXL, you can manipulate Excel spreadsheets without the need for Microsoft Office applications, also you can create advanced Excel spreadsheets without Microsoft Excel and Excel interop.

Create a New Visual Studio Project

Before installing IronXL, it is necessary to create a new Visual Studio C# project or load an existing one. Below are the steps to create a new project in Visual Studio.

  1. Open Visual Studio and click on the File menu, a drop-down menu will appear. In the dropdown menu, click on the New menu, and another side menu will appear. Click on Project.

    How to Edit a Spreadsheet in C#, Figure 1: Navigate to creating a new project in Visual Studio Navigate to creating a new project in Visual Studio

  2. A new window will appear. In this new window, click on the search bar and write Console Application in the search bar and select the one with the C# option. After that, click on the Next button.

    How to Edit a Spreadsheet in C#, Figure 2: Create a new project dialog in Visual Studio Create a new project dialog in Visual Studio

  3. A new configuration window will open. In this new window write the project Name, set the project location and then click on the Next button.

    How to Edit a Spreadsheet in C#, Figure 3: Configure the new project Configure the new project

  4. The last window will appear, in this window select the target framework and click on Create button.

    How to Edit a Spreadsheet in C#, Figure 4: Target Framework selection Target Framework selection

Installing Spreadsheet Library IronXL

Once the Project is created now let's install the free IronXL C# library, below are the steps to install IronXL in your C# project.

  1. In Visual Studio, go to Tools, a drop-down menu will appear. In the dropdown menu, click on the NuGet Package Manager, a side menu will appear. Click on the Manage NuGet Packages for Solution.

    How to Edit a Spreadsheet in C#, Figure 5: Navigate to NuGet Package Manager Navigate to NuGet Package Manager

  2. A new window will appear. In this new window, go to the browser tab and in the search bar, write IronXL. A list of IronXL Packages will appear, select the latest package and click on install.

    How to Edit a Spreadsheet in C#, Figure 6: Install the IronXL package in the NuGet Package Manager Install the IronXL package in the NuGet Package Manager

Edit Excel Files Spreadsheets using IronXL

IronXL offers many spreadsheet manipulation features that allow users to change the values of cells, font size, background color of columns, cell ranges, and many more.

This section will demonstrate how to load a dataset from a spreadsheet Excel file, edit, then format, and manipulate them.

Changing Spreadsheet Entries Using C#

Below is the code example that changes Excel entries using C# programming language code with a few lines of code.

Input Excel File

How to Edit a Spreadsheet in C#, Figure 7: The input Excel file The input Excel file

using IronXL;

// Load an existing Excel file
WorkBook workBook = WorkBook.Load("test.xlsx");

// Get the default worksheet
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Set the value of cell A1
workSheet["A1"].Value = "Hello World Created by IronXL";

// Save the modified workbook as a new file
workBook.SaveAs("sample.xlsx");
using IronXL;

// Load an existing Excel file
WorkBook workBook = WorkBook.Load("test.xlsx");

// Get the default worksheet
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Set the value of cell A1
workSheet["A1"].Value = "Hello World Created by IronXL";

// Save the modified workbook as a new file
workBook.SaveAs("sample.xlsx");
Imports IronXL

' Load an existing Excel file
Private workBook As WorkBook = WorkBook.Load("test.xlsx")

' Get the default worksheet
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Set the value of cell A1
Private workSheet("A1").Value = "Hello World Created by IronXL"

' Save the modified workbook as a new file
workBook.SaveAs("sample.xlsx")
$vbLabelText   $csharpLabel

The provided code utilizes the IronXL library in C# to manipulate spreadsheets in Excel files. It begins by importing the IronXL namespace. Then, it loads an existing Excel file named "test.xlsx" into a WorkBook object.

The default worksheet of this workbook is accessed and assigned to a WorkSheet variable, denoted as "workSheet". The code sets the value of cell A1 in the worksheet to "Hello World Created by IronXL". Finally, the modified spreadsheet is saved as an XLSX file, "sample.xlsx", using the SaveAs method.

Output File

How to Edit a Spreadsheet in C#, Figure 8: The output file The output file

Adding Spreadsheet Cell Values Using Formulas

IronXL offers a free version of this feature where you can use formulas on spreadsheets and write them to other cells. Below is the example using C# IronXL Spreadsheet Library.

using IronXL;

// Load the existing Excel file
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Get the default worksheet
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Select a range of cells from D2 to D5
var range = workSheet["D2:D5"];

// Calculate the sum of the values in the range
decimal sum = range.Sum();

// Set the sum in cell D6 and label in C6
workSheet["D6"].Value = sum;
workSheet["C6"].Value = "Total Price:";

// Save the modified workbook
workBook.SaveAs("sample.xlsx");
using IronXL;

// Load the existing Excel file
WorkBook workBook = WorkBook.Load("sample.xlsx");

// Get the default worksheet
WorkSheet workSheet = workBook.DefaultWorkSheet;

// Select a range of cells from D2 to D5
var range = workSheet["D2:D5"];

// Calculate the sum of the values in the range
decimal sum = range.Sum();

// Set the sum in cell D6 and label in C6
workSheet["D6"].Value = sum;
workSheet["C6"].Value = "Total Price:";

// Save the modified workbook
workBook.SaveAs("sample.xlsx");
Imports IronXL

' Load the existing Excel file
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")

' Get the default worksheet
Private workSheet As WorkSheet = workBook.DefaultWorkSheet

' Select a range of cells from D2 to D5
Private range = workSheet("D2:D5")

' Calculate the sum of the values in the range
Private sum As Decimal = range.Sum()

' Set the sum in cell D6 and label in C6
Private workSheet("D6").Value = sum
Private workSheet("C6").Value = "Total Price:"

' Save the modified workbook
workBook.SaveAs("sample.xlsx")
$vbLabelText   $csharpLabel

This code employs the IronXL library in C# to manipulate an Excel file named "sample.xlsx." It begins by loading the existing Excel file into a WorkBook object.

The default worksheet of this workbook is accessed and assigned to a WorkSheet variable, denoted as "workSheet". Subsequently, a specific range of cells, from D2 to D5, is selected using the workSheet["D2:D5"] notation.

The sum of the values within this range is then calculated using the Sum method and stored in a variable named "sum".

The code updates cell D6 in the new worksheet with this calculated sum, and cell C6 is assigned the label "Total Price:". Finally, the modified workbook is saved back to the same file, "sample.xlsx," using the SaveAs method.

Output File

How to Edit a Spreadsheet in C#, Figure 9: The output file The output file

Conclusion

This article has explored the significance of C# in spreadsheet manipulation and introduced IronXL as a powerful library that enhances the capabilities of C#.

This tutorial discussed the process of creating a new Visual Studio C# project and outlined the steps to install IronXL. The article also delved into practical examples of using IronXL to edit Excel files, demonstrating how to change cell values and apply formulas to spreadsheet data.

IronXL proves to be a valuable tool for developers working on projects involving data analysis, reporting, and support for other spreadsheet-related functionalities, providing a seamless and efficient solution for exporting data used for C# applications.

Furthermore, 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, merging cells, inserting math functions, and even managing charts and adding images.

The sample code and example of using formulas in spreadsheets can be found in this example. For the complete tutorial on editing spreadsheets, visit the following how-to.

Opt into IronXL's trial today to begin exploring all of its features, and see how IronXL can help benefit your projects, whether you want to create a new Excel file, edit existing worksheets, or add new cell data to a spreadsheet or cell range.

If you find IronXL to be beneficial to your working environment and wish to continue using the features it has to offer for all your spreadsheet data manipulation needs and more, you can purchase a license once your trial ends.

Frequently Asked Questions

What is a comprehensive C# library for advanced spreadsheet manipulation?

IronXL is a comprehensive C# library designed for advanced spreadsheet manipulation without relying on Microsoft Office applications. It simplifies complex spreadsheet operations in C# development.

How can I edit a spreadsheet using C#?

To edit a spreadsheet using C# and IronXL, you need to install the IronXL library, load an Excel file using WorkBook.Load, modify cell values with the Worksheet.Value method, and save the file using the SaveAs method.

What are the steps to create a new Visual Studio project?

To create a new project, open Visual Studio, navigate to File > New > Project, search for 'Console Application' with the C# option, configure the project name and location, and select the target framework.

How do you install a spreadsheet manipulation library in a Visual Studio project?

Install IronXL by navigating to Tools > NuGet Package Manager > Manage NuGet Packages for Solution, search for IronXL in the browser tab, select the latest package, and click install.

Can a library handle Excel operations without Microsoft Excel?

Yes, IronXL allows you to perform Excel operations such as editing, creating, and formatting spreadsheets without needing Microsoft Excel or Excel Interop.

What are some features of a library for spreadsheet manipulation?

IronXL supports changing cell values, applying formulas, converting file formats, cell data formatting, merging cells, inserting math functions, managing charts, and adding images.

How do you sum cell values in a spreadsheet using a library?

To sum cell values, select the range using workSheet["D2:D5"], use the range.Sum() method to calculate the sum, and then set the result in a cell using workSheet["D6"].Value.

What is the advantage of using a library in C# projects?

IronXL enhances the efficiency and functionality of C# applications involving Excel spreadsheet processing, providing a robust and user-friendly solution for handling tabular data.

Is there a trial available for the library?

Yes, IronXL offers a trial that allows you to explore its features. Once the trial ends, you can purchase a license to continue using its capabilities for spreadsheet data manipulation.

Where can I find more examples of using a C# library for spreadsheets?

You can find more examples and complete tutorials on IronXL's features, such as using formulas and editing spreadsheets, on their official website, particularly in their 'how-to' and 'examples' sections.

Regan Pun
Software Engineer
Regan graduated from the University of Reading, with a BA in Electronic Engineering. Before joining Iron Software, his previous job roles had him laser-focused on single tasks; and what he most enjoys at Iron Software is the spectrum of work he gets to undertake, whether it’s adding value to sales, technical support, product development or marketing. He enjoys understanding the way developers are using the Iron Software library, and using that knowledge to continually improve documentation and develop the products.
Talk to an Expert Five Star Trust Score Rating

Ready to Get Started?

Nuget Passed