When working with Excel in C#, copying rows, columns, and cells can feel like a simple task, but it often comes with a catch—how to keep the formatting intact. Whether you’re organizing data for a report, creating a presentation, or just trying to keep your spreadsheet looking sharp, maintaining the original look is crucial. In this article, we will demonstrate the methods to copy rows, columns, and cells in Excel while preserving the original formatting in C# using Spire.XLS for .NET.

• Copy Rows in Excel with Formatting in C#
• Copy Columns in Excel with Formatting in C#
• Copy Cells in Excel with Formatting in C#

Install Spire.XLS for .NET

To begin with, you need to add the DLL files included in the Spire.XLS for.NET package as references in your .NET project. The DLLs files can be either downloaded from this link or installed via NuGet.

PM> Install-Package Spire.XLS

Copy Rows in Excel with Formatting in C#

Copying rows in Excel while preserving their formatting can be efficiently achieved using the Worksheet.CopyRow(CellRange sourceRow, Worksheet destSheet, int destRowIndex, CopyRangeOptions copyOptions) method. This method enables you to duplicate rows either within the same worksheet or across different worksheets with precision. Additionally, you can control the copying behavior, such as copying all formatting, conditional formatting, data validations, styles, or even just the formula values, through the CopyRangeOptions parameter.

The following steps explain how to copy rows across different worksheets with formatting using Spire.XLS for .NET.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get the source worksheet and the destination worksheet using the Workbook.Worksheets[index] property.
• Get the desired row that you want to copy using the Worksheet.Rows[index] property.
• Copy the row and its formatting from the source worksheet to the destination worksheet using the Worksheet.CopyRow(CellRange sourceRow, Worksheet destSheet, int destRowIndex, CopyRangeOptions copyOptions) method.
• Copy the column widths of cells in the source row to the corresponding cells in the destination row.
• Save the workbook to a file using the Workbook.SaveToFile() method.

using Spire.Xls;

namespace CopyRows
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("ContactList.xlsx");

            // Get the source worksheet
            Worksheet sheet1 = workbook.Worksheets[0];

            // Get the destination worksheet
            Worksheet sheet2 = workbook.Worksheets[1];

            // Get the desired row that you want to copy
            CellRange row = sheet1.Rows[0];

            // Copy the row from the source worksheet to the first row of the destination worksheet
            sheet1.CopyRow(row, sheet2, 1, CopyRangeOptions.All);

            int columns = sheet1.Columns.Length;

            // Copy the column widths of the cells in the source row to the corresponding cells in the destination row
            for (int i = 0; i < columns; i++)
            {
                double columnWidth = row.Columns[i].ColumnWidth;
                sheet2.Rows[0].Columns[i].ColumnWidth = columnWidth;
            }

            // Save the workbook to a file
            workbook.SaveToFile("CopyRow.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

Copy Columns in Excel with Formatting in C#

Similarly, copying columns in Excel with formatting can be accomplished using the Worksheet.CopyColumn(CellRange sourceColumn, Worksheet destSheet, int destColIndex, CopyRangeOptions copyOptions) method. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get the source worksheet and the destination worksheet using the Workbook.Worksheets[index] property.
• Get the desired column that you want to copy using the Worksheet.Columns[index] property.
• Copy the column and its formatting from the source worksheet to the destination worksheet using the Worksheet.CopyColumn(CellRange sourceColumn, Worksheet destSheet, int destColIndex, CopyRangeOptions copyOptions) method.
• Copy the row heights of cells in the source column to the corresponding cells in the destination column.
• Save the workbook to a file using the Workbook.SaveToFile() method.

using Spire.Xls;

namespace CopyColumns
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("ContactList.xlsx");

            // Get the source worksheet
            Worksheet sheet1 = workbook.Worksheets[0];

            // Get the destination worksheet
            Worksheet sheet2 = workbook.Worksheets[1];

            // Get the desired column that you want to copy
            CellRange column = sheet1.Columns[0];

            // Copy the column from the source worksheet to the first column of the destination worksheet
            sheet1.CopyColumn(column, sheet2, 1, CopyRangeOptions.All);

            int rows = column.Rows.Length;

            // Copy the row heights of cells in the source column to the corresponding cells in the destination column
            for (int i = 0; i < rows; i++)
            {
                double rowHeight = column.Rows[i].RowHeight;
                sheet2.Columns[0].Rows[i].RowHeight = rowHeight;
            }

            // Save the workbook to a file
            workbook.SaveToFile("CopyColumn.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

Copy Cells in Excel with Formatting in C#

In addition to copying rows and columns in Excel with formatting, Spire.XLS for .NET also allows copying cell ranges with formatting using the CellRange.Copy(CellRange destRange, CopyRangeOptions copyOptions) method. The detailed steps are as follows.

• Create an object of the Workbook class.
• Load an Excel file using the Workbook.LoadFromFile() method.
• Get the source worksheet and the destination worksheet using the Workbook.Worksheets[index] property.
• Get the source cell range and the destination cell range using the Worksheet.Range[] property.
• Copy the source cell range and its formatting from the source worksheet to the destination cell range in the destination worksheet using the CellRange.Copy(CellRange destRange, CopyRangeOptions copyOptions) method.
• Copy the row heights and column widths of the source cell range to the destination cell range.
• Save the workbook to a file using the Workbook.SaveToFile() method.

using Spire.Xls;

namespace CopyCells
{
    internal class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("ContactList.xlsx");

            // Get the source worksheet
            Worksheet sheet1 = workbook.Worksheets[0];
            // Get the destination worksheet
            Worksheet sheet2 = workbook.Worksheets[1];

            // Get the source cell range
            CellRange range1 = sheet1.Range["A1:E7"];
            // Get the destination cell range
            CellRange range2 = sheet2.Range["A1:E7"];

            // Copy the source cell range from the source worksheet to the destination cell range in the destination worksheet
            range1.Copy(range2, CopyRangeOptions.All);

            // Copy the row heights and column widths of the source cell range to the destination cell range
            for (int i = 0; i < range1.Rows.Length; i++)
            {
                CellRange row = range1.Rows[i];
                for (int j = 0; j < row.Columns.Length; j++)
                {
                    CellRange column = row.Columns[j];
                    range2.Rows[i].Columns[j].ColumnWidth = column.ColumnWidth;
                    range2.Rows[i].RowHeight = row.RowHeight;
                }
            }

            // Save the workbook to a file
            workbook.SaveToFile("CopyCells.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

Apply for a Temporary License

If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.

When working with Excel files programmatically in C#, determining the data type of a cell is critical for accurate data processing. Whether you're validating user inputs, parsing spreadsheets, or migrating data, knowing how to get the cell data types ensures your application handles information correctly.

In this guide, you'll learn how to use Spire.XLS for .NET library to check the cell data types in Excel in C#, covering the following aspects:

• Install the .NET Excel Library
• Learn the Cell Data Types in Spire.XLS
• How to Get Cell Data Type in Excel in C#

Install the .NET Excel Library

Before proceeding, you need to have the Spire.XLS for .NET library installed in your project. A recommended way to install it is via NuGet, and there are two available options:

• 1.Right-click the project in “Solution Explorer -> Manage NuGet Packages”. Search for “Spire.XLS” and install the latest version.
• 2.Go to “Tools -> NuGet Package Manager -> Package Manager Console”, and then run the following command:
• Package Manager

PM> Install-Package Spire.XLS

Alternatively, you can download the library from this link and manually add the DLL files as references.

Learn the Cell Data Types in Spire.XLS

Spire.XLS provides the XlsWorksheet.TRangeValueType enumeration to represents the cell value types. It includes following members:

How to Get Cell Data Type in Excel in C#

To check the value type of a cell, you need to follow the below steps.

• Create a Workbook object.
• Load an XLS or XLSX Excel file, and then get a specified worksheet within it.
• Get a specified cell range in the sheet though the Worksheet.Range[] property.
• Iterate through each cell in the cell range.
• Get the row number and column number of the current cell.
• Call the Worksheet.GetCellType(int row, int column, bool bNeedFormulaSubType) method to get the value type of the current cell, and return a specific enumeration member of the XlsWorksheet.TRangeValueType enumeration type.
• Converts the value of the enumeration member to its corresponding text string.
• Write the text string to another cell and set its font style.
• Save the result file using the Workbook.SaveToFile() method.

using System.Drawing;
using Spire.Xls;
using Spire.Xls.Core.Spreadsheet;

namespace GetCellType
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a Workbook object
            Workbook workbook = new Workbook();
            // Load an Excel file
            workbook.LoadFromFile("Test1.xlsx");

            // Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];
            // Get a specified cell range
            CellRange range = sheet.Range["A2:A8"];

            // Iterate through all cells in the range
            foreach (CellRange cell in range)
            {
                // Get the row number and column number of the current cell
                int row = cell.Row;
                int column = cell.Column;

                // Get the value type of the current cell
                XlsWorksheet.TRangeValueType cellType = sheet.GetCellType(row, column, false);

                // Convert the cell type value to a text string and write to another cell
                sheet[row, column + 1].Text = cellType.ToString();

                // Set the font style of the output cell
                sheet[row, column + 1].Style.Font.Color = Color.Red;
                sheet[row, column + 1].Style.Font.IsBold = true;
            }

            // Save the result file
            workbook.SaveToFile("GetCellType.xlsx", ExcelVersion.Version2016);
        }
    }
}

A screenshot of the result file:

Conclusion

Spire.XLS simplifies checking Excel cell data types in C# with its intuitive API. By leveraging the GetCellType() method, you can accurately determine whether a cell contains a number, string, boolean, etc. This approach ensures robust data processing for applications that interact with Excel files.

Get a Free License

To fully experience the capabilities of Spire.XLS for .NET without any evaluation limitations, you can request a free 30-day trial license.