public void GenerateExcel() { Read_From_Excel excel = new Read_From_Excel(); excel.getExcelFile("Copy of DMAS_NPM_Worker Roles.xlsx"); //excel.GenerateExcelFile(); }
private void handleFiles(List <string> files) { List <string> excels = new List <string>(); //fill up dictionary /*dictionary = new Dictionary<string, string>(); * foreach (var r in System.IO.File.ReadAllLines(dic)) * { * if (!string.IsNullOrEmpty(r)) * { * var words = r.Split('=').ToList(); * * if (words.Count == 2 && !dictionary.ContainsKey(words[0])) * { * dictionary.Add(words[0], words[1]); * } * } * }*/ //get all excels string exten; foreach (var f in files) { exten = System.IO.Path.GetExtension(f); if (exten == ".xlsx" || exten == ".xls") { excels.Add(f); } } if (excels.Count == 0) { TextBox1.AppendText("no excel files found in folder !!\n"); TextBox1.BackColor = Color.Green; return; } //iterate over excels; foreach (var ex in excels) { var excel = Read_From_Excel.getExcelFile(ex); TextBox1.BackColor = Color.Green; TextBox1.AppendText(string.Format("reading excel:[{0}]\n", ex)); //calc MD5 for excel template //////// string template = ""; foreach (var w in excel[0]) { template += w; } var MD5 = Read_From_Excel.CalculateMD5Hash(template); ////////////////////////////////////// var configuration = ConfigurationManger.getCMInstance().getConfig(); credit cval; //if (!Config.instance().templateDic.TryGetValue(MD5, out cval)) if (!configuration.ExTemplates.TryGetValue(MD5, out cval)) {/*temp solution !!*/ configuration.ExTemplates.Add(MD5, new credit(-1, -1, -1)); //writeConfigFile(Exceltemplate + "=" + MD5 + "," + "?????" + "\n"); TextBox1.BackColor = Color.Red; TextBox1.AppendText(string.Format("missing template in config file[{0}] !!!\n", config)); TextBox1.AppendText("better call Matan Integration Inc. [052-7758661]\n"); return; } else//template found { List <string> r; //var excelRanged = excel.GetRange(cval.StartRow, excel.Count - 1); for (int i = cval.StartRow; i < excel.Count; i++) //foreach (var r in excel.GetRange(1,excel.Count-1)) { r = excel[i]; if (r.Count < cval.sum || r.Count < cval.nameIndex) { continue; } string category; if (!configuration.B2Category.TryGetValue(r[cval.nameIndex], out category)) { configuration.BussinessWithoutCategory.Add(new BwithoutCategory(r[cval.nameIndex])); //writeFile(FILES_TO_WRITE.MISSING, r[cval.nameIndex]); //TextBox1.BackColor = Color.Yellow; TextBox1.AppendText(string.Format("missing category for [{0}] !!!\n\n", r[cval.nameIndex])); } else//value found in dectionary { if (cval.namesToExclude.Contains(category)) { continue;//leave current iteration , exclude filter triggered } if (!CategoryResultDic.ContainsKey(category)) { CategoryResultDic.Add(category, new creditResult()); } double dSum = Convert.ToDouble(r[cval.sum]); if (dSum > 0) { CategoryResultDic[category].category = category; CategoryResultDic[category].relatedRows.Add(r); CategoryResultDic[category].sum += dSum; } } } TextBox1.BackColor = Color.WhiteSmoke; TextBox1.AppendText("parsing finished\n"); System.IO.File.WriteAllText(result, ""); foreach (var pair in CategoryResultDic) { TextBox1.AppendText(string.Format("Category\n{0}\n", pair.Value.category)); TextBox1.AppendText(string.Format("final sum\n{0}\n", pair.Value.sum)); writeFile(FILES_TO_WRITE.RESULT, pair.Value); } } } }