private static void CalcChain(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc) { var debug = parser.Logger != null; foreach (var ix in dc.CalcOrder) { var item = dc.list[ix]; try { var ws = wb.Worksheets.GetBySheetID(item.SheetID); var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column); SetValue(wb, item, v); if (debug) { parser.Logger.LogCellCounted(); } Thread.Sleep(0); } catch (FormatException fe) { throw (fe); } catch (Exception e) { var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value); SetValue(wb, item, error); } } }
public static object Calculate(this ExcelWorksheet worksheet, string Formula, ExcelCalculationOption options) { try { worksheet.CheckSheetType(); if (string.IsNullOrEmpty(Formula.Trim())) { return(null); } Init(worksheet.Workbook); var parser = worksheet.Workbook.FormulaParser; parser.InitNewCalc(); if (Formula[0] == '=') { Formula = Formula.Substring(1); //Remove any starting equal sign } var dc = DependencyChainFactory.Create(worksheet, Formula, options); var f = dc.list[0]; dc.CalcOrder.RemoveAt(dc.CalcOrder.Count - 1); CalcChain(worksheet.Workbook, parser, dc); return(parser.ParseCell(f.Tokens, worksheet.Name, -1, -1)); } catch (Exception ex) { return(new ExcelErrorValueException(ex.Message, ExcelErrorValue.Create(eErrorType.Value))); } }
private static void CalcChain(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc, bool setResultSyle = false) { var debug = parser.Logger != null; foreach (var ix in dc.CalcOrder) { var item = dc.List[ix]; try { var ws = wb.Worksheets.GetBySheetID(item.SheetID); var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column, out DataType dataType); if (v is IEnumerable enumerable && !(v is string)) { v = enumerable.Cast <object>().FirstOrDefault(); } CalculationExtension.SetValue(wb, item, v); if (setResultSyle) { CalculationExtension.SetStyle(wb, item, dataType); } if (debug) { parser.Logger.LogCellCounted(); } Thread.Sleep(0); } catch (Exception ex) when((ex is OperationCanceledException) == false) { parser.Logger?.Log(ex); var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value); SetValue(wb, item, error); } } }
private static void CalcChain(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc, ExcelCalculationOption options) { wb.FormulaParser.Configure(config => { config.AllowCircularReferences = options.AllowCircularReferences; config.PrecisionAndRoundingStrategy = options.PrecisionAndRoundingStrategy; }); var debug = parser.Logger != null; foreach (var ix in dc.CalcOrder) { var item = dc.list[ix]; try { var ws = wb.Worksheets.GetBySheetID(item.SheetID); var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column); SetValue(wb, item, v); if (debug) { parser.Logger.LogCellCounted(); } Thread.Sleep(0); } catch (FormatException fe) { throw (fe); } catch (CircularReferenceException cre) { throw cre; } catch (Exception e) { var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value); SetValue(wb, item, error); } } }
private static void CalcChain_V2(ExcelWorkbook wb, FormulaParser parser, DependencyChain dc, ExcelRangeBase range = null) { if (dc.CalcOrder.Count == 0) { if (range != null && range.Address.ToString().StartsWith("P")) { } } int fiColValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_COLUMN; int fiRowValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_ROW; int ftpColValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_COLUMN; int ftpRowValidator = FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_ROW; foreach (var ix in dc.CalcOrder) { var item = dc.list[ix]; rows.Add(item.Row); sheetId = item.SheetID; try { var ws = wb.Worksheets.GetBySheetID(item.SheetID); bool goDefault = true; if (ws.Cells[fiRowValidator, fiColValidator] != null && ws.Cells[fiRowValidator, fiColValidator].Text != "" && ws.Cells[fiRowValidator, fiColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_COLUMN_HEADER.ToLower()) { // FI Worksheet #region FI WORKSHEETS if (range != null && item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST && range.Address.ToString().StartsWith("P") || item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS) { if (ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != null && ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != "" && ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text.ToLower() == FundingConstants_V2.PROGRAM_TYPE.FTS.ToLower()) { decimal x1 = 0; bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Text.Replace("$", ""), out x1); if (isValidX1) { decimal grantPercentage = .5m; string nrictype = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.NRIC_TYPE].Text.ToString(); string progStartDateStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.PROG_START_DATE].Text.ToString(); string scAgeFilterStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.SC_AGE_FILTER].Text.ToString(); bool isSCAgeFilter = !string.IsNullOrEmpty(scAgeFilterStr) && scAgeFilterStr.ToLower() == "yes"; DateTime progStartDate = parseDate(progStartDateStr); if (nrictype == FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.NRIC_SC && progStartDate.CompareTo(FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.SCAGEFILTER_DATE_THRESHOLD) >= 0 && isSCAgeFilter ) { grantPercentage = .9m; } x1 = x1 * grantPercentage; x1 = Math.Round(x1, 2, MidpointRounding.AwayFromZero); string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_FTS, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row); if (item.Formula == null) { x1 = 0; } else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", "")) { x1 = 0; } else if (x1 > 2000.00m) { x1 = 2000.00m; } SetValue(wb, item, x1); goDefault = false; } else { SetValue(wb, item, ""); goDefault = false; } } else { SetValue(wb, item, ""); goDefault = false; } } else if (range != null && item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST && range.Address.ToString().StartsWith("P") || item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS) { if (ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != null && ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text != "" && ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TYPE_OF_SCHEME].Text.ToLower() == FundingConstants_V2.PROGRAM_TYPE.IBF_STS.ToLower()) { decimal x1 = 0; bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Text.Replace("$", ""), out x1); //if(isValidX1) //{ decimal grantPercentage = .7m; string nrictype = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.NRIC_TYPE].Text.ToString(); string progStartDateStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.PROG_START_DATE].Text.ToString(); string scAgeFilterStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.SC_AGE_FILTER].Text.ToString(); bool isSCAgeFilter = !string.IsNullOrEmpty(scAgeFilterStr) && scAgeFilterStr.ToLower() == "yes"; DateTime progStartDate = parseDate(progStartDateStr); if (nrictype == FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.NRIC_SC && progStartDate.CompareTo(FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.SCAGEFILTER_DATE_THRESHOLD) >= 0 && isSCAgeFilter ) { grantPercentage = .9m; } x1 = x1 * grantPercentage; x1 = Math.Round(x1, 2, MidpointRounding.AwayFromZero); string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row); if (item.Formula == null) { x1 = 0; } else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", "")) { x1 = 0; } else if (x1 > 7000.00m) { x1 = 7000.00m; } SetValue(wb, item, x1); goDefault = false; //} //else //{ // SetValue(wb, item, ""); // goDefault = false; //} } else { SetValue(wb, item, ""); goDefault = false; } } else if (item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST) { //if (ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text != "" || // ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text != "") //{ decimal x1 = 0; decimal x2 = 0; bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text.Replace("$", ""), out x1); bool isValidX2 = decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text.Replace("$", ""), out x2); string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_INTERNAL_COST, item.Row, item.Row); decimal sum = x1 + x2; if (item.Formula == null) { sum = 0; } else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", "")) { sum = 0; } else { SetValue(wb, item, sum); goDefault = false; } } else if (item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST) { //if (ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text != "" && // ws.Cells[item.Row, FundingConstants.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text != "") decimal x1a = 0; decimal x2a = 0; bool isValidX1a = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text.Replace("$", ""), out x1a); bool isValidX2a = decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text.Replace("$", ""), out x2a); if ((x1a + x2a) != 0m) { decimal x1 = 0; decimal x2 = 0; bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_COST].Text.Replace("$", ""), out x1); bool isValidX2 = decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.INHOUSE_FEECHARGED].Text.Replace("$", ""), out x2); string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_PROGRAMME_COST, item.Row, item.Row, item.Row); decimal sum = x1 + x2; if (item.Formula == null) { sum = 0; } else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", "")) { sum = 0; } else { SetValue(wb, item, sum); goDefault = false; } } else if (ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.EXTERNAL_TOTAL].Text != "") { decimal x1 = 0; bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.EXTERNAL_TOTAL].Value + "", out x1); string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_PROGRAMME_COST, item.Row, item.Row, item.Row); if (item.Formula == null) { x1 = 0; } else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", "")) { x1 = 0; } else { SetValue(wb, item, x1); goDefault = false; } } } #endregion } else if (ws.Cells[ftpRowValidator, ftpColValidator] != null && ws.Cells[ftpRowValidator, ftpColValidator].Text != "" && ws.Cells[ftpRowValidator, ftpColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_COLUMN_HEADER.ToLower()) { // FTP Worksheet #region FTP Worksheet if (range != null && item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS && range.Address.ToString().StartsWith("M") || item.Column == FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS) { decimal x1 = 0; bool isValidX1 = Decimal.TryParse(ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.PROGRAMME_FEE].Value.ToString(), out x1); //if(isValidX1) //{ decimal grantPercentage = .7m; string nrictype = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.NRIC_TYPE].Text.ToString(); string progStartDateStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.PROG_START_DATE].Text.ToString(); string scAgeFilterStr = ws.Cells[item.Row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.SC_AGE_FILTER].Text.ToString(); bool isSCAgeFilter = !string.IsNullOrEmpty(scAgeFilterStr) && scAgeFilterStr.ToLower() == "yes"; DateTime progStartDate = parseDate(progStartDateStr); if (nrictype == FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.NRIC_SC && progStartDate.CompareTo(FundingConstants_V2.CLAIMS_WORKSHEET_CONSTANTS.SCAGEFILTER_DATE_THRESHOLD) >= 0 && isSCAgeFilter ) { grantPercentage = .9m; } x1 = x1 * grantPercentage; x1 = Math.Round(x1, 2, MidpointRounding.AwayFromZero); //string formula = "IF(EXACT(N"+item.Row+",\"IBF-STS\"),MIN(O"+item.Row+"*0.5,2000),\"\")"; // =ROUND(MIN(K16*0.7,7000),2) // string formula = "MIN(K"+item.Row+"*0.7,7000)"; string formula = string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row, item.Row); if (item.Formula == null) { x1 = 0; } else if (item.Formula != null && formula != item.Formula.Trim().Replace(" ", "")) { x1 = 0; } else if (x1 > 7000.00m) { x1 = 7000.00m; } SetValue(wb, item, x1); goDefault = false; //} //else //{ // SetValue(wb, item, ""); // goDefault = false; //} } #endregion } if (goDefault) { var v = parser.ParseCell(item.Tokens, ws == null ? "" : ws.Name, item.Row, item.Column); SetValue(wb, item, v); } } catch (FormatException fe) { throw (fe); } catch (Exception e) { var error = ExcelErrorValue.Parse(ExcelErrorValue.Values.Value); SetValue(wb, item, error); } } var worksheet = wb.Worksheets.GetBySheetID(sheetId); foreach (int row in rows) { if (worksheet.Cells[fiRowValidator, fiColValidator] != null && worksheet.Cells[fiRowValidator, fiColValidator].Text != "" && worksheet.Cells[fiRowValidator, fiColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FI_COLUMN_HEADER.ToLower()) { #region FI WORKSHEETS if (row >= 22) { if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS].Formula) || (!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS].Formula) && worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_FTS, row, row, row, row, row, row, row, row, row, row, row))) { worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_FTS, 0); } if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) || (!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) && worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS, row, row, row, row, row, row, row, row, row, row, row))) { worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.GRANT_AMOUNT_IBFSTS, 0); } if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST].Formula) || (!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST].Formula) && worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_INTERNAL_COST, row, row))) { worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_INTERNAL_COST, 0); } if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Formula) || (!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Formula) && worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FI_WORKSHEET.TOTAL_PROGRAMME_COST, row, row, row))) { worksheet._values.SetValue(row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FI_WORKSHEET.TOTAL_PROGRAMME_COST, 0); } } #endregion } else if (worksheet.Cells[ftpRowValidator, ftpColValidator] != null && worksheet.Cells[ftpRowValidator, ftpColValidator].Text != "" && worksheet.Cells[ftpRowValidator, ftpColValidator].Text.ToLower() == FundingConstants_V2.CLAIMS_WORKSHEET_VALIDATOR_COLUMNS.FTP_COLUMN_HEADER.ToLower()) { #region FTP Worksheets if (row >= 16) { if (String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) || (!String.IsNullOrEmpty(worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula) && worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Formula != string.Format(FundingConstants_V2.CLAIMS_WORKSHEET_FORMULAS.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS, row, row, row, row, row, row, row, row, row, row))) { worksheet.Cells[row, FundingConstants_V2.CLAIMS_WORKSHEET_COLUMN_INDEXES.FTP_WORKSHEET.GRANT_AMOUNT_IBFSTS].Value = 0; } } #endregion } } }