This project has moved. For the latest updates, please go here.

copying range with formulas

Apr 27, 2011 at 7:32 AM

Hi,

Following this example : http://closedxml.codeplex.com/wikipage?title=Copying%20Ranges&referringTitle=Documentation


Is there a particular manipulation when copying range containing formulas ?

I copied a range many times in the same worksheet but the formulas are the same than in the original range.

If the formula in the original range is =B1+C1 , if I copy a range of cells to column N, I want the formula to be =N1+M1, but it's =B1+C1 again

Coordinator
Apr 27, 2011 at 3:06 PM

You're right, the formula should adjust when you copy it. I've already fixed this and it will appear on the next release.

Thanks for the feedback,

Coordinator
Apr 27, 2011 at 3:07 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Apr 27, 2011 at 3:58 PM

Thanks for support!

I also found that it does not copy when there is a dropdownlist in a cell.

Coordinator
Apr 27, 2011 at 5:02 PM

I'll add it to the list...

Apr 28, 2011 at 7:22 AM
Edited Apr 28, 2011 at 7:22 AM

Thanks !

For the formula copying, I resolved it with a heavy treatment, I'll replace it when you release the feature.

For info, here is what I did :

 

 // copy range to paste many time.
            IXLRange classRange = worksheet.Range("$L$2:$O$18");

            int rangeWidth = classRange.LastCell().Address.ColumnNumber - classRange.FirstCell().Address.ColumnNumber;
            int rangeHeight = classRange.LastCell().Address.RowNumber - classRange.FirstCell().Address.RowNumber;

            for (int i = 0; i < 4; i++) // test with a few copy
            {
                // starts copy at column 17, and 5 because 4 column wide and 1 spacing column 
                int colunmIndex = 17 + (i * 5);

                try
                {
                    worksheet.Cell(2, colunmIndex).Value = classRange;

                    // replace formulas
                   
                    for (int column = 0, rangeColumn = 1; column < rangeWidth; column++, rangeColumn++)
                    {
                        for (int row = 0, rangeRow = 1; row < rangeHeight; row++, rangeRow++)
                        {
                            if (!string.IsNullOrEmpty(worksheet.Cell(2 + row, colunmIndex + column).FormulaA1))
                            {
                                worksheet.Cell(2 + row, colunmIndex + column).FormulaA1 =
                                    worksheet.Cell(2 + row, colunmIndex + column).FormulaA1.Replace(
                                    classRange.Cell(rangeRow, rangeColumn).Address.ColumnLetter,
                                    worksheet.Cell(2 + row, colunmIndex + column).Address.ColumnLetter);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    // do something smart...
                }
            }

 

Thanks again for your work

Coordinator
Apr 28, 2011 at 4:19 PM

I'll give you two better options:

Option #1: Download and modify the source code. On XLCell.cs modify the CopyValues and CopyFrom methods to the following:

        internal void CopyValues(XLCell source)
        {
            this.cellValue = source.cellValue;
            this.dataType = source.dataType;
            this.FormulaR1C1 = source.FormulaR1C1;
        }

        public IXLCell CopyFrom(IXLCell otherCell)
        {
            var source = otherCell as XLCell;
            cellValue = source.cellValue;
            dataType = source.dataType;
            FormulaR1C1 = source.FormulaR1C1;
            style = new XLStyle(this, source.style);
            
            if (source.hyperlink != null)
            {
                SettingHyperlink = true;
                Hyperlink = new XLHyperlink(source.Hyperlink);
                SettingHyperlink = false;
            }

            return this;
        }

Option #2: Change your code so that instead of using the replace function you just set the FormulaR1C1, like this:

 

targetCell.FormulaR1C1 = sourceCell.FormulaR1C1;

 

 

Apr 29, 2011 at 11:10 AM

Thanks a lot !

I modified the source code and it works great