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

Formula problem when going to next row

Sep 27, 2012 at 12:08 PM

 

Hi,
Can someone help me with this. 
I have an existing excel with a table and i am populating using the code below.
if (table.Rows.Count > 0)
                {
                    wstable.InsertRowsBelow(table.Rows.Count - 1);
                    var excelrow = wstable.DataRange.FirstRow();
                    int loop = 1;                    
                    
                    foreach (DataRow row in talbe.Rows)
                    {
                        foreach (DataColumn column in table.Columns)
                        {
                            excelrow.Field(column.ColumnName).Value = row[column.ColumnName];
                        }
                        if (formula.IsFilled())                            
                            excelrow.Field("Formula").SetFormulaA1(formula);
                        else
                            formula = excelrow.Field("Formula").FormulaA1;
                        loop = loop + 1;
                        excelrow = wstable.DataRange.Row(loop);
                    }                    
                }

But when i try to open the generated file and add another row, the formula does not automatically add the formula. Ihave to click one of the formula and use the auto correct to apply to the entire column. Did i need to do more in my code?
Thanks

Best regards,
Naidelih

 

Developer
Sep 27, 2012 at 3:15 PM
Edited Sep 28, 2012 at 12:29 AM

** Deleted **

Sep 27, 2012 at 10:33 PM
Edited Sep 27, 2012 at 10:33 PM

Greets,

I am handling copying formulas into new rows like this:

IXLCells cells = row.InsertRowsAbove(amount).Cells();

foreach (IXLCell c in cells.Where(c => !string.IsNullOrEmpty(c.CellAbove().FormulaA1)))
{
c.CopyFrom(c.CellAbove());
} 

Got 2 rows with defined formulas and I insert new ones between them. Then I copy formulas from the 1st defined row, then from the 1st inserted row, then 2nd inserted row etc. cell by cell. If formula has locked column or row value using $ then it doesn't change after copying otherwise it does change just like you would strech the rows in excel (like adriancs has shown in the picture). You can copy from CellBelow or other or InsertRowsBelow instead of Above according to what you need.

Hope it helps ;)

Kind Regards

PKret

Sep 28, 2012 at 4:28 AM
Edited Sep 28, 2012 at 4:29 AM

Hi PKret,

Thanks for the reply. I used your code but i am still not been able to fix the problem. Here is what my code looks like now.

 

var ws = workbook.Worksheet("Sheet 1");
                var wstable = ws.Table(0);                
                if (dtable.Rows.Count > 0)
                {
                    ClosedXML.Excel.IXLCells cells = wstable.InsertRowsBelow(dtable.Rows.Count-1).Cells();
                    foreach (ClosedXML.Excel.IXLCell c in cells.Where(c => !string.IsNullOrEmpty(c.CellAbove().FormulaA1)))
                    {
                        c.CopyFrom(c.CellAbove());
                    } 
                    var excelrow = wstable.DataRange.FirstRow();
                    int loop = 1;                    
                    
                    foreach (DataRow row in dtable.Rows)
                    {
                        foreach (DataColumn column in dtable.Columns)
                        {
                            excelrow.Field(column.ColumnName).Value = row[column.ColumnName];
                        }                        
                        loop = loop + 1;
                        excelrow = wstable.DataRange.Row(loop);
                    }
                    
                }

I have no problem with the existing rows. They all have the formula using the code above. But my problem is when openning the file and manually add a row in excel the formula does not immediately copy the formula to the new row. I have to click on one of the formula and autocorrect option icon shows then you can click the overwrite function to use the formula to the entire column. Please let me know if you have more inputs on this.

Thanks

Best regards,

naidelih

Sep 28, 2012 at 9:28 AM

As I see Excel does not copy the formula is you add row manually. It might extend the named range depending on place where you insert the row and amount of range rows (impossible to extend single row named range by adding rows) but I do not think it will copy the formulas without your help there.

 

Kind Regards

PKret

Sep 28, 2012 at 11:52 AM

Hi,

For my situation, the original file was configured to have the ability to automatically add the formula on the next row. When im just downloading the original file its ok but when i tried to populate it with the data then the problem occurs. The formula on the last column is important for me because this will give the user the errors for his inputs should he edit or add more rows in the table. I might be missing something here or implemented the soln incorrectly with my code.

Thanks for any more inputs.

Best regards,

naidelih