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