Copy Format from One Cell Range to Another in C#

Introduction

When working with a worksheet, we may get a cell or a range of cells formatted, then we can use the Format Painter to quickly copy formatting from one place and apply it to another. Applying existing cell format to others will save us much time especially when there are numerous cells needed to be displayed in same style. This article presents how to copy format from a range of cells to another programmatically in C#.

Tools We Need

– Spire.XLS.dll
– Visual Studio

Purpose

As is shown in the screenshot of test file, contents in the first column have been formatted with different styles, what we want is to correspondingly apply cell formatting from column 1 to column 3.

1

Steps

1. Create a new instance of Wordbook class and load the test file with specified path.
2. Initialize a new worksheet. Get specified Worksheet.Range, return the style of specified range to CellRange.Style object. Apply style formatting to another range.
3. Save the changes to a new workbook file.

Entire Code

Workbook workbook = new Workbook();

workbook.LoadFromFile(“Test.xlsx”);

Worksheet sheet = workbook.Worksheets[0];

int count = sheet.Rows.Count();

for (int i = 1; i < count + 1; i++)

{

    sheet.Range[string.Format(“C{0}”, i)].Style = sheet.Range[string.Format(“A{0}”, i)].Style;

}

workbook.SaveToFile(“Result.xlsx”, ExcelVersion.Version2010);

Result

2


Copy Formats Manually:

http://office.microsoft.com/en-us/excel-help/copy-formats-from-one-cell-or-range-to-another-HP005199434.aspx

Advertisements

Author: janewdaisy

.NET Program Beginner. Share methods about how to use C#/VB.NET to export data, operate Word, Excel, PDF and other useful skills.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s