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