Clipboard Support
The document model offers an easy way to copy and paste multiple values. The library provides flexible API that allows you to choose the content and formatting that gets included in the pasted region. This article demonstrates how to copy and paste data with different paste options.
Copy
In order to copy values that appear in your worksheet, create a CellSelection for the desired cell region and invoke its Copy() method. The method returns a WorksheetFragment instance that holds only the values you copied. The WorksheetFragment is a piece of worksheet designed to keep the data you copy and its properties. You can later pass the worksheet fragment to the Paste() method in order to get the values pasted in another worksheet.
Example 1 creates a new workbook with a single worksheet and assigns some sample values to the A1:B3 region. Further, the code creates a selection for the cell region and calls its Copy() method. The returned WorksheetFragment can later be used for pasting operation.
Example 1: Copy selected cells
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue("Product");
worksheet.Cells[1, 0].SetValue("Service");
worksheet.Cells[0, 1].SetValue(17.4);
worksheet.Cells[1, 1].SetValue(12.9);
worksheet.Cells[2, 0].SetValue("Total");
worksheet.Cells[2, 1].SetValue("=SUM(A1:B1)");
CellRange copiedCellRange = new CellRange(0, 0, 2, 1);
WorksheetFragment worksheetFragment = worksheet.Cells[copiedCellRange].Copy();
Paste
The document model provides control over the content and the formatting included in the pasted region. The Paste() method requires two arguments: a WorksheetFragment that contains the values to be pasted and a PasteOptions instance that determines the type of information that will be pasted from the fragment. The PasteOptions class introduces a PasteType property that indicates the type of the paste. These are the supported paste types:
All: Pastes everything, including text, numbers, formulas and their formatting.
Formulas: Pastes text, numbers and formulas. However, this option ignores cells formatting.
Formulas and Number Formatting: Pastes text, numbers and formulas, and applies formatting of the copied cells to the pasted cells.
Column Widths: Pastes text, numbers and formulas, and applies the column widths of the copied cells to the pasted cells.
Values: Pastes the calculated result of the formulas, ignoring formatting and column width.
Values and Number Formatting: Pastes the calculated results of any formula and the formatting of the copied cells to the pasted cells.
Formatting: Pastes only the formatting of the copied cells to the pasted cells.
Example 2 creates a new workbook with an empty worksheet. Further, the example sets the Value of cell A1 to =CONCATENATE("Rad" ,"Spreadsheet") and its ForeColor to green. The code copies the contents of A1 and pastes it in A2 using All PasteType.
Example 2: Copy all
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets.Add();
worksheet.Cells[0, 0].SetValue("=CONCATENATE(\"Rad\", \"Spreadsheet\")");
worksheet.Cells[0, 0].SetForeColor(new ThemableColor(Colors.Green));
WorksheetFragment worksheetFragment = worksheet.Cells[0, 0].Copy();
PasteOptions pasteOptions = new PasteOptions(PasteType.All);
worksheet.Cells[1, 0].Paste(worksheetFragment, pasteOptions);
Using different PasteType, however, produces different output. Example 3 pastes the contents of A1 with Values PasteType, which results in a value "RadSpreadsheet" instead of =CONCATENATE("Rad" ,"Spreadsheet") and default ForeColor instead of green:
Example 3: Paste using PasteType.Values
PasteOptions pasteOptionsValues = new PasteOptions(PasteType.Values);
worksheet.Cells[2, 0].Paste(worksheetFragment, pasteOptionsValues);
If you would like to paste the formula contained in A1, not only its result, and keep its formatting, combine the Values and Formats paste types:
Example 4 combines the Value and Formats paste types and preserves both the contents and formatting of the copied cell selection.
Example 4: Combine Values and Formats PasteType
PasteOptions valuesAndFormatting = new PasteOptions(PasteType.Formulas | PasteType.Formats);
worksheet.Cells[3, 0].Paste(worksheetFragment, valuesAndFormatting);