Copy comments from one workbook/worksheet to another with formatting in .NET

[mananh]

Hi...

I am evaluating latest version of Aspose.cells .NET and I want to copy all the comment from one workbook/worksheet to another with all font and color formatting.

Is there any way to do the same?

Thanks

[babar.raza]

Hi Haresh,

Thank you for contacting Aspose support.

You can achieve your requirement by copying the source worksheet (containing the comments) to a new worksheet (of same workbook or a new one) and then remove the contents of the copied worksheet, in case you wish to copy only the comments. Please check the following piece of code for your reference.

C#

//Load source spreadsheet
var source = new Workbook(dir + “book2.xlsx”);
//Access Worksheet containing comments
var sourceSheet = source.Worksheets[0];
//Create another instance of Workbook to store the result
var destination = new Workbook();
//Access first Worksheet of the second Workbook
var destinationSheet = destination.Worksheets[0];
//Copy source Worksheet to new Workbook
destinationSheet.Copy(sourceSheet);
//Clear the contents of the copied Worksheet if you wish to copy only the comments
destinationSheet.Cells.ClearContents(0, 0, destinationSheet.Cells.MaxDataRow, destinationSheet.Cells.MaxDataColumn);
//Save Result
destination.Save(dir + “output.xlsx”);

[mananh]

Hi Raza,

Thank you for quick response
I've tried your solution but it's work perfect with file which is not contain any other shape like textbox, picture, But if worksheet has picture or button or textbox it's also remain in new copied worksheet.
I only want to copy comment with formatting to another worksheet at same cell location which contain different data
Is there any other way to do so...?

Thanks
Haresh

[amjad.sahi]

Hi,

Well, you may refine your code a bit to accomplish your task. See the following updated sample code with the attached template file for your reference:

e.g

Sample code:

//Load source spreadsheet

var source = new Workbook("e:\\test2\\book2_new1.xlsx");

//Access Worksheet containing comments

var sourceSheet = source.Worksheets[0];

//Create another instance of Workbook to store the result

var destination = new Workbook();

//Access first Worksheet of the second Workbook

var destinationSheet = destination.Worksheets[0];

//Copy source Worksheet to new Workbook

destinationSheet.Copy(sourceSheet);

//Clear the contents of the copied Worksheet if you wish to copy only the comments

destinationSheet.Cells.ClearContents(0, 0, destinationSheet.Cells.MaxDataRow, destinationSheet.Cells.MaxDataColumn);

**Aspose.Cells.Drawing.ShapeCollection shapes = destinationSheet.Shapes;**

**for (int i = shapes.Count -1; i >=0; i--)**

**{**

**if (!(shapes[i].GetType().ToString() == "Aspose.Cells.Drawing.CommentShape"))**

**{**

**shapes.RemoveAt(i);**

**}**

**}**

//Save Result

destination.Save("e:\\test2\\output1.xlsx");

Hope, this helps a bit.

Thank you.