Cells.InsertRange not working?

timocref · September 21, 2009, 7:38am

Hi Aspose team,

I’m trying to write a function that inserts a range in a worksheet. Since the worksheet has some formatting in the first rows, I cannot use insert column. The Cells-class has a function InsertRange, but this function doesn’t seem to do anything. Attached is a test-program showing the behaviour of InsertRange.

I expected the cells right to the range being inserted shifted right and the named range also being shifted. Furthermore should the formula’s in the rows above be updated.

Is there another function I can use to insert a range (and not a whole column or row)? This functionality is supported by Excel.

Thanks,

Timo

nausherwan.aslam · September 21, 2009, 12:02pm

Hi Timo,

Thank you for considering Aspose.

Well, I checked your sample code and found no issue in it. Actually, you are inserting the new range in "Sheet2". As "Sheet2" is empty, so you are unable to see any change after inserting the range. Please check the issue again by either creating a new range in "Sheet1" or using some data in "Sheet2" and let us know if you still face any problem.

Thank You & Best Regards,

timocref · September 22, 2009, 2:36am

Hi,

Thanks for looking into the problem. You where right about the wrong sheet where the inserts where done, sorry my mistake.

However, the problem is still there. I changed the code to insert in the correct sheet. Insert is done, cells are shifted right, but:

Formula in Cell B1 is not updated
Named ranges are not shifted. See formula in cell B2. It sums 2 named ranges pointing to the cells being shifted.

Could you please look in to this problem?

Thanks,

Timo

nausherwan.aslam · September 22, 2009, 3:29am

Hi Timo,

Thank you for considering Aspose.

We have found your mentioned issues after an initial test. We will look into it and get back to you soon.

Thank You & Best Regards,

nausherwan.aslam · September 23, 2009, 4:19am

Hi Timo,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. We have fixed your mentioned issue.

Thank You & Best Regards,

timocref · September 23, 2009, 5:34am

Thanks for the quick responses and fix for issues being reported, fix works as expected.

Timo

timocref · September 24, 2009, 5:30am

Hi,

After some more testing there still seems to be a problem when inserting a range.

In the previously posted example I've created a named range that is defined to row 10 (whole row). After the inserts are done, this same named range is now only 1 column.

I created another named range on Row 11. This range is only 14 columns in width. This range is extended after the inserts (as it should be) and works fine.

The problem is when the range is extended beyond the maximum width of the sheet (in this case beyond 256 columns). If extending the range would take it beyond the 256 column bound it should maintain it at 256.

Thanks for looking into this,

Timo

amjad.sahi · September 24, 2009, 8:47am

Hi,

Well, I think it works like MS Excel. We appreciate if you could create a simple project (as you did before), zip it and post it here to show the issue with all the details.

We will check it soon.

Thank you.

timocref · September 25, 2009, 5:16am

Hi,

Attached is a sample project. The Excel-file has 3 named ranges:

NR_Insert: Is used to insert a range of cells
TestRow10Range: This range is defined to (full) row 10
TestRow11Range: This range is defined to A11-N11

When running the project it will display 2 messageboxes showing the number of columns in both ranges. The first messagebox is shown before the insert is done, the other after the insert is done.

Before inserts are done, TestRow10Range has 256 columns, TestRow11Range has 14 columns.

After two inserts, TestRow10Range has 1 columns, TestRow11Range has 16 columns.

The number of columns for TestRow11Range is correct, there are 2 columns inserted within the range, thus the range is extended with 2 columns.

For TestRow10Range, the result is not correct. The number of columns cannot exceed the maximum number of columns in a sheet (=256) but if if would, the number of columns should stay fixed at 256 (being the complete row). Aspose will start the columncount at 0 if it exceeds 256. This should not be the case.

You can easily try this in MS Excel by insert the range manually in the given file ("TestInsertCellArea.xls") and looking at both ranges. MS Excel will maintain the TestRow10Range on the whole row.

Thanks in advance,

Timo

nausherwan.aslam · September 25, 2009, 6:26am

Hi Timo,

Thank you for providing the sample application to reproduce the issue.

We have found your mentioned issue after an initial test. We will look into it and get back to you soon.

Thank You & Best Regards,

nausherwan.aslam · September 29, 2009, 4:15am

Hi Timo,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. We have fixed your mentioned issue. If you still face any problem, please do let us know.

Thank You & Best Regards,

timocref · September 30, 2009, 5:33am

Thanks, works perfect…

timocref · October 1, 2009, 8:50am

Hi,

I found yet another problem when inserting a range. This problem also occurs when a range is copied.

See attached project. Code will insert a range (using Cells.InsertRange). Inside this range is an array-formula (in MS-Excel those formula's are shown with {} around the formula). Then the code copies another range to cells below. Inside this range there's also an array-formula.

Opening the result in MS-Excel will cause an error to pop-up (File error: data may have been lost.). After clicking OK the file opens, BUT:

The array-formula in the shifted range is replaced by "=#REF!"
In the copied version of the range the formula is also replaced by "=#REF!"

Could you please look into this?

Thanks in advance,

Timo

amjad.sahi · October 1, 2009, 1:07pm

Hi Timo,

Thanks for providing us the sample project.

We found the issue (as you have mentioned) after an initial test, we will figure it out soon.

Thank you.

nausherwan.aslam · October 10, 2009, 4:39am

Hi,

Thank you for considering Aspose.

Please try the attached latest version of Aspose.Cells. We have fixed your mentioned issue.

Thank You & Best Regards,

timocref · October 12, 2009, 4:09am

Thanks, all works well.

Any news on the reference to external sheets using only named ranges? https://forum.aspose.com/t/71186

Timo

nausherwan.aslam · October 12, 2009, 6:27am

Hi Timo,

Thank you for considering Aspose.

Well, currently the external references are not supported. We will update you regarding the feasibility of the feature soon.

Thank You & Best Regards

timocref · October 12, 2009, 6:46am

Hi Nausherwan,

Not to put extra pressure on it, but this functionality is crucial for my customer. The application we are developing for them creates Excel-sheets that are dependant on each-other. If I cannot create external references I cannot use Aspose.

Note that I only have to insert the formula’s in a correct way. It’s not that I’m reading the generated sheets with Aspose. As soon as the sheets are generated, they are opened in MS Excel. We’re only using Aspose to achieve a speedup boost we could not possibly achieve using MS Excel COM programming.

amjad.sahi · October 12, 2009, 7:14am

Hi,

Thanks for sharing your concerns.

OK, We will investigate and try to set external referenced formula that contains named range(s) for your need. And, we will get back to you in the related thread:
https://forum.aspose.com/t/71186

Thank you.

aspose.notifier · November 16, 2009, 5:20am

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

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