예제 #1
0
        private void btnAccept_Click(object sender, EventArgs e)
        {
            int cantRows = 0;

            if (txtCantIndices.Text.Trim() != string.Empty)
            {
                cantRows = Convert.ToInt32(txtCantIndices.Text);
                Excel.Worksheet NewActiveWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

                if ((cantRows > 0) && (cantRows <= NewActiveWorksheet.Rows.Count))
                {
                    Excel.Range currentCell = (Excel.Range)Globals.ThisAddIn.Application.ActiveCell.Cells;
                    NewActiveWorksheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel);
                    Generales.InsertIndice(NewActiveWorksheet, cantRows, currentCell, ConFormula, NroPrincipal);
                    NewActiveWorksheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false);
                    this.Close();
                }
                else
                {
                    MessageBox.Show("Especifique por favor un dato válido.", "Agregar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
            else
            {
                MessageBox.Show("Especifique por favor la cantidad de índices a insertar.", "Agregar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
예제 #2
0
        /// <summary>Función para Insertar la Explicación.
        /// <para>Inserta la Explicación en el archivo de Excel. Referencia: <see cref="InsertaExplicacion(Excel.Worksheet, Excel.Range, string)"/> se agrega la referencia ExcelAddIn.Generales para invocarla.</para>
        /// <seealso cref="InsertaExplicacion(Excel.Worksheet, Excel.Range, string)"/>
        /// </summary>
        public static void InsertaExplicacion(Excel.Worksheet xlSht, Excel.Range currentCell, string Explicacion)
        {
            var rangej = xlSht.get_Range(string.Format("{0}:{0}", currentCell.Row + 1, Type.Missing));

            rangej.Select();

            rangej.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
            xlSht.Cells[currentCell.Row + 1, 1] = " EXPLICACION ";
            xlSht.Cells[currentCell.Row + 1, 2] = Explicacion;

            ((Excel.Range)xlSht.Cells[currentCell.Row + 1, 2]).NumberFormat = "General";
            ((Excel.Range)xlSht.Cells[currentCell.Row + 1, 2]).WrapText     = true;

            currentCell.Select();
            currentCell            = xlSht.Range[xlSht.Cells[currentCell.Row + 1, 1], xlSht.Cells[currentCell.Row + 1, 2]];
            currentCell.Font.Color = System.Drawing.Color.FromArgb(0, 0, 255);
            currentCell.Locked     = true;

            int iTotalColumns = xlSht.UsedRange.Columns.Count;
            int k             = 3;

            while (k <= iTotalColumns)
            {
                currentCell        = xlSht.Range[xlSht.Cells[currentCell.Row, k], xlSht.Cells[currentCell.Row, k]];
                currentCell.Locked = true;
                k++;
            }
            //ref
            string           NombreHoja = xlSht.Name.ToUpper().Replace(" ", "");
            List <oSubtotal> ColumnasST = Generales.DameColumnasST(NombreHoja);
            int _Registro = 1;

            Excel.Workbook wb           = Globals.ThisAddIn.Application.ActiveWorkbook;
            string         _NameFile    = wb.Name;
            int            NroPrincipal = currentCell.Row + 1;
            int            row          = DameRangoPrincipal(NroPrincipal, xlSht);

            foreach (oSubtotal ST in ColumnasST)
            {
                if (_Registro == 1)
                {
                    _Registro += 1;
                    Generales.ActualizarReferencia(_NameFile, xlSht.Name.ToUpper(), ST.Columna + row.ToString(), 0, ST.Columna, row.ToString(), 0, "A");
                }
            }
        }
예제 #3
0
        private void btnEliminaeExplicacion_Click(object sender, RibbonControlEventArgs e)
        {
            Excel.Range currentCell = (Excel.Range)Globals.ThisAddIn.Application.ActiveCell;
            int         NroRow      = currentCell.Row;

            Excel.Worksheet NewActiveWorksheet = Globals.ThisAddIn.Application.ActiveSheet;
            currentCell = (Excel.Range)NewActiveWorksheet.Cells[NroRow, 1];

            string indice = currentCell.Value2;

            if (indice.ToUpper().Trim() == "EXPLICACION")
            {
                NewActiveWorksheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel);



                currentCell.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);

                //ref
                string           NombreHoja = NewActiveWorksheet.Name.ToUpper().Replace(" ", "");
                List <oSubtotal> ColumnasST = Generales.DameColumnasST(NombreHoja);
                int            _Registro    = 1;
                Excel.Workbook wb           = Globals.ThisAddIn.Application.ActiveWorkbook;
                string         _NameFile    = wb.Name;

                int row = Generales.DameRangoPrincipal(NroRow, NewActiveWorksheet);
                foreach (oSubtotal ST in ColumnasST)
                {
                    if (_Registro == 1)
                    {
                        _Registro += 1;
                        Generales.ActualizarReferencia(_NameFile, NewActiveWorksheet.Name.ToUpper(), ST.Columna + row.ToString(), 0, ST.Columna, row.ToString(), 1, "E");
                    }
                }
                //ref
                NewActiveWorksheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false);
            }
            else
            {
                MessageBox.Show("La fila seleccionada no es una explicación ", "Eliminar Explicación", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
예제 #4
0
        private void btnAccept_Click(object sender, EventArgs e)
        {
            string Mensaje = string.Empty;

            Excel.Range     currentCell        = (Excel.Range)Globals.ThisAddIn.Application.ActiveCell.Cells;
            Excel.Worksheet NewActiveWorksheet = Globals.ThisAddIn.Application.ActiveSheet;

            if (TxtExplicacion.Text.Trim() == string.Empty)
            {
                MessageBox.Show("Especifique por favor la explicación.", "Explicación índice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            else
            {
                if (TxtExplicacion.Text.Length < 100)
                {
                    Mensaje = "La explicación especificada tiene " + lblcontador.Text + " caracteres, debe contener al menos 100. ¿Desea continuar ? ";

                    DialogResult dialogo = MessageBox.Show(Mensaje,
                                                           "Explicación índice", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (dialogo == DialogResult.Yes)
                    {
                        NewActiveWorksheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel);
                        Generales.InsertaExplicacion(NewActiveWorksheet, currentCell, TxtExplicacion.Text);
                        NewActiveWorksheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false);
                        this.Close();
                    }
                }
                else
                {
                    if (TxtExplicacion.Text.Length >= 100)
                    {
                        NewActiveWorksheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel);

                        Generales.InsertaExplicacion(NewActiveWorksheet, currentCell, TxtExplicacion.Text);

                        NewActiveWorksheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false);
                        this.Close();
                    }
                }
            }
        }
예제 #5
0
        public void Cerrar()
        {
            Excel.Workbook libro = Globals.ThisAddIn.Application.ActiveWorkbook;

            Generales.Proteccion(false);
            //Activamos los mensajes de Alerta del Excel
            Globals.ThisAddIn.Application.DisplayAlerts = false;
            //Si se ocultaron hojas las vuelve visible todas
            for (int k = 1; k <= libro.Worksheets.Count; k++)
            {
                if (Array.IndexOf(_nombre, ((Excel.Worksheet)libro.Sheets[k]).Name.ToString().Trim().ToUpper()) != -1)
                {
                    ((Excel.Worksheet)libro.Sheets[k]).Visible = XlSheetVisibility.xlSheetVisible;
                }
                else
                {
                    ((Excel.Worksheet)libro.Sheets[k]).Visible = XlSheetVisibility.xlSheetHidden;
                }
            }
            Generales.Proteccion(true);
            //Activamos los mensajes de Alerta del Excel
            Globals.ThisAddIn.Application.DisplayAlerts = true;
        }
예제 #6
0
        /// <summary>
        /// Prepara el excel para la impresion . oculta los indies sin datos de la grilla
        /// </summary>
        /// <param name="_Ocultar"></param> true muesta los datos false oculta
        /// <param name="_grilla"></param>hojas en las que se aplicara los cambios
        /// <param name="mostrar"></param>si se manda true pon visible todas las filas
        public void _PrepararImpresion(Boolean _Ocultar, DataGridView _grilla, Boolean mostrar)
        {
            if (!Verificar(_grilla))
            {
                return;
            }
            int EspacioFilas = 0;
            int fila         = 3;
            int columna      = 1;
            int ind;
            int fv = 0;
            //obtenemos el numero de hojas

            int    numhojas = Globals.ThisAddIn.Application.Sheets.Count;
            String nom;

            //cargar array de nombres
            _Cargararraynombre(_HojasSPR);
            //Contraseña
            Generales.Proteccion(false);
            Globals.ThisAddIn.Application.DisplayAlerts = false;
            int numhj = 0;

            for (int i = 1; i <= _grilla.RowCount; i++)
            {
                if (_grilla.Rows[i - 1].Cells["Imprimir"].Value.ToString().Trim().ToUpper() == "TRUE" || mostrar)
                {
                    numhj = Array.IndexOf(_nombre, _grilla.Rows[i - 1].Cells["Anexo"].Value.ToString().Trim().ToUpper());
                    Globals.ThisAddIn.Application.Sheets[_grilla.Rows[i - 1].Cells["Anexo"].Value.ToString().Trim().ToUpper()].Activate();
                    fila         = 3;
                    EspacioFilas = 0;
                    nom          = Globals.ThisAddIn.Application.Sheets[_grilla.Rows[i - 1].Cells["Anexo"].Value.ToString().Trim().ToUpper()].Name.ToString().Trim();
                    ind          = Array.IndexOf(_nombre, Globals.ThisAddIn.Application.Sheets[_grilla.Rows[i - 1].Cells["Anexo"].Value.ToString().Trim().ToUpper()].Name.ToString().Trim().ToUpper());
                    do
                    {
                        columna = 1;
                        fv      = 0;
                        for (int j = 3; j <= _ValidarInt(_HojasSPR[numhj, 2]); j++)
                        {
                            if ((_ValidarString(((Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[fila, j].Value).Trim() == "" || _ValidarInt(((Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[fila, j].Value) == 0) && _ValidarString(((Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[fila, 1].Value).Trim().Length > 0)
                            {
                                fv++;
                            }
                        }
                        if (!mostrar)
                        {
                            if (fv == _ValidarInt(_HojasSPR[numhj, 2]) - 2)
                            {
                                ((Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Rows[fila].Hidden = _Ocultar;
                            }
                            else
                            {
                                ((Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Rows[fila].Hidden = false;
                            }
                        }
                        else
                        {
                            ((Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Rows[fila].Hidden = false;
                        }


                        if (_ValidarString(((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[fila, columna].Value).Trim().Length == 0 && _ValidarString(((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[fila, columna + 1].Value).Trim().Length == 0)
                        {
                            EspacioFilas++;
                        }
                        else
                        {
                            EspacioFilas = 0;
                        }
                        fila++;

                        if (EspacioFilas == 12 && ind != -1)
                        {
                            _HojasSPR[ind, 1] = (fila - 12).ToString().Trim();
                        }
                    } while (EspacioFilas < 12);//Si existen mas de 12 espacios en blanco ya no genera mas filas
                }
            }
            Generales.Proteccion(true);
            Globals.ThisAddIn.Application.DisplayAlerts = true;
        }
예제 #7
0
        /// <summary>
        /// Prepara la vista previa
        /// </summary>
        /// <param name="_grilla" ></param>El DataGridView de donde se verifica lo que se imprime
        /// <param name="_BandW"></param>Ture si es en Blanco y Negro y False si es a color
        /// <param name="_impresora"></param>nombre de la impresora o Type.Missing si mostraremos una vista previa
        public void _Imprimir(DataGridView _grilla, Boolean _BandW, object _impresora)
        {
            if (!Verificar(_grilla))
            {
                MessageBox.Show("Debe seleccionar al menos un anexo. ", "Imprimir SIPRED", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return;
            }
            Excel.Workbook libron = Globals.ThisAddIn.Application.ActiveWorkbook;
            Excel.Workbook libro  = libron;

            Generales.Proteccion(false);
            //Desactivamos los mensajes de Alerta del Excel
            Globals.ThisAddIn.Application.DisplayAlerts = false;
            int numhj = 0;

            _Cargararraynombre(_HojasSPR);

            for (int k = 1; k <= _grilla.RowCount; k++)
            {
                numhj = Array.IndexOf(_nombre, _grilla.Rows[k - 1].Cells["Anexo"].Value.ToString().Trim().ToUpper());
                if (_grilla.Rows[k - 1].Cells["Imprimir"].Value.ToString().Trim().ToUpper() == "TRUE")
                {
                    numhj = Array.IndexOf(_nombre, _grilla.Rows[k - 1].Cells["Anexo"].Value.ToString().Trim().ToUpper());
                    //para mantener las dos primeras filas y columnas fijas en la vista previa
                    ((Excel.Worksheet)libro.Sheets[_grilla.Rows[k - 1].Cells["Anexo"].Value.ToString().Trim().ToUpper()]).PageSetup.PrintTitleRows    = "$1:$2";
                    ((Excel.Worksheet)libro.Sheets[_grilla.Rows[k - 1].Cells["Anexo"].Value.ToString().Trim().ToUpper()]).PageSetup.PrintTitleColumns = "$A:$B";
                    ((Excel.Worksheet)libro.Sheets[_grilla.Rows[k - 1].Cells["Anexo"].Value.ToString().Trim().ToUpper()]).PageSetup.Zoom          = 65;
                    ((Excel.Worksheet)libro.Sheets[_grilla.Rows[k - 1].Cells["Anexo"].Value.ToString().Trim().ToUpper()]).PageSetup.BlackAndWhite = _BandW;
                    //Cuando Es un Anexo: Orientacion horizontal
                    if (_grilla.Rows[k - 1].Cells["Anexo"].Value.ToString().Trim().ToUpper().Contains("ANEXO"))
                    {
                        ((Excel.Worksheet)libro.Sheets[_grilla.Rows[k - 1].Cells["Anexo"].Value.ToString().Trim().ToUpper()]).PageSetup.Orientation = XlPageOrientation.xlLandscape;
                    }
                    else
                    {
                        ((Excel.Worksheet)libro.Sheets[_grilla.Rows[k - 1].Cells["Anexo"].Value.ToString().Trim().ToUpper()]).PageSetup.Orientation = XlPageOrientation.xlPortrait;
                    }
                }
                else if (numhj != -1)
                {
                    ((Excel.Worksheet)libro.Sheets[_grilla.Rows[k - 1].Cells["Anexo"].Value.ToString().Trim().ToUpper()]).Visible = XlSheetVisibility.xlSheetHidden;
                }

                //Notas a ocultar
                //Si se ocultaron hojas las vuelve visible todas
                for (int x = 1; x <= libro.Worksheets.Count; x++)
                {
                    if (Array.IndexOf(_nombre, ((Excel.Worksheet)libro.Sheets[x]).Name.ToString().Trim().ToUpper()) != -1 && _HojasSPR[Array.IndexOf(_nombre, ((Excel.Worksheet)libro.Sheets[x]).Name.ToString().Trim().ToUpper()), 4].Trim().Length == 0)
                    {
                        ((Excel.Worksheet)libro.Sheets[x]).Visible = XlSheetVisibility.xlSheetHidden;
                    }
                }
            }
            //Generales.Proteccion(false);
            if (_impresora == Type.Missing)
            {
                libro.PrintOut(Type.Missing, Type.Missing, Type.Missing, true, _impresora, Type.Missing, Type.Missing, Type.Missing);
            }
            else if (_impresora.ToString() == "PDF")
            {
                string _Path = Configuration.Path + "\\SIPRED" + DateTime.Now.ToString("yyyyMMdd_HHmm") + ".pdf";
                libro.ExportAsFixedFormat(Type: XlFixedFormatType.xlTypePDF, Filename: _Path, Quality: XlFixedFormatQuality.xlQualityStandard, OpenAfterPublish: true);
            }
            else
            {
                libro.PrintOut(Type.Missing, Type.Missing, Type.Missing, false, _impresora, Type.Missing, Type.Missing, Type.Missing);
            }

            //Si se ocultaron hojas las vuelve visible todas
            for (int k = 1; k <= libro.Worksheets.Count; k++)
            {
                if (Array.IndexOf(_nombre, ((Excel.Worksheet)libro.Sheets[k]).Name.ToString().Trim().ToUpper()) != -1)
                {
                    ((Excel.Worksheet)libro.Sheets[k]).Visible = XlSheetVisibility.xlSheetVisible;
                }
                else
                {
                    ((Excel.Worksheet)libro.Sheets[k]).Visible = XlSheetVisibility.xlSheetHidden;
                }
            }
            Generales.Proteccion(true);
            //Activamos los mensajes de Alerta del Excel
            Globals.ThisAddIn.Application.DisplayAlerts = true;
        }
예제 #8
0
        /// <summary>
        /// Carga el Grid 2-Formulas y la descripción de la misma segun el click que haga el usuario en el Grid  1- Cruces
        /// </summary>
        /// <param name="Row"></param>
        private void CrucesCLick(int Row)
        {
            try
            {
                if (Row >= 0)
                {
                    int IDCruce = Convert.ToInt32(DtCruces.Rows[Row].Cells[0].Value.ToString());
                    int IdTp    = Convert.ToInt32(cmbTipo.SelectedValue);

                    Workbook  wb          = Globals.ThisAddIn.Application.ActiveWorkbook;
                    Worksheet xlSht       = null;
                    Range     currentCell = null;
                    Range     currentFind = null;
                    oCruce    _Cruce      = _Cruces.Where(o => o.IdCruce == IDCruce).FirstOrDefault();

                    _Cruce.setCeldas();
                    List <oCelda> CeldaNws = new List <oCelda>();
                    foreach (oCelda _Celda in _Cruce.CeldasFormula)
                    {
                        xlSht = (Worksheet)wb.Worksheets.get_Item(_Celda.Anexo);
                        int _maxValue = xlSht.UsedRange.SpecialCells(XlCellType.xlCellTypeLastCell).Row;

                        currentCell = (Range)xlSht.get_Range("A1", "A" + (_maxValue).ToString());
                        currentFind = currentCell.Find(_Celda.Indice, Type.Missing, XlFindLookIn.xlValues, XlLookAt.xlPart,
                                                       XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false,
                                                       Type.Missing, Type.Missing);

                        _Celda.Fila = currentFind.Row;

                        currentCell = (Range)xlSht.Cells[_Celda.Fila, 2];
                        if (currentCell.get_Value(Type.Missing) != null)
                        {
                            _Celda.Concepto = currentCell.get_Value(Type.Missing).ToString();
                        }
                        else
                        {
                            _Celda.Concepto = "";
                        }

                        CeldaNws.Add(_Celda);
                    }

                    var _FormulaI = (from x in CeldaNws
                                     select new
                    {
                        Anexo = x.Anexo,
                        Indice = x.Indice,
                        x.Concepto,
                        Col = Generales.ColumnAdress(x.Columna),
                        CodSAT = ""
                    }).ToList();

                    DtFormula.DataSource = ToDataTable(_FormulaI);
                    txtDetalle.Text      = "Cruce: " + _Cruce.Formula + System.Environment.NewLine + "Condición: " + _Cruce.Condicion;
                }
                else
                {
                    DtFormula.DataSource = null;
                    txtDetalle.Text      = "";
                }
            }
            catch
            {
                this.Hide();
                MessageBox.Show("Archivo no válido, favor de generar el archivo mediante el AddIn D.SAT", "Información Incorrecta", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
예제 #9
0
        private void btnEliminar_Click(object sender, EventArgs e)
        {
            bool   _Connection = new lSerializados().CheckConnection(Configuration.UrlConnection);
            string _Message    = "No existe conexión con el servidor de datos... Contacte a un Administrador de Red para ver las opciones de conexión.";
            string _Title      = "Conexión de Red";

            if (_Connection)
            {
                int           Row           = DtComprobaciones.CurrentCell.RowIndex;
                int           IDcompro      = Convert.ToInt32(DtComprobaciones.Rows[Row].Cells[0].Value.ToString());
                oComprobacion _Comprobacion = _Comprobaciones.Where(o => o.IdComprobacion == IDcompro).FirstOrDefault();
                DialogResult  dialogo       = MessageBox.Show("Desea eliminar fórmula número " + IDcompro.ToString() + "?",
                                                              "Confirme", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

                if (dialogo == DialogResult.Yes)
                {
                    _Message = ((cmbTipo.SelectedIndex == 0) ? "- Debe seleccionar un tipo de plantilla" : "");
                    if (_Message.Length > 0)
                    {
                        MessageBox.Show(_Message, "Información Faltante", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    if (_Comprobacion.AdmiteCambios == 0)
                    {
                        MessageBox.Show("La fórmula no puede ser eliminada ya que es un cálculo de " + cmbTipo.Text, "Eliminar", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                        return;
                    }

                    DialogResult _response = DialogResult.None;

                    oComprobacion _Template = new oComprobacion()
                    {
                        IdComprobacion  = IDcompro,
                        IdTipoPlantilla = cmbTipo.SelectedIndex,
                        Concepto        = "",
                        Formula         = "",
                        Condicion       = "",
                        Nota            = ""
                    };
                    KeyValuePair <bool, string[]> _result = new lComprobacionesAdmin(_Template, "E").Delete();
                    string _Messages = "";
                    foreach (string _Msg in _result.Value)
                    {
                        _Messages += ((_Messages.Length > 0) ? "\r\n" : "") + _Msg;
                    }
                    if (_result.Key && _response != DialogResult.Yes)
                    {
                        _Messages = "Fórmula eliminada con éxito";
                    }
                    MessageBox.Show(_Messages, (_result.Key) ? "Proceso Existoso" : "Información Faltante", MessageBoxButtons.OK, (_result.Key) ? MessageBoxIcon.Information : MessageBoxIcon.Exclamation);

                    if (_result.Key)
                    {
                        string       _Path      = Configuration.Path;
                        oPlantilla[] _Templates = Assembler.LoadJson <oPlantilla[]>($"{_Path}\\jsons\\Plantillas.json");
                        oPlantilla   _Temp      = _Templates.FirstOrDefault(o => o.IdTipoPlantilla == _Template.IdTipoPlantilla && o.Anio == (int)cmbAnio.SelectedValue);

                        //Libro Actual de Excel.
                        Excel.Worksheet xlSht;
                        Excel.Workbook  wb         = Globals.ThisAddIn.Application.ActiveWorkbook;
                        string[]        Formula    = _Comprobacion.Formula.Split('=');
                        string[]        _celdaBase = Formula[0].Replace("[", "").Replace("]", "").Split(',');
                        string[]        _celdaFin  = Formula[1].Replace("[", "").Replace("]", "").Split(',');
                        Excel.Range     _RangeO;
                        Excel.Range     _RangeR;

                        xlSht = (Excel.Worksheet)wb.Worksheets.get_Item(_celdaBase[0]);
                        for (int a = 1; a < 1000; a++)
                        {
                            _RangeO = (Excel.Range)xlSht.get_Range($"A" + a.ToString());

                            if (_RangeO != null)
                            {
                                if (_RangeO.Value.ToString() == _celdaBase[1])
                                {
                                    _RangeR              = (Excel.Range)xlSht.get_Range($"{Generales.ColumnAdress(Int32.Parse(_celdaBase[2]))}" + a.ToString());
                                    _RangeR.Formula      = "";
                                    _RangeR.Value        = "";
                                    _RangeR.NumberFormat = "@";
                                    _RangeR.Value2       = "";

                                    a = 1001;
                                }
                            }
                        }
                        FileJson(_Temp, cmbTipo.SelectedIndex.ToString());
                        this.Hide();
                    }
                }
            }
            else
            {
                MessageBox.Show(_Message, _Title, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
예제 #10
0
        /// <summary>Función para Insertar el Indice.
        /// <para>Inserta el Indice en el archivo de Excel. Referencia: <see cref="InsertIndice(Excel.Worksheet, int, Excel.Range, bool, int)"/> se agrega la referencia ExcelAddIn.Generales para invocarla.</para>
        /// <seealso cref="InsertIndice(Excel.Worksheet, int, Excel.Range, bool, int)"/>
        /// </summary>
        public static void InsertIndice(Excel.Worksheet xlSht, int CantReg, Excel.Range currentCell, bool ConFormula, int NroPrincipal)
        {
            Worksheet sheet = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);

            Excel.Workbook wb         = Globals.ThisAddIn.Application.ActiveWorkbook;
            string         _NameFile  = wb.Name;
            string         _AnexoFile = sheet.Name;
            FileInfo       _Excel     = new FileInfo(Globals.ThisAddIn.Application.ActiveWorkbook.FullName);

            Excel.Range currentFind     = null;
            Excel.Range currentFindExpl = null;

            Excel.Range RangeLocked   = null;
            int         NroRow        = currentCell.Row;
            int         NroColumn     = currentCell.Column;
            string      IndicePrevio  = "";
            long        IndiceInicial = 0;

            int  iTotalColumns = 0;
            int  k             = 1;
            int  i             = 1;
            long indiceNvo     = 0;
            int  CantExpl      = 0;

            currentCell     = (Excel.Range)xlSht.Cells[NroRow, 1];
            IndicePrevio    = currentCell.get_Value(Type.Missing).ToString();
            currentFindExpl = (Excel.Range)xlSht.Cells[NroRow + 1, 1];

            if (currentFindExpl.get_Value(Type.Missing) != null)
            {
                if (currentFindExpl.get_Value(Type.Missing).ToString().ToUpper().Trim() == "EXPLICACION")
                {
                    NroRow++;
                }
            }

            IndiceInicial = Convert.ToInt64(IndicePrevio) + 100;
            int        rowexpl = 0;
            List <int> FilasExplicacion = new List <int>();
            int        CantRango = 0; long IndiceInicialx = IndiceInicial;

            foreach (Excel.Name cname in Globals.ThisAddIn.Application.Names)
            {
                if (cname.Name == "IA_0" + Convert.ToString(IndiceInicialx))
                {
                    CantRango++;
                    IndiceInicialx  = IndiceInicialx + 100;
                    rowexpl         = cname.RefersToRange.Cells.Row + 1;
                    currentFindExpl = (Excel.Range)xlSht.Cells[rowexpl, 1];

                    if (currentFindExpl.get_Value(Type.Missing) != null)
                    {
                        if (currentFindExpl.get_Value(Type.Missing).ToString().ToUpper().Trim() == "EXPLICACION")
                        {
                            CantExpl++;
                            if (!FilasExplicacion.Contains(rowexpl + CantReg))//los indices que tienen explicacion la fila actual + los registros que ingresó nvos
                            {
                                FilasExplicacion.Add(rowexpl + CantReg);
                            }
                        }
                    }
                }
            }

            currentFind = currentCell.Find(IndiceInicial, Type.Missing, Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
                                           Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
                                           Type.Missing, Type.Missing);

            int NroRowx = 0;

            CantRango = CantRango + CantExpl;
            int NroPrincipalAux = DameRangoPrincipal(NroPrincipal, xlSht);

            while (i <= CantReg)
            {
                indiceNvo = Convert.ToInt64(IndicePrevio) + 100;
                Excel.Range rangej = xlSht.get_Range(string.Format("{0}:{0}", NroRow + i, Type.Missing));
                rangej.Select();
                rangej.Insert(Excel.XlInsertShiftDirection.xlShiftDown, Excel.XlInsertFormatOrigin.xlFormatFromLeftOrAbove);

                var rangeall   = xlSht.get_Range(string.Format("{0}:{0}", NroPrincipalAux - 1, Type.Missing));
                var rangeaCopy = xlSht.get_Range(string.Format("{0}:{0}", NroRow + i, Type.Missing));
                iTotalColumns = xlSht.UsedRange.Columns.Count;
                rangeall.Copy();
                rangeaCopy.PasteSpecial(Excel.XlPasteType.xlPasteFormulas, Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone, false, false);
                rangeaCopy.Locked = false;

                k = 1;

                while (k <= iTotalColumns)
                {
                    if (!(rangeaCopy.Cells[k].HasFormula))
                    {
                        rangeaCopy.Cells[k].Value = "";
                    }

                    k = k + 1;
                }

                xlSht.Cells[NroRow + i, 1] = "0" + Convert.ToString(indiceNvo);
                sheet.Controls.Remove("IA_0" + indiceNvo);
                AddNamedRange(NroRow + i, 1, "IA_0" + Convert.ToString(indiceNvo));
                currentCell  = (Excel.Range)xlSht.Cells[NroRow + i, 1];
                IndicePrevio = currentCell.get_Value(Type.Missing).ToString();
                //currentCell = xlSht.Range[xlSht.Cells[NroRow + i, 1], xlSht.Cells[NroRow + i, 3]];
                //currentCell.Font.Color = System.Drawing.Color.FromArgb(0, 0, 255);

                ((Excel.Range)xlSht.Cells[NroRow + 1, 2]).NumberFormat = "General"; // le doy formato text al concepto
                ((Excel.Range)xlSht.Cells[NroRow + 1, 2]).WrapText     = true;
                RangeLocked        = (Excel.Range)xlSht.Cells[NroRow + i, 1];
                RangeLocked.Locked = true; // con esto bloqueo solo la primera columna

                i++;
            }

            if (currentFind != null)
            {
                NroRowx      = NroRow + CantReg;
                currentCell  = (Excel.Range)xlSht.Cells[NroRowx, 1];
                IndicePrevio = currentCell.get_Value(Type.Missing).ToString();

                int j = 1;

                while (j <= CantRango)
                {
                    if (!FilasExplicacion.Contains(NroRowx + j))
                    {
                        indiceNvo = Convert.ToInt64(IndicePrevio) + 100;
                        xlSht.Cells[NroRowx + j, 1] = "0" + Convert.ToString(indiceNvo);
                        sheet.Controls.Remove("IA_0" + indiceNvo);
                        AddNamedRange(NroRowx + j, 1, "IA_0" + Convert.ToString(indiceNvo));
                        currentCell  = (Excel.Range)xlSht.Cells[NroRowx + j, 1];
                        IndicePrevio = currentCell.get_Value(Type.Missing).ToString();
                    }

                    j++;
                }
            }

            string           NombreHoja = xlSht.Name.ToUpper().Replace(" ", "");
            List <oSubtotal> ColumnasST = DameColumnasST((NombreHoja));

            Excel.Range Sum_Range = null;
            int         NroFinal  = NroRow + CantReg + CantRango;

            int    _Rango = 0;
            string _Renglon;
            string _Columna;
            string _rCelda = "";

            foreach (oSubtotal ST in ColumnasST)
            {
                Sum_Range         = xlSht.get_Range(ST.Columna + (NroPrincipalAux).ToString(), ST.Columna + (NroPrincipalAux).ToString());
                Sum_Range.Formula = "=sum(" + ST.Columna + (NroPrincipalAux + 1).ToString() + ":" + ST.Columna + (NroFinal).ToString() + ")";
                _Rango            = NroFinal - NroPrincipalAux; // nroprincipalaux siempre tiene el numero de la fila padre al restar te da la cantidad de rows insertadas
                _Renglon          = (Sum_Range.Row).ToString();
                _Columna          = Generales.ColumnAdress(Sum_Range.Column);
                _rCelda           = _Columna + "" + _Renglon;
                InsertarReferencia(_NameFile, _AnexoFile, _rCelda, _Rango, _Columna, _Renglon, CantReg);
            }

            Sum_Range = xlSht.get_Range("B" + (NroPrincipal).ToString(), "B" + (NroPrincipal).ToString());
            Sum_Range.Select();
        }
예제 #11
0
        public void btnAceptar_Click(object sender, EventArgs e)
        {
            Globals.ThisAddIn._result.Clear();
            Globals.ThisAddIn._CrucesSinDiferencia.Clear();
            Globals.ThisAddIn._CrucesQueNoAplican.Clear();
            string _Path = ExcelAddIn.Access.Configuration.Path;

            oValidaCruces[] _ValidaCruces = Assembler.LoadJson <oValidaCruces[]>($"{_Path}\\jsons\\ValidacionCruces.json");

            Generales.Proteccion(false);

            try
            {
                if (!ValidaCruces(_ValidaCruces))
                {
                    this.Hide();
                    return;
                }

                this.pgbCruces.Visible   = true;
                lblTitle.Text            = "Comienzo verificación, por favor espere!! ";
                this.btnAceptar.Visible  = false;
                this.btnCancelar.Visible = false;
                int progress = 0;
                progress       += 10;
                pgbCruces.Value = progress;
                oTipoPlantilla[] _TemplateTypes = Assembler.LoadJson <oTipoPlantilla[]>($"{_Path}\\jsons\\TiposPlantillas.json");
                oCruce[]         _Cruces        = Assembler.LoadJson <oCruce[]>($"{_Path}\\jsons\\Cruces.json");

                _TotalValidaciones = _Cruces.Count();

                //List<oCruce> _result = new List<oCruce>();
                FileInfo _Excel = new FileInfo(Globals.ThisAddIn.Application.ActiveWorkbook.FullName);

                progress       += 10;
                pgbCruces.Value = progress;
                //FileInfo _Excel = new FileInfo($"{_Path}\\jsons\\SIPRED-EstadosFinancierosGeneral.xlsm");
                oTipoPlantilla _TemplateType = null;

                using (ExcelPackage _package = new ExcelPackage(_Excel))
                {
                    foreach (oTipoPlantilla _TT in _TemplateTypes)
                    {
                        if (_package.Workbook.Worksheets.Where(o => o.Name == _TT.Clave).FirstOrDefault() != null)
                        {
                            _TemplateType = _TT;
                        }
                    }
                    progress       += 10;
                    pgbCruces.Value = progress;
                    if (_TemplateType != null)
                    {
                        //INTEROOP//
                        Workbook  wb = Globals.ThisAddIn.Application.ActiveWorkbook;
                        Worksheet xlSht = null;
                        Range     currentCell = null;
                        Range     currentFind = null; Range oRng; Range vlrange;
                        string[]  Formula;
                        string    indice;
                        string    DetalleFr;

                        try
                        {
                            foreach (oCruce _Cruce in _Cruces.Where(o => o.IdTipoPlantilla == _TemplateType.IdTipoPlantilla))
                            {
                                _Cruce.setCeldas();
                                List <oCelda> CeldaNws = new List <oCelda>();

                                foreach (oCelda _Celda in _Cruce.CeldasFormula)
                                {
                                    ExcelWorksheet _workSheet = _package.Workbook.Worksheets[_Celda.Anexo];
                                    lblTitle.Text = "Verificando " + _Celda.Anexo + " " + _Celda.Original.ToString();
                                    if (_workSheet != null)
                                    {
                                        xlSht = (Worksheet)wb.Worksheets.get_Item(_Celda.Anexo);
                                        int _maxValue = xlSht.UsedRange.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
                                        currentCell = (Range)xlSht.get_Range("A1", "A" + (_maxValue).ToString());
                                        currentFind = currentCell.Find(_Celda.Indice, Type.Missing, XlFindLookIn.xlValues, XlLookAt.xlPart,
                                                                       XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false,
                                                                       Type.Missing, Type.Missing);

                                        if (currentFind != null)
                                        {
                                            _Celda.Fila = currentFind.Row;
                                            _Celda.setFullAddressCeldaExcel(_workSheet.Cells[_Celda.Fila, _Celda.Columna]);
                                            _Celda.Concepto = _workSheet.Cells[_Celda.Fila, 2].Text;
                                            currentCell     = (Range)xlSht.Cells[_Celda.Fila, _Celda.Columna];

                                            if (currentCell.get_Value(Type.Missing) != null)
                                            {
                                                _Celda.Valor = currentCell.get_Value(Type.Missing).ToString();
                                            }
                                            else
                                            {
                                                _Celda.Valor = "0";
                                            }
                                        }

                                        int j = 0;
                                        if (_Cruce.Formula.Contains(":") && _Cruce.Formula.Contains("SUM"))
                                        {
                                            if (_Cruce.Formula.Contains("="))
                                            {
                                                Formula = _Cruce.Formula.Split('=');
                                                for (j = 0; j < Formula.Count(); j++)
                                                {
                                                    if (Formula[j].Contains(":"))
                                                    {
                                                        DetalleFr = Formula[j];
                                                        break;
                                                    }
                                                }
                                            }

                                            if (_Cruce.CeldasFormula[j].Anexo == _Cruce.CeldasFormula[j + 1].Anexo)
                                            {
                                                ExcelWorksheet _workSheetAnx = _package.Workbook.Worksheets[_Cruce.CeldasFormula[j].Anexo];

                                                if (CeldaNws.Count() == 0)
                                                {
                                                    CeldaNws = _Cruce.CeldasFormula.ToList();
                                                }

                                                if ((_Cruce.CeldasFormula[j].Fila != _Cruce.CeldasFormula[j + 1].Fila) && (_Cruce.CeldasFormula[j].Columna == _Cruce.CeldasFormula[j + 1].Columna))
                                                {
                                                    for (int r = _Cruce.CeldasFormula[j].Fila; r < _Cruce.CeldasFormula[j + 1].Fila; r++)
                                                    {
                                                        oRng = (Range)xlSht.Cells[r, 1];
                                                        if (oRng.get_Value(Type.Missing) != null)
                                                        {
                                                            indice = oRng.get_Value(Type.Missing).ToString();

                                                            if (_Cruce.CeldasFormula[j].Indice != indice && _Cruce.CeldasFormula[j + 1].Indice != indice)
                                                            {
                                                                oCelda CeldaNw = new oCelda();
                                                                CeldaNw.Fila     = oRng.Row;
                                                                CeldaNw.Indice   = indice;
                                                                CeldaNw.Columna  = _Celda.Columna;
                                                                CeldaNw.Anexo    = _Cruce.CeldasFormula[j].Anexo;
                                                                CeldaNw.Original = "";
                                                                CeldaNw.Grupo    = j;
                                                                CeldaNw.Concepto = _workSheetAnx.Cells[CeldaNw.Fila, 2].Text;
                                                                vlrange          = (Range)xlSht.Cells[r, CeldaNw.Columna];

                                                                if (vlrange.get_Value(Type.Missing) != null)
                                                                {
                                                                    CeldaNw.Valor = vlrange.get_Value(Type.Missing).ToString();
                                                                }
                                                                else
                                                                {
                                                                    CeldaNw.Valor = "0";
                                                                }

                                                                CeldaNw.setFullAddressCeldaExcel(_workSheetAnx.Cells[CeldaNw.Fila, CeldaNw.Columna]);
                                                                if (!CeldaNws.Contains(CeldaNw))
                                                                {
                                                                    CeldaNws.Add(CeldaNw);
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                                else
                                                {
                                                    if ((_Cruce.CeldasFormula[j].Fila == _Cruce.CeldasFormula[j + 1].Fila && _Cruce.CeldasFormula[j].Fila != -1) && (_Cruce.CeldasFormula[j].Columna != _Cruce.CeldasFormula[j + 1].Columna))
                                                    {
                                                        Worksheet xlShtAnx = (Worksheet)wb.Worksheets.get_Item(_Cruce.CeldasFormula[j].Anexo);
                                                        CeldaNws = new List <oCelda>();
                                                        if (CeldaNws.Count() == 0)
                                                        {
                                                            CeldaNws = _Cruce.CeldasFormula.ToList();
                                                        }

                                                        for (int r = _Cruce.CeldasFormula[j].Columna; r < _Cruce.CeldasFormula[j + 1].Columna; r++)
                                                        {
                                                            if (_Cruce.CeldasFormula[j].Columna != r && _Cruce.CeldasFormula[j + 1].Columna != r)
                                                            {
                                                                oCelda CeldaNw = new oCelda();
                                                                CeldaNw.Fila     = _Cruce.CeldasFormula[j].Fila;
                                                                CeldaNw.Indice   = _Cruce.CeldasFormula[j].Indice;
                                                                CeldaNw.Columna  = r;
                                                                CeldaNw.Anexo    = _Cruce.CeldasFormula[j].Anexo;
                                                                CeldaNw.Original = "";
                                                                CeldaNw.Grupo    = j;
                                                                CeldaNw.Concepto = _Cruce.CeldasFormula[j].Concepto;
                                                                vlrange          = (Range)xlShtAnx.Cells[_Cruce.CeldasFormula[j].Fila, r];

                                                                if (vlrange.get_Value(Type.Missing) != null)
                                                                {
                                                                    CeldaNw.Valor = vlrange.get_Value(Type.Missing).ToString();
                                                                }
                                                                else
                                                                {
                                                                    CeldaNw.Valor = "0";
                                                                }

                                                                CeldaNw.setFullAddressCeldaExcel(_workSheetAnx.Cells[CeldaNw.Fila, CeldaNw.Columna]);
                                                                if (!CeldaNws.Contains(CeldaNw))
                                                                {
                                                                    CeldaNws.Add(CeldaNw);
                                                                }
                                                            }
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }

                                pgbCruces.Value = progress;
                                foreach (oCeldaCondicion _Celda in _Cruce.CeldasCondicion)
                                {
                                    ExcelWorksheet _workSheet = _package.Workbook.Worksheets[_Celda.Anexo];
                                    if (_workSheet != null)
                                    {
                                        xlSht = (Worksheet)wb.Worksheets.get_Item(_Celda.Anexo);
                                        int _maxValue = xlSht.UsedRange.SpecialCells(XlCellType.xlCellTypeLastCell).Row;
                                        currentCell = (Range)xlSht.get_Range("A1", "A" + (_maxValue).ToString());


                                        currentFind = currentCell.Find(_Celda.Indice, Type.Missing, XlFindLookIn.xlValues, XlLookAt.xlPart,
                                                                       XlSearchOrder.xlByRows, XlSearchDirection.xlNext, false,
                                                                       Type.Missing, Type.Missing);

                                        if (currentFind != null)
                                        {
                                            _Celda.Fila = currentFind.Row;
                                            _Celda.setFullAddressCeldaExcel(_workSheet.Cells[_Celda.Fila, _Celda.Columna]);
                                            _Celda.Concepto = _workSheet.Cells[_Celda.Fila, 2].Text;
                                        }
                                    }
                                }
                                //catch
                                _Cruce.setFormulaExcel();

                                if (CeldaNws.Count() > 0)
                                {
                                    _Cruce.CeldasFormula = CeldaNws.OrderBy(x => x.Indice).ToArray();
                                }

                                xlSht = (Worksheet)wb.Worksheets.get_Item("SIPRED");
                                Range    Test_Range    = (Range)xlSht.get_Range("A1");
                                string   ValorAnterior = Test_Range.get_Value(Type.Missing);
                                string[] formula;
                                Test_Range.Formula      = "=" + _Cruce.FormulaExcel;
                                _Cruce.ResultadoFormula = Test_Range.get_Value(Type.Missing).ToString();
                                xlSht.Cells[1, 1]       = ValorAnterior;// restauro

                                if (_Cruce.FormulaExcel.Contains("="))
                                {
                                    formula       = _Cruce.FormulaExcel.Split('=');
                                    Test_Range    = (Range)xlSht.get_Range("A3");
                                    ValorAnterior = Test_Range.get_Value(Type.Missing);
                                    if (chksigno.Checked)
                                    {
                                        Test_Range.Formula = "=ABS(" + formula[0] + ")-ABS(" + formula[1] + ")";
                                    }
                                    else
                                    {
                                        Test_Range.Formula = "=(" + formula[0] + " - " + formula[1] + ")";
                                    }
                                    if (!Globals.ThisAddIn.Application.WorksheetFunction.IsErr(Test_Range))
                                    {
                                        _Cruce.Diferencia = Test_Range.get_Value(Type.Missing).ToString();
                                    }
                                    else
                                    {
                                        _Cruce.Diferencia = "0";
                                    }
                                    //_Cruce.Diferencia = Test_Range.get_Value(Type.Missing).ToString();
                                    xlSht.Cells[3, 1] = ValorAnterior;// restauro
                                }

                                if (_Cruce.CondicionExcel != "")
                                {
                                    Test_Range                = (Range)xlSht.get_Range("A2");
                                    ValorAnterior             = Test_Range.get_Value(Type.Missing);
                                    Test_Range.Formula        = "=" + _Cruce.CondicionExcel;
                                    _Cruce.ResultadoCondicion = Test_Range.get_Value(Type.Missing).ToString();
                                    xlSht.Cells[2, 1]         = ValorAnterior;// restauro
                                    _Cruce.Condicion          = "[" + _Cruce.Condicion + "] = " + _Cruce.ResultadoCondicion;
                                }
                                else
                                {
                                    _Cruce.ResultadoCondicion = "si";
                                }

                                if ((_Cruce.ResultadoFormula.ToLower() == "false") && ((_Cruce.ResultadoCondicion.ToLower() == "si")))
                                //if ((_Cruce.ResultadoFormula.ToLower() == "false") && ((_Cruce.ResultadoCondicion.ToLower() == "si") || (_Cruce.CondicionExcel == "")))
                                {
                                    if ((_Cruce.Diferencia == "") || (_Cruce.Diferencia == null))
                                    {
                                        _Cruce.Diferencia = "0";
                                    }

                                    if (_Cruce.Diferencia != "0") // puede ser negativa
                                    {
                                        //calculo la diferencia
                                        if (_Cruce.FormulaExcel.Contains("="))
                                        {
                                            if (_Cruce.FormulaExcel.Contains("="))
                                            {
                                                formula            = _Cruce.FormulaExcel.Split('=');
                                                Test_Range         = (Range)xlSht.get_Range("A4");
                                                ValorAnterior      = Test_Range.get_Value(Type.Missing);
                                                Test_Range.Formula = "=" + formula[0];
                                                _Cruce.Grupo1      = Test_Range.get_Value(Type.Missing).ToString();
                                                xlSht.Cells[4, 1]  = ValorAnterior;// restauro

                                                Test_Range         = (Range)xlSht.get_Range("A5");
                                                ValorAnterior      = Test_Range.get_Value(Type.Missing);
                                                Test_Range.Formula = "=" + formula[1];
                                                _Cruce.Grupo2      = Test_Range.get_Value(Type.Missing).ToString();
                                                xlSht.Cells[5, 1]  = ValorAnterior;// restauro
                                            }
                                        }
                                        Globals.ThisAddIn._result.Add(_Cruce);
                                    }
                                    else
                                    {
                                        Globals.ThisAddIn._CrucesSinDiferencia.Add(_Cruce);
                                    }
                                }
                                else
                                {
                                    Globals.ThisAddIn._CrucesQueNoAplican.Add(_Cruce);
                                }

                                if (progress <= 70)
                                {
                                    progress       += 10;
                                    pgbCruces.Value = progress;
                                }
                            }

                            Generales.Proteccion(true);
                        }
                        catch (Exception ex)
                        {
                            Generales.Proteccion(true);
                            MessageBox.Show(ex.Message, "Cruces", MessageBoxButtons.OK, MessageBoxIcon.Error);
                        }
                        progress += 5;

                        pgbCruces.Value = progress;
                    }
                    else if (_TemplateType == null)
                    {
                        MessageBox.Show("Archivo no válido, favor de generar el archivo mediante el AddIn D.SAT", "Información Incorrecta", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    }
                }

                progress       += 15;
                pgbCruces.Value = progress;
                if (Globals.ThisAddIn._result.Count > 0 || Globals.ThisAddIn._CrucesQueNoAplican.Count > 0 || Globals.ThisAddIn._CrucesSinDiferencia.Count > 0)
                {
                    Globals.ThisAddIn.TaskPane.Visible = true;
                    FIllValidacionDeCruceUC(Globals.ThisAddIn._result.ToArray());
                    //CreatePDF(Globals.ThisAddIn._result.ToArray(), _Cruces, _Path, _Excel.Name);
                }
                else
                {
                    MessageBox.Show("No se encontraron diferencias", "Información Correcta", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
                this.Hide();
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Exception Raised: {ex.Message.ToString()}");
            }
        }
예제 #12
0
        private void btnEliminarIndice_Click(object sender, RibbonControlEventArgs e)
        {
            Excel.Workbook wb           = Globals.ThisAddIn.Application.ActiveWorkbook;
            Worksheet      sheetControl = Globals.Factory.GetVstoObject(Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet);

            Excel.Worksheet sheet           = Globals.ThisAddIn.Application.ActiveSheet;
            string          IndiceActivo    = "";
            string          IndiceSiguiente = "";
            string          _NameFile       = wb.Name;
            bool            Eliminar        = false;
            List <string>   NombreRangos    = new List <string>();
            List <string>   NombreRangosDEL = new List <string>();
            List <int>      FilaPadre       = new List <int>();
            int             FilapadreAux    = 0;
            long            dif             = 0;
            string          NamedRange      = "";
            bool            tienedif        = false;

            Excel.Range objRange    = null;
            Excel.Range currentCell = (Excel.Range)Globals.ThisAddIn.Application.Selection; // filas seleccionadas

            try
            {
                foreach (Excel.Range cell in currentCell.Cells)
                {
                    try
                    {
                        foreach (Excel.Name item1 in wb.Names)
                        {
                            // comparo la direccion de la celda con la del nombre del rango
                            if (item1.Name.Substring(0, 3) == "IA_")
                            {
                                if (item1.RefersToRange.Cells.get_Address() == cell.Address)
                                {
                                    NamedRange = item1.Name;

                                    break;
                                }
                            }
                        }

                        FilapadreAux = cell.Row;

                        if (!FilaPadre.Contains(FilapadreAux))
                        {
                            FilaPadre.Add(FilapadreAux);
                        }

                        objRange     = (Excel.Range)sheet.Cells[cell.Row, 1];
                        IndiceActivo = objRange.Value2;

                        if (IndiceActivo.ToUpper().Trim() == "EXPLICACION")
                        {
                            MessageBox.Show("No es posible eliminar el índice EXPLICACION.", "Eliminar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            Eliminar = false;
                            break;
                        }
                        if ((NamedRange != "IA_" + IndiceActivo) || (NamedRange == ""))
                        {
                            MessageBox.Show("No es posible eliminar un índice de formato guía", "Eliminar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            Eliminar = false;
                            break;
                        }
                        else
                        {
                            Eliminar = true;
                            NombreRangosDEL.Add("IA_" + IndiceActivo);
                        }
                    }
                    catch (Exception ex)
                    {
                        //MessageBox.Show(ex.Message);
                    }
                }

                if (Eliminar)
                {
                    sheet.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel);
                    currentCell = (Excel.Range)Globals.ThisAddIn.Application.Selection;
                    int CantRowDelete = currentCell.Cells.Rows.Count;
                    objRange        = (Excel.Range)sheet.Cells[currentCell.Cells.Row + 1, 1];
                    IndiceSiguiente = objRange.Value2;

                    if (IndiceSiguiente != null)
                    {
                        if (IndiceSiguiente.ToUpper().Trim() == "EXPLICACION")
                        {
                            objRange.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
                            CantRowDelete += 1;
                        }
                    }

                    currentCell.EntireRow.Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
                    NombreRangosDEL.Sort();
                    string NM = NombreRangosDEL.FirstOrDefault();
                    sheetControl.Controls.Remove(NM);

                    foreach (Excel.Name item2 in wb.Names)
                    {
                        if (item2.Name.Substring(0, 3) == "IA_")
                        {
                            NombreRangos.Add(item2.Name);
                        }
                    }

                    string[] split = NM.Split('_');
                    NM = split[1];
                    // foreach (string Nm in NombreRangosDEL)
                    long   NamedRng  = Convert.ToInt64(NM) + 100;
                    string IndiceSig = "0" + Convert.ToString(NamedRng);
                    string IndiceAnt = "";

                    while (NombreRangos.Contains("IA_" + IndiceSig))
                    {
                        sheetControl.Controls.Remove("IA_" + IndiceSig);

                        NamedRng  = Convert.ToInt64(IndiceSig) + 100;
                        IndiceSig = "0" + Convert.ToString(NamedRng);
                    }

                    FilaPadre.Sort();
                    int row = FilaPadre.FirstOrDefault();

                    objRange = (Excel.Range)sheet.Cells[row, 1];

                    if (objRange.get_Value(Type.Missing) != null)
                    {
                        IndiceActivo = objRange.get_Value(Type.Missing).ToString();
                    }

                    objRange = (Excel.Range)sheet.Cells[row - 1, 1];

                    if (objRange.get_Value(Type.Missing) != null)
                    {
                        IndiceAnt = objRange.get_Value(Type.Missing).ToString();
                    }

                    //me salto la explciacion
                    if (IndiceAnt.Trim() == "EXPLICACION")
                    {
                        objRange = (Excel.Range)sheet.Cells[row - 2, 1];
                        if (objRange.get_Value(Type.Missing) != null)
                        {
                            IndiceAnt = objRange.get_Value(Type.Missing).ToString();
                        }
                    }

                    while (NombreRangos.Contains("IA_" + IndiceActivo))
                    {
                        tienedif = false;

                        dif = Convert.ToInt64(IndiceActivo) - Convert.ToInt64(IndiceAnt);
                        while (dif != 100)
                        {
                            IndiceAnt    = "0" + Convert.ToString(Convert.ToInt64(IndiceActivo) - 100);
                            IndiceActivo = IndiceAnt;

                            dif = dif - 100;

                            tienedif = true;
                        }

                        objRange        = (Excel.Range)sheet.Cells[row, 1];
                        objRange.Value2 = IndiceAnt;

                        if (tienedif)
                        {
                            Generales.AddNamedRange(row, 1, "IA_" + Convert.ToString(IndiceAnt));
                        }

                        //busco el siguiente activo
                        row++;
                        objRange = (Excel.Range)sheet.Cells[row, 1];

                        if (objRange.get_Value(Type.Missing) != null)
                        {
                            IndiceActivo = objRange.get_Value(Type.Missing).ToString();
                        }
                        else
                        {
                            break;
                        }
                    }

                    row = Generales.DameRangoPrincipal(FilaPadre.FirstOrDefault(), sheet);// busco el numero de fila OTRO para agregarle luego la sumatoria de los indices nuevos
                    Excel.Range objRangeJ = ((Excel.Range)sheet.Cells[FilaPadre[0], 1]);
                    objRangeJ.Select();

                    try
                    {                                                                     // limpio si hay error en la formula
                        Excel.Range      objRangeI  = ((Excel.Range)sheet.Cells[row, 1]); //.SpecialCells(Excel.XlCellType.xlCellTypeFormulas, Excel.XlSpecialCellsValue.xlErrors);//obten las celdas con errores
                        string           NombreHoja = sheet.Name.ToUpper().Replace(" ", "");
                        List <oSubtotal> ColumnasST = Generales.DameColumnasST(NombreHoja);
                        int _Registro = 1;

                        foreach (oSubtotal ST in ColumnasST)
                        {
                            objRangeI = sheet.get_Range(ST.Columna + row.ToString(), ST.Columna + row.ToString());
                            //objRangeI.Clear();

                            if (_Registro == 1)
                            {
                                _Registro += 1;
                                Generales.ActualizarReferencia(_NameFile, sheet.Name.ToUpper(), ST.Columna + row.ToString(), NombreRangos.Count, ST.Columna, row.ToString(), CantRowDelete, "E");
                            }
                        }
                        //wb.Save();
                    }
                    catch (Exception ex)
                    {
                    }

                    sheet.Protect(ExcelAddIn.Access.Configuration.PwsExcel, true, true, false, true, true, true, true, false, false, false, false, false, false, true, false);
                }
            }
            catch (Exception ex)
            {
                //  MessageBox.Show(ex.Message);
            }
        }
예제 #13
0
        private void btnPrellenar_Click(object sender, RibbonControlEventArgs e)
        {
            string _CnStr      = string.Format(Configuration.ConnectionStringPrellenado, Configuration.Server, Configuration.DataBase, Configuration.User, Configuration.Password);
            bool   _Connection = new lSerializados().CheckConnection(Configuration.UrlConnection);

            string _RFC  = String.Empty;
            int    _Anio = 0;

            string[] strFileame;
            int      _MaxRow = 0;

            Excel.Workbook  wb = Globals.ThisAddIn.Application.ActiveWorkbook;
            Excel.Worksheet xlSht;

            xlSht   = (Excel.Worksheet)wb.Worksheets.get_Item("Contribuyente");
            _MaxRow = xlSht.UsedRange.Count + 1;


            if (xlSht != null)
            {
                Excel.Range range     = (Excel.Range)xlSht.get_Range("A1:A" + _MaxRow.ToString());
                Excel.Range findindex = range.Find("01A000000", Type.Missing, Excel.XlFindLookIn.xlValues,
                                                   Excel.XlLookAt.xlPart, Excel.XlSearchOrder.xlByRows,
                                                   Excel.XlSearchDirection.xlNext, false, Type.Missing, Type.Missing);
                if (findindex != null)
                {
                    range = (Excel.Range)xlSht.Cells[findindex.Row, 3];
                    _RFC  = range.Value;
                }
            }

            strFileame = Globals.ThisAddIn.Application.Name.Split('-');

            DataTable dt = new DataTable();

            dt.Clear();
            dt.Columns.Add("INDICE");
            dt.Columns.Add("SALDO");
            dt.Columns.Add("CUENTAS");

            DataTable dt2 = new DataTable();

            dt2.Clear();
            dt2.Columns.Add("INDICE");
            dt2.Columns.Add("SALDO");
            dt2.Columns.Add("CUENTAS");

            SqlConnection _DbConn = new SqlConnection(_CnStr);

            try
            {
                if (_Connection)
                {
                    if (!String.IsNullOrWhiteSpace(_RFC))
                    {
                        if (strFileame[0].Equals("SIPRED"))
                        {
                            _Anio = Convert.ToInt16(strFileame[1]);
                            _RFC  = _RFC.Replace("_", "");
                        }
                        else
                        {
                            _Anio = Properties.Settings.Default.Anio;
                            _RFC  = _RFC.Replace("_", "");
                        }

                        using (_DbConn)
                        {
                            SqlCommand _SqlComm = new SqlCommand("dbo.SP_DAgrupa_ObtieneSaldoIndice", _DbConn);
                            _SqlComm.Parameters.AddWithValue("@RFC", _RFC);
                            _SqlComm.Parameters.AddWithValue("@Ejercicio", _Anio.ToString());
                            _SqlComm.Parameters.AddWithValue("@Indice", "");

                            _SqlComm.CommandType = CommandType.StoredProcedure;
                            SqlDataAdapter da = new SqlDataAdapter();

                            da.SelectCommand             = _SqlComm;
                            da.SelectCommand.CommandType = CommandType.StoredProcedure;
                            da.Fill(dt);

                            _SqlComm.Parameters.Clear();

                            _SqlComm.Parameters.AddWithValue("@RFC", _RFC);
                            _SqlComm.Parameters.AddWithValue("@Ejercicio", (_Anio - 1).ToString());
                            _SqlComm.Parameters.AddWithValue("@Indice", "");
                            SqlDataAdapter daT2 = new SqlDataAdapter();
                            daT2.SelectCommand             = _SqlComm;
                            daT2.SelectCommand.CommandType = CommandType.StoredProcedure;
                            daT2.Fill(dt2);
                        }
                    }
                    else
                    {
                        MessageBox.Show("Primero debe indicar el RFC del Cliente [Contribuyentes indice 01A000000]", "Falta Información", MessageBoxButtons.OK, MessageBoxIcon.Information);
                    }
                }
                else
                {
                    MessageBox.Show("Porfavor verifique que tiene conexión a internet.", "Sin acceso a la red", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
            catch (SqlException sqlex)
            {
                MessageBox.Show($"Error en la conexión. {sqlex.Message.ToString()}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error en la conexión. {ex.Message.ToString()}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            finally
            {
                _DbConn.Close();

                if (dt.Rows.Count > 0 || dt2.Rows.Count > 0)
                {
                    Cursor.Current = Cursors.WaitCursor;
                    for (int _wCount = 1; _wCount <= wb.Worksheets.Count; _wCount++)
                    {
                        xlSht = wb.Worksheets[_wCount];
                        Generales._Macro(false, xlSht, Configuration.PwsExcel);
                    }

                    //Generales.Proteccion(false);
                    ValidaSaldoIndice(dt, dt2);
                    //Generales.Proteccion(true);

                    for (int _wCount = 1; _wCount <= wb.Worksheets.Count; _wCount++)
                    {
                        xlSht = wb.Worksheets[_wCount];
                        Generales._Macro(true, xlSht, Configuration.PwsExcel);
                    }
                    Cursor.Current = Cursors.Default;
                }
                else
                {
                    MessageBox.Show($"No hay datos para el cliente {_RFC}, periodo {_Anio}.", "Sin Datos", MessageBoxButtons.OK, MessageBoxIcon.Information);
                }
            }
        }
예제 #14
0
        private void btnAgregarIndice_Click(object sender, RibbonControlEventArgs e)
        {
            Excel.Worksheet ActiveWorksheet = Globals.ThisAddIn.Application.ActiveSheet;
            Excel.Workbook  wb = Globals.ThisAddIn.Application.ActiveWorkbook;
            Excel.Range     objRange;
            Excel.Range     currentCell = (Excel.Range)Globals.ThisAddIn.Application.ActiveCell;
            NroFilaPrincipal = currentCell.Row;
            NroColPrincipal  = currentCell.Column;
            int    iTotalColumns; int k = 1;
            bool   puedeinsertar = false;
            string IndicePrevio;

            tieneformula = false;
            string tag = "";

            try
            {
                objRange     = (Excel.Range)ActiveWorksheet.Cells[NroFilaPrincipal, 1];
                IndicePrevio = objRange.get_Value(Type.Missing).ToString();
                if (IndicePrevio.ToUpper().Trim() != "EXPLICACION")
                {
                    foreach (Excel.Name item in wb.Names)
                    {
                        if (item.Name.Substring(0, 3) == "IA_")
                        {
                            tag = item.RefersToRange.Cells.get_Address();

                            if (tag == objRange.Address)
                            {
                                if ((NroFilaPrincipal - 1) > 0)
                                {
                                    var RangeConFr = ActiveWorksheet.get_Range(string.Format("{0}:{0}", NroFilaPrincipal, Type.Missing));
                                    iTotalColumns = ActiveWorksheet.UsedRange.Columns.Count;

                                    while (k <= iTotalColumns)
                                    {
                                        if (RangeConFr.Cells[k].HasFormula)
                                        {
                                            tieneformula = true;
                                            break;
                                        }

                                        k = k + 1;
                                    }
                                }

                                puedeinsertar = true;
                                break;
                            }
                        }
                    }
                    if (puedeinsertar)
                    {
                        Indices NewIndices = new Indices(NroFilaPrincipal, tieneformula);
                        NewIndices.ShowDialog();
                    }
                    else
                    {
                        objRange = (Excel.Range)ActiveWorksheet.Cells[NroFilaPrincipal, 2];
                        var ConceptoPrevio          = objRange.get_Value(Type.Missing);
                        List <oConcepto> ConceptVal = new List <oConcepto>();
                        ConceptVal = Generales.DameConceptosValidos();
                        bool   CncValido = false;
                        string indicex   = "";

                        if (ConceptoPrevio != null)
                        {
                            ConceptoPrevio = ConceptoPrevio.ToString();
                            CncValido      = Generales.EsConceptoValido(ConceptoPrevio);

                            if (CncValido)
                            {
                                NroFilaPrincipal = objRange.Row;
                                NroColPrincipal  = objRange.Column;
                                if ((NroFilaPrincipal - 1) > 0)
                                {
                                    var RangeConFr = ActiveWorksheet.get_Range(string.Format("{0}:{0}", NroFilaPrincipal - 1, Type.Missing));
                                    objRange = (Excel.Range)ActiveWorksheet.Cells[NroFilaPrincipal - 1, 1];
                                    if (objRange.get_Value(Type.Missing) != null)
                                    {
                                        indicex = objRange.get_Value(Type.Missing).ToString();
                                    }

                                    if (indicex != "01060025000000")
                                    {
                                        iTotalColumns = ActiveWorksheet.UsedRange.Columns.Count;

                                        while (k <= iTotalColumns)
                                        {
                                            if (RangeConFr.Cells[k].HasFormula)
                                            {
                                                tieneformula = true;
                                                break;
                                            }

                                            k = k + 1;
                                        }
                                    }
                                }

                                Indices NewIndices = new Indices(NroFilaPrincipal, tieneformula);
                                NewIndices.Show();
                            }
                            else
                            {
                                MessageBox.Show("No es posible agregar índices debajo del índice " + IndicePrevio, "Agregar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                            }
                        }
                    }
                }
                else
                {
                    MessageBox.Show("No es posible agregar índices debajo del índice EXPLICACION", "Agregar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("No es posible agregar índices en la fila seleccionada", "Agregar índice", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
        private void btguardar_Click(object sender, EventArgs e)
        {
            bool   _Connection = new lSerializados().CheckConnection(Configuration.UrlConnection);
            string _Message    = "No existe conexión con el servidor de datos... Contacte a un Administrador de Red para ver las opciones de conexión.";
            string _Title      = "Conexión de Red";

            if (_Connection)
            {
                _Message  = (txtConcepto.Text.Trim() == "") ? "- Debe indicar concepto." : "";
                _Message += (txtcelda.Text.Trim() == "") ? ((_Message.Length > 0) ? "\r\n" : "") + "- Debe indicar celda." : "";
                _Message += (txtformula.Text.Trim() == "") ? ((_Message.Length > 0) ? "\r\n" : "") + "- Debe indicar fórmula." : "";
                _Message += (chkCondicionar.Checked && txtCondicion.Text.Trim() == "") ? ((_Message.Length > 0) ? "\r\n" : "") + "- Debe indicar condición." : "";

                if (_Message.Length > 0)
                {
                    MessageBox.Show(_Message, "Información Faltante", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                DialogResult _response = DialogResult.None;
                string       Formulax  = (txtcelda.Text + "=" + txtformula.Text);
                string       condicion = "";

                if (chkCondicionar.Checked)
                {
                    condicion = txtCondicion.Text;
                }


                oComprobacion _Template = new oComprobacion()
                {
                    IdComprobacion  = Convert.ToInt32(txtNro.Text),
                    IdTipoPlantilla = TpPlantilla,//buscar
                    Concepto        = txtConcepto.Text,
                    Formula         = Formulax,
                    Condicion       = condicion,
                    Nota            = txtNota.Text,
                };

                if (accion == "A")
                {
                    KeyValuePair <bool, string[]> _result = new lComprobacionesAdmin(_Template, accion).Add();

                    string _Messages = "";
                    foreach (string _Msg in _result.Value)
                    {
                        _Messages += ((_Messages.Length > 0) ? "\r\n" : "") + _Msg;
                    }
                    if (_result.Key && _response != DialogResult.Yes)
                    {
                        _Messages = "Comprobación agregada con éxito";
                    }
                    MessageBox.Show(_Messages, (_result.Key) ? "Proceso Existoso" : "Información Faltante", MessageBoxButtons.OK, (_result.Key) ? MessageBoxIcon.Information : MessageBoxIcon.Exclamation);
                    //if (_result.Key) this.Hide();
                }
                else
                if (accion == "M")
                {
                    KeyValuePair <bool, string[]> _result = new lComprobacionesAdmin(_Template, accion).Update();

                    string _Messages = "";
                    foreach (string _Msg in _result.Value)
                    {
                        _Messages += ((_Messages.Length > 0) ? "\r\n" : "") + _Msg;
                    }
                    if (_result.Key && _response != DialogResult.Yes)
                    {
                        _Messages = "Comprobación modificada con éxito";
                    }
                    MessageBox.Show(_Messages, (_result.Key) ? "Proceso Existoso" : "Información Faltante", MessageBoxButtons.OK, (_result.Key) ? MessageBoxIcon.Information : MessageBoxIcon.Exclamation);
                    //if (_result.Key) this.Hide();
                }

                string       _Path      = Configuration.Path;
                oPlantilla[] _Templates = Assembler.LoadJson <oPlantilla[]>($"{_Path}\\jsons\\Plantillas.json");
                oPlantilla   _Temp      = _Templates.FirstOrDefault(o => o.IdTipoPlantilla == _Template.IdTipoPlantilla && o.Anio == _iYear);

                if (_oComprobacion != null)
                {
                    //Libro Actual de Excel.
                    Excel.Worksheet xlSht;
                    Excel.Workbook  wb         = Globals.ThisAddIn.Application.ActiveWorkbook;
                    string[]        Formula    = _oComprobacion.Formula.Split('=');
                    string[]        _celdaBase = Formula[0].Replace("[", "").Replace("]", "").Split(',');
                    string[]        _celdaFin  = Formula[1].Replace("[", "").Replace("]", "").Split(',');
                    Excel.Range     _RangeO;
                    Excel.Range     _RangeR;

                    xlSht = (Excel.Worksheet)wb.Worksheets.get_Item(_celdaBase[0]);
                    for (int a = 1; a < 1000; a++)
                    {
                        _RangeO = (Excel.Range)xlSht.get_Range($"A" + a.ToString());

                        if (_RangeO != null)
                        {
                            if (_RangeO.Value.ToString() == _celdaBase[1])
                            {
                                _RangeR              = (Excel.Range)xlSht.get_Range($"{Generales.ColumnAdress(Int32.Parse(_celdaBase[2]))}" + a.ToString());
                                _RangeR.Formula      = "";
                                _RangeR.Value        = "";
                                _RangeR.NumberFormat = "@";
                                _RangeR.Value2       = "";

                                a = 1001;
                            }
                        }
                    }
                }

                _Form.Close();
                FileJson(_Temp, TpPlantilla.ToString());
                _Form.Show();
            }
            else
            {
                MessageBox.Show(_Message, _Title, MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        private void CreatePDF(oCruce[] _result, oCruce[] cruces, string path, string NombreLibro)
        {
            var fecha    = DateTime.Now;
            var name     = "Cruce_" + fecha.Year.ToString() + fecha.Month.ToString() + fecha.Day.ToString() + fecha.Hour.ToString() + fecha.Minute.ToString() + fecha.Second.ToString();
            var filepath = path + "\\" + name + ".pdf";
            // Creamos el documento con el tamaño de página tradicional
            Document doc = new Document(PageSize.LETTER);
            // Indicamos donde vamos a guardar el documento
            PdfWriter writer = PdfWriter.GetInstance(doc, new FileStream(filepath, FileMode.Create));

            // Le colocamos el título y el autor
            // **Nota: Esto no será visible en el documento
            doc.AddTitle("Curces");
            doc.AddCreator("D-SAT");
            // Abrimos el archivo
            doc.Open();
            // Creamos el tipo de Font que vamos utilizar
            iTextSharp.text.Font titlefont         = new iTextSharp.text.Font(iTextSharp.text.Font.FontFamily.HELVETICA, 8, iTextSharp.text.Font.BOLD, BaseColor.BLACK);
            iTextSharp.text.Font _standardFont     = new iTextSharp.text.Font(iTextSharp.text.Font.FontFamily.HELVETICA, 7, iTextSharp.text.Font.NORMAL, BaseColor.BLACK);
            iTextSharp.text.Font _standardFontbold = new iTextSharp.text.Font(iTextSharp.text.Font.FontFamily.HELVETICA, 7, iTextSharp.text.Font.BOLD, BaseColor.BLACK);
            // Escribimos el encabezado en el documento
            //doc.Add(new Paragraph("eISSIF XML 17"));
            //doc.Add(new Paragraph("Cruces", _standardFont));
            //doc.Add(new Paragraph("SIPRED - ESTADOS FINANCIEROS GENERAL"));

            var titulo = new Paragraph("eISSIF XML 17", titlefont);

            titulo.Alignment = Element.ALIGN_CENTER;
            doc.Add(titulo);

            titulo           = new Paragraph(NombreLibro, titlefont);
            titulo.Alignment = Element.ALIGN_CENTER;
            doc.Add(titulo);

            titulo           = new Paragraph("Informe de Cruces: Diferencias", titlefont);
            titulo.Alignment = Element.ALIGN_CENTER;
            doc.Add(titulo);

            PdfPTable tblHeader = new PdfPTable(7);

            tblHeader.WidthPercentage = 100;
            PdfPCell cellNum = new PdfPCell(new Phrase("Número", titlefont));

            cellNum.BorderWidth       = 0;
            cellNum.BorderWidthTop    = 0.75f;
            cellNum.BorderWidthBottom = 0.75f;
            cellNum.BorderColorTop    = new BaseColor(Color.Blue);
            cellNum.BorderColorBottom = new BaseColor(Color.White);

            PdfPCell cellconc = new PdfPCell(new Phrase("Concepto", titlefont));

            cellconc.BorderWidth       = 0;
            cellconc.BorderWidthTop    = 0.75f;
            cellconc.BorderWidthBottom = 0.75f;
            cellconc.BorderColorTop    = new BaseColor(Color.Blue);
            cellconc.BorderColorBottom = new BaseColor(Color.White);
            cellconc.Colspan           = 6;

            tblHeader.AddCell(cellNum);
            tblHeader.AddCell(cellconc);

            PdfPCell col1 = new PdfPCell(new Phrase("", titlefont));

            col1.BorderWidth       = 0;
            col1.BorderWidthTop    = 0.75f;
            col1.BorderWidthBottom = 0.75f;
            col1.BorderColorBottom = new BaseColor(Color.Blue);
            col1.BorderColorTop    = new BaseColor(Color.White);

            PdfPCell col2 = new PdfPCell(new Phrase("Índice", titlefont));

            col2.BorderWidth       = 0;
            col2.BorderWidthTop    = 0.75f;
            col2.BorderWidthBottom = 0.75f;
            col2.BorderColorBottom = new BaseColor(Color.Blue);
            col2.BorderColorTop    = new BaseColor(Color.White);

            PdfPCell col3 = new PdfPCell(new Phrase("Col.", titlefont));

            col3.BorderWidth       = 0;
            col3.BorderWidthTop    = 0.75f;
            col3.BorderWidthBottom = 0.75f;
            col3.BorderColorBottom = new BaseColor(Color.Blue);
            col3.BorderColorTop    = new BaseColor(Color.White);

            PdfPCell col4 = new PdfPCell(new Phrase("Concepto", titlefont));

            col4.BorderWidth       = 0;
            col4.BorderWidthTop    = 0.75f;
            col4.BorderWidthBottom = 0.75f;
            col4.BorderColorBottom = new BaseColor(Color.Blue);
            col4.BorderColorTop    = new BaseColor(Color.White);
            col4.Colspan           = 2;

            PdfPCell col6 = new PdfPCell(new Phrase("Gpo. 1", titlefont));

            col6.BorderWidth       = 0;
            col6.BorderWidthTop    = 0.75f;
            col6.BorderWidthBottom = 0.75f;
            col6.BorderColorBottom = new BaseColor(Color.Blue);
            col6.BorderColorTop    = new BaseColor(Color.White);

            PdfPCell col7 = new PdfPCell(new Phrase("Gpo. 2", titlefont));

            col7.BorderWidth       = 0;
            col7.BorderWidthTop    = 0.75f;
            col7.BorderWidthBottom = 0.75f;
            col7.BorderColorBottom = new BaseColor(Color.Blue);
            col7.BorderColorTop    = new BaseColor(Color.White);

            tblHeader.AddCell(col1);
            tblHeader.AddCell(col2);
            tblHeader.AddCell(col3);
            tblHeader.AddCell(col4);
            tblHeader.AddCell(col6);
            tblHeader.AddCell(col7);
            doc.Add(Chunk.NEWLINE);

            foreach (var item in _result)
            {
                PdfPCell cellid = new PdfPCell(new Phrase(item.IdCruce.ToString(), titlefont));
                cellid.BorderWidth     = 0;
                cellid.BorderWidthTop  = 1;
                cellid.BorderColorTop  = new BaseColor(Color.White);
                cellid.BackgroundColor = new BaseColor(Color.Gray);

                var      strConcepto  = cruces.Where(c => c.IdCruce == item.IdCruce).FirstOrDefault();
                PdfPCell cellconcepto = new PdfPCell(new Phrase(strConcepto.Concepto, titlefont));
                cellconcepto.BorderWidth     = 0;
                cellconcepto.BorderWidthTop  = 1;
                cellconcepto.BorderColorTop  = new BaseColor(Color.White);
                cellconcepto.Colspan         = 6;
                cellconcepto.BackgroundColor = new BaseColor(Color.Gray);

                tblHeader.AddCell(cellid);
                tblHeader.AddCell(cellconcepto);

                PdfPCell cellformula = new PdfPCell(new Phrase(item.Formula, _standardFont));
                cellformula.BorderWidth = 0;
                cellformula.Colspan     = 7;
                tblHeader.AddCell(cellformula);

                if (item.Condicion != null || item.Condicion.Length > 0)
                {
                    PdfPCell cellcondicion = new PdfPCell(new Phrase(item.Condicion, _standardFont));
                    cellcondicion.BorderWidth = 0;
                    cellcondicion.Colspan     = 7;
                    tblHeader.AddCell(cellcondicion);
                }

                var formula1 = item.Formula.Split('=')[0];
                var formula2 = item.Formula.Split('=')[1];

                var valor = 1;
                foreach (var detail in item.CeldasFormula)
                {
                    var color = Color.LightGray;

                    if ((valor % 2) == 0)
                    {
                        color = Color.White;
                    }

                    PdfPCell cellanexo = new PdfPCell(new Phrase(detail.Anexo, _standardFont));
                    cellanexo.BorderWidth     = 0;
                    cellanexo.BackgroundColor = new BaseColor(color);
                    PdfPCell cellindice = new PdfPCell(new Phrase(detail.Indice, _standardFont));
                    cellindice.BorderWidth     = 0;
                    cellindice.BackgroundColor = new BaseColor(color);
                    PdfPCell cellcolumna = new PdfPCell(new Phrase(Generales.ColumnAdress(detail.Columna), _standardFont));
                    cellcolumna.BorderWidth     = 0;
                    cellcolumna.BackgroundColor = new BaseColor(color);
                    PdfPCell cellconceptodet = new PdfPCell(new Phrase(detail.Concepto, _standardFont));
                    cellconceptodet.BorderWidth     = 0;
                    cellconceptodet.BackgroundColor = new BaseColor(color);
                    cellconceptodet.Colspan         = 2;

                    var strgpo1 = string.Empty;
                    var strgpo2 = string.Empty;

                    if (detail.Original != "")
                    {
                        if (formula1.Contains(detail.Original))
                        {
                            strgpo1 = detail.Valor == "0" ? "" : detail.Valor;
                        }

                        if (formula2.Contains(detail.Original))
                        {
                            strgpo2 = detail.Valor == "0" ? "" : detail.Valor;
                        }
                    }
                    else
                    {
                        if (detail.Grupo == 0)
                        {
                            strgpo1 = detail.Valor == "0" ? "" : detail.Valor;
                        }
                        else
                        if (detail.Grupo == 1)
                        {
                            strgpo2 = detail.Valor == "0" ? "" : detail.Valor;
                        }
                    }
                    PdfPCell cellgpo1 = new PdfPCell(new Phrase(strgpo1, _standardFont));
                    cellgpo1.BorderWidth         = 0;
                    cellgpo1.BackgroundColor     = new BaseColor(color);
                    cellgpo1.HorizontalAlignment = Element.ALIGN_RIGHT;

                    PdfPCell cellgpo2 = new PdfPCell(new Phrase(strgpo2, _standardFont));
                    cellgpo2.BorderWidth         = 0;
                    cellgpo2.BackgroundColor     = new BaseColor(color);
                    cellgpo2.HorizontalAlignment = Element.ALIGN_RIGHT;

                    tblHeader.AddCell(cellanexo);
                    tblHeader.AddCell(cellindice);
                    tblHeader.AddCell(cellcolumna);
                    tblHeader.AddCell(cellconceptodet);
                    tblHeader.AddCell(cellgpo1);
                    tblHeader.AddCell(cellgpo2);

                    valor++;
                }

                PdfPCell cellcalc = new PdfPCell(new Phrase("Cálculos", _standardFontbold));
                cellcalc.BorderWidth         = 0;
                cellcalc.HorizontalAlignment = Element.ALIGN_RIGHT;
                cellcalc.Colspan             = 5;

                PdfPCell cellgpot1 = new PdfPCell(new Phrase(item.Grupo1, _standardFont));
                cellgpot1.BorderWidth         = 0;
                cellgpot1.HorizontalAlignment = Element.ALIGN_RIGHT;

                PdfPCell cellgpot2 = new PdfPCell(new Phrase(item.Grupo2, _standardFont));
                cellgpot2.BorderWidth         = 0;
                cellgpot2.HorizontalAlignment = Element.ALIGN_RIGHT;

                tblHeader.AddCell(cellcalc);
                tblHeader.AddCell(cellgpot1);
                tblHeader.AddCell(cellgpot2);

                PdfPCell celldifempty = new PdfPCell(new Phrase(" ", _standardFont));
                celldifempty.BorderWidth = 1;
                celldifempty.BorderColor = new BaseColor(Color.White);
                celldifempty.Colspan     = 5;

                PdfPCell celldifText = new PdfPCell(new Phrase("Diferencia", _standardFontbold));
                celldifText.BorderWidth     = 1;
                celldifText.BorderColor     = new BaseColor(Color.White);
                celldifText.BackgroundColor = new BaseColor(Color.LightGray);

                PdfPCell celldif = new PdfPCell(new Phrase(item.Diferencia, _standardFontbold));
                celldif.BorderWidth         = 1;
                celldif.BorderColor         = new BaseColor(Color.White);
                celldif.HorizontalAlignment = Element.ALIGN_RIGHT;
                celldifText.BackgroundColor = new BaseColor(Color.LightGray);

                tblHeader.AddCell(celldifempty);
                tblHeader.AddCell(celldifText);
                tblHeader.AddCell(celldif);
            }

            doc.Add(tblHeader);

            doc.Close();
            writer.Close();

            Process.Start(filepath);
        }
        private void btnGenerar_Click(object sender, EventArgs e)
        {
            //Variables generales.
            string _Path    = Configuration.Path;
            int    x        = 0;
            double r        = 0;
            int    progress = 0;

            oComprobacion[] _Comprobaciones = Assembler.LoadJson <oComprobacion[]>($"{_Path}\\jsons\\Comprobaciones.json");
            //Libro Actual de Excel.
            Excel.Worksheet xlSht;
            Excel.Workbook  wb = Globals.ThisAddIn.Application.ActiveWorkbook;
            string          _DestinationPath = "";
            string          _newTemplate     = "";

            //_Name = _aName[2].ToString();
            //_IdTipo = _Name.Split('_')[1].ToString();
            Generales.Proteccion(false);//desprotejo
            //Cuándo es para transferir, pide la ruta en donde guardar el archivo a transferir.
            if (!_formulas)
            {
                for (int y = 0; y < 1;)
                {
                    fbdTemplate.ShowDialog();
                    _DestinationPath = fbdTemplate.SelectedPath;
                    y = 1;
                    if (_DestinationPath == "")
                    {
                        MessageBox.Show("Debe especificar un ruta", "Ruta Invalida", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                        y = 0;
                    }
                }

                Cursor.Current = Cursors.WaitCursor;
                // el nombre de una Key debe incluir un root valido.
                const string userRoot  = "HKEY_CURRENT_USER";
                const string subkey    = "Software\\Microsoft\\Office\\Excel\\Addins\\SAT.Dictamenes.SIPRED.Client";
                const string keyName   = userRoot + "\\" + subkey;
                object       addInName = "SAT.Dictamenes.SIPRED.Client";

                Registry.SetValue(keyName, "LoadBehavior", 3);
                Globals.ThisAddIn.Application.COMAddIns.Item(ref addInName).Connect = true;
                string _sOpen = wb.Worksheets.Item[wb.Worksheets.Count].Name;

                _newTemplate = $"{_DestinationPath}\\Transferencia-{Globals.ThisAddIn.Application.ActiveWorkbook.Name}";

                for (int _wCount = 1; _wCount <= wb.Worksheets.Count; _wCount++)
                {
                    if (wb.Worksheets.Item[_wCount].Name == "SIPRED")
                    {
                        wb.Worksheets.Item[_wCount].Delete();
                    }
                }

                string _NameFile = wb.Name;
                wb.SaveCopyAs(_newTemplate);
                wb.Save();
                wb.Close();

                Globals.ThisAddIn.Application.Visible = true;
                Globals.ThisAddIn.Application.Workbooks.Open(_newTemplate);

                wb = Globals.ThisAddIn.Application.ActiveWorkbook;
                x  = 0;
                foreach (oComprobacion _Comprobacion in _Comprobaciones.Where(o => o.IdTipoPlantilla == Convert.ToInt32(1)).ToArray())
                {
                    _Comprobacion.setFormulaExcel();
                    xlSht = (Excel.Worksheet)wb.Worksheets.get_Item(_Comprobacion.Destino.Anexo);

                    if (_Comprobacion.EsValida() && _Comprobacion.EsFormula())
                    {
                        for (int a = 1; a < 1000; a++)
                        {
                            Excel.Range _Celda = (Excel.Range)xlSht.get_Range("A" + a.ToString());
                            if (Convert.ToString(_Celda.Value) == Convert.ToString(_Comprobacion.Destino.Indice))
                            {
                                try
                                {
                                    string _Rango    = Generales.ColumnAdress(Convert.ToInt32(_Comprobacion.Destino.Columna)) + a.ToString();
                                    Int32  _iColumna = _Comprobacion.Destino.Columna;

                                    _Celda = (Excel.Range)xlSht.get_Range(_Rango);
                                    _Celda.NumberFormat = "0";
                                    object _Value = _Celda.Value;
                                    _Celda.Formula = "";
                                    _Celda.Value   = _Value;
                                }
                                catch (Exception ex)
                                { }
                                a = 1001;
                            }
                        }
                    }
                }

                Excel.Range _Range;
                Excel.Range _RangeP;
                string      _sValor;
                Int64       _Valor;
                int         _Hijo;

                for (int _wCount = 1; _wCount <= wb.Worksheets.Count; _wCount++)
                {
                    string _sAnexo = wb.Worksheets.Item[_wCount].Name;
                    if (_sAnexo.IndexOf("NEXO") > 0)
                    {
                        xlSht = (Excel.Worksheet)wb.Worksheets.get_Item(_sAnexo);
                        for (int a = 1; a < 1000; a++)
                        {
                            _Range = (Excel.Range)xlSht.get_Range("A" + a.ToString());

                            if (Int64.TryParse(_Range.Value, out _Valor))
                            {
                                _Valor  = Convert.ToInt64(_Range.Value);
                                _sValor = Convert.ToString(_Range.Value);
                                _Hijo   = Convert.ToInt32(_sValor.Substring(_sValor.Length - 4, 4));

                                if (_Hijo == 100)
                                {
                                    for (int c = 3; c < 53; c++)
                                    {
                                        _RangeP = (Excel.Range)xlSht.get_Range($"{Generales.ColumnAdress(c)}{(a - 1).ToString()}");
                                        _RangeP.NumberFormat = "0";
                                        object _Value = _RangeP.Value;
                                        _RangeP.Formula = "";
                                        _RangeP.Value   = _Value;
                                    }
                                    a = 1001;
                                }
                            }
                        }
                    }
                }

                wb.Save();
                Cursor.Current = Cursors.Default;
            }
            //Asigna valores vacios a las celdas de las formulas y de tipo "General".
            if (_formulas)
            {
                x = 0;
                foreach (oComprobacion _Comprobacion in _Comprobaciones.Where(o => o.IdTipoPlantilla == Convert.ToInt32(1)).ToArray())
                {
                    _Comprobacion.setFormulaExcel();
                    xlSht = (Excel.Worksheet)wb.Worksheets.get_Item(_Comprobacion.Destino.Anexo);

                    string   _fExcel  = _Comprobacion.FormulaExcel.Replace("SUM", "").Replace("(", "").Replace(")", "").Replace("+0", "").Replace("*", "+").Replace("/", "+").Replace("IF", "").Replace("<0", "").Replace(">0", "+").Replace(",0)", "").Replace(",", "+").Replace("-", "+").Replace(">", "+").Replace("<", "+").Replace("=", "+");
                    string[] _sfExcel = _fExcel.Split('+');

                    for (int z = 0; z < _sfExcel.Length; z++)
                    {
                        if (_sfExcel[z] != "")
                        {
                            decimal temp = 0;
                            if (!decimal.TryParse(_sfExcel[z], out temp))
                            {
                                Excel.Range _Celda = (Excel.Range)xlSht.get_Range(_sfExcel[z]);
                                _Celda.NumberFormat = "0.00";
                                if (!_Open)
                                {
                                    _Celda.Value = "";
                                }
                            }
                        }
                    }
                    //Barra de Progreso.
                    x++;
                    r = x % 16;
                    if (r == 0.00)
                    {
                        progress += 10;
                        if (progress < 100)
                        {
                            fnProgressBar(progress);
                        }
                    }
                }
                x = 0;
                foreach (oComprobacion _Comprobacion in _Comprobaciones.Where(o => o.IdTipoPlantilla == Convert.ToInt32(1)).ToArray())
                {
                    _Comprobacion.setFormulaExcel();
                    xlSht = (Excel.Worksheet)wb.Worksheets.get_Item(_Comprobacion.Destino.Anexo);
                    xlSht.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel);
                }
                //Asigna las formulas a las celdas al crear un nuevo archivo
                //De lo contrario si es transferir quita las formulas y asigna el valor del resultado de la formula.
                //Se agina el progreso del ProgessBar según la cantidad de celdas divididas entre 16.
                foreach (oComprobacion _Comprobacion in _Comprobaciones.Where(o => o.IdTipoPlantilla == Convert.ToInt32(1)).ToArray())
                {
                    _Comprobacion.setFormulaExcel();
                    xlSht = (Excel.Worksheet)wb.Worksheets.get_Item(_Comprobacion.Destino.Anexo);
                    Excel.Range _Range = (Excel.Range)xlSht.get_Range(_Comprobacion.Destino.CeldaExcel);

                    if (x == 0)
                    {
                        xlSht.Activate();
                    }
                    _Range.NumberFormat = "0.00";
                    if (_Comprobacion.EsValida() && _Comprobacion.EsFormula())
                    {
                        _Range.Formula = $"={_Comprobacion.FormulaExcel}";
                    }
                    else if (_Comprobacion.EsValida() && !_Comprobacion.EsFormula())
                    {
                        _Range.Value = _Comprobacion.FormulaExcel;
                    }
                    //Barra de Progreso.
                    x++;
                    r = x % 16;
                    if (r == 0.00)
                    {
                        progress += 10;
                        if (progress < 100)
                        {
                            fnProgressBar(progress);
                        }
                    }
                }
                //Se guarda el archivo original.
                wb.Save();
            }
            if (_Form != null)
            {
                _Form.Close();
            }
            this.Close();
        }
예제 #18
0
        public void QuitarFormulas()
        {
            //objeto vacio
            object obj             = Type.Missing;
            int    numhojas        = 0;
            int    EspacioFilas    = 0;
            int    EspacioColumnas = 0;
            int    fila            = 1;
            int    columna         = 1;
            int    ind             = 0;
            String nom             = "";
            String pr = "";

            Microsoft.Office.Interop.Excel.Range cell1;
            Microsoft.Office.Interop.Excel.Range cell2;
            Microsoft.Office.Interop.Excel.Range range;
            double num = 0;
            String psw = "";

            //Nuevo Excel
            Excel.Application exceln = new Excel.Application();
            //libro abierto
            Excel.Workbook libro = Globals.ThisAddIn.Application.ActiveWorkbook;
            //nuevo libro
            //Excel.Workbook libron = exceln.Workbooks.Add(obj);
            Excel.Workbook libron = libro;
            //obtenemos el numero de hojas
            numhojas = libro.Sheets.Count;
            //cargar array de nombres
            Cargararraynombre(HojasSPR);
            //seleccionamos instanci hoja
            Excel.Worksheet hoja = libro.Sheets[1];
            //Creamos instancia nueva hoja
            Excel.Worksheet hojan = new Excel.Worksheet();
            int[]           hojas = new int[numhojas];
            //Contraseña
            //psw = "AAAABABABAAG";
            //LENARHOJAS
            Globals.ThisAddIn.Application.DisplayAlerts = false;

            Generales.Proteccion(false);

            for (int i = 1; i <= numhojas; i++)
            {
                //seleccionamos la hoja de orden i
                hoja = libro.Sheets[i];
                //Creamos nueva hoja de orden i
                hojan        = libron.Sheets[i];
                hojas[i - 1] = ValidarInt(Regex.Replace(hoja.Name, @"[^\d]", "").ToString().Trim());
                //seleccionamos la hoja numero i
                hoja.Activate();
                hojan.Activate();

                nom = libron.Worksheets[i].Name.ToString().Trim();
                ind = Array.IndexOf(nombre, libron.Worksheets[i].Name.ToString().Trim().ToUpper());
                //Barra de progreso
                if (this == null)
                {
                    return;
                }
                Invoke(new System.Action(() => this.label1.Text = "Trabajando Hoja : [" + (Globals.ThisAddIn.Application.ActiveSheet).Name + "] .........."));
                if (this == null)
                {
                    return;
                }
                Invoke(new System.Action(() => pgb_proceso.Value = pgb_proceso.Value + pgb_proceso.Step));

                //pasamos los datos
                EspacioFilas = 0;
                fila         = 1;
                columna      = 1;
                //hojan.Unprotect(ExcelAddIn.Access.Configuration.PwsExcel);

                do
                {
                    //Eliminar Comentarios
                    foreach (Comment a in ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Comments)
                    {
                        a.Delete();
                    }

                    columna         = 1;
                    EspacioColumnas = 0;
                    do
                    {
                        cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[fila, columna];
                        if (ValidarString(cell1.FormulaLocal).Trim().Length > 0)
                        {
                            if (ValidarString(cell1.FormulaLocal).Trim().Substring(0, 1) == "=")
                            {
                                hojan.Cells[fila, columna].Value = ValidarString(hoja.Cells[fila, columna].Value);
                            }
                        }

                        if (Double.TryParse(ValidarString(cell1.Value), out num))
                        {
                            if (ValidarString(hoja.Cells[fila, columna]).Trim().Substring(0, 1) != "0")
                            {
                                try
                                {
                                    //Evitar el error de seguridad
                                    if (!hojan.Cells[fila, columna].Locked)
                                    {
                                        hojan.Cells[fila, columna].Value = num;
                                    }
                                }
                                catch { }
                            }
                        }

                        if (ind != -1)
                        {
                            if (ValidarInt(ValidarString(HojasSPR[ind, 2]).Trim()) <= columna && ((ValidarInt(ValidarString(HojasSPR[ind, 1]).Trim()) <= fila) || (ValidarInt(ValidarString(HojasSPR[ind, 1]).Trim()) == 0)))
                            {
                                hojan.Cells[fila, columna].Value = "";
                            }
                        }

                        if (ValidarString(hoja.Cells[fila, columna].Value).Trim().Length == 0)
                        {
                            EspacioColumnas++;
                        }
                        else
                        {
                            EspacioColumnas = 0;
                        }
                        columna++;
                    } while (EspacioColumnas < 10);//Si existen mas de 10 espacios en blanco ya no genera mas columnas

                    columna = 1;
                    pr      = ValidarString(hoja.Cells[fila, columna].Value).Trim();
                    if (ValidarString(hoja.Cells[fila, columna].Value).Trim().Length == 0 && ValidarString(hoja.Cells[fila, columna + 1].Value).Trim().Length == 0)
                    {
                        EspacioFilas++;
                    }
                    else
                    {
                        EspacioFilas = 0;
                    }
                    fila++;

                    if (EspacioFilas == 12 && ind != -1)
                    {
                        HojasSPR[ind, 1] = (fila - 12).ToString().Trim();
                    }
                } while (EspacioFilas < 12);//Si existen mas de 12 espacios en blanco ya no genera mas filas
            }
            //Ordenar Hojas Excel

            //deactivar mensajes alerta que genera al eliminar
            Globals.ThisAddIn.Application.DisplayAlerts = false;
            for (int j = 0; j < 5; j++)
            {
                for (int i = 1; i <= numhojas; i++)
                {
                    try
                    {
                        nom = libron.Worksheets[i].Name.ToString().Trim();
                        ind = Array.IndexOf(nombre, libron.Worksheets[i].Name.ToString().Trim().ToUpper());
                        //Barra de progreso
                        if (this == null)
                        {
                            return;
                        }
                        Invoke(new System.Action(() => this.label1.Text = "Trabajando Hoja : [" + (Globals.ThisAddIn.Application.ActiveSheet).Name + "] .........."));

                        if (ind != -1)
                        {
                            ind++;
                            libron.Sheets[i].Activate();
                            //Color plomo de la etiqueta
                            //Protegerhoja
                            //((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Tab.Color = System.Drawing.Color.FromArgb(251, 155, 13);
                            //((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Protect(psw, true);

                            ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Tab.Color = System.Drawing.Color.FromArgb(100, 100, 100);

                            //((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Tab.ColorIndex = 0;
                            ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Move(Globals.ThisAddIn.Application.Worksheets[ind]);

                            //Ocultar columnas

                            cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[80, ValidarInt(ValidarString(HojasSPR[i - 1, 2]).Trim())];
                            cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1048576, 16384];
                            range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                            range.EntireColumn.Hidden = true;

                            cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1, 1];
                            cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[80, ValidarInt(ValidarString(HojasSPR[i - 1, 2]).Trim())];
                            range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                            ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Range[cell1, cell2].Columns.Hidden = false;

                            //Ocultar filas
                            cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[ValidarInt(ValidarString(HojasSPR[i - 1, 1]).Trim()), 1];
                            cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1048576, 16384];
                            range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                            range.EntireRow.Hidden = true;

                            cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1, 1];
                            cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[ValidarInt(ValidarString(HojasSPR[i - 1, 1]).Trim()), ValidarInt(ValidarString(HojasSPR[i - 1, 2]).Trim())];
                            range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                            ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Range[cell1, cell2].Rows.Hidden = false;
                        }
                        else
                        {
                            Excel.Worksheet m_objSheet = (Excel.Worksheet)(Globals.ThisAddIn.Application.ActiveWorkbook.Sheets.get_Item(i));
                            m_objSheet.Delete();
                            numhojas--;
                            i = i - 1;
                        }
                    }
                    catch (Exception e)
                    {
                        Generales.Proteccion(true);
                        MessageBox.Show(e.Message);
                    }
                }
            }

            //Agregar Hojas
            for (int i = 0; i < 5; i++)
            {
                int             count = libron.Worksheets.Count;
                Excel.Worksheet HN    = libron.Worksheets.Add(Type.Missing,
                                                              libron.Worksheets[count], Type.Missing, Type.Missing);
                if (i == 0)
                {
                    HN.Name = "Notas";
                    HN.Cells[1, 1].Value               = "SERVICIO DE ADMINISTRACION TRIBUTARIA";
                    HN.Cells[1, 1].Font.Size           = 12;
                    HN.Cells[3, 1].Value               = "SISTEMA DE PRESENTACION DEL DICTAMEN 2017";
                    HN.Cells[3, 1].Font.Size           = 10;
                    HN.Cells[5, 1].Value               = "NOMBRE DEL CONTRIBUYENTE:";
                    HN.Cells[5, 1].Font.Size           = 9;
                    HN.Cells[5, 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    HN.Cells[6, 1].AddComment("Es réplica de: \n Anexo: Contribuyente \n Índice: 01A001000 \n Columna: C");
                    HN.Cells[6, 1].Value                = ((Excel.Worksheet)Globals.ThisAddIn.Application.Worksheets["Contribuyente"]).Range["C4"].Value;
                    HN.Cells[8, 1].Value                = "INFORMACION DEL ANEXO : 4.1. NOTAS A LOS ESTADOS FINANCIEROS";
                    HN.Cells[8, 1].Font.Size            = 9;
                    HN.Cells[8, 1].HorizontalAlignment  = XlHAlign.xlHAlignLeft;
                    HN.Cells[46, 1].Value               = "LISTA DE NOTAS:";
                    HN.Cells[46, 1].Font.Size           = 9;
                    HN.Cells[46, 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;

                    range = HN.Range[HN.Cells[12, 1], HN.Cells[44, 1]];


                    range.Merge();

                    range.EntireColumn.ColumnWidth = 100;
                    range.Borders.LineStyle        = XlLineStyle.xlContinuous;
                    range.Borders.Weight           = XlBorderWeight.xlThin;


                    range = HN.Range[HN.Cells[1, 1], HN.Cells[1, 1]];
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range = HN.Range[HN.Cells[3, 1], HN.Cells[3, 1]];
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range           = HN.Range[HN.Cells[1, 1], HN.Cells[44, 1]];
                    range.Font.Name = "Arial";
                    range           = HN.Range[HN.Cells[1, 1], HN.Cells[3, 1]];
                    range.Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range           = HN.Range[HN.Cells[3, 1], HN.Cells[3, 1]];
                    range.Font.Bold = true;


                    //Ocultar columnas
                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[48, 2];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1048576, 16384];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    range.EntireColumn.Hidden = true;

                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1, 1];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[47, 1];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Range[cell1, cell2].Columns.Hidden = false;

                    //Ocultar filas
                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[48, 2];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1048576, 16384];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    range.EntireRow.Hidden = true;

                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1, 1];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[47, 1];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Range[cell1, cell2].Rows.Hidden = false;
                }
                if (i == 1)
                {
                    HN.Name = "Declaratoria";

                    HN.Cells[1, 1].Value = "SERVICIO DE ADMINISTRACION TRIBUTARIA";
                    HN.Cells[1, 1].Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    HN.Cells[1, 1].Font.Size           = 12;
                    HN.Cells[3, 1].Value               = "SISTEMA DE PRESENTACION DEL DICTAMEN 2017";
                    HN.Cells[3, 1].Font.Size           = 10;
                    HN.Cells[3, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    HN.Cells[5, 1].Value               = "NOMBRE DEL CONTRIBUYENTE:";
                    HN.Cells[5, 1].Font.Size           = 9;
                    HN.Cells[5, 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    HN.Cells[6, 1].AddComment("Es réplica de: \n Anexo: Contribuyente \n Índice: 01A001000 \n Columna: C");
                    HN.Cells[6, 1].Value               = ((Excel.Worksheet)Globals.ThisAddIn.Application.Worksheets["Contribuyente"]).Range["C4"].Value;
                    HN.Cells[8, 1].Value               = "INFORMACION DEL ANEXO : 9.1. DECLARATORIA";
                    HN.Cells[8, 1].Font.Size           = 9;
                    HN.Cells[8, 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;

                    range = HN.Range[HN.Cells[13, 1], HN.Cells[45, 1]];

                    range.Merge();

                    range.EntireColumn.ColumnWidth = 100;
                    range.Borders.LineStyle        = XlLineStyle.xlContinuous;
                    range.Borders.Weight           = XlBorderWeight.xlThin;



                    range = HN.Range[HN.Cells[1, 1], HN.Cells[1, 1]];
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range = HN.Range[HN.Cells[3, 1], HN.Cells[3, 1]];
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range           = HN.Range[HN.Cells[1, 1], HN.Cells[44, 1]];
                    range.Font.Name = "Arial";
                    range           = HN.Range[HN.Cells[1, 1], HN.Cells[3, 1]];
                    range.Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range           = HN.Range[HN.Cells[3, 1], HN.Cells[3, 1]];
                    range.Font.Bold = true;

                    //Ocultar columnas
                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[46, 2];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1048576, 16384];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    range.EntireColumn.Hidden = true;

                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1, 1];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[45, 1];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Range[cell1, cell2].Columns.Hidden = false;

                    //Ocultar filas
                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[46, 2];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1048576, 16384];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    range.EntireRow.Hidden = true;

                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1, 1];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[45, 1];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Range[cell1, cell2].Rows.Hidden = false;
                }
                if (i == 2)
                {
                    HN.Name = "Opinión";

                    HN.Cells[1, 1].Value = "SERVICIO DE ADMINISTRACION TRIBUTARIA";
                    HN.Cells[1, 1].Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    HN.Cells[1, 1].Font.Size           = 12;
                    HN.Cells[3, 1].Value               = "SISTEMA DE PRESENTACION DEL DICTAMEN 2017";
                    HN.Cells[3, 1].Font.Size           = 10;
                    HN.Cells[3, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    HN.Cells[5, 1].Value               = "NOMBRE DEL CONTRIBUYENTE:";
                    HN.Cells[5, 1].Font.Size           = 9;
                    HN.Cells[5, 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    HN.Cells[6, 1].AddComment("Es réplica de: \n Anexo: Contribuyente \n Índice: 01A001000 \n Columna: C");
                    HN.Cells[6, 1].Value               = ((Excel.Worksheet)Globals.ThisAddIn.Application.Worksheets["Contribuyente"]).Range["C4"].Value;
                    HN.Cells[8, 1].Value               = "INFORMACION DEL ANEXO : OPINION";
                    HN.Cells[8, 1].Font.Size           = 9;
                    HN.Cells[8, 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;

                    range = HN.Range[HN.Cells[13, 1], HN.Cells[45, 1]];

                    range.Merge();

                    range.EntireColumn.ColumnWidth = 100;
                    range.Borders.LineStyle        = XlLineStyle.xlContinuous;
                    range.Borders.Weight           = XlBorderWeight.xlThin;
                    range = HN.Range[HN.Cells[1, 1], HN.Cells[1, 1]];
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range = HN.Range[HN.Cells[3, 1], HN.Cells[3, 1]];
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range           = HN.Range[HN.Cells[1, 1], HN.Cells[44, 1]];
                    range.Font.Name = "Arial";
                    range           = HN.Range[HN.Cells[1, 1], HN.Cells[3, 1]];
                    range.Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range           = HN.Range[HN.Cells[3, 1], HN.Cells[3, 1]];
                    range.Font.Bold = true;

                    //Ocultar columnas
                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[46, 2];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1048576, 16384];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    range.EntireColumn.Hidden = true;

                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1, 1];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[45, 1];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Range[cell1, cell2].Columns.Hidden = false;

                    //Ocultar filas
                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[46, 2];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1048576, 16384];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    range.EntireRow.Hidden = true;

                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1, 1];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[45, 1];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Range[cell1, cell2].Rows.Hidden = false;
                }
                if (i == 3)
                {
                    HN.Name = "Informe";

                    HN.Cells[1, 1].Value = "SERVICIO DE ADMINISTRACION TRIBUTARIA";
                    HN.Cells[1, 1].Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    HN.Cells[1, 1].Font.Size           = 12;
                    HN.Cells[3, 1].Value               = "SISTEMA DE PRESENTACION DEL DICTAMEN 2017";
                    HN.Cells[3, 1].Font.Size           = 10;
                    HN.Cells[3, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    HN.Cells[5, 1].Value               = "NOMBRE DEL CONTRIBUYENTE:";
                    HN.Cells[5, 1].Font.Size           = 9;
                    HN.Cells[5, 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    HN.Cells[6, 1].AddComment("Es réplica de: \n Anexo: Contribuyente \n Índice: 01A001000 \n Columna: C");
                    HN.Cells[6, 1].Value               = ((Excel.Worksheet)Globals.ThisAddIn.Application.Worksheets["Contribuyente"]).Range["C4"].Value;
                    HN.Cells[8, 1].Value               = "INFORMACION DEL ANEXO : INFORME";
                    HN.Cells[8, 1].Font.Size           = 9;
                    HN.Cells[8, 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;

                    range = HN.Range[HN.Cells[13, 1], HN.Cells[45, 1]];

                    range.Merge();

                    range.EntireColumn.ColumnWidth = 100;
                    range.Borders.LineStyle        = XlLineStyle.xlContinuous;
                    range.Borders.Weight           = XlBorderWeight.xlThin;



                    range = HN.Range[HN.Cells[1, 1], HN.Cells[1, 1]];
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range = HN.Range[HN.Cells[3, 1], HN.Cells[3, 1]];
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range           = HN.Range[HN.Cells[1, 1], HN.Cells[44, 1]];
                    range.Font.Name = "Arial";
                    range           = HN.Range[HN.Cells[1, 1], HN.Cells[3, 1]];
                    range.Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range           = HN.Range[HN.Cells[3, 1], HN.Cells[3, 1]];
                    range.Font.Bold = true;

                    //Ocultar columnas
                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[46, 2];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1048576, 16384];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    range.EntireColumn.Hidden = true;

                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1, 1];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[45, 1];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Range[cell1, cell2].Columns.Hidden = false;

                    //Ocultar filas
                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[46, 2];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1048576, 16384];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    range.EntireRow.Hidden = true;

                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1, 1];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[45, 1];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Range[cell1, cell2].Rows.Hidden = false;
                }
                if (i == 4)
                {
                    HN.Name = "Información Adicional";

                    HN.Cells[1, 1].Value = "SERVICIO DE ADMINISTRACION TRIBUTARIA";
                    HN.Cells[1, 1].Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    HN.Cells[1, 1].Font.Size           = 12;
                    HN.Cells[3, 1].Value               = "SISTEMA DE PRESENTACION DEL DICTAMEN 2017";
                    HN.Cells[3, 1].Font.Size           = 10;
                    HN.Cells[3, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    HN.Cells[5, 1].Value               = "NOMBRE DEL CONTRIBUYENTE:";
                    HN.Cells[5, 1].Font.Size           = 9;
                    HN.Cells[5, 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;
                    HN.Cells[6, 1].AddComment("Es réplica de: \n Anexo: Contribuyente \n Índice: 01A001000 \n Columna: C");
                    HN.Cells[6, 1].Value               = ((Excel.Worksheet)Globals.ThisAddIn.Application.Worksheets["Contribuyente"]).Range["C4"].Value;
                    HN.Cells[8, 1].Value               = "INFORMACION DEL ANEXO : INFORMACION ADICIONAL";
                    HN.Cells[8, 1].Font.Size           = 9;
                    HN.Cells[8, 1].HorizontalAlignment = XlHAlign.xlHAlignLeft;

                    range = HN.Range[HN.Cells[13, 1], HN.Cells[45, 1]];

                    range.Merge();

                    range.EntireColumn.ColumnWidth = 100;
                    range.Borders.LineStyle        = XlLineStyle.xlContinuous;
                    range.Borders.Weight           = XlBorderWeight.xlThin;
                    range = HN.Range[HN.Cells[1, 1], HN.Cells[1, 1]];
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range = HN.Range[HN.Cells[3, 1], HN.Cells[3, 1]];
                    range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range           = HN.Range[HN.Cells[1, 1], HN.Cells[44, 1]];
                    range.Font.Name = "Arial";
                    range           = HN.Range[HN.Cells[1, 1], HN.Cells[3, 1]];
                    range.Style.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                    range           = HN.Range[HN.Cells[3, 1], HN.Cells[3, 1]];
                    range.Font.Bold = true;

                    //Ocultar columnas
                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[46, 2];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1048576, 16384];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    range.EntireColumn.Hidden = true;

                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1, 1];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[45, 1];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Range[cell1, cell2].Columns.Hidden = false;

                    //Ocultar filas
                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[46, 2];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1048576, 16384];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    range.EntireRow.Hidden = true;

                    cell1 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[1, 1];
                    cell2 = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Cells[45, 1];
                    range = ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).get_Range(cell1, cell2);

                    ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Range[cell1, cell2].Rows.Hidden = false;
                }
            }

            //for (int i = 1; i <= numhojas; i++)
            //{
            //    try
            //    {
            //        nom = libron.Worksheets[i].Name.ToString().Trim();
            //        ind = Array.IndexOf(nombre, libron.Worksheets[i].Name.ToString().Trim().ToUpper());
            //        if (ind != -1)
            //        {
            //            ind++;
            //            libron.Sheets[i].Activate();
            //            //((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Protect(ExcelAddIn.Access.Configuration.PwsExcel, true);

            //        }
            //    }
            //    catch (Exception e)
            //    {
            //        Generales.Proteccion(true);
            //        MessageBox.Show(e.Message);
            //    }
            //}
            Generales.Proteccion(true);


            if (this == null)
            {
                return;
            }
            Invoke(new System.Action(() => this.pgb_proceso.Value = this.pgb_proceso.Maximum));
            Invoke(new System.Action(() => this.label1.Text       = "Proceso Terminado."));

            GuardarExcel();

            //activar mensajes alerta
            Globals.ThisAddIn.Application.DisplayAlerts = true;
            Invoke(new System.Action(() => this.Visible = false));
            //this.Visible = false;
            Invoke(new System.Action(() => this.Close()));
        }