private List <RowManagerContrAgGr> GetRows(List <ShahmatcaModel> shahmatcaModels) { int numberRow = 0; List <ClientInfo> clientInfos = _context.Set <ClientInfo>().ToList(); List <Data.Entities.OneCInfo.UserInfo> userInfos = _context.Set <Data.Entities.OneCInfo.UserInfo>().ToList(); List <RowManagerContrAgGr> rowManagerContrAgGrs = new List <RowManagerContrAgGr>(); foreach (ShahmatcaModel shahmatcaModel in shahmatcaModels) { ClientInfo clientInfo = clientInfos.FirstOrDefault(c => c.OneCId.ToString() == shahmatcaModel.Contragent_ID); Data.Entities.OneCInfo.UserInfo userInfo = userInfos.FirstOrDefault(u => u.OneCId.ToString() == shahmatcaModel.Manager_ID); if (clientInfo != null && userInfo != null) { RowManagerContrAgGr rowManagerContrAgGr = rowManagerContrAgGrs.FirstOrDefault(r => r.Contragent_ID == shahmatcaModel.Contragent_ID); if (rowManagerContrAgGr == null) { rowManagerContrAgGrs.Add(new RowManagerContrAgGr() { Contragent = shahmatcaModel.Contragent, Contragent_ID = shahmatcaModel.Contragent_ID, GR_Contragent = shahmatcaModel.GR_Contragent, Manager = shahmatcaModel.Manager, Manager_ID = shahmatcaModel.Manager_ID, NumberRow = numberRow }); numberRow++; } } } return(rowManagerContrAgGrs); }
private void CreateXsml(Dictionary <string, List <OhvatModel> > ohvats) { foreach (string id in ohvats.Keys) { if (!Directory.Exists("PDF/Manager")) { Directory.CreateDirectory("PDF/Manager"); } Data.Entities.OneCInfo.UserInfo userInfo = _context.Set <Data.Entities.OneCInfo.UserInfo>().FirstOrDefault(c => c.OneCId.ToString() == id); if (userInfo == null) { continue; } SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument. Create($"PDF/Manager/Oxvat{userInfo.UserId}.xlsx", SpreadsheetDocumentType.Workbook); WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook. AppendChild <Sheets>(new Sheets()); WorkbookStylesPart stylePart = workbookpart.AddNewPart <WorkbookStylesPart>(); stylePart.Stylesheet = GenerateStylesheet(); stylePart.Stylesheet.Save(); Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart. GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet" }; sheets.Append(sheet); int i = 0; CreatHeadXSML(spreadsheetDocument, ref i, worksheetPart); CreatBody(spreadsheetDocument, ref i, worksheetPart, ohvats.GetValueOrDefault(id), userInfo); worksheetPart.Worksheet.Save(); spreadsheetDocument.Close(); } }
private void CreatBody(SpreadsheetDocument spreadsheetDocument, ref int i, WorksheetPart worksheetPart, List <OhvatModel> ohvatModels, Data.Entities.OneCInfo.UserInfo userInfo) { SharedStringTablePart shareStringPart = GetSharedStringTablePart(spreadsheetDocument.WorkbookPart); int j = 0; foreach (OhvatModel ohvatModel in ohvatModels) { ClientInfo clientInfo = _context.Set <ClientInfo>() .FirstOrDefault(c => c.OneCId.ToString() == ohvatModel.Contragent_ID); Client client = _context.Set <Client>() .FirstOrDefault(c => clientInfo != null && c.Id == clientInfo.ClientId); if (client != null) { List <ClientPhone> clientPhones = _context.Set <ClientPhone>() .Where(c => c.ClientId == client.Id).ToList(); string nameColummn = GetCharOfTabel(4); InsertSharedStringItem(ohvatModel.Contragent, shareStringPart); Cell cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, 4, 30); cell.CellValue = new CellValue(i.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); worksheetPart.Worksheet.Save(); i++; nameColummn = GetCharOfTabel(5); InsertSharedStringItem(ohvatModel.GR_Contragent, shareStringPart); cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, 4, 30); cell.CellValue = new CellValue(i.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); worksheetPart.Worksheet.Save(); i++; string clietType = client.ClientType == ClientTypes.WithoutType ? "Без типа" : client.ClientType == ClientTypes.Small ? "Небольшой" : client.ClientType == ClientTypes.Middle1 ? "Средний 1" : client.ClientType == ClientTypes.Middle2 ? "Средний 2" : client.ClientType == ClientTypes.Large1 ? "Крупный 1" : client.ClientType == ClientTypes.Large2 ? "Крупный 2" : client.ClientType == ClientTypes.Large3 ? "Крупный 3" : client.ClientType == ClientTypes.VeryLarge ? "Очень крупный" : ""; nameColummn = GetCharOfTabel(6); InsertSharedStringItem(clietType, shareStringPart); cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, 4, 30); cell.CellValue = new CellValue(i.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); worksheetPart.Worksheet.Save(); i++; string numberOfShipments = client.NumberOfShipments == NumberOfShipments.WithoutType ? "Не указан" : client.NumberOfShipments == NumberOfShipments.OnePerMonth ? "1 раз в месяц" : client.NumberOfShipments == NumberOfShipments.OnePerTwoWeek ? "1 раз в 2 недели" : client.NumberOfShipments == NumberOfShipments.ThreePerMonth ? "1 раз в неделю" : client.NumberOfShipments == NumberOfShipments.FivePerMonth ? "5 раз в месяц" : client.NumberOfShipments == NumberOfShipments.SixPerMonth ? "6 раз в месяц" : client.NumberOfShipments == NumberOfShipments.TwoPerWeek ? "2 раза в неделю" : ""; nameColummn = GetCharOfTabel(7); InsertSharedStringItem(clietType, shareStringPart); cell = InsertCellInWorksheet(numberOfShipments, (uint)j + 9, worksheetPart, 4, 30); cell.CellValue = new CellValue(i.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); worksheetPart.Worksheet.Save(); i++; nameColummn = GetCharOfTabel(8); InsertSharedStringItem(ohvatModel.KolNom, shareStringPart); cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, (uint)GetIdStyles(Convert.ToDouble(ohvatModel.KolNom), Convert.ToDouble(ohvatModel.KolNomPred)), 30); cell.CellValue = new CellValue(i.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); worksheetPart.Worksheet.Save(); i++; nameColummn = GetCharOfTabel(9); InsertSharedStringItem(ohvatModel.KolNom_5, shareStringPart); cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, (uint)GetIdStyles(Convert.ToDouble(ohvatModel.KolNom_5), Convert.ToDouble(ohvatModel.KolNom_5Pred)), 30); cell.CellValue = new CellValue(i.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); worksheetPart.Worksheet.Save(); i++; nameColummn = GetCharOfTabel(10); InsertSharedStringItem(ohvatModel.KolNomPred, shareStringPart); cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, 12, 30); cell.CellValue = new CellValue(i.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); worksheetPart.Worksheet.Save(); i++; nameColummn = GetCharOfTabel(11); InsertSharedStringItem(ohvatModel.KolNom_5Pred, shareStringPart); cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, 12, 30); cell.CellValue = new CellValue(i.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); worksheetPart.Worksheet.Save(); i++; nameColummn = GetCharOfTabel(12); InsertSharedStringItem(ohvatModel.Summa, shareStringPart); cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, (uint)GetIdStyles(Convert.ToDouble(ohvatModel.Summa.Replace('.', ',')), Convert.ToDouble(ohvatModel.SummaPred.Replace('.', ','))), 30); cell.CellValue = new CellValue(i.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); worksheetPart.Worksheet.Save(); i++; nameColummn = GetCharOfTabel(13); InsertSharedStringItem(ohvatModel.Summa_5, shareStringPart); cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, (uint)GetIdStyles(Convert.ToDouble(ohvatModel.Summa_5.Replace('.', ',')), Convert.ToDouble(ohvatModel.Summa_5Pred.Replace('.', ','))), 30); cell.CellValue = new CellValue(i.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); worksheetPart.Worksheet.Save(); i++; nameColummn = GetCharOfTabel(14); InsertSharedStringItem(ohvatModel.SummaPred, shareStringPart); cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, 12, 30); cell.CellValue = new CellValue(i.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); worksheetPart.Worksheet.Save(); i++; nameColummn = GetCharOfTabel(15); InsertSharedStringItem(ohvatModel.Summa_5Pred, shareStringPart); cell = InsertCellInWorksheet(nameColummn, (uint)j + 9, worksheetPart, 12, 30); cell.CellValue = new CellValue(i.ToString()); cell.DataType = new EnumValue <CellValues>(CellValues.SharedString); worksheetPart.Worksheet.Save(); i++; j++; } } }