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 November 15, 2023

How to Edit a Spreadsheet in C#

Creating, managing, and analyzing data is a fundamental aspect of modern business and data-driven decision-making. In this context, C# .NET core, a versatile and powerful programming language, offers an ideal platform for working with spreadsheets.

Spreadsheets are indispensable tools for organizing and manipulating data, and C# provides a robust ecosystem for interacting with spreadsheet files.

Whether you need to automate data entry, generate reports pivot tables and charts, perform complex calculations, or extract valuable insights from large datasets, C# empowers you to excel in the world of spreadsheet applications. C#

In this article, we will explore the art of spreadsheet manipulation in C#, with the help of most advanced C# data manipulating Library named as 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 steps are a brief overview of how this can be done with ease in five simple steps

Install the C# library for editing Spreadsheets.
Utilize the WorkBook.Load method to open an Excel file.
Use the Worksheet.Value method to Set the Value of the sell.
Apply range.Sum method to sum the values of the cells.
Save the newly generated Spreadsheet as .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 easily be implemented in your web application or your .NET applications.

In this article, we 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 of 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 first we need 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.

Open the Visual Studio and click on file menu, there a drop-down menu will appear, in the dropdown menu click on the new, another side menu will appear in this side menu click on project.
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 C# option, after that click on next button.
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.
The last window will appear, in this window select the target framework and click on create.

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 you C#

In the Visual Studio go to Tools, a drop-down menu will appear in the dropdown menu click on the NuGet Package manager, a new side menu will appear in the side menu click on the NuGet Package Manager for Solutions.
A new window in 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.

Edit Excel Files Spreadsheets using IronXL

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

In this section we will load data set a spreadsheet from Excel files and 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

using IronXL;
WorkBook workBook = WorkBook.Load("test.xlsx");
WorkSheet ws = workBook.DefaultWorkSheet;
ws["A1"].Value = "Hello World Created by IronXL";
workBook.SaveAs("sample.xlsx");

using IronXL;
WorkBook workBook = WorkBook.Load("test.xlsx");
WorkSheet ws = workBook.DefaultWorkSheet;
ws["A1"].Value = "Hello World Created by IronXL";
workBook.SaveAs("sample.xlsx");

Imports IronXL
Private workBook As WorkBook = WorkBook.Load("test.xlsx")
Private ws As WorkSheet = workBook.DefaultWorkSheet
Private ws("A1").Value = "Hello World Created by IronXL"
workBook.SaveAs("sample.xlsx")

VB C#

The provided code utilizes the IronXL library in C# to open source manipulate Spreadsheet 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 ws. The code sets the value of cell A1 in the worksheet to "Hello World Created by IronXL". Finally, the modified Spreadsheet is saved as XLSX files, for example the one we created in this code sample is a new Excel file named "sample.xlsx", which we save using the SaveAs method.

Output File

How to Edit a Spreadsheet in C#: Figure 8

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;
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet ws = workBook.DefaultWorkSheet;
var range = ws["D2:D5"];
decimal sum = range.Sum();
ws["D6"].Value = sum;
ws["C6"].Value = "Total Price:";
workBook.SaveAs("sample.xlsx");

using IronXL;
WorkBook workBook = WorkBook.Load("sample.xlsx");
WorkSheet ws = workBook.DefaultWorkSheet;
var range = ws["D2:D5"];
decimal sum = range.Sum();
ws["D6"].Value = sum;
ws["C6"].Value = "Total Price:";
workBook.SaveAs("sample.xlsx");

Imports IronXL
Private workBook As WorkBook = WorkBook.Load("sample.xlsx")
Private ws As WorkSheet = workBook.DefaultWorkSheet
Private range = ws("D2:D5")
Private sum As Decimal = range.Sum()
Private ws("D6").Value = sum
Private ws("C6").Value = "Total Price:"
workBook.SaveAs("sample.xlsx")

VB C#

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 ws. Subsequently, a specific range of cells, from D2 to D5, is selected using the ws["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

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#

We 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 the support for other spreadsheet-related functionalities, providing a seamless and efficient solution exporting data used for C# applications. Its ability to handle spreadsheet operations without the need for Microsoft Office applications or Excel Interop further highlights its versatility and utility in C#

The sample Code and Example of Using Formulas in Spreadsheets can be found here. For the complete tutorial on Editing Spreadsheet visit at the following link.

Opt in to IronXL's trial today to begin exploring all of its features, and see how IronXL can help benefit your projects, whether you're wanting 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.