//public ExcelOptions()
//{
// FileName = "";
// WorksheetName = "";
// }
public ExcelOptions(string fileName
, string worksheetName
, string worksheetTitle
, IEnumerable <IExcelColumn> columns
//xlCenter
, OfficeOpenXml.Style.ExcelHorizontalAlignment dataHorizontalAligment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
//xlCenter
, OfficeOpenXml.Style.ExcelVerticalAlignment dataVerticalAligment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center
, IEnumerable <IMergeInfo> merges = null)
{
FileName = fileName;
WorksheetName = worksheetName;
WorksheetTitle = worksheetTitle;
ColumnList = columns;
DataHorizontalAlignment = dataHorizontalAligment;
DataVerticalAlignment = dataVerticalAligment;
MergeInfoList = merges;
BorderAroundColumnHeaders = false;
SetColumnHeadersBackgroundColor = false;
//colors at http://dmcritchie.mvps.org/excel/colors.htm
ColumnHeadersBackgroundColor = System.Drawing.ColorTranslator.FromHtml("#FFFFFF");
ColumnHeadersTextColor = System.Drawing.ColorTranslator.FromHtml("#000000");
//default title font
TitleFont = new System.Drawing.Font("Tahoma", 10, System.Drawing.FontStyle.Bold);
DefaultWorksheetFont = new System.Drawing.Font("Tahoma", 8, System.Drawing.FontStyle.Regular);
}
public void PopulateCell(ref ExcelWorksheet ws, string value, int intRow, int intCol,
OfficeOpenXml.Style.ExcelHorizontalAlignment ExcelHorizontalAlignment,
OfficeOpenXml.Style.ExcelVerticalAlignment ExcelVerticalAlignment,
int ExcelFontSize,
bool IsBold)
{
ws.Cells[intRow, intCol].Value = value;
ws.Cells[intRow, intCol].Style.HorizontalAlignment = ExcelHorizontalAlignment;
ws.Cells[intRow, intCol].Style.VerticalAlignment = ExcelVerticalAlignment;
// ws.Cells["A1"].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
// ws.Cells["A1"].Style.Fill.BackgroundColor.SetColor(Color.White);
ws.Cells[intRow, intCol].Style.Font.Bold = IsBold;
ws.Cells[intRow, intCol].Style.Font.Size = ExcelFontSize;
ws.Cells[intRow, intCol].Style.Font.Color.SetColor(Color.Black);
}
public MergeInfo(int fromRow
, string fromColumn
, int toRow
, string toColumn
//xlHAlignCenter -4108 Center.
, OfficeOpenXml.Style.ExcelHorizontalAlignment hAligment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
//xlVAlignBottom -4107 Bottom.
, OfficeOpenXml.Style.ExcelVerticalAlignment vAligment = OfficeOpenXml.Style.ExcelVerticalAlignment.Bottom
)
{
FromRow = fromRow;
FromColumn = fromColumn;
ToRow = toRow;
ToColumn = toColumn;
HorizontalAlignment = hAligment;
VerticalAlignment = vAligment;
}
private void CabecalhoGeral()
{
string dataAtual = DateTime.Now.ToShortDateString();
string horaAtual = DateTime.Now.ToShortTimeString();
ModelEmpresa dadosRel = new ModelEmpresa();
dadosRel = DadosEmpRel();
FileInfo ExCab = new FileInfo(ArqExcel);
using (ExcelPackage pakCab = new ExcelPackage(ExCab))
{
ExcelWorksheet ws = pakCab.Workbook.Worksheets.Add("Grid Notas");
OfficeOpenXml.Style.ExcelHorizontalAlignment HCentro = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
OfficeOpenXml.Style.ExcelVerticalAlignment VCentro = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
OfficeOpenXml.Style.ExcelHorizontalAlignment HLeft = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
lnIni = 1;
if (GeraCab)
{
if (dadosRel.logoOk) //Adiciona a Logo
{
Image LogoEmp = GetImagem(dadosRel.logoEmp);
ws.Cells["A1:A5"].Merge = true;
OfficeOpenXml.Drawing.ExcelPicture picture = ws.Drawings.AddPicture("0", LogoEmp);
picture.From.Column = 0;
picture.SetPosition(3, 3);
picture.SetSize(205, 105);
}
//Controller
ws.Cells["B1:Q2"].Merge = true;
ws.Cells["B1"].Value = "CONTROLLER ERP";
ws.Cells["B1"].Style.HorizontalAlignment = HCentro;
ws.Cells["B1"].Style.VerticalAlignment = VCentro;
ws.Cells["B1"].Style.Font.Size = 18;
ws.Cells["B1"].Style.Font.Bold = true;
ws.Cells["B1"].Style.Font.Name = "Calibri";
//Data
ws.Cells["R1"].Value = "Data: " + dataAtual;
ws.Cells["R1"].Style.Font.Bold = true;
ws.Cells["R1"].Style.HorizontalAlignment = HCentro;
//Hora
ws.Cells["R2"].Value = "Hora: " + horaAtual;
ws.Cells["R2"].Style.Font.Bold = true;
ws.Cells["R2"].Style.HorizontalAlignment = HCentro;
//Nome da Empresa
ws.Cells["B3:Q3"].Merge = true;
ws.Cells["B3"].Value = dadosRel.nomeEmp.ToUpper();
ws.Cells["B3"].Style.HorizontalAlignment = HCentro;
ws.Cells["B3"].Style.VerticalAlignment = VCentro;
ws.Cells["B3"].Style.Font.Size = 16;
ws.Cells["B3"].Style.Font.Bold = true;
ws.Cells["B3"].Style.Font.Name = "Calibri";
//Nome do Relatório
ws.Cells["B4:Q4"].Merge = true;
ws.Cells["B4"].Value = NomeRelat.ToUpper();
ws.Cells["B4"].Style.HorizontalAlignment = HCentro;
ws.Cells["B4"].Style.VerticalAlignment = VCentro;
ws.Cells["B4"].Style.Font.Size = 14;
ws.Cells["B4"].Style.Font.Bold = true;
ws.Cells["B4"].Style.Font.Name = "Calibri";
lnIni = 6;
}
//Nome e Tamanho das Colunas do Cabeçalho
cel1 = "A" + lnIni;
ws.Cells[cel1].Value = "Desc. Tipo de Nota";
ws.Column(1).Width = 30.00D;
cel1 = "B" + lnIni;
ws.Cells[cel1].Value = "Sequência";
ws.Column(2).Width = 10.00D;
cel1 = "C" + lnIni;
ws.Cells[cel1].Value = "Emissão";
ws.Column(3).Width = 12.00D;
cel1 = "D" + lnIni;
ws.Cells[cel1].Value = "Série";
ws.Column(4).Width = 6.00D;
cel1 = "E" + lnIni;
ws.Cells[cel1].Value = "Número";
ws.Column(5).Width = 12.00D;
cel1 = "F" + lnIni;
ws.Cells[cel1].Value = "Código";
ws.Column(6).Width = 7.00D;
cel1 = "G" + lnIni;
ws.Cells[cel1].Value = "Nome";
ws.Column(7).Width = 40.00D;
cel1 = "H" + lnIni;
ws.Cells[cel1].Value = "Valor";
ws.Column(8).Width = 15.00D;
cel1 = "I" + lnIni;
ws.Cells[cel1].Value = "Status";
ws.Column(9).Width = 10.00D;
cel1 = "J" + lnIni;
ws.Cells[cel1].Value = "NF Vinculada";
ws.Column(10).Width = 12.00D;
cel1 = "K" + lnIni;
ws.Cells[cel1].Value = "Num. RPS";
ws.Cells[cel1].Style.WrapText = true;
ws.Column(11).Width = 12.00D;
cel1 = "L" + lnIni;
ws.Cells[cel1].Value = "Num. Pedido";
ws.Column(12).Width = 15.00D;
cel1 = "M" + lnIni;
ws.Cells[cel1].Value = "Data Pedido";
ws.Column(13).Width = 12.00D;
cel1 = "N" + lnIni;
ws.Cells[cel1].Value = "Placa";
ws.Column(14).Width = 9.00D;
cel1 = "O" + lnIni;
ws.Cells[cel1].Value = "UF";
ws.Column(15).Width = 4.00D;
cel1 = "P" + lnIni;
ws.Cells[cel1].Value = "Código";
ws.Column(16).Width = 7.00D;
cel1 = "Q" + lnIni;
ws.Cells[cel1].Value = "Transportadora";
ws.Column(17).Width = 30.00D;
cel1 = "R" + lnIni;
ws.Cells[cel1].Value = "Observações";
ws.Column(18).Width = 60.00D;
//Fim dos Campos do Cabeçalho
//Faz a Formatação do Cabeçalho
ln = lnIni;
cel1 = "A" + lnIni;
cel2 = "R" + lnIni;
ws.Row(lnIni).Height = 30.00D;
ws.Cells[cel1 + ":" + cel2].Style.Font.Bold = true;
ws.Cells[cel1 + ":" + cel2].Style.VerticalAlignment = VCentro;
ws.Cells[cel1 + ":" + cel2].Style.HorizontalAlignment = HLeft;
ws.Cells[cel1 + ":" + cel2].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
ws.Cells[cel1 + ":" + cel2].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
ws.Cells[cel1 + ":" + cel2].Style.Border.Left.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
ws.Cells[cel1 + ":" + cel2].Style.Border.BorderAround(OfficeOpenXml.Style.ExcelBorderStyle.Thick);
//Alinha os Campos diferentes:
cel1 = "C" + lnIni; //Emissão e Série
cel2 = "D" + lnIni;
ws.Cells[cel1 + ":" + cel2].Style.HorizontalAlignment = HCentro;
cel1 = "F" + lnIni; //Código
ws.Cells[cel1].Style.HorizontalAlignment = HCentro;
cel1 = "I" + lnIni; //Status
ws.Cells[cel1].Style.HorizontalAlignment = HCentro;
cel1 = "M" + lnIni; //Data Pedido, Placa e UF
cel2 = "O" + lnIni;
ws.Cells[cel1 + ":" + cel2].Style.HorizontalAlignment = HCentro;
cel1 = "P" + lnIni; //Código
ws.Cells[cel1].Style.HorizontalAlignment = HCentro;
//Termina Formatação Cabeçalho
// Save to file
pakCab.Save();
}
}
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();
}
}
internal void MakeCellAlignment(int row, int column, OfficeOpenXml.Style.ExcelHorizontalAlignment horizontal, OfficeOpenXml.Style.ExcelVerticalAlignment vertical)
{
sheet.Cells[row, column].Style.HorizontalAlignment = horizontal;
sheet.Cells[row, column].Style.VerticalAlignment = vertical;
}
