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
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.
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
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.
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
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.
Ok, I will use this method - thanks very much.
Shane