private ListaNotas CarregaXML() { XmlSerializer deserializer = new XmlSerializer(typeof(ListaNotas)); TextReader reader = new StreamReader(PatchXml); object obj = deserializer.Deserialize(reader); ListaNotas XmlData = (ListaNotas)obj; reader.Close(); return(XmlData); }
public void GeraPlanilha() { //Define Uso Não Comercial ExcelPackage.LicenseContext = LicenseContext.NonCommercial; if (!string.IsNullOrEmpty(ArqExcel)) { ArqExcel = PastaUser + @"\Controller\GridNotas.xlsx"; } FileInfo Exfile = new FileInfo(ArqExcel); if (Exfile.Exists) { if (ArquivoEmUso(ArqExcel)) { MessageBox.Show("Parece que a Planilha já esta Aberta! É necessário fechá-la antes de gerar uma nova.", "Ops! Algo deu Errado.", MessageBoxButton.OK, MessageBoxImage.Information); return; } else { Exfile.Delete(); } } CabecalhoGeral(); //msg('Gerando a Planilha por favor aguarde...', nowait) lnIni += 1; //Ajusta a Linha Inicial dos Dados para usar no final. ln = lnIni; //Variável que será utilizada na geração dos dados. ListaNotas Notas = CarregaXML(); using (ExcelPackage package = new ExcelPackage(Exfile)) { ExcelWorksheet ws = package.Workbook.Worksheets["Grid Notas"]; //Recupera a planilha para edição. OfficeOpenXml.Style.ExcelHorizontalAlignment HCentro = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center; OfficeOpenXml.Style.ExcelVerticalAlignment VCentro = OfficeOpenXml.Style.ExcelVerticalAlignment.Center; OfficeOpenXml.Style.ExcelHorizontalAlignment HLeft = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left; foreach (Nota nota in Notas.ListaDeNotas) { cel1 = "A" + ln; //Tipo de Nota ws.Cells[cel1].Value = nota.destipnot; cel1 = "B" + ln; //Sequência ws.Cells[cel1].Style.Numberformat.Format = "0"; ws.Cells[cel1].Value = nota.nroentsai; cel1 = "C" + ln; //Emissão ws.Cells[cel1].Style.Numberformat.Format = "dd/MM/yyyy"; ws.Cells[cel1].Style.HorizontalAlignment = HCentro; ws.Cells[cel1].Value = nota.datemientsai.ToShortDateString(); cel1 = "D" + ln; //Série ws.Cells[cel1].Style.Numberformat.Format = "@"; ws.Cells[cel1].Style.HorizontalAlignment = HCentro; ws.Cells[cel1].Value = nota.serentsai; cel1 = "E" + ln; //Número ws.Cells[cel1].Style.Numberformat.Format = "0"; ws.Cells[cel1].Value = nota.nfentsai; cel1 = "F" + ln; //Código - Cliente/Fornecedor ws.Cells[cel1].Style.Numberformat.Format = "0"; ws.Cells[cel1].Style.HorizontalAlignment = HCentro; ws.Cells[cel1].Value = nota.codclifor; cel1 = "G" + ln; //Nome - Cliente/Fornecedor ws.Cells[cel1].Style.Numberformat.Format = "@"; ws.Cells[cel1].Value = nota.nomentsai; cel1 = "H" + ln; //Valor ws.Cells[cel1].Style.Numberformat.Format = "_-R$ * #.##0,00_-;-R$ * #.##0,00_-;_-R$ * '-'??_-;_-@_-"; ws.Cells[cel1].Value = nota.totgerfinentsai; cel1 = "I" + ln; //Status ws.Cells[cel1].Style.Numberformat.Format = "@"; switch (nota.staentsai) { case "A": ws.Cells[cel1].Value = "Atualizada"; break; case "D": ws.Cells[cel1].Value = "Digitada"; break; case "C": ws.Cells[cel1].Value = "Cancelada"; break; case "R": ws.Cells[cel1].Value = "Reprocessada"; break; case "P": ws.Cells[cel1].Value = "F. de Caixa"; break; case "E": ws.Cells[cel1].Value = "F. de Caixa"; break; } cel1 = "J" + ln; //NF Vinculada ws.Cells[cel1].Style.Numberformat.Format = "0"; ws.Cells[cel1].Value = nota.nfCupFisEntSai; cel1 = "K" + ln; //Num. RPS ws.Cells[cel1].Style.Numberformat.Format = "0"; ws.Cells[cel1].Value = nota.nrorpsentsai; cel1 = "L" + ln; //Num. Pedido ws.Cells[cel1].Style.Numberformat.Format = "@"; ws.Cells[cel1].Value = string.IsNullOrEmpty(nota.infcretercentsai) ? "" : nota.infcretercentsai; cel1 = "M" + ln; //Data Pedido ws.Cells[cel1].Style.Numberformat.Format = "dd/MM/yyyy"; ws.Cells[cel1].Style.HorizontalAlignment = HCentro; ws.Cells[cel1].Value = string.IsNullOrEmpty(nota.infcretercentsai) ? "" : nota.datatufinentsai.ToShortDateString(); cel1 = "N" + ln; //Placa ws.Cells[cel1].Style.Numberformat.Format = "@"; ws.Cells[cel1].Style.HorizontalAlignment = HCentro; ws.Cells[cel1].Value = nota.plaveientsai; cel1 = "O" + ln; //UF ws.Cells[cel1].Style.Numberformat.Format = "@"; ws.Cells[cel1].Style.HorizontalAlignment = HCentro; ws.Cells[cel1].Value = nota.ufplaveientsai; cel1 = "P" + ln; //Código ws.Cells[cel1].Style.Numberformat.Format = "0"; ws.Cells[cel1].Style.HorizontalAlignment = HCentro; ws.Cells[cel1].Value = nota.codtra; cel1 = "Q" + ln; //Transportadora ws.Cells[cel1].Style.Numberformat.Format = "@"; ws.Cells[cel1].Value = nota.nomtra; cel1 = "R" + ln; //Observações ws.Cells[cel1].Style.Numberformat.Format = "@"; ws.Cells[cel1].Value = nota.obsentsai; ln++; } //Gera as Bordas dos Dados impressos cel1 = "A" + lnIni; cel2 = "R" + ln; ws.Cells[cel1 + ":" + cel2].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin; ws.Cells[cel1 + ":" + cel2].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thick); ln++; //Finaliza o Rodapé cel1 = "A" + ln; cel2 = "D" + ln; ws.Cells[cel1 + ":" + cel2].Merge = true; ws.Cells[cel1].Style.Font.Bold = true; ws.Cells[cel1].Value = "Relatório " + NomeRelat; ws.Cells[cel1].Style.HorizontalAlignment = HLeft; cel1 = "O" + ln; cel2 = "R" + ln; ws.Cells[cel1 + ":" + cel2].Merge = true; ws.Cells[cel1].Style.Font.Bold = true; ws.Cells[cel1].Value = "CONTROLLER - ERP"; ws.Cells[cel1].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right; // Save to file package.Save(); } }