Free Support Forum - aspose.com

Paste Special

Hello,


I want to copy column B which has formulas and paste again on column B but only the values (i.e. without any formulas).

Is it possible to get this Paste Special functionality?

Regards,
Jash

Hi,


I think you may try to use Cells.removeFormulas() to replace the formulas with their respective values in the cells, see the sample code below:
e.g
Sample code:

Cells cells = worksheet.getCells();
cells.removeFormulas();

Regarding Paste Special functionality, you may instantiate PasteOptions object and specify your desired option. For example, you may create the source Range and destination Range and then copy the range with respect to your desired PasteOptions to get Paste Special functionality.
e.g
Sample code:

PasteOptions options = new PasteOptions();
options.setPasteType(PasteType.VALUES);

range.copy(sourceRange, options);

Thank you.

Thank You for the reply.


I want to know how to set ‘range’ and ‘sourceRange’ values?

Regards,
Jash

Hi,


Please see and run the following sample code with the attached template file for your reference. In the template file’s first worksheet cells, e.g B1:B9 have formulas in it. We will replace the formulas with their calculated values accordingly:
e.g
Sample code:

Workbook workbook = new Workbook(“Bk_pastespecial.xlsx”);

Cells cells = workbook.getWorksheets().get(0).getCells();

//Create source and destination ranges accordingly.
Range range = cells.createRange(“B1:B9”);
Range dRange = cells.createRange(“B1:B9”);

//Calculate the formulas to get calculated values in the formula cells.
workbook.calculateFormula();
PasteOptions options = new PasteOptions();
options.setPasteType(PasteType.VALUES);
dRange.copy(range, options);
workbook.save(“out1.xlsx”);

Let us know if I can be of any further help.

Thank you.