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 readAPD(string filename = "C:\\Users\\i028512\\Documents\\Visual Studio 2017\\Projects\\SimpleAPDImporter\\r83410048802.txt", string from = null, string to = null)
{
//^\s\d+(\.\w+)?\s{2,}((\S+\s)*)\s{2,}(((\-?\d*\.\d+)\s*)|(\*{2,}\s*))+$
StreamReader reader = new FileInfo(filename).OpenText();
string line;
Excel.Application application = new Excel.Application();
try {
application.ScreenUpdating = false;
Excel.Workbook newWorkbook = application.Workbooks.Add();
Excel.Worksheet sheet = null;
application.Calculation = Excel.XlCalculation.xlCalculationManual;
int i = 3;
string chapterName = null;
string escapedChapterName = null;
string[] titleString = null;
int fromIndex = 0, toIndex = 0;
while ((line = reader.ReadLine()) != null)
{
if (title.IsMatch(line))
{
titleString = line.Split(new char[] { ' ', '\t' }, StringSplitOptions.RemoveEmptyEntries);
toIndex = titleString.Length - 1;
if (from != null)
{
fromIndex = Array.IndexOf(titleString, from);
}
if (to != null)
{
toIndex = Array.IndexOf(titleString, to);
}
}
else
if (newChapter.IsMatch(line))
{
StartNewSheet(line, newWorkbook, ref sheet, ref i, ref chapterName, ref escapedChapterName, titleString, fromIndex, toIndex);
}
else
{
if ((chapterName != null) && (line.StartsWith(chapterName)))
{
sheet.Cells[2][i].Value2 = "Total Calculated";
Excel.Range range = sheet.Range[sheet.Cells[3][i], sheet.Cells[3 + titleString.Length - 1][i]];
range.NumberFormat = "0.00";
sheet.Cells[3][i].FormulaR1C1 = "=SUM(R3C:R[-1]C)";
range.FillRight();
i++;
sheet.Cells[2][i].Value2 = "Total";
SetValues(sheet, i, line.Substring(chapterName.Length), fromIndex, toIndex);
}
else
{
Match match = parts.Match(line);
if (match.Success)
{
HandleLine(line, sheet, ref i, fromIndex, toIndex);
}
}
}
}
SetCellSize(sheet);
} finally {
application.Visible = true;
application.ScreenUpdating = true;
application.Calculate();
application.Calculation = Excel.XlCalculation.xlCalculationAutomatic;
}
}
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();
}
}
}
}
