private void workExcel_DoWork(object sender, DoWorkEventArgs e)
{
cnx = new SqlConnection(cdn);
cmd = new SqlCommand();
cmd.Connection = cnx;
nh = new CalculoNomina.Core.NominaHelper();
nh.Command = cmd;
CalculoNomina.Core.tmpPagoNomina pn = new CalculoNomina.Core.tmpPagoNomina();
pn.idempresa = GLOBALES.IDEMPRESA;
pn.fechainicio = periodoInicio.Date;
pn.fechafin = periodoFin.Date;
pn.tiponomina = _tipoNomina;
DataTable dt = new DataTable();
try
{
cnx.Open();
dt = nh.obtenerPreNominaTabular(pn, NetoCero, Orden, _periodo);
cnx.Close();
cnx.Dispose();
}
catch (Exception error)
{
MessageBox.Show("Error: \r\n \r\n" + error.Message, "Error");
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Add();
Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;
excel.Cells[1, 1] = dt.Rows[0][0];
excel.Cells[1, 6] = "Periodo";
excel.Cells[2, 1] = "RFC:";
excel.Cells[3, 1] = "REG. PAT:";
excel.Cells[2, 2] = dt.Rows[0][1];
excel.Cells[3, 2] = dt.Rows[0][2];
excel.Cells[2, 6] = dt.Rows[0][3];
excel.Cells[2, 7] = dt.Rows[0][4];
//SE COLOCAN LOS TITULOS DE LAS COLUMNAS
int iCol = 1;
for (int i = 6; i < dt.Columns.Count; i++)
{
excel.Cells[5, iCol] = dt.Columns[i].ColumnName;
iCol++;
}
//SE COLOCAN LOS DATOS
int contadorDt = dt.Rows.Count;
int contador = 0;
int progreso = 0;
iCol = 1;
int iFil = 6;
Microsoft.Office.Interop.Excel.Range rng;
for (int i = 0; i < dt.Rows.Count; i++)
{
progreso = (contador * 100) / contadorDt;
workExcel.ReportProgress(progreso, "Reporte a Excel");
contador++;
if (i != dt.Rows.Count - 1)
{
for (int j = 6; j < dt.Columns.Count; j++)
{
excel.Cells[iFil, iCol] = dt.Rows[i][j];
iCol++;
}
iFil++;
#region AGRUPADO POR DEPARTAMENTO
//if (dt.Rows[i][5].ToString() == dt.Rows[i + 1][5].ToString())
// for (int j = 6; j < dt.Columns.Count; j++)
// {
// excel.Cells[iFil, iCol] = dt.Rows[i][j];
// iCol++;
// }
//else
//{
// for (int j = 6; j < dt.Columns.Count; j++)
// {
// excel.Cells[iFil, iCol] = dt.Rows[i][j];
// iCol++;
// }
// iFil++;
// rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 1];
// rng.Font.Bold = true;
// excel.Cells[iFil, 1] = dt.Rows[i][5];
// rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 9];
// rng.NumberFormat = "#,##0.00";
// rng.Font.Bold = true;
// excel.Cells[iFil, 9] = totalPercepciones.ToString();
// rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 15];
// rng.NumberFormat = "#,##0.00";
// rng.Font.Bold = true;
// excel.Cells[iFil, 15] = totalDeducciones.ToString();
// iFil++;
// totalPercepciones = 0;
// totalDeducciones = 0;
//}
#endregion
}
else
{
for (int j = 6; j < dt.Columns.Count; j++)
{
excel.Cells[iFil, iCol] = dt.Rows[i][j];
iCol++;
}
}
iCol = 1;
}
iFil++;
for (int i = 6; i < iFil; i++)
{
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[i, 2];
rng.Columns.AutoFit();
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[i, 14];
rng.NumberFormat = "#,##0.00";
rng.Formula = string.Format("=C{0}+D{0}+E{0}+F{0}+G{0}+H{0}+I{0}+J{0}+K{0}+L{0}+M{0}", i);
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[i, 23];
rng.NumberFormat = "#,##0.00";
rng.Formula = string.Format("=O{0}+P{0}+Q{0}+R{0}+S{0}+T{0}+V{0}", i);
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[i, 24];
rng.NumberFormat = "#,##0.00";
rng.Formula = string.Format("=N{0}+U{0}-W{0}", i);
}
int suma = iFil - 1;
iFil++;
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 3];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(C6:C{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 4];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(D6:D{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 5];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(E6:E{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 6];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(F6:F{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 7];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(G6:G{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 8];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(H6:H{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 9];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(I6:I{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 10];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(J6:J{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 11];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(K6:K{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 12];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(L6:L{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 13];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(M6:M{0})", suma.ToString());
//TOTAL PERCEPCIONES
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 14];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(N6:N{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 15];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(O6:O{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 16];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(P6:P{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 17];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(Q6:Q{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 18];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(R6:R{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 19];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(S6:S{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 20];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(T6:T{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 21];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(U6:U{0})", suma.ToString());
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 22];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(V6:V{0})", suma.ToString());
//TOTAL DEDUCCIONES
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 23];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(W6:W{0})", suma.ToString());
//TOTAL NETO
rng = (Microsoft.Office.Interop.Excel.Range)excel.Cells[iFil, 24];
rng.NumberFormat = "#,##0.00";
rng.Font.Bold = true;
rng.Formula = string.Format("=SUM(X6:X{0})", suma.ToString());
excel.Range["A1", "G3"].Font.Bold = true;
excel.Range["A5", "X5"].Font.Bold = true;
excel.Range["B:X"].EntireColumn.AutoFit();
excel.Range["A6"].Select();
excel.ActiveWindow.FreezePanes = true;
excel.Range["A5", "X5"].Interior.ColorIndex = 36;
excel.Range["A5", "M5"].Font.ColorIndex = 1;
excel.Range["O5", "V5"].Font.ColorIndex = 1;
excel.Range["N5"].Font.ColorIndex = 32;
excel.Range["W5"].Font.ColorIndex = 32;
excel.Range["X5"].Font.ColorIndex = 32;
excel.Range["B6", "X" + iFil.ToString()].NumberFormat = "#,##0.00";
workSheet.SaveAs("Reporte_Tabular.xlsx");
excel.Visible = true;
workExcel.ReportProgress(100, "Reporte a Excel");
}