Copy Ranges not keeping the cell formats

Aspose.Cells Product Family
1

I have a spreadsheet that has some conditional formating in some of the cells. I am trying to copy a range that contains these cells to another spreadsheet but the formating is not copying over.

2

Hi,

Well, currently it is not available (When you copy a range using Range.Copy() method to somewhere, all the style formattings are copied except the conditional formatting is not attched to the cells.). We will support this feature soon for your need.

Thank you.

3

Any idea of time frame besides “soon”. Is there any other copy method that would get the conditional cell formats? I tried the row copy but that to does not work

4

Hi,

Thanks for your inquiry.

Well, normally, we try our best to fix any issue or add the newer feature ASAP for our clients. If the feature is a small one (after analyzing it), it should take lessor time to make it, sometimes within 3-6 hours you know. And we always try to perform these tasks in parallel with other important tasks which are already described in our planner worklist.

I think currently as a workaround, you may try to extract the conditional formattings attributes from those range of cells and add conditional formattings after you have copied the cells range.

Following is a sample example.

E.g.,

// Create a Workbook object
Workbook workbook = new Workbook();
// Open the Excel file
workbook.Open("d:\\test\\book1.xls");
// Get the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];
// Create a named range of the Cells
Range range1 = worksheet.Cells.CreateRange("A1", "C9");
// Get the source range of cells...i.e., A21..C29.
Range range2 = worksheet.Cells.CreateRange("A21", "C29");
// Copy the data with formatting of the range to the first range.
range1.Copy(range2);

ConditionalFormattings cfs = workbook.Worksheets[0].ConditionalFormattings;
FormatConditions fcs = cfs[0];
//Get the Font text color.
FormatCondition fc = fcs[0];
//Get the Formula1
string f1 = fc.Formula1;
OperatorType op1 = fc.Operator;
Color c1 = fc.Style.Font.Color;
MessageBox.Show(f1);
MessageBox.Show(op1.ToString());


int conditionalFormattingIndex = workbook.Worksheets[0].ConditionalFormattings.Add();
FormatConditions formatConditions = workbook.Worksheets[0].ConditionalFormattings[conditionalFormattingIndex];
CellArea cellArea = new CellArea();
cellArea.StartColumn = 0;
cellArea.StartRow = 0;
cellArea.EndColumn = 0;
cellArea.EndRow = 8;
formatConditions.AddArea(cellArea);

int formatConditionIndex = formatConditions.AddCondition(FormatConditionType.CellValue,op1, f1, null);
FormatCondition formatCondition = formatConditions[formatConditionIndex];
formatCondition.Style.Font.Color = c1;


// Save the modified Excel file in default (Excel 2003) format
workbook.Save("d:\\test\\outwithcondformat.xls",FileFormatType.Default);

Thank you.