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 July 23, 2023

How to Merge Cells in Excel Without Losing Data

1.0 Introduction

Microsoft Excel is a spreadsheet program made by Microsoft that is available for Windows, macOS, Android, iOS, and iPad. It features computation and calculation skills, graphing tools, pivot tables, and the macro programming language Visual Basic for Applications (VBA). Excel is a part of the Microsoft 365 software collection. Spreadsheet program like Excel uses a grid of multiple cells organized in numbered, multiple rows and letter-named columns to organize data manipulations like arithmetic calculations. It can handle requirements in finance, engineering, and statistics, thanks to a wide range of integrated functions. It also has a fully functional three-dimensional graphic display and can display combined data as charts, line graphs, and histograms. To analyze how various elements impact it from various angles, data can be segmented into sections.

2.0 Merge Multiple Cells into single cell using MS Excel

The built-in Merge and Center option in Excel is the quickest and simplest approach to integrate two or more cells. There are only 2 fast steps in the entire process:

Choose the adjacent Excel cells that you want to combine into one cell.
Click the Merge & Center button in the Home tab's Alignment group to merge two cells or multiple columns in Excel.

In this example, upper left cell A1 contains a list of fruits, and we want to combine data with two adjacent empty cells (B1 and C1) to create a single, sizable cell that can hold the complete list.

How to Merge Cells in Excel Without Losing Data: Figure 1 - Merging Cells

The text is centered, and the selected cells are combined into one large cell once you click Merge and Center, as shown in the above screenshot.

2.1 Excel's options to combine multiple cells

Select the choice you want from the drop-down menu by clicking the tiny drop-down arrow next to the Merge & Center button, which will give you access to a few more merging options supplied by Excel such as Merge Across, Unmerge Cells.

How to Merge Cells in Excel Without Losing Data: Figure 2 - Combine Cells

Using the Merge Across command, each row's individually chosen cells are combined.

Using the Merge Cells command, you can combine the selected cells into one without centering the text and without losing data.

Simply pick the merged cell and click the desired alignment in the Alignment group on the Home page to change the text alignment of only the data after merging all the cells.

There are a few things to remember when utilizing Excel's built-in functions to combine adjacent cells:

Because only the content of the upper-left cell will remain after merging, all data in the other cells will be removed. Therefore, make sure that all the data you want to include in a merged cell is entered in the left most cell of the chosen range.
The selected cells are likely in Edit mode if the Merge and Center button is grayed out. Try merging cells after exiting Edit mode by pressing the Enter key.

3.0 IronXL Library Features

You can quickly read and change Microsoft Excel documents with the IronXL library in C#. Without installing Microsoft Excel or relying on Microsoft Office Interop Excel, IronXL is a standalone .NET software library that can read other spreadsheet formats.

One of the best Excel spreadsheet libraries for C# is IronXL, which works with both .NET Framework and .NET Core. It supports a number of .NET Frameworks, including Console, Windows Forms, and Web Applications. IronXL makes it simple and quick to read Excel files with or without merged cells. Numerous Excel file types, including XLSX, XLS, CSV, TSV, XLST, XLSM, and others are supported. Numerous procedures, including import, edit, export of data tables, export of datasets, and others, are available. With IronXL, you can export and save files with a variety of extensions, including XLS, CSV, TSV, JSON, and more.

You can easily read, alter, and create Excel spreadsheet files in the .NET environment with IronXL's user-friendly C# API. It offers full support for Azure, .NET Core, .NET Framework, Xamarin, Mobile, Linux, and macOS.

IronXL supports various Excel column data formats, including text, integers, formulas, dates, currencies, and percentages, and it is capable of carrying out calculations in a manner similar to Excel.

3.1 Creating a New Project in Visual Studio

Open Visual Studio, select "New project" and "Console App" from the File menu. In this article, we'll be using a C# Console Application.

How to Merge Cells in Excel Without Losing Data: Figure 3 - New Project

In the relevant text box, type the project name and the file path. Next, select the necessary .NET Framework by clicking the Create button. The project will now create the program.cs file's structure and open if you choose a Console application, allowing you to enter the program's code and build or run it.

How to Merge Cells in Excel Without Losing Data: Figure 4 - Project Configuration

The solution's necessary IronXL library must then be downloaded. By entering the following code in the package manager, you can download the package:

Install-Package IronXL.Excel

How to Merge Cells in Excel Without Losing Data: Figure 5 - IronXL

The "IronXL" package can also be found and downloaded using the NuGet Package Manager. Dependency management in your project is made simple with the NuGet Package Manager.

How to Merge Cells in Excel Without Losing Data: Figure 6 - NuGet Package Manager

3.3 Merge Cell using IronXL

IronXL allows us to merge multiple columns/cells in the existing Excel sheets. Below is the sample code to merge multiple cells.

var exceldoc = IronXL.WorkBook.LoadExcel("demo.xlsx");
WorkSheet workSheet = exceldoc.DefaultWorkSheet;
var range = workSheet["A1:C1"];
workSheet.Merge(range.RangeAddressAsString);
exceldoc.Save();

var exceldoc = IronXL.WorkBook.LoadExcel("demo.xlsx");
WorkSheet workSheet = exceldoc.DefaultWorkSheet;
var range = workSheet["A1:C1"];
workSheet.Merge(range.RangeAddressAsString);
exceldoc.Save();

IRON VB CONVERTER ERROR developers@ironsoftware.com

VB C#

In the above example code, first we are loading the existing Excel workbook into the IronXL workbook object. Then we are setting the default worksheet. In the next step we are setting the worksheet range then with the help of the Merge function available in the worksheet, IronXL will combine multiple cells in Excel. After that we are using the Save function to save the Excel again.

A group of cells can be merged with Merge. Only the first column's and row's value of the merged region's cells will be displayed; the merging operation will not remove any value or data from the other cells. In IronXL, the value of those merged cells is still available.

4.0 Conclusion

A very well-liked Excel add-in called IronXL doesn't rely on outside libraries. It is a standalone solution and doesn't need Microsoft Excel installed. It works with a variety of platforms.

With IronXL, you may programmatically carry out a vast array of functions on MS Excel documents. You can sort strings or numbers, trim and add data in empty cells, look up and replace values in blank cell, merge and unmerge cells, save files, concatenate function and other operations. You may specify cell data types and evaluate spreadsheet data with it as well. IronXL also has CSV file reading and writing capabilities.

When IronXL is released, it will cost $599 to acquire. Additionally, customers have the choice to pay an annual membership fee for product upgrades and support. IronXL offers unrestricted redistribution rights for an extra fee. You can click here to go to the appropriate source and get more specific pricing details.