public static byte[] GetExcelCuentasCorrientes(List <ObjetoCtasCtesPorConciliar> lstCtaCorriente, decimal SaldoAperturaGeneral, ClientesContablesModel objCliente, bool InformarMembrete)
{
string RutaPlanillaLibroMayor = ParseExtensions.Get_AppData_Path("ESTADOCTASCORRIENTES.xlsx");
byte[] ExcelByteArray = null;
using (XLWorkbook excelFile = new XLWorkbook(RutaPlanillaLibroMayor))
{
var workSheet = excelFile.Worksheet(1);
if (InformarMembrete == true)
{
workSheet.Cell("A1").Value = objCliente.RazonSocial;
workSheet.Cell("A2").Value = objCliente.RUTEmpresa;
workSheet.Cell("A3").Value = objCliente.Giro;
workSheet.Cell("A4").Value = objCliente.Direccion;
workSheet.Cell("A5").Value = objCliente.Ciudad;
workSheet.Cell("A6").Value = objCliente.Representante;
workSheet.Cell("A7").Value = objCliente.RUTRepresentante;
}
else
{
workSheet.Cell("A1").Value = string.Empty;
workSheet.Cell("A2").Value = string.Empty;
workSheet.Cell("A3").Value = string.Empty;
workSheet.Cell("A4").Value = string.Empty;
workSheet.Cell("A5").Value = string.Empty;
workSheet.Cell("A6").Value = string.Empty;
workSheet.Cell("A7").Value = string.Empty;
}
int numeroFilaExcel = 12;
int Correlativo = 0;
decimal TotalGeneralDebe = 0;
decimal TotalGeneralHaber = 0;
decimal TotalGeneralSaldo = 0;
workSheet.Columns("D").Style.DateFormat.Format = "dd-MM-yyyy";
workSheet.Column("A").Style.NumberFormat.Format = "#,##0 ;-#,##0";
workSheet.Columns("I:K").Style.NumberFormat.Format = "#,##0 ;-#,##0";
workSheet.Cell(numeroFilaExcel, "A").Value = "Saldo Apertura: " + ParseExtensions.NumeroConPuntosDeMiles(SaldoAperturaGeneral);
workSheet.Cell(numeroFilaExcel, "A").Style.Font.Bold = true;
numeroFilaExcel++;
foreach (var CapaCuentaContable in lstCtaCorriente)
{
foreach (var CapaAuxiliar in CapaCuentaContable.Contenido)
{
workSheet.Cell(numeroFilaExcel, "A").Value = ParseExtensions.ObtenerCodigoYNombreCtaContable(CapaAuxiliar.CodInterno, objCliente);
workSheet.Cell(numeroFilaExcel, "A").Style.Font.Bold = true;
numeroFilaExcel++;
workSheet.Cell(numeroFilaExcel, "A").Value = CapaAuxiliar.Rut;
workSheet.Cell(numeroFilaExcel, "A").Style.Font.Bold = true;
numeroFilaExcel++;
workSheet.Range("A" + numeroFilaExcel + ":K" + numeroFilaExcel).Rows
().Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
workSheet.Range("A" + numeroFilaExcel + ":K" + numeroFilaExcel).Rows
().Style.Border.InsideBorder = XLBorderStyleValues.Thin;
workSheet.Cell(numeroFilaExcel, "A").Value = " ";
workSheet.Cell(numeroFilaExcel, "B").Value = "RUT";
workSheet.Cell(numeroFilaExcel, "C").Value = "NOMBRE";
workSheet.Cell(numeroFilaExcel, "D").Value = "FECHA";
workSheet.Cell(numeroFilaExcel, "E").Value = "Folio";
workSheet.Cell(numeroFilaExcel, "F").Value = "COMPROBANTE";
workSheet.Cell(numeroFilaExcel, "G").Value = "DOCUMENTO";
workSheet.Cell(numeroFilaExcel, "H").Value = "VENCIMIENTO";
workSheet.Cell(numeroFilaExcel, "I").Value = "DEBE";
workSheet.Cell(numeroFilaExcel, "J").Value = "HABER";
workSheet.Cell(numeroFilaExcel, "K").Value = "SALDO";
numeroFilaExcel++;
workSheet.Range("A" + numeroFilaExcel + ":K" + numeroFilaExcel).Rows
().Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
workSheet.Range("A" + numeroFilaExcel + ":K" + numeroFilaExcel).Rows
().Style.Border.InsideBorder = XLBorderStyleValues.Thin;
workSheet.Cell(numeroFilaExcel, "A").Value = "Saldo Apertura: " + ParseExtensions.NumeroConPuntosDeMiles(CapaAuxiliar.SaldoRut);
workSheet.Cell(numeroFilaExcel, "A").Style.Font.Bold = true;
numeroFilaExcel++;
foreach (var CapaDetalle in CapaAuxiliar.Contenido)
{
workSheet.Range("A" + numeroFilaExcel + ":K" + numeroFilaExcel).Rows
().Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
workSheet.Range("A" + numeroFilaExcel + ":K" + numeroFilaExcel).Rows
().Style.Border.InsideBorder = XLBorderStyleValues.Thin;
Correlativo++;
workSheet.Cell(numeroFilaExcel, "A").Value = Correlativo;
workSheet.Cell(numeroFilaExcel, "B").Value = CapaDetalle.RutPrestador;
workSheet.Cell(numeroFilaExcel, "C").Value = CapaDetalle.NombrePrestador;
workSheet.Cell(numeroFilaExcel, "D").Value = CapaDetalle.Fecha;
workSheet.Cell(numeroFilaExcel, "E").Value = CapaDetalle.Folio;
if (!string.IsNullOrWhiteSpace(CapaDetalle.Comprobante2))
{
workSheet.Cell(numeroFilaExcel, "F").Value = CapaDetalle.Comprobante2;
}
else
{
workSheet.Cell(numeroFilaExcel, "F").Value = CapaDetalle.Comprobante;
}
if (CapaDetalle.Documento == 0)
{
workSheet.Cell(numeroFilaExcel, "G").Value = "Honorarios";
}
else
{
workSheet.Cell(numeroFilaExcel, "G").Value = ParseExtensions.EnumGetDisplayAttrib(CapaDetalle.Documento);
}
workSheet.Cell(numeroFilaExcel, "H").Value = "";
workSheet.Cell(numeroFilaExcel, "I").Value = CapaDetalle.Debe;
workSheet.Cell(numeroFilaExcel, "J").Value = CapaDetalle.Haber;
numeroFilaExcel++;
}
workSheet.Range("A" + numeroFilaExcel + ":K" + numeroFilaExcel).Rows
().Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
workSheet.Range("A" + numeroFilaExcel + ":K" + numeroFilaExcel).Rows
().Style.Border.InsideBorder = XLBorderStyleValues.Thin;
workSheet.Cell(numeroFilaExcel, "I").Value = CapaAuxiliar.TotalDebe;
workSheet.Cell(numeroFilaExcel, "I").Style.Font.Bold = true;
workSheet.Cell(numeroFilaExcel, "J").Value = CapaAuxiliar.TotalHaber;
workSheet.Cell(numeroFilaExcel, "J").Style.Font.Bold = true;
workSheet.Cell(numeroFilaExcel, "K").Value = CapaAuxiliar.SaldoRut;
workSheet.Cell(numeroFilaExcel, "K").Style.Font.Bold = true;
numeroFilaExcel++;
numeroFilaExcel++;
}
workSheet.Cell(numeroFilaExcel, "G").Value = "Saldo Apertura: " + ParseExtensions.NumeroConPuntosDeMiles(CapaCuentaContable.TotalSaldoAcumulado);
workSheet.Cell(numeroFilaExcel, "G").Style.Font.Bold = true;
numeroFilaExcel++;
workSheet.Cell(numeroFilaExcel, "G").Value = "TOTAL CUENTA CONTABLE: ";
workSheet.Cell(numeroFilaExcel, "G").Style.Font.Bold = true;
workSheet.Cell(numeroFilaExcel, "H").Value = ParseExtensions.ObtenerCodigoYNombreCtaContable(CapaCuentaContable.CodigoInterno, objCliente);
workSheet.Cell(numeroFilaExcel, "H").Style.Font.Bold = true;
workSheet.Cell(numeroFilaExcel, "I").Value = CapaCuentaContable.TotalDebe;
workSheet.Cell(numeroFilaExcel, "I").Style.Font.Bold = true;
TotalGeneralDebe += CapaCuentaContable.TotalDebe;
workSheet.Cell(numeroFilaExcel, "J").Value = CapaCuentaContable.TotalHaber;
workSheet.Cell(numeroFilaExcel, "J").Style.Font.Bold = true;
TotalGeneralHaber += CapaCuentaContable.TotalHaber;
workSheet.Cell(numeroFilaExcel, "K").Value = CapaCuentaContable.TotalSaldo;
workSheet.Cell(numeroFilaExcel, "K").Style.Font.Bold = true;
numeroFilaExcel++;
numeroFilaExcel++;
}
workSheet.Cell(numeroFilaExcel, "H").Value = "TOTAL FINAL: ";
workSheet.Cell(numeroFilaExcel, "H").Style.Font.Bold = true;
workSheet.Cell(numeroFilaExcel, "I").Value = TotalGeneralDebe;
workSheet.Cell(numeroFilaExcel, "I").Style.Font.Bold = true;
workSheet.Cell(numeroFilaExcel, "J").Value = TotalGeneralHaber;
workSheet.Cell(numeroFilaExcel, "J").Style.Font.Bold = true;
TotalGeneralSaldo = TotalGeneralHaber - TotalGeneralDebe;
workSheet.Cell(numeroFilaExcel, "K").Value = TotalGeneralSaldo;
workSheet.Cell(numeroFilaExcel, "K").Style.Font.Bold = true;
ExcelByteArray = ParseExtensions.GetExcelStream(excelFile);
if (ExcelByteArray == null)
{
return(null);
}
else
{
return(ExcelByteArray);
}
}
}
