Copy formula from single cell to a rangle

Aspose.Cells Product Family
1

I want to apply a formula to a range of cell.

I have the following code:
cells[“A2”].Formula = “=HYPERLINK(H2,‘Edit’)”;
PasteOptions options = new PasteOptions();
options.PasteType = PasteType.Formulas;
Range range = cells.CreateRange(1,0,1,1);
Range hyperlinkrange = cells.CreateRange(2, 0, DT.Rows.Count, 1);
hyperlinkrange.CopyData(range);

I don’t want to iterate through each row to copy the cell. I could do that with the worksheet.hyperlink function. The number of rows can range between 1 to 30,000.

2

Hi Tan,

Thanks for your posting and using Aspose.Cells.

You can use the SetSharedFormula() method for your needs. Please see the following documentation for your reference.

( Setting Shared Formula|Documentation )

Please see the following code. It sets your formula to 3000 rows. I have attached the output file generated by this code for your reference.

C#


Workbook workbook = new Workbook();


Worksheet worksheet = workbook.Worksheets[0];


Cell cell = worksheet.Cells[“A2”];


string sharedFormula = “=HYPERLINK(H2,"Edit")”;


int numberOfRows = 3000;


worksheet.Cells[“A2”].SetSharedFormula(sharedFormula, numberOfRows, 1);


workbook.Save(“output.xlsx”);

3

Thanks that works great.