We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Copy from single cell to multiple cells

Aspose.Cells Product Family
#1

Hi,
I noticed that if you copy from range A1 to cells A1:A10 Aspose does not copy the value into each cell, in the way that a paste operation in Excel does. See the example code below (which does not <!–[if gte mso 10]>

/* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-qformat:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin-top:0cm; mso-para-margin-right:0cm; mso-para-margin-bottom:10.0pt; mso-para-margin-left:0cm; line-height:115%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri","sans-serif"; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-fareast-font-family:"Times New Roman"; mso-fareast-theme-font:minor-fareast; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin;}

<![endif]–><span style=“font-size: 11pt; line-height: 115%; font-family: “Calibri”,“sans-serif”;”>actually
do anything).


sheet.Cells(“A1”).PutValue(“TEST”)

Dim source, target As Aspose.Cells.Range

source = sheet.Cells.CreateRange(0, 0, 1, 1)
target = sheet.Cells.CreateRange(0, 0, 10, 1)

target.Copy(source)

I have written a formula into a cell, and now want to paste it down to all rows in the column, but it appears that I cannot do this without manually setting each rows’s formula.
Is the copy/paste support in Cells limited to only copying “one to one”?

Thanks,

Shane

#2

Hi,

Well, the source and the destination range should be equal for copy/paste operation for ranges. We will check if we can enhance it. For your need, I think you may use SharedFormula feature provided by Aspose.Cells for copying and pasting formulas in a range of cells in a given column.

See the document for reference:
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/setting-shared-formula.html

Here is my sample code for your reference:

'Instantiate a new Workbook
Dim workbook As New Workbook()
'Open an excel file
workbook.Open(“e:\test\Book1.xls”)
'Get the cells collection in the first worksheet
Dim cells As Cells = workbook.Worksheets(0).Cells
'Apply the shared formula from B1 cell to the range i.e…, A10:A20
cells(“A10”).SetSharedFormula(cells(“B1”).Formula,10, 1)
'Save the excel file
workbook.Save(“e:\test\outsharedformulas.xls”);


Thank you.

#3

That works great - thanks very much.

One small problem however - SetSharedFormula does not appear to work for formula that contain a reference to another sheet - e.g. “=‘Sheet 2’!A1”.
When I tried this it set the range in the formula to an entirely different cell. Can you test this for me to see if you get the same result?

Thanks,

Shane

#4

Hi Shane,

Well, the reference to other sheet in the shared formula will not be automatically changed. So, I am afraid, you have to copy the cell one by one with Cell.Copy() method for that particular case.

Thank you.

#5

So if I have a formula in a cell that references another sheet, and I need to apply that same (adjusted) formula to all cells in that column, the only thing I can do is manually set the formula of each cell in a loop.

Is this right?

Shane

#6

Hi,

Yes, you need to manually set the formula accordingly on your destination cells.

Moreover, I think you may try to use Cell.Copy() in a loop, see the following sample code if it fits your need.

We have a template file that has a formula in B1 cell (of Sheet1) that has reference to other sheet. Now if you want to copy that formula into A1:A10 in Sheet1, you may try:

Workbook workbook = new Workbook();
//Open an excel file
workbook.Open(“e:\test\Book1.xls”);
//Get the cells collection in the first worksheet
Cells cells = workbook.Worksheets[0].Cells;


//Apply the formula in the range i.e…, A1:A10
for (int i = 0; i <= 9; i++)
{
cells[i, 0].Copy(cells[0, 1]);

}


//Save the excel file
workbook.Save(“e:\test\outBook1.xls”);


Thank you.

#7

Ok, I will use this method - thanks very much.

Shane