We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Copy a sheet from one excel to another excel file

I am using aspose for .net application.

have an excel workbook, In that work have many sheets. Among the sheets i am trying copy one sheet from the source workbook to destination workbook.
The Sheet has with Pivot Tables and macros. Those are not properly copying to the destination work book.

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(destinationFileName);
Aspose.Cells.Workbook newWorkbook = new Aspose.Cells.Workbook();
newWorkbook.Worksheets[0].Copy(workbook.Worksheets[dataSheetName]);
newWorkbook.Save(m_filePath + "\\" + "test.xlsx");

i am not using any remvoing method.

please help me

Hi,


Thanks for providing us some details and sample code.

Well, vba codes/ macros are stored at Workbook level, so when you copy a worksheet to another workbook, vba codes/macros won’t be copied. Regarding PivotTable issue, we need your template file to evaluate your issue on our end. Please provide your template Excel file (containing the PivotTable), we will check it soon.

Thank you.

Hi Amjad,

I have the similar requirement.

I need to copy the single worksheet with Macros and pivottables.

Is there really a chance to do this using aspose.



Your assistance will be appreciated highly…



Thanks in Advance…



Hi,

Thanks for your posting and using Aspose.Cells.

Please provide us your excel file so that we could test your issue. It seems, the above post by Amjad is still valid because macros are stored at Workbook level not at Worksheet level.

Hi,

For your reference, i am attaching the file. I need to copy (Enterprise Report) sheet to other workbook.You can observe that, we have pivot tables in that sheet. I will do some click events in that sheet.
I need to copy the single worksheet with Macros and pivottables.

Hi,

Thanks for your posting and using Aspose.Cells.

I have looked into this issue with a Microsoft Excel perspective and found Microsoft Excel does not support it as a standalone, but it supports it with external links dependency i.e. the generated excel file will depend on external links.

For example, when pivot table or macro is copied to another workbook, they refer to external links which do not work if these links are changed or renamed.

I have attached two excel files for your reference as well as screenshots. First contains pivot table and macro button. The second is generated by copying the sheet from the first one. As you can see in the second workbook “Book1.xlsx”, although pivot table and button are copied, but they do not work because they are referring to external workbook i.e “sample.xlsm” which I have renamed as “renamed-sample.xlsm”.

Let us know do you want Aspose.Cells to support this feature like Microsoft Excel does i.e with external links dependency.

Hi,


I wish Aspose.Cells to support external links feature like Excel.

Hi Shakeel,

Exactly,



actually aspose.cells doesn’t support click events … I have the logic to perform the click evens using Microsoft.interops …







Bu the challenge is Microsoft.interop doesn’t handle big files as it throws outofmemory exception since it uses huge % of RAM.

So I have the below approach like I will remove the rest of the sheets except the sheet I want to work.

though I am able to aceive this using aspose but unfortunately all the macros were disabled. its copying the content as a plain text.



the area I need your help is… could you please provide me a suggestion that can we copy single sheet into another excel with its dependency.( so that file size will be reduced and my dependent date will be in the new excel)…

Thanks in advance.



Hi,

Thanks for your posting and using Aspose.Cells.

I am investigating this issue and trying some workarounds. I will update you asap.

Hi,

Thanks for using Aspose.Cells.

Please see the following code. It copies source workbook to target workbook. This way all the things from source including pivot tables and macros are copied to target workbook. However, target workbook’s existing worksheets are removed. Then the code re-inserts the existing worksheets by worksheet copy method.

Please check all the participant files inside the code

  • Source.xlsm
  • Target.xlsm
  • TargetOut.xlsm

which I have attached as a zip file with this post.

C#
Workbook src = new Workbook(“Source.xlsm”);
Workbook tar = new Workbook(“Target.xlsm”);
Workbook tar1 = new Workbook(“Target.xlsm”);

//It will copy everything from source to target
//but the target sheets will be removed
tar.Copy(src);

//Get the target sheets and re-insert them
for (int i = 0; i < tar1.Worksheets.Count; i++)
{
Worksheet tar1Sht = tar1.Worksheets[i];
Worksheet tarSht = tar.Worksheets.Add(tar1Sht.Name);
tarSht.Copy(tar1Sht);
}

//You can now remove any unwanted sheet here
//…

//Save the target
tar.Save(“TargetOut.xlsm”);

Hi As per your reply, i used the same code…but at

tar.Copy(Src)… after the step src file sheets are not copying into tar workbook.
can you tell why it is not copying?

Hi Andala,


What version of Aspose.Cells for .NET API are you currently using? Please note, we have shared the code snippet after testing it against the latest release of Aspose.Cells for .NET (Latest Version). Please give a try to this release and let us know your feedback.

Hi,


We are using Aspose.Cells (7.5.2.2) version…

I am using below code. but pivot tables are not copying.

Aspose.Cells.Workbook src = new Aspose.Cells.Workbook(destinationFileName);
Aspose.Cells.Workbook tar = new Aspose.Cells.Workbook(m_filePath + “\” + “abc.xlsx”);
Aspose.Cells.Workbook tar1 = new Aspose.Cells.Workbook(m_filePath + “\” + “abc.xlsx”);
int srcSheetCount = src.Worksheets.Count;
for (int i = 0; i <= srcSheetCount; i++)
{
Aspose.Cells.Worksheet workSheet = src.Worksheets[i];
Aspose.Cells.Worksheet tarWorkSheet = tar.Worksheets.Add(workSheet.Name);
tarWorkSheet.Copy(workSheet);
}
License testlic = new License();
testlic.SetLicense(“Aspose.Cells.lic”);
tar.Save(m_filePath + “\” + “abc.xlsx”);

Hi,

Thanks for your posting and using Aspose.Cells.

I have attached a sample console application project created in Visual Studio 2013. It uses the 7.5.2.1 version. Please download it and run at your end and let us know your feedback. You can also update its version to 7.5.2.2.

Please see license setting code always comes first before you create a workbook object as I have shown in the sample project.

Hi,

I was wondering if Aspose now supports this feature (ie changes references to another sheet be an external link) when copying a single worksheet to another workbook?

We are trying to replace Excel with Aspose so it needs to behave in the same way.

We are currently using the 17.6.0.0 version of Aspose.Cells for .NET and it still appears that the resultant workbook has #REF wherever excel would have the external reference.

Thanks, Julie

@t1jsw,

Could you create a simple console demo application, zip it and post us here to show the issue, we will check it soon. Also provide your template file(s) and output file by Aspose.Cells APIs. Moreover, please provide your expected file which you can create by manually performing the copying steps in MS Excel, this will help us really to evaluate your issue precisely and consequently figure it out soon.

Hi Amjad,

This is the code:

   ''Use file stream to open workbook so it can be opened as ReadOnly
    Dim loStrm As New System.IO.FileStream("C:\Temp\Original.xlsx", IO.FileMode.Open, IO.FileAccess.Read, IO.FileShare.ReadWrite)
    Dim loOrigWB As New Aspose.Cells.Workbook(loStrm)
    loStrm.Close()

    Dim loNewWB As New Aspose.Cells.Workbook(Aspose.Cells.FileFormatType.Xlsx)

    loNewWB.Worksheets("Sheet1").Copy(loOrigWB.Worksheets("Sheet1"))
    loNewWB.Save("C:\Temp\AsposeCopy.xlsx")

Attached are 3 xlsx files. The original, the copy using excel and the copy using above code.

XlsxFiles.zip (20.4 KB)

Also below is the message I get when I open the aspose version and enable content.

image.png (3.4 KB)

As you can see the excel version has a valid reference to the original workbook but the Aspose version does not.

Thanks, Julie

@t1jsw,

Thanks for the template file, sample code and screenshot.

After an initial test, I observed the issue as you mentioned by using his sample code with your template file. I found invalid reference to original file issue when copying a sheet from one workbook to other.

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

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

@t1jsw

Please set the file name, otherwise we do not know which file should be referred to.

Please see the following code.

C#

var loStrm = new FileStream("d:\\filetemp\\Original.xlsx", FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
Workbook loOrigWB = new Aspose.Cells.Workbook(loStrm);
loOrigWB.FileName = "Original.xlsx";
loStrm.Close();

Workbook loNewWB = new Aspose.Cells.Workbook(Aspose.Cells.FileFormatType.Xlsx);
loNewWB.Worksheets["Sheet1"].Copy(loOrigWB.Worksheets["Sheet1"]);
loNewWB.Save(dir +"dest.xlsx");

Thanks. That works when you are saving into the same folder.

If you save into a different folder it puts the path you are saving into as the path rather than the original WB path so even though it now has correct file name for the reference it has the incorrect path.

Thanks, Julie