public static string WriteToExcel(DataTable dataTable, string resultFilePrefix, string templateFileName) { if (!CreateNewIWorkbook(resultFilePrefix, templateFileName, out IWorkbook workbook, out ISheet sheet, out string resultFile, string.Empty)) { return(string.Empty); } List <ItemTreatment> treatments = ParseDataTable(dataTable); int rowNumber = 1; int columnNumber = 0; foreach (ItemTreatment treatment in treatments) { List <object> valuesTreatment = new List <object>() { treatment.Treatdate, treatment.Filial, treatment.Treatcode, treatment.Client, treatment.Pcode, treatment.Histnum, treatment.Phone, treatment.TreatType, treatment.Depname, treatment.Dcode, treatment.Doc }; int serviceCountResult = 0; int serviceQuantityResult = 0; double serviceAmountResult = 0; int serviceRow = rowNumber; for (int treSer = 0; treSer < treatment.Services.Count; treSer++) { ItemService service = treatment.Services[treSer]; serviceCountResult += service.Count; serviceQuantityResult++; serviceAmountResult += service.Amount; List <object> valuesService = new List <object>() { service.Name, service.Kodoper, service.Count, service.Amount }; List <object> valuesToWrite = new List <object>(); valuesToWrite.AddRange(valuesTreatment); valuesToWrite.AddRange(valuesService); if (treSer > 0) { valuesToWrite[2] = null; valuesToWrite[4] = null; valuesToWrite[9] = null; } WriteOutRow(valuesToWrite, sheet, serviceRow, columnNumber); serviceRow++; } int referralQuantityResult = 0; int referralServiceQuantityResult = 0; int referralServiceCountResult = 0; double referralServiceAmountResult = 0; int referralScheduleQuantityResult = 0; int referralTreatQuantityResult = 0; int referralTreatServiceQuantityResult = 0; int referralTreatServiceCountResult = 0; double referralTreatServiceAmountResult = 0; int referralRow = rowNumber; for (int treRef = 0; treRef < treatment.Referrals.Count; treRef++) { ItemReferral referral = treatment.Referrals.ElementAt(treRef).Value; referralQuantityResult++; List <long> treatcodesByReferrals = new List <long>(); for (int refSer = 0; refSer < referral.Services.Count; refSer++) { ItemService service = referral.Services[refSer]; referralServiceQuantityResult++; referralServiceCountResult += service.Count; referralServiceAmountResult += service.Amount; List <object> valuesToWrite = new List <object>(); valuesToWrite.AddRange(valuesTreatment); valuesToWrite.AddRange(new List <object> { null, null, null, null }); List <object> valuesService = new List <object> { referral.RefType, refSer == 0 ? referral.Refid.ToString() : null, service.Name, service.Kodoper, service.Count, service.Amount }; if (referral.Schedule is null) { valuesService.AddRange(new List <object> { "Отсутствует", null, null, null, null, null, null, null }); } else { referralScheduleQuantityResult++; valuesService.AddRange(new List <object> { referral.Schedule.Workdate, refSer == 0 ? referral.Schedule.Schedid.ToString() : null, refSer == 0 ? treatment.Pcode.ToString() : null, referral.Schedule.Doc, referral.Schedule.DocPost, referral.Schedule.AuthorFilial, referral.Schedule.AuthorName, referral.Schedule.AuthorPost }); } if (referral.Treat is null) { valuesService.AddRange(new List <object> { "Нет", null, null, null, null, null, null, null }); } else { if (treatcodesByReferrals.Contains(referral.Treat.Treatcode)) { valuesService.AddRange(new List <object> { "Да", referral.Treat.Treatdate, null, null }); } else { valuesService.AddRange(new List <object> { "Да", referral.Treat.Treatdate, referral.Treat.Treatcode, treatment.Pcode }); referralTreatQuantityResult++; treatcodesByReferrals.Add(referral.Treat.Treatcode); } foreach (ItemService treatService in referral.Treat.Services) { if (treatService.IsUsed) { continue; } if (!treatService.Name.Equals(service.Name)) { continue; } referralTreatServiceQuantityResult++; referralTreatServiceCountResult += treatService.Count; referralTreatServiceAmountResult += treatService.Amount; valuesService.AddRange(new List <object> { treatService.Name, treatService.Kodoper, treatService.Count, treatService.Amount }); valuesService.Add( ((double)treatService.Amount / (double)service.Amount * 100.0d) .ToString("N2", CultureInfo.CurrentCulture)); treatService.IsUsed = true; break; } } valuesToWrite.AddRange(valuesService); if (treRef > 0) { valuesToWrite[2] = null; valuesToWrite[4] = null; valuesToWrite[9] = null; } WriteOutRow(valuesToWrite, sheet, referralRow, columnNumber); referralRow++; } } columnNumber = 0; if (serviceRow != rowNumber || referralRow != rowNumber) { rowNumber = serviceRow > referralRow ? serviceRow : referralRow; } else { WriteOutRow(valuesTreatment, sheet, rowNumber, columnNumber); rowNumber++; } List <object> valuesToWriteResult = new List <object>(); valuesToWriteResult.AddRange(valuesTreatment); valuesToWriteResult.Add("Итого по услугам"); valuesToWriteResult.Add(serviceQuantityResult); valuesToWriteResult.Add(serviceCountResult); valuesToWriteResult.Add(serviceAmountResult); valuesToWriteResult.Add("Итого по направлениям"); valuesToWriteResult.Add(referralQuantityResult); valuesToWriteResult.Add(null); valuesToWriteResult.Add(referralServiceQuantityResult); valuesToWriteResult.Add(referralServiceCountResult); valuesToWriteResult.Add(referralServiceAmountResult); valuesToWriteResult.Add("Итого по назначениям"); valuesToWriteResult.Add(referralScheduleQuantityResult); valuesToWriteResult.AddRange(new List <object> { null, null, null, null, null, null }); string serviceResult = "Нет услуг в направлениях"; if (referralServiceCountResult > 0) { serviceResult = ((double)referralTreatServiceCountResult / (double)referralServiceCountResult * 100.0) .ToString("N2", CultureInfo.CurrentCulture) + "% - исполнения (по услугам)"; } valuesToWriteResult.Add(serviceResult); valuesToWriteResult.Add("Итого по приемам"); valuesToWriteResult.Add(referralTreatQuantityResult); valuesToWriteResult.Add(null); valuesToWriteResult.Add(null); valuesToWriteResult.Add(referralTreatServiceQuantityResult); valuesToWriteResult.Add(referralTreatServiceCountResult); valuesToWriteResult.Add(referralTreatServiceAmountResult); if (referralServiceAmountResult > 0) { valuesToWriteResult.Add( ((double)referralTreatServiceAmountResult / (double)referralServiceAmountResult * 100.0d) .ToString("N2", CultureInfo.CurrentCulture)); } else { valuesToWriteResult.Add("Нет направлений"); } valuesToWriteResult[2] = null; valuesToWriteResult[4] = null; valuesToWriteResult[9] = null; WriteOutRow(valuesToWriteResult, sheet, rowNumber, columnNumber); columnNumber = 0; rowNumber++; } if (!SaveAndCloseIWorkbook(workbook, resultFile)) { return(string.Empty); } return(resultFile); }
static List <ItemTreatment> ParseDataTable(DataTable dataTable) { List <ItemTreatment> treatments = new List <ItemTreatment>(); foreach (DataRow row in dataTable.Rows) { string treatdate = row["TREATDATE"].ToString(); string treatcode = row["TREATCODE"].ToString(); string filial = row["FILIAL"].ToString(); string client = row["CLIENT"].ToString(); string pcode = row["PCODE"].ToString(); string histnum = row["HISTNUM"].ToString(); string phone = row["PHONE"].ToString(); string depname = row["DEPNAME"].ToString(); string dcode = row["DCODE"].ToString(); string doc = row["DOC"].ToString(); string services = row["SERVICES"].ToString(); string referralsAll = row["REFERRALS_ALL"].ToString(); string referralsScheduled = row["REFERRALS_SCHEDULED"].ToString(); string referralsCompleted = row["REFERRALS_COMPLETED"].ToString(); string[] phoneSplitted = phone.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries); phone = string.Join(", ", phoneSplitted); ItemTreatment itemTreatment = new ItemTreatment { Treatdate = treatdate, Treatcode = treatcode, Filial = filial, Client = client, Pcode = pcode, Histnum = histnum, Phone = phone, TreatType = services.ToLower().Contains("первичный") ? "Первичный" : (services.ToLower().Contains("повторный") ? "Повторный" : ""), Depname = depname, Dcode = dcode, Doc = doc }; string[] servicesSplitted = services.Split(new char[] { '^' }, StringSplitOptions.RemoveEmptyEntries); foreach (string service in servicesSplitted) { if (ParseService(service, row, out ItemService itemService)) { itemTreatment.Services.Add(itemService); } } string[] referralsAllSplitted = referralsAll.Split(new char[] { '#' }, StringSplitOptions.RemoveEmptyEntries); foreach (string referall in referralsAllSplitted) { string[] referralSplitted = referall.Split('^'); if (referralSplitted.Length != 3) { Console.WriteLine("referralsAll: " + referralsAll); Logging.ToLog("Неправильная длина объекта направление, должно быть 3 части, сейчас: " + referralSplitted.Length); continue; } ItemReferral itemReferral = new ItemReferral { Refid = long.Parse(referralSplitted[0]), RefType = referralSplitted[1] }; string[] refServicesSplitted = referralSplitted[2].Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries); foreach (string service in refServicesSplitted) { if (ParseService(service, row, out ItemService itemService)) { itemReferral.Services.Add(itemService); } } itemTreatment.Referrals.Add(itemReferral.Refid, itemReferral); } string[] referralsScheduledSplitted = referralsScheduled.Split(new char[] { '^' }, StringSplitOptions.RemoveEmptyEntries); foreach (string referralScheduled in referralsScheduledSplitted) { string[] scheduleSplitted = referralScheduled.Split('$'); if (scheduleSplitted.Length != 8) { Console.WriteLine("referralsScheduled: " + referralsScheduled); Logging.ToLog("Неправильная длина объекта запись, должно быть 8 частей, сейчас: " + scheduleSplitted.Length); continue; } try { long refid = long.Parse(scheduleSplitted[0]); ItemSchedule itemSchedule = new ItemSchedule { Schedid = long.Parse(scheduleSplitted[1]), Workdate = DateTime.Parse(scheduleSplitted[2]), Doc = scheduleSplitted[3], DocPost = scheduleSplitted[4], AuthorFilial = scheduleSplitted[5], AuthorName = scheduleSplitted[6], AuthorPost = scheduleSplitted[7] }; if (itemTreatment.Referrals.ContainsKey(refid)) { itemTreatment.Referrals[refid].Schedule = itemSchedule; } } catch (Exception e) { Logging.ToLog(e.Message + Environment.NewLine + e.StackTrace); } } string[] referralsCompletedSplitted = referralsCompleted.Split(new char[] { '#' }, StringSplitOptions.RemoveEmptyEntries); foreach (string referralCompleted in referralsCompletedSplitted) { string[] treatSplitted = referralCompleted.Split('^'); if (treatSplitted.Length != 4) { Console.WriteLine("referralsCompleted: " + referralsCompleted); Logging.ToLog("Неправильная длина объекта прием, должно быть 4 части, сейчас: " + treatSplitted.Length); continue; } try { long refid = long.Parse(treatSplitted[0]); ItemTreat itemTreat = new ItemTreat { Treatcode = long.Parse(treatSplitted[1]), Treatdate = DateTime.Parse(treatSplitted[2]) }; string treatServices = treatSplitted[3]; string[] treatServicesSplitted = treatServices.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries); foreach (string treatService in treatServicesSplitted) { if (ParseService(treatService, row, out ItemService itemService)) { itemTreat.Services.Add(itemService); } } if (itemTreatment.Referrals.ContainsKey(refid)) { itemTreatment.Referrals[refid].Treat = itemTreat; } } catch (Exception e) { Logging.ToLog(e.Message + Environment.NewLine + e.StackTrace); } } treatments.Add(itemTreatment); } return(treatments); }