static float ExecuteExcelFormula(string FormulaName) { int row = 2; string currentFormula = string.Empty; float result = 0.0f; oSheet = (Excel.Worksheet)workbook.ActiveSheet; while (true) { // look for first empty if (oSheet.Cells[row, "A"].Value == null) { break; } else { row++; } } if (row > 2) { row--; currentFormula = "=" + FormulaName + "(D2:D" + row + ")"; oSheet.Cells[row + 1, "W"].Value = currentFormula; oSheet.Calculate(); result = Convert.ToSingle(oSheet.Cells[row + 1, "W"].Value); oSheet.Cells[row + 1, "W"].Value = ""; } return(result); }
public void FormulaPop() { string destfolder = @"c:\xls\diffs\"; Excel.Application excelApp; excelApp = new Excel.Application(); Excel.Workbook excelworkbook; Directory.EnumerateFiles(destfolder, "*.xlsx", SearchOption.AllDirectories) .ToList() .ForEach(f => Form(f)); void Form(string excelFilePath) { try { excelworkbook = excelApp.Workbooks.Open(excelFilePath, UpdateLinks: 0); Excel.Worksheet excelWorksheet = excelworkbook.Worksheets[3]; Excel.Worksheet excelWorksheet1 = excelworkbook.Worksheets[1]; string SheetFormula = excelWorksheet1.Name; string formula = "=IF('" + SheetFormula + "'!A1<>'" + SheetFormula + " (2)'!A1, 0,1)"; Excel.Range rng = excelWorksheet.Range["A1"]; Excel.Range rngdown = excelWorksheet.Range["A1:A200"]; Excel.Range destdown = excelWorksheet.Range["A1:A200"]; Excel.Range destright = excelWorksheet.Range["A1:BE200"]; rng.Formula = formula; destdown.FillDown(); destright.FillRight(); excelworkbook.Save(); Console.WriteLine(formula); excelWorksheet.Calculate(); excelworkbook.Close(true); excelApp.Quit(); } catch (Exception ex) { Console.WriteLine(ex.Message); } } }
void CopySheet() { Excel.Application excelApp; string path1 = @"c:\xls\diffs\new\"; string path2 = @"c:\xls\diffs\old\"; string[] filesin1 = Directory.GetFiles(path1); string[] filesin2 = Directory.GetFiles(path2); foreach (string f in filesin1) { foreach (string f2 in filesin2) { string subf = f.Substring(17); string subf2 = f2.Substring(18); if (subf.Equals(subf2)) { form(); } //if (f2.Contains("Switchboard") && f.Contains("Switchboard")) //{ // form(); //} //if (f2.Contains("IP_ADDRESS") && f.Contains("IP_ADDRESS")) //{ // form(); //} //if (f2.Contains("DB") && f.Contains("DB")) //{ // form(); //} //if (f2.Contains("HW_CONFIG") && f.Contains("HW_CONFIG")) //{ // form(); //} //if (f2.Contains("SERIAL_PORTS") && f.Contains("SERIAL_PORTS")) //{ // form(); //} //if (f2.Contains("VERSION_INFO") && f.Contains("VERSION_INFO")) //{ // form(); //} //if (f2.Contains("HW_OVERVIEW") && f.Contains("HW_OVERVIEW")) //{ // form(); //} //if (f2.Contains("Fire Alarm") && f.Contains("Fire Alarm")) //{ // form(); //} //if (f2.Contains("Alarm_Data") && f.Contains("Alarm_Data")) //{ // form(); //} //if (f2.Contains("Station Fire Main ") && f.Contains("Station Fire Main ")) //{ // form(); //} //if (f2.Contains("Room") && f.Contains("Room")) //{ // form(); //} //if (f2.Contains("Escalator") && f.Contains("Escalator")) //{ // form(); //} //if (f2.Contains("Fire Panel") && f.Contains("Fire Panel")) //{ // form(); //} //if (f2.Contains("UPS") && f.Contains("UPS")) //{ // form(); //} //if (f2.Contains("HVAC") && f.Contains("HVAC")) //{ // form(); //} //if (f2.Contains("Lift") && f.Contains("Lift")) //{ // form(); //} //if (f2.Contains("Tunnel Fire Main") && f.Contains("Tunnel Fire Main")) //{ // form(); //} //if (f2.Contains("Pump") && f.Contains("Pump")) //{ // form(); //} //else //{ // //Console.WriteLine("No match"); //} //if (f2.Contains("Lighting") && f.Contains("Lighting")) //{ // form(); //} //else //{ // //Console.WriteLine("No match"); //} //if (f2.Contains("Water Meter") && f.Contains("Water Meter")) //{ // form(); //} //else //{ // //Console.WriteLine("No match"); //} void form() { excelApp = new Excel.Application(); Excel.Workbook excelworkbook1; Excel.Workbook excelworkbook2; excelworkbook1 = excelApp.Workbooks.Open(f, UpdateLinks: 0); excelworkbook2 = excelApp.Workbooks.Open(f2); Excel.Worksheet excelWorksheet2 = excelworkbook2.Worksheets[1]; excelWorksheet2.Copy(Type.Missing, After: excelworkbook1.Worksheets[1]); excelworkbook2.Save(); Excel.Worksheet excelWorksheet = excelworkbook1.Worksheets[3]; Excel.Worksheet excelWorksheet1 = excelworkbook1.Worksheets[1]; string SheetFormula = excelWorksheet1.Name; string diffs = "Changed"; string formula = "=IF('" + SheetFormula + "'!A1<>'" + SheetFormula + " (2)'!A1,\"" + diffs + "\",1)"; string formulacountifBE = "=COUNTIF(A1:CE700,\"" + diffs + "\")"; string formulacountifQ = "=COUNTIF(A1:Q700,\"" + diffs + "\")"; Excel.Range rng = excelWorksheet.Range["A1"]; Excel.Range rngBE = excelWorksheet.Range["CF1"]; Excel.Range rngQ = excelWorksheet.Range["CF2"]; Excel.Range rngdown = excelWorksheet.Range["A1:A700"]; Excel.Range destdown = excelWorksheet.Range["A1:A700"]; Excel.Range destright = excelWorksheet.Range["A1:CE700"]; rng.Formula = formula; rngBE.Formula = formulacountifBE; rngQ.Formula = formulacountifQ; destdown.FillDown(); destright.FillRight(); excelWorksheet.Calculate(); cellcontent = "Worksheet name: " + excelWorksheet2.Name + " changes on whole: " + excelWorksheet.Range["CF1"].Value + " changes on SCADA: " + excelWorksheet.Range["CF2"].Value; changelist.Add(cellcontent); excelworkbook2.Save(); excelworkbook2.Close(true); excelworkbook1.Close(true); excelApp.Quit(); } } } }