Copy worksheet from one workbook to another

Is it possible to copy a worksheet that is in a workbook and paste it into another workbook.

say i have ten workbooks and they each have a sheet called sheet1.
can i extract all the sheets called sheet1 from each of these workbooks and place them into a single workbook changing the sheet name as necessary?

Any help you can give here would be greatly appreciated.

Yes. You can use Copying and Moving Worksheets method to do it.

Hi noticed in another thread that you said that worksheet.copy does not support the copying of images and similar objects.
do you have any intention to include images in the copy process in the future?
as it stands we cant use it without the support for images.

thanks.

We will investigate to copy images. However, we don’t have plans for copying other drawing objects for its complexity.

Any news on whether you would be able to copy images when copying the worksheets across workbooks?
can it be done?
how long will it take to implement?

thanks,

Blaggard

Hi Blaggard,

We are working on this feature. However, because of the complexity, it will take about 2 months to make it. Thanks for your patience.

Hi,

I am evaluating version 3.4. The Worksheet.Copy method always throws an exception “System.OverflowException: Arithmetic operation resulted in an overflow”. Even when I’m copying a very simple worksheet with only some text in it. Is this a problem with the evalution version ?

  1. Please try v3.4.5 at Aspose.Cells for .NET (Latest Version) .

    2. If you still meet some problem, could you please post your file and sample code here? I will check it ASAP.

Hi,

I am using 3.4.5. Here’s the code:

private void Button1_Click(object sender, System.EventArgs e)

{

Excel testExcel=new Excel();

testExcel.Open(MapPath(Request.ApplicationPath)+"\excel\test.xls");

Excel reportExcel=new Excel();

reportExcel.Open(MapPath(Request.ApplicationPath)+"\excel\report.xls");

Worksheet sheetToCopy=testExcel.WorksheetsDevil;

int newSheetID=0;

Worksheet newSheet=null;

Excel newExcel=null;

if(chkInsertInSameSheet.Checked)

{

newSheetID=testExcel.Worksheets.Add();

newSheet=testExcel.Worksheets[newSheetID];

newExcel=testExcel;

}

else

{

newSheetID=reportExcel.Worksheets.Add();

newSheet=reportExcel.Worksheets[newSheetID];

newExcel=reportExcel;

}

newSheet.Copy(sheetToCopy); //==> throws an exception if not chkInsertInSameSheet.Checked

newExcel.Save(MapPath(Request.ApplicationPath)+"\excel\NEW.xls",FileFormatType.Default);

}


When I Copy the worksheet to the same workbook, no problem. When I Copy to another Workbook I always get an “System.OverflowException: Arithmetic operation resulted in an overflow”. The worksheet I am copying contains a few cells with only text.

I didn’t find any problem with your sample code. I have done some enhancement on Worksheet.Copy method since v3.4.5, so please try this attached fix.

If you still meet the problem, could you please post your test.xls and report.xls here? It must have something associated with those files. Thank you very much.

Hello,

Thank you for your quick response, unfortunately the fix did not work.

Here are the two xls-files. If I create a new xls file, add some sheets and use this as test.xls it works, but it doesn’t work with the test.xls I have to use for my project (which is supplied by the client). I tried deleting some worksheets from the test.xls and then it works, but I have no idea why this makes a difference.

Hello,

I just found that after deleting the worksheet with the name Données everything works fine. Any idea what could be wrong here ?

Thanks for your report. I fixed it. Please try this fix.

It works, thanks !

Documentation says:

Worksheet.Copy method

You can copy data from another worksheet in the same file or another file. However, this method does not support to copy drawing objects, such as comments, images and charts.

how do I copy the images charts etc.?

Workbook twkBook = OpenExcelTemplate();

Workbook wkBook = new Workbook(@"C:\Documents and Settings\mc28000\My Documents\testtemplate.xls");
wkBook.Worksheets.Clear();
wkBook.Worksheets.Add("Delete");


Worksheet wsPrintPageSetup = twkBook.Worksheets["PrintPageSetup"];
int i = wkBook.Worksheets.Add();
Worksheet wsCopyPrintPageSetup = wkBook.Worksheets[i];
wsCopyPrintPageSetup.Copy(wsPrintPageSetup); // Never copies the content or format
wsCopyPrintPageSetup.Name = "PrintSetupPage";


Worksheet wsOutputTemplate = twkBook.Worksheets["OutputTemplate"];
i = wkBook.Worksheets.Add();
Worksheet wsCopyOutputTemplate = wkBook.Worksheets[i];
wsCopyOutputTemplate.Copy(wsPrintPageSetup); // Never copies the content or format
wsCopyOutputTemplate.Name = "OutputTemplate";

wkBook.Save(@"C:\Documents and Settings\username\My Documents\test.xls");

Hi,

Please ignore the remarks for the Worksheet.Copy method:

Copying Worksheets

We will update the remarks soon.

For your information, in the latest versions of the product (e.g v6.0.0, 6.0.1), this method will also copy the formatting, drawing objects, charts, images etc. with data too.

Check the document:

Moving Worksheets

I will quote some lines from the document here:

“Aspose.Cells supports Copy or Move worksheets within or between workbooks. The worksheets including data, formattings, tables, matrices, charts, images and other objects are copied with the highest degree of precision.”

If you find any issue, give us your template Excel file containing the worksheet you want to copy, we will check it soon.

Thank you.