.
| Wiki Markupexcerpt |
|---|
ExcelApplication's [|Worksheet.CopyPaste]method allows you to copy data from an external worksheet. It can also be used to copy data from a worksheet within the same workbook. |
CopyPaste
...
is
...
a
...
method
...
of
...
the
...
Worksheet
...
object
...
and
...
should
...
be
...
called
...
from
...
the
...
destination
...
worksheet.
...
CopyPaste
...
uses
...
a
...
...
object
...
to
...
store
...
information
...
on
...
precisely
...
how
...
data
...
should
...
be
...
copied.
...
Options
...
include:
...
values,
...
formulas,
...
formatting,
...
merged
...
cells,
...
comments,
...
height
...
and
...
width.
...
Step
...
1:
...
Setting
...
up
...
your
...
code
...
to
...
copy
...
from
...
an
...
external
...
worksheet:
...
In
...
the
...
code
...
below,
...
a
...
source
...
worksheet
...
is
...
opened
...
and
...
a
...
destination
...
worksheet
...
is
...
created.
...
The
...
CopyPaste
...
method
...
takes
...
an
...
Area
...
object
...
to
...
represent
...
the
...
area
...
to
...
be
...
copied,
...
so
...
this
...
is
...
created
...
first.
...
| Code Block | ||
|---|---|---|
| ||
private ExcelApplication xlw; private Workbook wb, wb2; private Worksheet wsSrc, wsDest; private void GenerateWorkbook() { //--- Store the path to the source workbook string strPathSrc = @"C:\\WorkDirectory\\MySourceWorkbook.xls"; //--- Create an Excelwriter Application object xlw = new ExcelApplication(); //--- Use the Application object to create a workbook //--- that will contain the destination worksheet wb = xlw.Create(); //--- Specify the destination worksheet wsDest = wb.Worksheets[0]; wsDest.Name = "DestinationSheet"; //--- Open the workbook that contains the (source) worksheet to be copied wb2 = xlw.Open(strPathSrc); //--- Specify the worksheet that will be the source of the data wsSrc = wb2.Worksheets[0]; //--- You must first create an area that represents the information //--- in the source worksheet that you want to copy. Area areaSrc = wsSrc.CreateArea("A1:C3"); {code} h2. Step |
Step 2:
...
Creating
...
a
...
CopyPasteProperties
...
object
...
Now
...
that
...
the
...
data
...
to
...
be
...
copied
...
is
...
mapped
...
to
...
an
...
Area
...
object,
...
a
...
CopyProperties
...
object
...
should
...
be
...
created
...
from
...
the
...
destination
...
workbook.
...
The
...
CopyProperties
...
object
...
is
...
used
...
to
...
specify
...
precisely
...
what
...
within
...
the
...
area
...
should
...
be
...
copied.
...
In
...
the
...
code
...
below,
...
only
...
the
...
values
...
will
...
be
...
copied.
...
| Code Block | ||
|---|---|---|
| ||
CopyPasteProperties properties = wb.CreateCopyPasteProperties(CopyPasteProperties.CopyPasteType.ValuesOnly);{code} |
The
...
ValuesOnly
...
type
...
is
...
a
...
convenience
...
type.
...
The
...
same
...
result
...
could
...
be
...
achieved
...
by
...
using
...
the
...
None
...
type
...
and
...
adding
...
a
...
second
...
line
...
of
...
code
...
where
...
the
...
CopyValues
...
property
...
was
...
set
...
to
...
true.
...
| Code Block | ||
|---|---|---|
| ||
CopyPasteProperties properties = wb.CreateCopyPasteProperties(CopyPasteProperties.CopyPasteType.None); properties.CopyValues = true;{code} |
Using
...
the
...
None
...
type
...
allows
...
you
...
to
...
set
...
Copy
...
properties
...
individually.
...
The
...
available
...
properties
...
include:
...
| Code Block | ||
|---|---|---|
| ||
[CopyPasteProperties].CopyColumnWidth = [true|false];
[CopyPasteProperties].CopyComments = [true|false];
[CopyPasteProperties].CopyFormatting = [true|false];
[CopyPasteProperties].CopyFormulas = [true|false];
[CopyPasteProperties].CopyMergedCells = [true|false];
[CopyPasteProperties].CopyRowHeight = [true|false];
[CopyPasteProperties].CopyValues = [true|false];
{code}
|
Besides
...
None,
...
other
...
CopyPasteType
...
values
...
are
...
available,
...
saving
...
you
...
unnecessary
...
lines
...
of
...
code:
...
CopyPasteType | Sets the following CopyPasteProperty values to true |
|---|---|
| None | No CopyPasteProperty values set. |
All | CopyComments, CopyFormatting, CopyFormulas, CopyMergedCells and CopyValues |
AllPlusRowHeightAndColumnWidth | CopyComments, CopyFormatting, CopyFormulas, CopyMergedCells, CopyValues, CopyColumnWidth and CopyRowHeight |
FormulasAndFormatting | CopyFormulas and CopyFormatting |
ValuesAndFormatting | CopyValues and CopyFormatting |
ValuesFormulasAndFormatting | CopyValues, CopyFormulas and CopyFormatting |
ValuesOnly | CopyValues |
| Info |
|---|
Optionally, if you intend to use the CopyPasteType of All, you can save even more lines of code by using CopyPaste method without the properties object parameter. Omission of the properties object parameter will result in the assumption of the value of "All". |
Step 3: Calling the CopyPaste method from the Destination Worksheet
To complete the code sample that is being built, only the CopyPaste method now needs to be called. You will pass it the address of the cell that represents the upper left corner of the location to paste and the Area and CopyPasteProperties objects that were created above.
| Code Block | ||
|---|---|---|
| ||
//---Call the CopyPaste method from the destination worksheet. wsDest.CopyPaste("C3", areaSrc, properties); //---Open the spreadsheet in the browser to check the results xlw.Save(wb, Page.Response, "ExcelApp.xls", false); }{code} {scrollbar} |