Cut and paste a cell's conditional formats to another cell

Aspose.Cells Product Family
1

I have a cell that has two conditional formats.


I want to copy and paste both into another cell.

I’ve tried the CopyConditionalFormatting method, CopyRow, GetFormatConditions and then adding a CellArea to it, but none of these methods work. The latter was the closest method but it only copied one of the conditional formats over.

Has anyone else had any success?

Thanks,

M.
2

Hi,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue by executing the following sample code with the latest version:

        <a href="https://forum.aspose.com/t/37483">Aspose.Cells
            for .NET v8.6.2.4</a> and found Aspose.Cells copies only one conditional formatting and not all.<br><br>We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.<br><br>This issue has been logged as <br><br><ul><li><b>CELLSNET-44118</b> - CopyConditionalFormatting copies just one conditional formatting not all</li></ul><br>I have also attached the source excel file used in this code and output excel file generated by it for a reference.<br><b><br>C#</b><div class="csharpcode"><font color="#800080">

Workbook workbook = new Workbook(“source.xlsx”);

Worksheet worksheet = workbook.Worksheets[0];

//Copy from B1 to F1
worksheet.CopyConditionalFormatting(0, 1, 0, 5);

//Copy from B2 to F2
worksheet.CopyConditionalFormatting(1, 1, 1, 5);

//Copy from B3 to F3
worksheet.CopyConditionalFormatting(2, 1, 2, 5);

//Copy from B4 to F4
worksheet.CopyConditionalFormatting(3, 1, 3, 5);

//Copy from B5 to F5
worksheet.CopyConditionalFormatting(4, 1, 4, 5);

//Copy from B6 to F6
worksheet.CopyConditionalFormatting(5, 1, 5, 5);

workbook.Save(“output.xlsx”);

3

Thanks for looking into this.


I also want to mention a couple of other things to check out…

1) Test this method on a large scale. Try to copy one cell into a 1000 other cells using a for loop to increment row and column indexes.

2) When you do do a copy and paste that the relative cell references also work. I noticed that this didn’t work when using copy rows.

Cheers,

M.

4

Hi,

Thanks for your posting and using Aspose.Cells.

It will be helpful if you could provide us some sample codes relating to 2 issues you mentioned in your previous post.

Besides, this is to inform you that we have fixed your issue CELLSNET-44118 now. We will soon provide the fix after performing QA and including other enhancements and fixes.

5

Hi,


Please try our latest version/fix: Aspose.Cells for .NET v8.6.2.5 (attached).

We have fixed your issue “CELLSNET-44118” now.

Let us know your feedback.

Thank you.
6

I have updated my project with the latest Aspose.Cells dll and there are still some issues. Below is the code I’m using and attached is a template file I want to use.


I want to copy the conditional formats from row 9 to rows 10 through to 100. In the output file, if you type in 4 or 5 in cell J9, you can see that other cells in that row will change colour. In copied rows 10 to 100, if you change the values in J10/11/etc. you can see that the conditional formats do not match the original rows.

I’ve checked but I hope my code is sound. Mind you, I have been corrected before. :wink:



using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Aspose;
using Aspose.Cells;
using System.IO;

namespace AsposeTesting
{
public class Program
{
private static Workbook _wb = null;
private static FileStream _fstream = null;

static void Main(string[] args)
{
if (!File.Exists(“AsposeTesting.xlsx”))
return;
//Load Template File
License license = new License();
license.SetLicense(“Aspose.Cells.lic”);

_fstream = new FileStream(“AsposeTesting.xlsx”, FileMode.Open);
LoadOptions loadOpts = new LoadOptions(LoadFormat.Xlsx);
_wb = new Workbook(_fstream, loadOpts);
int srcRow = 8;
int srcStartCol = 0;
int srcEndCol = 37;
int destStartRow = 9;
int destEndRow = 100;

for (int row = destStartRow; row < destEndRow; row++)
{
for (int col = srcStartCol; col < srcEndCol; col++)
{
_wb.Worksheets[0].CopyConditionalFormatting(srcRow, col, row, col);
}
}
//Save to new file
if(File.Exists(“OutputFromTest.xlsx”))
File.Delete(“OutputFromTest.xlsx”);
_wb.Save(“OutputFromTest.xlsx”);

//Close Template
_fstream.Close();
_fstream.Dispose();
_wb.Dispose();
}
}
}

7

Hi,


Thanks for providing us sample code and new template file.

I have evaluated your scenario/ case a bit. You are right. The issue is still there. I used your recently attached template file with sample code and found the issue as you talked about when copying the conditional formatting from row 9 to rows (10 through 100). I tested and confirmed, in the output file, when I typed 5 in the cell J9 (source row), a few cells’ background color does changed in the same row. Now, when I typed 5 (same value) in the copied J10 cell (destination row), the relevant cells’ background color is not changed. I suspect some conditional formatting rules are missing here. Similarly when I typed in 4 in the cell J9, I can see that other cells (K9, N9, R9, S9, T9, U9, V9, W9, Y9 etc.) in that row will change their color. But when I did input 4 in J10 cell, I can see some cells change their color but still I do not see R10 and S10 cells change their color (which they should) and hence conditional formatting is completely missing for those cells. I have reopened your issue “CELLSNET-44118” again. Our concerned developer will soon look into it.

Once we have any update on it, we will let you know here.

Thank you.

8

Thanks Amjad,


I would also look into Cells.CopyRows() method to see if it’s doing the same thing. In your documentation, I thought I read that this method should copy over the conditional formats too.

Cheers,

M.
9

Hi,

Thanks for your investigation and looking into these fixes deeply and using Aspose.Cells.

If you find any issues, please do share with us your sample codes. It will help us fixing the issues more precisely. Thanks for your efforts and cooperation in this regard.

10

Just use the sample code I provided above and replace the method with CopyRows.

11

Hi,


Yes, Cells.CopyRows() method does copy conditional formatting (with cells’ common/general formatting/styles) as well. I have tested your scenario/ case a bit using the Cells.CopyRows() method, I think it works fine. I used the following updated code segment:
e.g
Sample code:

…
FileStream _fstream = null;

if (!File.Exists(“e:\test2\AsposeTesting.xlsx”))
return;
//Load Template File
License license = new License();
license.SetLicense(“Aspose.Cells.lic”);

_fstream = new FileStream(“e:\test2\AsposeTesting.xlsx”, FileMode.Open);
LoadOptions loadOpts = new LoadOptions(LoadFormat.Xlsx);
_wb = new Workbook(_fstream, loadOpts);
int srcRow = 8;
int destStartRow = 9;
int destEndRow = 100;

for (int row = destStartRow; row < destEndRow; row++)
{
_wb.Worksheets[0].Cells.CopyRows(_wb.Worksheets[0].Cells, srcRow, row, 1);
}
…

Could you try it and let us know if it works fine or you still find any issue.

Thank you.
12

Hi,


We have evaluated your issue further. Please use Cells.CopyRows method to replace your original method in your code, See the following more refined code segment for your reference:
e.g
Sample code:

…
LoadOptions loadOpts = new LoadOptions(LoadFormat.Xlsx);
_wb = new Workbook(_fstream, loadOpts);
int srcRow = 8;
int destStartRow = 9;
int destEndRow = 100;

_wb.Worksheets[0].Cells.CopyRows(_wb.Worksheets[0].Cells, srcRow, row, destEndRow - destStartRow + 1);

And we are planning to obsolete the Worksheet.CopyConditionalFormatting method in future versions for performance considerations.

Thank you.
13

Hi, in newer versions of Aspose Cells does destRange.Copy(sourceRange); will also copy the conditional formatting for all the cells within the range? Before we had to loop through each cell in source Range and copy the conditional formatting over.

14

@programmerboy,

Thanks for your query. We are working on this query and will share our feedback shortly.

15

@programmerboy,

Yes you are right, the conditional formatting will be copied for all the cells in the range. You may please try the following sample code and provide your feedback.

// Creating a file stream containing the Excel file to be opened
FileStream fstream = new FileStream("Book2.xlsx", FileMode.Open);

// Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);

// Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[0];

// Copying conditional format settings from cell "A1" to cell "B1"
//worksheet.CopyConditionalFormatting(0, 0, 0, 1);

int TotalRowCount = 0;

for (int i = 0; i < workbook.Worksheets.Count; i++)
{
    Worksheet sourceSheet = workbook.Worksheets[i];

    Range sourceRange = sourceSheet.Cells.MaxDisplayRange;

    Range destRange = worksheet.Cells.CreateRange(sourceRange.FirstRow + TotalRowCount, sourceRange.FirstColumn,
            sourceRange.RowCount, sourceRange.ColumnCount);

    destRange.Copy(sourceRange);

    TotalRowCount = sourceRange.RowCount + TotalRowCount;
}
            

// Saving the modified Excel file
workbook.Save("output2.xls");

// Closing the file stream to free all resources
fstream.Close();

Book2.zip (5.9 KB)
output2.zip (4.5 KB)