Hi,
I have a requirement where I have to move sheets across workbooks. I somehow could not figure out how to do in Aspose. Please guide me.
This is possible in Ms. Excel and so I am sure there would be some way to do this in Aspose too.
Thanks,
Malay
Hi,
I think you are using Aspose.Cells for Java.
Well, currently the feature (shifting worksheets across workbooks) is not supported, we will consider it to support it soon.
I think you may copy worksheets across workbooks using Worksheet.copy() method and later delete your unwanted worksheets (source sheets).
Thank you.
Hi Amjad,
I did try to achieve my objective using Worksheet.copy( ) method. But, somehow its not fitting in to ny requirement. I am putting my scenario below, please suggest me if I am not doing it in right way.
Requirement
Moving sheets across workbooks by keeping the references of the formula intact.
Steps ( Please perform the steps in Ms Excel; i am attaching the sample files)
1> Check the formula on "Aggregation" sheet (Source.xls); cell C8. Its {='Contributor-1'!C8+'Contributor-2'!C8}.
2> Move the sheet "Contributor-1" to the other workbook (Target.xls). Now check the formula in[Source.xls]Aggregation!C8; it is {='[Target.xls]Contributor-1'!C8+'Contributor-2'!C8}. Here Ms Excel automatically changes the reference in the formula to point to the new workbook.
3> Move the sheet "Aggregation" to the other workbook (Target.xls). Now check the formula in[Target.xls]Aggregation!C8; it is {='Contributor-1'!C8+'[Source.xls]Contributor-2'!C8}. Here Ms Excel automatically changes the reference in the formula to point to the new workbook {Contributor-1'!C8}; at the same time since one of the contributor of the formula is still on the "Source.xls", it updates the reference of the 2nd contributor and point to the "Source.xls".
4> Move back both "Aggregation" and "Contributor-1" to "Source.xls" again. The formula on "Aggregation" sheet (Source.xls); cell C8 is back in its original form. Its {='Contributor-1'!C8+'Contributor-2'!C8}.
The above functionality is what I require which I am not able to achive using "Aspose.Cells for Java". I tried the copy option in excel itself and it does not suffice to my need. Going by the fact that Aspose behaves just as Ms Excel, I assumed that Worksheet.copy( ) is not the solution for me. However, please guide me if there is any way to achieve my requirement.
What is wrong with Copying Worksheet
1> Check the formula on "Aggregation" sheet (Source.xls); cell C8. Its {='Contributor-1'!C8+'Contributor-2'!C8}.
2> Copy the sheet "Contributor-1" to the other workbook (Target.xls). Now check the formula in[Source.xls]Aggregation!C8; it is {='Contributor-1'!C8+'Contributor-2'!C8}. Here Ms Excel still points to the same workbook.
3> Copy the sheet "Aggregation" to the other workbook (Target.xls). Now check the formula in[Source.xls]Aggregation!C8; it is { ='[Source.xls]Contributor-1'!C8+'[Source.xls]Contributor-2'!C8 }. Here Ms Excel automatically changes the reference in the formula to point to the source workbook; at the same time even if one of the contributor of the formula is present in the "Target.xls", it do not recognise it.
4> Copy the "Contributor-1" to "Source.xls" back again. Since there is already a sheet with the same name in source may be we have to delete the sheet from "Source.xls".
5> On deletion of "Cotributor-1" from "Source.xls"; the formula gets currupt. The formula on [Source.xls]Aggregation!C8 is now { =#REF!C8+'Contributor-2'!C8 } and on [Target.xls]Aggregation!C8 is now { ='[Source.xls]#REF'!C8+'[Source.xls]Contributor-2'!C8 }. Even if we copy the "Contibutor-1" from "Target.xls" to "Source.xls", there is no difference made. The formula is lost. This is where it goes wrong for me ....................
Please help me in achieving this objective. I am stuck here.
Thanks,
Malay
Hi Malay,
We will look into the feature ASAP but after completing some important tasks on hand.
Thanks for being patient!
Hi,
This feature has been supported. Please download the latest version: Aspose.Cells for Java 7.0.1