Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin
Wiki Markup
ExcelApplication's [CopyPaste|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 [CopyPasteProperties] object to store information on precisely how data should be copied.  Options include: values, formulas, formatting, merged cells, comments, height and width.

h2. 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:c#}
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 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:c#}	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:c#}	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:c#}
	[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\ Note: CopyColumnWidth and CopyRowHeight are set to false to avoid unexpected results. ||
|| 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".{info}

h2. 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:c#}
     //---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}