private void PopolaDSGrigliaODL() { foreach (PrioritaDS.USR_PRD_MOVFASIRow odl in _dsPriorita.USR_PRD_MOVFASI) { DataTable dtGriglia = _dsGriglieODL.Tables[_nomeTabellaGrigliaODL]; DataRow riga = dtGriglia.NewRow(); PrioritaDS.USR_PRD_FASIRow fase = _dsPriorita.USR_PRD_FASI.Where(x => x.IDPRDFASE == odl.IDPRDFASE).FirstOrDefault(); if (fase == null) { new Exception(String.Format("ODL senza fase. IDPRDMOVFASE: {0} IDPRDFASE: {1}", odl.IDPRDMOVFASE, odl.IDPRDFASE)); } PrioritaDS.USR_PRD_LANCIODRow lanciod = _dsPriorita.USR_PRD_LANCIOD.Where(x => x.IDLANCIOD == fase.IDLANCIOD).FirstOrDefault(); if (lanciod == null) { new Exception(String.Format("ODL senza lancio. IDPRDMOVFASE: {0} IDPRDFASE: {1} IDLANCIOD: {2}", odl.IDPRDMOVFASE, odl.IDPRDFASE, fase.IDLANCIOD)); } PrioritaDS.MAGAZZRow articolo = _dsAnagrafica.MAGAZZ.Where(x => x.IDMAGAZZ == odl.IDMAGAZZ).FirstOrDefault(); riga[(int)Colonne.IDLANCIOD] = lanciod.IsIDLANCIODNull() ? string.Empty : lanciod.IDLANCIOD; riga[(int)Colonne.IDPRDFASE] = fase.IsIDPRDFASENull() ? string.Empty : fase.IDPRDFASE; riga[(int)Colonne.IDPRDMOVFASE] = odl.IsIDPRDMOVFASENull() ? string.Empty : odl.IDPRDMOVFASE; riga[(int)Colonne.IDMAGAZZ] = odl.IsIDMAGAZZNull() ? string.Empty : odl.IDMAGAZZ; riga[(int)Colonne.AZIENDA] = odl.AZIENDA; if (!lanciod.IsSEGNALATORENull()) { PrioritaDS.SEGNALATORIRow segnalatore = _dsAnagrafica.SEGNALATORI.Where(x => x.CODICE == lanciod.SEGNALATORE).FirstOrDefault(); if (segnalatore != null) { riga[(int)Colonne.SEGNALATORE] = segnalatore.IsRAGIONESOCNull() ? string.Empty : segnalatore.RAGIONESOC; } } riga[(int)Colonne.COMMESSA] = lanciod.IsNOMECOMMESSANull() ? string.Empty : lanciod.NOMECOMMESSA; riga[(int)Colonne.DATACOMMESSA] = lanciod.IsDATACOMMESSANull() ? string.Empty : lanciod.DATACOMMESSA.ToShortDateString(); riga[(int)Colonne.ODL] = odl.IsNUMMOVFASENull() ? string.Empty : odl.NUMMOVFASE; riga[(int)Colonne.DATAODL] = odl.IsDATAMOVFASENull() ? string.Empty : odl.DATAMOVFASE.ToShortDateString(); riga[(int)Colonne.BARCODE] = odl.IsBARCODENull() ? string.Empty : odl.BARCODE; if (!odl.IsCODICECLIFONull()) { PrioritaDS.CLIFORow clifo = _dsAnagrafica.CLIFO.Where(x => x.CODICE == odl.CODICECLIFO).FirstOrDefault(); if (clifo != null) { riga[(int)Colonne.REPARTO] = clifo.IsRAGIONESOCNull() ? string.Empty : clifo.RAGIONESOC; } } if (!odl.IsCODICECLIFONull()) { PrioritaDS.TABFASRow tabfas = _dsAnagrafica.TABFAS.Where(x => x.IDTABFAS == odl.IDTABFAS).FirstOrDefault(); if (tabfas != null) { riga[(int)Colonne.FASE] = tabfas.IsDESTABFASPPRENull() ? string.Empty : tabfas.DESTABFAS; } } if (articolo != null) { riga[(int)Colonne.ARTICOLO] = articolo.MODELLO; } //StringBuilder BIL = new StringBuilder(); //List<PrioritaDS.USR_VENDITETRow> vendite = _dsPriorita.USR_VENDITET.Where(x => x.IDPRDMOVFASE == odl.IDPRDMOVFASE).ToList(); //if (vendite.Count > 0) //{ // foreach (PrioritaDS.USR_VENDITETRow vendita in vendite) // { // string str = string.Format("{0} del {1}, ", vendita.NUMDOC, vendita.DATDOC.ToShortDateString()); // BIL.Append(str); // } //} //riga[(int)Colonne.BIL] = BIL.Length == 0 ? string.Empty : BIL.ToString().Substring(0, BIL.Length - 2); riga[(int)Colonne.QUANTITA] = odl.QTA; riga[(int)Colonne.QUANTITADATERMINARE] = odl.IsQTADATERNull() ? 0 : odl.QTADATER; dtGriglia.Rows.Add(riga); } }
public byte[] CreaExcelScadenze(PrioritaDS ds, PrioritaDS dsAnagrafica) { byte[] content; MemoryStream ms = new MemoryStream(); //string filename = @"c:\temp\mancanti.xlsx"; using (SpreadsheetDocument document = SpreadsheetDocument.Create(ms, SpreadsheetDocumentType.Workbook)) { WorkbookPart workbookPart = document.AddWorkbookPart(); workbookPart.Workbook = new Workbook(); WorksheetPart worksheetPart = workbookPart.AddNewPart <WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(); // Adding style WorkbookStylesPart stylePart = workbookPart.AddNewPart <WorkbookStylesPart>(); stylePart.Stylesheet = GenerateStylesheet(); stylePart.Stylesheet.Save(); int numeroColonne = 6; Columns columns = new Columns(); for (int i = 0; i < numeroColonne; i++) { Column c = new Column(); UInt32Value u = new UInt32Value((uint)(i + 1)); c.Min = u; c.Max = u; c.Width = 20; c.CustomWidth = true; columns.Append(c); } worksheetPart.Worksheet.AppendChild(columns); Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets()); Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Scadenze" }; sheets.Append(sheet); workbookPart.Workbook.Save(); SheetData sheetData = worksheetPart.Worksheet.AppendChild(new SheetData()); // Constructing header Row row = new Row(); row.Append(ConstructCell("Barcode", CellValues.String, 2)); row.Append(ConstructCell("ODL", CellValues.String, 2)); row.Append(ConstructCell("Reparto", CellValues.String, 2)); row.Append(ConstructCell("Articolo", CellValues.String, 2)); row.Append(ConstructCell("Quantità", CellValues.String, 2)); row.Append(ConstructCell("Scadenza", CellValues.String, 2)); // Insert the header row to the Sheet Data sheetData.AppendChild(row); List <string> IDPRDMOVFASE = ds.RW_SCADENZE.Select(x => x.IDPRDMOVFASE).Distinct().ToList(); foreach (string idMovFase in IDPRDMOVFASE) { PrioritaDS.USR_PRD_MOVFASIRow odl = ds.USR_PRD_MOVFASI.Where(x => x.IDPRDMOVFASE == idMovFase).FirstOrDefault(); PrioritaDS.MAGAZZRow articolo = dsAnagrafica.MAGAZZ.Where(x => x.IDMAGAZZ == odl.IDMAGAZZ).FirstOrDefault(); foreach (PrioritaDS.RW_SCADENZERow scadenza in ds.RW_SCADENZE.Where(x => x.IDPRDMOVFASE == idMovFase).OrderBy(x => x.DATA)) { row = new Row(); row.Append(ConstructCell(odl.BARCODE, CellValues.String, 1)); row.Append(ConstructCell(odl.NUMMOVFASE, CellValues.String, 1)); row.Append(ConstructCell(odl.CODICECLIFO, CellValues.String, 1)); row.Append(ConstructCell(articolo.MODELLO, CellValues.String, 1)); row.Append(ConstructCell(scadenza.QTA.ToString(), CellValues.String, 1)); row.Append(ConstructCell(scadenza.DATA.ToShortDateString(), CellValues.String, 1)); sheetData.AppendChild(row); } } workbookPart.Workbook.Save(); document.Save(); document.Close(); ms.Seek(0, SeekOrigin.Begin); content = ms.ToArray(); } return(content); }