public KeyValuePair <FileHeaderInfo, ValuesDynamicDictionary> ReadSingleFileToFileCache(string filepath) { FileHeaderInfo fileheaderinfo = new FileHeaderInfo(filepath); //file meta data fileheaderinfo.ColumnsUsed = ReadFirstLineOfColumnsToIntArray(filepath); //what particular columns are in that EQRMS extract //2. Read the contents of the file into cache // a) There are two invariants for the same loop // i) The indexer to keep track of where we are in the string[] from reading a line in the file // columnNumber) The current column we are upto as per the corresponding key(e.g. col #215) for the column's value we are populating ValuesDynamicDictionary values = new ValuesDynamicDictionary(); using (var sr = new StreamReader(filepath)) { for (int i = 0; i < numberOfRowsToSkip; i++) { sr.ReadLine(); } //skip the first x lines which contain column names and other useless info while (!sr.EndOfStream) { string[] readLine = sr.ReadLine().Split(columnDelimeter); //get 1 row numberofRowsInTotal++; foreach (var columnsIndexes in fileheaderinfo.ColumnsUsed) { values.Add(columnsIndexes.Value, readLine[columnsIndexes.Key]); } } } KeyValuePair <FileHeaderInfo, ValuesDynamicDictionary> ret = new KeyValuePair <FileHeaderInfo, ValuesDynamicDictionary>(fileheaderinfo, values); return(ret); }
public void WriteToTxtFile(ValuesDynamicDictionary cache, FileHeaderInfo fileHeader, List <Dictionary <string, Value> > formulas) { string completePath = FilePath + Program.OutputTxtFileName; string[] rowHeadline = new string[fileHeader.ColumnsUsed.Count + formulas[0].Keys.Count + 1]; //1. a) merge the headline from raw cache and formulas cache into a single array int invariant = 0; while (invariant < fileHeader.ColumnsUsed.Count + formulas[0].Keys.Count) { rowHeadline[invariant] = "AsOfDate"; invariant++; for (int i = 0; i < fileHeader.ColumnsUsed.Count; i++) { rowHeadline[invariant] = EQRMSRawColumns.ProcessedColumnIntAsKey[fileHeader.ColumnsUsed[i]]; ++invariant; } for (int i = 0; i < formulas[0].Count; i++) { var lol = formulas.First().Keys; rowHeadline[invariant] = lol.ElementAt(i); ++invariant; } } File.AppendAllText(completePath, string.Join("\t", rowHeadline) + "\n"); int rowsIndex = 0; while (rowsIndex < cache.Count()) { int columnIndex = 0; string[] row = new string[cache.ValuesUnbound.Count + formulas[0].Count + 1]; row[columnIndex] = fileHeader.AsOfDate.ToString(); columnIndex++; for (int i = 0; i < fileHeader.ColumnsUsed.Count; i++) { row[columnIndex] = cache.GetValue(fileHeader.ColumnsUsed[i], rowsIndex).ToString(); ++columnIndex; } for (int i = 0; i < formulas[rowsIndex].Count; i++) { row[columnIndex] = formulas[rowsIndex].Values.ElementAt(i).ToString(); ++columnIndex; } ++rowsIndex; File.AppendAllText(completePath, string.Join("\t", row) + "\n"); //File.AppendText(FilePath).WriteLine(string.Join("\t", row)); } }
private Dictionary <string, Value> FormulasCalculateOneRow(int row, ValuesDynamicDictionary rawcache) { Dictionary <string, Value> ret = new Dictionary <string, Value>(); List <string> noneMatches = new List <string>(); EQRMSFormulaLayers formulasLayered = new EQRMSFormulaLayers(); foreach (var layer in formulasLayered.FormulasLayered) { foreach (var kvp in layer) { //0. Extract the formula into array.... Fair Economic (DTDS T-1) <USD> - Total PAA string after = kvp.Value; MatchCollection formulas = ExtractFormulaNames(kvp.Value); //1. Replace column names with values... 150000 - 150000 foreach (Match match in formulas) { string str = match.ToString().Substring(1, match.Length - 2); //remove quotes //1. a) Did we recalculate this formula already? If so use that if (ret.ContainsKey(str)) { string value = ret[str].ToString(); if (value == "") { value = "0.000000001337"; } //Empty cells confuse Antlr, after = after.Replace(match.ToString(), value); } //1. b) If we didn't, then fetch the value from the raw cache instead (value in report extract) else if (EQRMSRawColumns.ProcessedColumnNameAsKey.ContainsKey(str)) { string value = rawcache.GetValue(EQRMSRawColumns.ProcessedColumnNameAsKey[str], row).ToString(); if (value == "") { value = "0.000000001337"; } after = after.Replace(match.ToString(), value); } //1. c) Couldn't find as a column anywhere? Store this column, these could be "PFS" or "ExternalSecurity" etc. but cache them encase to aid debugging else { noneMatches.Add(str); } } //2. Now pass the valuefied formula to Antlr Value result = CalculateFormula(after); //3. Store result in a new cache, so that we can retain both the original + recalculated values for comparison ret.Add(kvp.Key, result); } } return(ret); }
public List <Dictionary <string, Value> > FormulasCalculateSingleFile(ValuesDynamicDictionary cache) { List <Dictionary <string, Value> > ret = new List <Dictionary <string, Value> >(); //Parallel.ForEach(cache.ValuesUnbound, e => //{ // ret.Add(FormulasCalculateOneRow(i, cache)); //}); for (int i = 0; i < cache.Count(); i++) { ret.Add(FormulasCalculateOneRow(i, cache)); } return(ret); }
public void WriteToExcel(ValuesDynamicDictionary cache, FileHeaderInfo fileHeader, List <Dictionary <string, Value> > formulas) { //1. Column headline Console.WriteLine(cache.Count()); string[] rowHeadline = new string[fileHeader.ColumnsUsed.Count + formulas[0].Keys.Count]; int maxLen = fileHeader.ColumnsUsed.Count + formulas[0].Keys.Count; int tabOneRowCounter = NumberOfRows(FirstTab); //1. a) merge the headline from raw cache and formulas cache into a single array int invariant = 0; while (invariant < maxLen) { for (int i = 0; i < fileHeader.ColumnsUsed.Count; i++) { rowHeadline[invariant] = EQRMSRawColumns.ProcessedColumnIntAsKey[fileHeader.ColumnsUsed[i]]; ++invariant; } for (int i = 0; i < formulas[0].Count; i++) { var lol = formulas.First().Keys; rowHeadline[invariant] = lol.ElementAt(i); ++invariant; } } //1. b) write the headline Excel.Range h1 = FirstTab.Cells[tabOneRowCounter, 1]; Excel.Range h2 = FirstTab.Cells[tabOneRowCounter, rowHeadline.Length]; Excel.Range headerRange = FirstTab.get_Range(h1, h2); headerRange.Value2 = rowHeadline; tabOneRowCounter++; //2. a) merge two caches row at a time int rowsIndex = 0; while (rowsIndex < cache.Count()) { int columnIndex = 0; string[] row = new string[cache.ValuesUnbound.Count + formulas[0].Count]; //Raw extracted data for (int i = 0; i < fileHeader.ColumnsUsed.Count; i++) { row[columnIndex] = cache.GetValue(fileHeader.ColumnsUsed[i], rowsIndex).ToString(); ++columnIndex; } //Our re-calculated formulas using Antlr for (int i = 0; i < formulas[rowsIndex].Count; i++) { row[columnIndex] = formulas[rowsIndex].Values.ElementAt(i).ToString(); ++columnIndex; } ++rowsIndex; //2b) Write the row Excel.Range c1 = FirstTab.Cells[tabOneRowCounter, 1]; Excel.Range c2 = FirstTab.Cells[tabOneRowCounter, row.Length]; Excel.Range firstTabRangeFormula = FirstTab.get_Range(c1, c2); firstTabRangeFormula.Value2 = row; ++tabOneRowCounter; } }