Transfer format from one range to another one

[Christian Nein]

Hello,

we are currently evaluating XlsIO as we are searching for a component to displace our existing Office 2003 PIA solution.

Now I have a problem concerning the transfer of formats. What we want to do is to transfer a format defined in a template sheet to a newly generated range. Following you find the code we are using:

IWorksheet sheet = workbook.Worksheets[templateSheet]; // templateSheet is a string and passed as function parameter
IRange rangeSrc = sheet.Range[referenceSrc]; // referenceSrc is a string and passed as function parameter
rangeDest.CellStyle = rangeSrc.CellStyle; // rangeDest is IRange and passed as function parameter

Unfortunately this does not work properly. With Excel Interop Assemblies we used the following code which really worked fine:

Excel.Worksheet sheet = application.Worksheets[templateSheet] as Excel.Worksheet; // templateSheet is a string and passed as function parameter
sheet.Select(Missing.Value);
application.Goto(referenceSrc, Missing.Value);
Excel.Range rangeSrc = application.Selection as Excel.Range;
rangeSrc.Copy(Missing.Value);
rangeDest.Select(); // rangeDest is Excel.Range and passed as function parameter
rangeDest.PasteSpecial(Excel.XlPasteType.xlPasteFormats, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, objFalse, objFalse);

I would like to know how I can translate this code to Syncfusion XlsIO.

Thanks in advance & best regards
Christian

[Melba Winshia]

Hi Christian,

You can transform formats by using CopyTo method. Please refer the following code snippet to achieve this:

[c#]

//Copying the range.
X.Range["A1:A10"].CopyTo (X.Range ["B1"],ExcelCopyRangeOptions.CopyStyles);

Here is the sample for your reference:

http://websamples.syncfusion.com/samples/XlsIO.Windows/63038/main.htm

And also, you can transfer formats by creating a Global style and then you can assign that style to other ranges. Please refer the following code snippet to achieve this:

//Set style.
IStyle BackColorStyle;
BackColorStyle = workbook.Styles.Add("BackColorStyle");

//Set back color.
BackColorStyle.ColorIndex = ExcelKnownColors.Pale_blue;s
//Number format
BackColorStyle.NumberFormat = "hh:mm";

//Apply style
X.Range["A1:A10"].CellStyleName = "BackColorStyle";

//Copy style
X.Range["C1:C10"].CellStyleName = X.Range["A1:A10"].CellStyleName;

Here is the sample for your reference:

http://websamples.syncfusion.com/samples/XlsIO.Windows/63038_1/main.htm

Kindly let me know if you have any other questions.

Thanks,
Melba

[Christian Nein]

Hi Melba,

unfortunately the code

rangeSrc.CopyTo(rangeDest, ExcelCopyRangeOptions.CopyStyles);

not only copies style but also the content. To me this seems to be a bug. It also seems to be a problem, if template and destination range differ in size. This is also a problem for us because the user does not know the size of the tables to be generated when formatting the template.

The alternative way of using the CellSytle object seems to work but it is quite troublesome because I have to "copy" ALL style properties of the template range (including all the borders settings in detail) to the newly generated sytle object before assigning the style to the destination range by

rangeDest.CellStyleName = styleName;

A simple "Copy style" command like the original Format Painter in MS Excel would make this much easier.

Would it help you if I sent you the xlt-template we are using?

Thanks in advance & best regards
Christian

[Melba Winshia]

Hi Christian,

Issue 1:(rangeSrc.CopyTo(rangeDest, ExcelCopyRangeOptions.CopyStyles)
--------

This is not a bug. We have added this overload to CopyStyles along with data only. Already we have logged a feature request ("Feature to copy the styles and formats alone") to copy the styles and formats alone and notified our development team. We will implement this feature in our forthcoming new version releases or service pack releases. We usually have a timeframe of at least three months between releases. The feature implementation would also greatly depend on the factors like product design, code compatibility and complexity. We will get back to you once the feature is implemented."

Issue 2:(The alternative way of using the CellSytle object seems to work)
--------

Currently the only way to transfer a format defined in a template sheet to a newly generated range is by using CellStyleName only.

Kindly let me know if you have any other question.

Thanks,
Melba