Copy Range between two different Worksheet from two different Workbooks doesn't copy ListObjects

Aspose.Cells Product Family
1

As per subject, in my application I’m creating a workbook on the fly and copying in its first sheet in the same position of the source sheet a range that completely contains some listobjects. These are not copied too.


How can I solve the problem?
2
Hi,

Thanks for pointing it out.

After an initial test, I can notice the issue. When I copy a range of cells from a sheet having a ListObject in it, it only copies data of ListObject but not ListObject as object with its formatting.

Here is my sample code to test your scenario.

Sample code:

Workbook workbook = new Workbook();

//Create data source Table1
Worksheet worksheet = workbook.Worksheets[0];

worksheet.Cells[0, 0].Value = "Header1";
worksheet.Cells[0, 1].Value = "Header2";
worksheet.Cells[0, 2].Value = "Header3";

for (int i = 1; i < 10; i++)
{
int value = i - 1;
worksheet.Cells[i, 0].Value = "Value1-" + (value < 10 ? "a" : "b");
worksheet.Cells[i, 1].Value = "Value2-" + value;
worksheet.Cells[i, 2].Value = value;
}

worksheet.ListObjects.Add(0, 0, 10, 2, true);

Workbook destWorkbook = new Workbook();
Worksheet destSheet = destWorkbook.Worksheets[0];

Aspose.Cells.Range sourceRange = worksheet.Cells.MaxDisplayRange;
Aspose.Cells.Range destRange = destSheet.Cells.CreateRange(sourceRange.FirstRow, sourceRange.FirstColumn, sourceRange.RowCount, sourceRange.ColumnCount);

PasteOptions options = new PasteOptions();
options.PasteType = PasteType.All;
destRange.Copy(sourceRange, options);

destWorkbook.Save("e:\\test2\\outputfilerngcopys.xlsx");


I am using latest version / fix of the product e.g v7.4.1.x.

I have logged a ticket with an id: "CELLSNET-41475" for your issue. We will look into your issue soon.

Thank you.

3

Ah thank you, also for the pieace of code. Yesterday I was in a hurry and I couldnt write something down. The issue is appearing also when you start from a file made directly with excel and using the simple Copy method without the pastoptions parameter.


We are using the version 7.4.1.0, when it will be ready can you give us a link for the 3.5 framework client profile?


4

Hi,


Yes, sure, PasteOptions do not make any impact and the issue also occurs by simply using the line:

destRange.Copy(sourceRange);

Please spare us little time, we will look into it. Once we analyze the issue or we have any update on it, we will let you know here.

Thank you.
5

Hi,


Please try the new fix/version: Aspose.Cells for .NET (Latest Version)

Your issue should be fixed in it.

Thank you.

6

Hi, thanks,

can you give me the 7.4.1.4 for 3.5 client profile?


7

Hi,


We are scheduled to release our next official release of the product Aspose.Cells for .NET v7.4.2 in the next week early (Monday or Tuesday). So, you may download the new version from our Downloads module and get your desired version from its respective folder @ your installation directory.

Thank you.
8

The issues you have found earlier (filed as CELLSNET-41475) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.
9

Doesn’t work for me.


In our case, the range that I’m copying from A3:D11, contains two separated listed object, one in B5:B6 ( header + one row) and one in B9:D10 (again headers + one row)

These two are not copied between workbooks
10

Hi,


Please either paste your runnable sample code with template files or create a sample console application(zip it prior attaching here) to reproduce the issue on our end. We will check your issue soon.

Thank you.
11

Ok, you can find an example in https://docs.google.com/a/vizubi.com/file/d/0B-EFyDLlJnAsNkhaX0RhY2NKT00/edit


You just neet to run the project Cells.Case6986\Cells.Case6986.csproj

12

Hi,


I have downloaded and checked your referred project @: https://docs.google.com/a/vizubi.com/file/d/0B-EFyDLlJnAsNkhaX0RhY2NKT00/edit?pli=1

Well, you got to use PasteOptions while copying ranges b/w worksheets/workbooks to render/copy the List objects / tables. See the updated code that works fine as I tested using your template file.

Sample code:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Aspose.Cells;

namespace Vizubi.RegressionsTest.Aspose.Cells.Case6986
{
class Program
{
static void Main(string[] args)
{
var wbk = new Workbook(@“E:\test2\Cells.Case6986\template.xlsx”);
var wbktmp = new Workbook();

System.Console.WriteLine(wbk.Worksheets[0].ListObjects.Count.ToString()); // 2

PasteOptions options = new PasteOptions();
options.PasteType = PasteType.All;

wbktmp.Worksheets[0].Cells.CreateRange(0, 0, 9, 4).Copy(wbk.Worksheets[0].Cells.CreateRange(2, 0, 9, 4),options);

System.Console.Write("Case 6986.1: ");
System.Console.WriteLine(wbktmp.Worksheets[0].ListObjects.Count == wbk.Worksheets[0].ListObjects.Count ? “SUCCESS” : “FAILED”); //SUCCESS

wbktmp = new Workbook();

wbktmp.Worksheets[0].Cells.CreateRange(0, 0, 100, 100).Copy(wbk.Worksheets[0].Cells.CreateRange(0, 0, 100, 100), options);


System.Console.Write("Case 6986.2: ");
System.Console.WriteLine(wbktmp.Worksheets[0].ListObjects.Count == wbk.Worksheets[0].ListObjects.Count ? “SUCCESS” : “FAILED”); //SUCCESS

System.Console.ReadLine();
}
}
}


Thank you.

13

Thanks, I will test immediately the solution. Just one question: the usage of the pastoptions is related to the copying of format conditions too?

14

Hi,


Yes, it will copy everything as per MS Excel when copying ranges.

Let us know if you still have the issue,

Thanks
15

Not working! The test as I wrote it will pass, but the listobjects are in different positions and if you save the wbktmp somewhere as xlsx and try to open it you will notice it is unreadable from excel.


Seems like startrow is not correctly valorized?

16

Hi,


Thanks for providing us further details.

I can notice the issue as you have mentioned. After using PasteOptions while copying ranges b/w workbooks, it works fine but when I opened the generated file into MS Excel, I got the error: “Excel found unreadable content…”.

I have logged a ticket with an id “CELLSNET-41514” for your issue. We will look into your issue soon.

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

Thank you.
17

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

We have fixed the issue.

Please download and try this fix: Aspose.Cells for .NET (Latest Version) and let us know your feedback.

18

Hi,


Please find attached and try our latest fix/version: Aspose.Cells for .NET v7.4.2.1 (compiled on .NET 2.x and targeted for any CPU with any of the normal .NET framework versions greater than or equal to 2.x (i.e. 2.x, 3.0, 3.5, 4.0, 4.5 etc.)).

We have fixed the issue. Let us know your feedback.

Thank you.

19

Hello,

I tested and the specific case is resolved, but when I use it with the more extended case that we are using inside, other problem arises.

I extended the example posted at https://docs.google.com/a/vizubi.com/file/d/0B-EFyDLlJnAsNkhaX0RhY2NKT00/edit

This examples generates 4 output and each of them present different problems.

Just to be clear I noticed that the ClearRange (for which I created a switch in the code as you can see in the method ExtendedTemplate) doesn’t delete the ListObjects (or better, it deletes just one of the two).

Moreover all the four outputs when you open with excel they have WRONG content inside.

20

Hi,


Thanks for sharing the new project.

After an initial test, I can notice the issue by using your new project. The issue seems to be related to List Object / Table which is somehow not rendered fine. I can generate the files fine though. After opening each of the four files, MS Excel will display a message “Excel found unreadable content…” and has to repair it to finally view the file ino it. The tables / listobjects are also not rendered fine with data.
I have reopened your existing issue “CELLSNET-41514”. We will look into it soon.

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

Thank you.