private void workerExportar_DoWork(object sender, DoWorkEventArgs e)
{
string c = "";
c = campos.Substring(0, campos.Length - 1);
cnx = new SqlConnection(cdn);
cmd = new SqlCommand();
cmd.Connection = cnx;
eh = new Exportacion.Core.ExportacionHelper();
eh.Command = cmd;
DataTable dt = new DataTable();
try
{
cnx.Open();
if (_tipoReporte == GLOBALES.EXPORTACATALOGO_GENERAL)
dt = eh.datosExportar(GLOBALES.IDEMPRESA, c, tablaDireccion + tablaComplemento + tablaInfonavit);
else
dt = eh.datosExportar(GLOBALES.IDEMPRESA, c, tablaDireccion + tablaComplemento + tablaInfonavit, tipoNomina, periodo,
dtpFechaInicio.Value.Date, dtpFechaFin.Value.Date);
cnx.Close();
cnx.Dispose();
}
catch (Exception error)
{
MessageBox.Show("Error: Al obtener los datos para exportar. \r\n \r\n" + error.Message, "Error");
workerExportar.ReportProgress(0, "Cancelado");
workerExportar.CancelAsync();
return;
}
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Add();
Microsoft.Office.Interop.Excel._Worksheet workSheet = excel.ActiveSheet;
//SE COLOCAN LOS TITULOS DE LAS COLUMNAS
int iCol = 1;
int iFil = 2;
int i = 0;
if (_tipoReporte == GLOBALES.EXPORTACATALOGO_GENERAL)
for (i = 0; i < dt.Columns.Count; i++)
{
excel.Cells[1, iCol] = dt.Columns[i].ColumnName;
iCol++;
}
else
for (i = 1; i < dt.Columns.Count; i++)
{
excel.Cells[1, iCol] = dt.Columns[i].ColumnName;
iCol++;
}
iCol = 1;
int progreso = 0;
int totalRegistro = dt.Rows.Count;
if (_tipoReporte == GLOBALES.EXPORTACATALOGO_GENERAL)
for (i = 0; i < dt.Rows.Count; i++)
{
progreso = (i * 100) / totalRegistro;
workerExportar.ReportProgress(progreso);
if (i != dt.Rows.Count - 1)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
excel.Cells[iFil, iCol] = dt.Rows[i][j];
iCol++;
}
iFil++;
}
else
{
for (int j = 0; j < dt.Columns.Count; j++)
{
excel.Cells[iFil, iCol] = dt.Rows[i][j];
iCol++;
}
}
iCol = 1;
}
else
for (i = 0; i < dt.Rows.Count; i++)
{
progreso = (i * 100) / totalRegistro;
workerExportar.ReportProgress(progreso);
if (i != dt.Rows.Count - 1)
{
for (int j = 1; j < dt.Columns.Count; j++)
{
excel.Cells[iFil, iCol] = dt.Rows[i][j];
iCol++;
}
iFil++;
}
else
{
for (int j = 1; j < dt.Columns.Count; j++)
{
excel.Cells[iFil, iCol] = dt.Rows[i][j];
iCol++;
}
}
iCol = 1;
}
workerExportar.ReportProgress(100);
excel.Range["A:AI"].EntireColumn.AutoFit();
excel.Range["A1:AI1"].Font.Bold = true;
excel.Range["A2"].Select();
excel.ActiveWindow.FreezePanes = true;
excel.Range["A1"].AutoFilter(1, System.Type.Missing);
workSheet.SaveAs(nombreArchivo);
excel.Visible = true;
}
private void frmExportarEmpleado_Load(object sender, EventArgs e)
{
lstvCampos.View = View.Details;
lstvCampos.CheckBoxes = false;
lstvCampos.GridLines = false;
lstvCampos.MultiSelect = false;
lstvCampos.Columns.Add("Campos", 100, HorizontalAlignment.Left);
lstvOrdenCampos.View = View.Details;
lstvOrdenCampos.CheckBoxes = false;
lstvOrdenCampos.MultiSelect = false;
lstvOrdenCampos.GridLines = false;
lstvOrdenCampos.Columns.Add("Campos", 100, HorizontalAlignment.Left);
cnx = new SqlConnection(cdn);
cmd = new SqlCommand();
cmd.Connection = cnx;
eh = new Exportacion.Core.ExportacionHelper();
eh.Command = cmd;
ph = new Periodos.Core.PeriodosHelper();
ph.Command = cmd;
Periodos.Core.Periodos periodo = new Periodos.Core.Periodos();
periodo.idempresa = GLOBALES.IDEMPRESA;
List<Periodos.Core.Periodos> lstPeriodos = new List<Periodos.Core.Periodos>();
List<Exportacion.Core.Exportacion> lstExportacion = new List<Exportacion.Core.Exportacion>();
try {
cnx.Open();
lstExportacion = eh.obtenerDatos(GLOBALES.IDEMPRESA, "frmListaEmpleados");
lstPeriodos = ph.obtenerPeriodos(periodo);
cnx.Close();
cnx.Dispose();
}
catch (Exception error)
{
MessageBox.Show("Error: Al obtener campos a exportar.\r\n \r\n" + error.Message,"Error");
this.Dispose();
}
for (int i = 0; i < lstExportacion.Count; i++)
{
lstvCampos.Items.Add(lstExportacion[i].campo);
if (lstExportacion[i].activo)
lstvOrdenCampos.Items.Add(lstExportacion[i].campo);
}
cmbPeriodo.DataSource = lstPeriodos;
cmbPeriodo.DisplayMember = "pago";
cmbPeriodo.ValueMember = "dias";
cmbTipoNomina.SelectedIndex = 0;
if (_tipoReporte == GLOBALES.EXPORTACATALOGO_GENERAL)
{
cmbPeriodo.Enabled = false;
cmbTipoNomina.Enabled = false;
dtpFechaInicio.Enabled = false;
}
else
{
cmbPeriodo.Enabled = true;
cmbTipoNomina.Enabled = true;
dtpFechaInicio.Enabled = true;
}
}
private void guardaEstatus(bool estatus, string campo)
{
cnx = new SqlConnection(cdn);
cmd = new SqlCommand();
cmd.Connection = cnx;
eh = new Exportacion.Core.ExportacionHelper();
eh.Command = cmd;
for (int i = 0; i < lstvOrdenCampos.Items.Count; i++)
{
Exportacion.Core.Exportacion exp = new Exportacion.Core.Exportacion();
exp.activo = estatus;
exp.campo = campo;
exp.formulario = "frmListaEmpleados";
try
{
cnx.Open();
eh.actualizaExportacion(exp);
cnx.Close();
}
catch
{
MessageBox.Show("Error: Al actualizar el campo: " + lstvOrdenCampos.Items[i].Text, "Error");
this.Dispose();
}
}
cnx.Dispose();
}
private void frmExportarEmpleado_FormClosing(object sender, FormClosingEventArgs e)
{
cnx = new SqlConnection(cdn);
cmd = new SqlCommand();
cmd.Connection = cnx;
Exportacion.Core.ExportacionHelper exph = new Exportacion.Core.ExportacionHelper();
exph.Command = cmd;
for (int i = 0; i < lstvOrdenCampos.Items.Count; i++)
{
Exportacion.Core.Exportacion exp = new Exportacion.Core.Exportacion();
exp.orden = i;
exp.campo = lstvOrdenCampos.Items[i].Text;
exp.formulario = "frmListaEmpleados";
try
{
cnx.Open();
exph.actualizaOrden(exp);
cnx.Close();
}
catch (Exception)
{
MessageBox.Show("Error: Al actualizar el orden de los campos.", "Error");
cnx.Dispose();
}
}
cnx.Dispose();
}
