Setting cell values in a formula

[jyotisingh]

Hello,

Please see the attached excel file.

I need to write and set formulae for the balance section (range C14 to E16). Each cell in this should have the difference between their corresponding cell value in 'Total' section and sum of corresponding cell value in 'segment A' and 'segment B'. That I have been able to do.

But an addition validation check is that the resultant difference should not be less than zero. For example if =C2-(C6+C10) results out be less than zero, then set C6 and C10 to blank.

Can this be done? Please help.

Thanks.

[Amjad_Sahi]

Hi,

I am afraid we have no good solution for your case. We think you can parse the formula by yourself, if the formula is simple or the meaning of the formula is clear for you, you can do it by your own code. For example, if the format of the balance segment’s formulas is always same as total-(a+b), you may use the code as follows (you may amend the code accordingly - it is just a hint for you):

Workbook workbook = new Workbook();
            workbook.Open(@“F:\FileTemp\Aspose+Issue.xls”);
            workbook.CalculateFormula();
            Cells cells = workbook.Worksheets[0].Cells;
            for (int row = 13; row <= 15; row++)
            {
                for (int column = 2; column <= 4; column++)
                {
                    Cell cell = cells[row, column];
                    if (cell.IsFormula && cell.DoubleValue < 0)
                    {
                        string formula = cell.Formula;
                       //formaule should be C2-(C6+C10);
                        int index = formula.IndexOf(’-’);
                        int index1 = formula.IndexOf(’+’);
                        string cell1 = formula.Substring(index + 2,index1 -(index+2));
                        string cell2 = formula.Substring(index1 + 1, (formula.Length  - 1 -(index1 +1)));
                        cells[cell1].PutValue(null);
                        cells[cell2].PutValue(null);
                    }
                }
            }

            workbook.Save(@“F:\FileTemp\dest.xls”);


Thank you.

[jyotisingh]

I am working with Webworksheet.Do you suggest any solution for that?

[Amjad_Sahi]

Hi,

I have tried to convert the original sample code for GridWeb control accordingly.

Sample code:

GridWeb1.WebWorksheets.ImportExcelFile(“e:\test\Book1.xls”);
GridWeb1.WebWorksheets.RunAllFormulas();
WebCells cells = GridWeb1.WebWorksheets[0].Cells;
for (int row = 13; row <= 15; row++)
{
for (int column = 2; column <= 4; column++)
{
WebCell cell = cells[row, column];
if (cell.Formula != null && Convert.ToDouble(cell.Value) < 0)
{
string formula = cell.Formula;
//formaule should be C2-(C6+C10);
int index = formula.IndexOf(’-’);
int index1 = formula.IndexOf(’+’);
string cell1 = formula.Substring(index + 2,index1 -(index+2));
string cell2 = formula.Substring(index1 + 1, (formula.Length - 1 -(index1 +1)));
cells[cell1].PutValue(null);
cells[cell2].PutValue(null);
}
}
}

GridWeb1.WebWorksheets.SaveToExcelFile(“e:\test\output.xls”);



Thank you.