Copy Paste Special

Aspose.Cells Product Family
1

I'm trying to copy a range of cells with a mix of values and formulas. The cells are also formatted. In excel, the following copies the cells in the range, then pastes them (using PasteSpecial) back over the same range. This keeps all formatting in place, only replacing the formulas with the values.

ActiveCell.SpecialCells(xlLastCell).Select
ActiveSheet.Range(Cells(1, 1), Cells(ActiveCell.row, 255)).Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

A couple questions regarding aspose.cells. First, is this even possible? My first try was:

Dim range1 As Range = wsSheet.Cells.CreateRange(0, 0, wsSheet.Cells.MaxDataRow, 254)
range1.CopyValue(range1)

As you can see, I created a range, then tried to use CopyValue to copy the values back over the same range. When I open the workbook, there are no changes at all - the sheet still has all formulas and formatting. So is the problem trying to copy a range over itself? Assuming I can get that to work, will CopyValues remove all the formatting?

I'm simply trying to flatten the entire worksheet, so the formatting remains but all formulas are replaced with the values. I'm guessing I can loop over every cell in the range and replace the formulas with the value, but some of the sheets are very large, so hopefully one of the existing methods will do what I need.

Ultimately I'll probably be copying a worksheet from one workbook to another, so if there is a way to do this (keep formatting but convert formulas to values) using one of the worksheet copy methods that would also work.

Rick

2

Hi,

I think you may loop through a range and then check if the cell has formula in it, so you may try to replace with the calculated value to only those cells having formulas. I think it will not be a big deal regarding performance too.

May the following sample code help you for your requirements, kindly consult it and try to create your own.

Sample code:

Dim workbook As New Workbook()
workbook.Open("f:\test\MyBook.xls")
Dim worksheet As Worksheet = workbook.Worksheets(0)
Dim range As Range = worksheet.Cells.CreateRange(0,0,worksheet.Cells.MaxDataRow,254)
workbook.CalculateFormula()

For i As Integer = range.FirstRow To range.RowCount + range.FirstRow - 1

For j As Integer = range.FirstColumn To range.ColumnCount + range.FirstColumn - 1

If worksheet.Cells(i, j).IsFormula Then
worksheet.Cells(i, j).PutValue(worksheet.Cells(i, j).StringValue, True)

End If

Next j

Next i

workbook.Save("f:\test\outputBook.xls")
Thank you.
3

Implemented the above solution, and performance seems to be a big issue. For a sheet with 138 rows, it takes around 15 seconds to perform the 2 loops (i and j). For a sheet with 39 rows, it still takes 3-4 seconds. Using the Excel-native CopyPasteSpecial, it takes milliseconds in either case.

Rick

4

Hi,

Could you create a sample console application to reproduce the issue. And, which version of Aspose.Cells you are using? Could you try the attached version.

Thank you.

5

In trying to create an app for this problem, I keep running into the weekday problem I mentioned in another message (<A href="https://forum.aspose.com/t/79693</A>). I suspect the initial problem may be caused because there were numerous #NAME errors because I was using a UDF within Excel. To fix that, I converted those to native Excel formulas, but now Aspose.cells won't calculate the sheets.</P> <P>Regarding the version, how do I tell exactly which version I have? I know it is the latest version available for download (4.7) but don't know if it is 4.7.0.8.</P> <P>Also, assuming a new version of cells is sent to me as a patch, how exactly do I replace the version I have. Note that I have the Total version so I'm guessing I'll need to replace only 1 dll?</P> <P>Rick</P>

6

Hi Rick,

We have posted a fix for your other issue (Weekday formula issue), so please try it in your project. If you still find any issue, please do create a sample application and post it here to reproduce the issue you are facing.

To know about the version/fix you are using, please check the doc topic for your reference: Check Version Number of the Component

And, yes, you need to replace 1 .dll file i.e…, Aspose.Cells.dll.

Feel free to contact us any time if you need further help or clarifications.

Thank you.

7

I attached the spreadsheet in the Weekday thread. It still takes ~20 seconds to flatten 2 sheets using the code below. I call this function with the sheetname (in the sample I sent you it will be "Weekly Data" then "Monthly Data"). The Weekly sheet takes about 15 seconds, the monthly about 4. I tried both with testing to see if the cell is a formula, and also (as below) just converting all cells to values. In Excel, using CopyPasteSpecial, this takes less than a second per sheet.

Sub DoPaste(ByVal wsSheet As Worksheet)
Dim oWatch As New Stopwatch
oWatch.Start()
Dim range As Range = wsSheet.Cells.CreateRange(0, 0, wsSheet.Cells.MaxDataRow, 254)
'Workbook.CalculateFormula()
For i As Integer = range.FirstRow To range.RowCount + range.FirstRow
For j As Integer = range.FirstColumn To range.ColumnCount + range.FirstColumn - 1
'If wsSheet.Cells(i, j).IsFormula Then
wsSheet.Cells(i, j).PutValue(wsSheet.Cells(i, j).Value, True)
'End If
Next j
Next i
oWatch.Stop()
Debug.Print("time " & oWatch.ElapsedMilliseconds.ToString)
End Sub

8

Hi,

I think you may change your code to:

Sub DoPaste(ByVal wsSheet As Worksheet)
Dim oWatch As New Stopwatch
oWatch.Start()
Dim range As Range = wsSheet.Cells.CreateRange(0, 0, wsSheet.Cells.MaxDataRow, 254)

For i As Integer = range.FirstRow To range.RowCount + range.FirstRow - 1
For j As Integer = range.FirstColumn To range.ColumnCount + range.FirstColumn - 1

If wsSheet.Cells(i, j).IsFormula Then
wsSheet.Cells(i, j).PutValue(wsSheet.Cells(i, j).Value)

End If

Next j
Next i
oWatch.Stop()
Debug.Print("time " & oWatch.ElapsedMilliseconds.ToString)
End Sub

Since sufficient amount of time is consumed for converting the data to its specific types using the overloaded method Cell.PutValue(value, True), so, you may use Cell.PutValue(value, True) instead.

Thank you.

9

Thanks! 407 ms for the first sheet, 39 ms for the second!

Rick