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);
}
}
/// <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");
}
}
}
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);
}
}
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();
}
}
}
}
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;
}
/// <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;
}
/// <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;
}
/// <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);
}
}
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);
}
}
/// <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();
}
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()}");
}
}
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);
}
}
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);
}
}
}
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();
}
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()));
}
