public ActionResult Generate(FileGenViewModel model) { LFile document = docRepo.LFiles.Where(p => p.LFile_ID == model.DocID).FirstOrDefault(); List <Struct> structs = structRepo.Structs.Where(s => s.LFile_ID == model.DocID).ToList(); List <Document> document_out = new List <Document>(); // creating the document output list List <string> supra_duplicated; //getting filter values List <Elements> filter = model.FiltValues; List <VPercent> filtPerc = new List <VPercent>(); if (filter != null) { foreach (var fl in filter) { if (Convert.ToInt32(fl.Percent) < 100) { VPercent val = new VPercent { Name = fl.Name, Value = fl.Val, Counter = (int)Math.Truncate(Convert.ToDecimal((Convert.ToInt32(fl.Percent) * 0.01) * (model.NDocs * model.NBatch))) }; filtPerc.Add(val); } } } int itnum = 0; Level2 = 0; //iterating over batch number for (int l = 0; l < model.NBatch; l++) { supra_duplicated = new List <string>(); for (int i = 0; i < model.NDocs; i++) { // iterating over the number of documents int order_line = 0; itnum++; Document new_doc = new Document(); List <Dictionary <string, string> > doc = new List <Dictionary <string, string> >(); new_doc.Doc = doc; List <string> duplicated = new List <string>(); foreach (var st in structs) { // checking the structs List <StructField> structfields = structFieldRepo.StructFields.Where(s => s.StructID == st.ID).OrderBy(k => k.Field_Order).ToList(); Dictionary <string, string> str_formed = new Dictionary <string, string>(); foreach (var m in structfields) { str_formed.Add(m.Field.Field_Name, "");// filling the created structure with the document Struc-Fields } ; if (st.Order_In_Doc == 1) // if structure is document header { if (str_formed.ContainsKey("RECORD TYPE")) { str_formed["RECORD TYPE"] = "H"; } if (model.FiltValues != null) { foreach (var par in model.FiltValues) { // searching in the params from input if (par.Type == "text") { if (str_formed.ContainsKey(par.Name)) { str_formed[par.Name] = par.Val; } } else { int value1 = Convert.ToInt32(par.Val); DataField namefilter = datafieldRepo.DataFields.Where(m => m.ID == value1).FirstOrDefault(); if ((namefilter != null) && (str_formed.ContainsKey(namefilter.Field.Field_Name))) { // if param is in header is assigned the incoming value str_formed[namefilter.Field.Field_Name] = par.Name; } } } // FillFromDBLinked(ref str_formed); } FillFromDB(ref str_formed, itnum, ref duplicated); UpdCounters(ref filtPerc); UpdFilter(ref filter, ref filtPerc, ref supra_duplicated); new_doc.Doc.Add(str_formed);// adding the header to the document } else if ((st.Order_In_Doc < document.Num_Struct) && (st.Order_In_Doc > 1) && st.Multiple) {// if struct is detail if (model.NDets == 0) { model.NDets = 1; } var detnumb = model.NDets; Random rnd = new Random(); if (model.Max) { detnumb = rnd.Next(1, model.NDets); } for (int j = 0; j < detnumb; j++) // iterating over the number of details { Dictionary <string, string> new_detail = new Dictionary <string, string>(str_formed); if (new_detail.ContainsKey("RECORD TYPE")) { new_detail["RECORD TYPE"] = "D"; } if (new_detail.ContainsKey("ORDER LINE NO")) { order_line++; new_detail["ORDER LINE NO"] = order_line.ToString(); } if (new_doc.Doc.Count() > 0) { // if there is already a header var header = new_doc.Doc[0]; foreach (var word in str_formed) { // checking header and filling detail with header values if (header.ContainsKey(word.Key) && (new_detail[word.Key] == "")) { new_detail[word.Key] = header[word.Key]; } } } if (model.FiltValues != null) { foreach (var par in model.FiltValues) { // searching in the params from input if (str_formed.ContainsKey(par.Name)) { // if param in detail is assigned the incoming value new_detail[par.Name] = par.Val; } } } // FillFromDBLinked(ref new_detail); FillFromDB(ref new_detail, itnum, ref supra_duplicated); new_doc.Doc.Add(new_detail);// adding the new detail to the document } } else { // if struct is Comment Dictionary <string, string> new_comment = new Dictionary <string, string>(str_formed); if (new_comment.ContainsKey("RECORD TYPE")) { new_comment["RECORD TYPE"] = "C"; } if (new_comment.ContainsKey("ORDER LINE NO")) { new_comment["ORDER LINE NO"] = order_line.ToString(); } if (new_doc.Doc.Count() > 0) { // if there is already a header var header = new_doc.Doc[0]; foreach (var word in str_formed) { // checking header and filling comment with header values if (header.ContainsKey(word.Key) && (new_comment[word.Key] == "")) { new_comment[word.Key] = header[word.Key]; } } } if (new_doc.Doc.Count() > 1) { // if there is already a detail var detail = new_doc.Doc[1]; foreach (var word in str_formed) { // checking header and filling comment with header values if (detail.ContainsKey(word.Key) && (new_comment[word.Key] == "")) { new_comment[word.Key] = detail[word.Key]; } } } if (model.FiltValues != null) { foreach (var par in model.FiltValues) { // searching in the params from input if (str_formed.ContainsKey(par.Name)) { // if param in detail is assigned the incoming value new_comment[par.Name] = par.Val; } } } // FillFromDBLinked(ref new_comment); FillFromDB(ref new_comment, itnum, ref duplicated); new_doc.Doc.Add(new_comment);// adding the new detail to the document } } document_out.Add(new_doc); } supra_duplicated = null; } // end of document iteration // generating excel int counter = 0; //excel generation 2 using (ExcelEngine excelEngine = new ExcelEngine()) { //Set the default application version as Excel 2016. excelEngine.Excel.DefaultVersion = ExcelVersion.Excel2013; //Create a workbook with a worksheet. IWorkbook workbook = excelEngine.Excel.Workbooks.Create(1); //Access first worksheet from the workbook instance. IWorksheet worksheet = workbook.Worksheets[0]; int rownum = 0; int detnum = 0; foreach (var docm in document_out) { counter++; string cell = ""; foreach (var row in docm.Doc) { string[] list = new string[row.Count()]; int aux = 0; if ((row.ContainsKey("RECORD TYPE") && (row["RECORD TYPE"] != "D") && (detnum > 0))) { detnum = 0; } if (detnum < 1) { foreach (var field in row) { list[aux] = field.Key; aux++; } rownum++; worksheet.ImportArray(list, rownum, 1, false); aux = 0; } if ((row.ContainsKey("RECORD TYPE") && (row["RECORD TYPE"] == "D"))) { detnum++; } else { detnum = 0; } foreach (var field in row) { list[aux] = field.Value; aux++; } rownum++; cell = "M" + rownum; if (list.Count() > 12) { string test_elem = list[12]; if (test_elem.Substring(0, 1) == "0") { string mask = ""; for (int k = 0; k < test_elem.Length; k++) { mask += "0"; } worksheet.Range[cell].NumberFormat = mask; } else { worksheet.Range[cell].NumberFormat = "0"; } } worksheet.ImportArray(list, rownum, 1, false); worksheet.AutofitRow(rownum); } //rownum ++2; } string fileName = ""; if (model.FileName != null) { fileName = "C:\\tkfile\\" + model.FileName + counter + ".xlsx"; } else { fileName = "C:\\tkfile\\" + counter + ".xlsx"; } workbook.SaveAs(fileName); workbook.Close(); excelEngine.Dispose(); } //end of excel generation 2 return(PartialView("_GenViewSuccPartial")); }