We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy.
Unfortunately, activation email could not send to your email. Please try again.

Transfer format from one range to another one

Thread ID:

Created:

Updated:

Platform:

Replies:

63038 Jun 27,2007 07:29 AM Jul 3,2007 07:56 AM Windows Forms 3
loading
Tags: XlsIO
Christian Nein
Asked On June 27, 2007 07:29 AM

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 [Syncfusion]
Replied On June 28, 2007 04:24 AM

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
Replied On July 2, 2007 10:48 AM

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 [Syncfusion]
Replied On July 3, 2007 07:56 AM

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

CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.

;