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