DotnetSlackers Code-Snippet

MaximumASP
Social Bookmarking
Online Shopping
asp.net hosting
UK online local dating

Copy Worksheets with Tables, Charts & Images Within or b/t Workbooks

Published: 30 Sep 2011 By Sher Azam

This technical tip shows how to Copy or Move worksheets within or between workbooks. Sometimes, you do need a number of worksheets with common formatting and data entry. For example, if you work with quarterly budgets, you might want to create a workbook with sheets that contain the same column headings, row headings, and formulas. There is a way to do this: by creating one sheet and then copying it three times. Generally, it's easier to create and copy the worksheets.

// The following example shows how to copy a worksheet from one workbook to another workbook.

[C#]
 
//Create a new Workbook.
Workbook excelWorkbook0 = new Workbook();
 
//Get the first worksheet in the book.
Worksheet ws0 = excelWorkbook0.Worksheets[0];
 
//Put some data into header rows (A1:A4)
for (int i = 0; i < 5; i++)
{
    ws0.Cells[i, 0].PutValue(string.Format("Header Row {0}", i));
}
 
//Put some detail data (A5:A999)
for (int i = 5; i < 1000; i++)
{
    ws0.Cells[i, 0].PutValue(string.Format("Detail Row {0}", i));
}
 
//Define a pagesetup object based on the first worksheet. 
PageSetup pagesetup = ws0.PageSetup;
 
//The first five rows are repeated in each page... 
//It can be seen in print preview.
pagesetup.PrintTitleRows = "$1:$5";
 
//Create another Workbook.
Workbook excelWorkbook1 = new Workbook();
 
//Get the first worksheet in the book.
Worksheet ws1 = excelWorkbook1.Worksheets[0];
 
//Name the worksheet.
ws1.Name = "MySheet";
 
//Copy data from the first worksheet of the first workbook into the
//first worksheet of the second workbook.
ws1.Copy(ws0);
 
//Save the excel file.
excelWorkbook1.Save(@"d:testcopyworksheet.xls");

[VB]

'Create a new Workbook.
Dim excelWorkbook0 As Workbook = New Workbook()
 
'Get the first worksheet in the book.
Dim ws0 As Worksheet = excelWorkbook0.Worksheets(0)
 
'Put some data into header rows (A1:A4)
Dim i As Integer
For i = 0 To 4 Step 1
 
    ws0.Cells(i, 0).PutValue(String.Format("Header Row {0}", i))
Next
 
'Put some detail data (A5:A999)
For i = 5 To 999 Step 1
 
    ws0.Cells(i, 0).PutValue(String.Format("Detail Row {0}", i))
Next
 
'Define a pagesetup object based on the first worksheet. 
Dim pagesetup As PageSetup = ws0.PageSetup
 
'The first five rows are repeated in each page... 
'It can be seen in print preview.
pagesetup.PrintTitleRows = "$1:$5"
 
'Create another Workbook.
Dim excelWorkbook1 As Workbook = New Workbook()
 
'Get the first worksheet in the book.
Dim ws1 As Worksheet = excelWorkbook1.Worksheets(0)
 
'Name the worksheet.
ws1.Name = "MySheet"
 
'Copy data from the first worksheet of the first workbook into the
'first worksheet of the second workbook.
ws1.Copy(ws0)
 
'Save the excel file.
excelWorkbook1.Save("d:testcopyworksheet.xls")

Copy worksheets within workbooks Move worksheets within workbooks Copy worksheets between workbooks worksheets with data formatting

Please login to leave a comment.

Login

Lost Password?

Navigation

Our Projects

Email Service

News Archive

Subscribe

Product Spotlight

Hosting

Copy Worksheets with Tables, Charts & Images Within or b/t Workbooks