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();
}
}
