protected void _btnExportarExcel_Click(object sender, EventArgs e)
{
List <List <string> > contenido = new List <List <string> >();
List <string> fila = new List <string>();
string[] badChars = { "á", "é", "í", "ó", "ú", "ñ" };
string[] goodChars = { "á", "é", "í", "ó", "ú", "ñ" };
foreach (GridViewRow r in _gvwEventos.Rows)
{
for (int i = 0; i < r.Cells.Count; i++)
{
string text = r.Cells[i].Text;
for (int k = 0; k < badChars.Length; k++) // Limpiar caracteres
{
text = text.Replace(badChars[k], goodChars[k]); // Quitar tildes u caracteres especiales del nombre
}
fila.Add(text);
}
contenido.Add(fila);
fila = new List <string>();
}
ReporteExcel report = new ReporteExcel();
SpreadsheetGear.IWorkbook workbook = report.generarReporte("Bitacora " + _txtFechaConsulta.Text.Replace('/', '-'), new List <string> {
"Fecha", "Operador", "Evento"
}, contenido);
Thread STAThread = new Thread(() =>
{
// Stream the Excel spreadsheet to the client in a format
// compatible with Excel 97/2000/XP/2003/2007/2010.
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=" + "Bitacora " + _txtFechaConsulta.Text + ".xls");
workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.Excel8);
Response.End();
});
STAThread.SetApartmentState(ApartmentState.STA);
STAThread.Start();
STAThread.Join();
}
public IActionResult DownloadReport()
{
// Create a new workbook.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
SpreadsheetGear.IRange cells = worksheet.Cells;
// Set the worksheet name.
worksheet.Name = "2005 Sales";
// Load column titles and center.
cells["B1"].Formula = "North";
cells["C1"].Formula = "South";
cells["D1"].Formula = "East";
cells["E1"].Formula = "West";
cells["B1:E1"].HorizontalAlignment = SpreadsheetGear.HAlign.Center;
// Load row titles using multiple cell text reference and iteration.
int quarter = 1;
foreach (SpreadsheetGear.IRange cell in cells["A2:A5"])
{
cell.Formula = "Q" + quarter++;
}
// Load random data and format as $ using a multiple cell range.
SpreadsheetGear.IRange body = cells[1, 1, 4, 4];
body.Formula = "=RAND() * 10000";
body.NumberFormat = "$#,##0_);($#,##0)";
// Save workbook to an Open XML (XLSX) workbook stream.
System.IO.Stream stream = workbook.SaveToStream(
SpreadsheetGear.FileFormat.OpenXMLWorkbook);
// Reset stream's current position back to the beginning.
stream.Seek(0, System.IO.SeekOrigin.Begin);
// Stream the Excel spreadsheet to the client in a format
// compatible with Excel 97/2000/XP/2003/2007/2010/2013/2016.
return(new FileStreamResult(stream,
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
/// <summary>
/// Método que se encarga de cargar una imagen en memoria cargada en el componente Input para carga de archivos
/// </summary>
/// <returns>Retorna uan arreglo de bytes con la imagen</returns>
protected void _btnConsultar_Click(object sender, EventArgs e)
{
Usuario user = null;
int contador = 0;
_controladorSGC = new ControladorSGC();
_imgMensajeBusqueda.Visible = false;
_lblMensajeBusqueda.Visible = false;
_sesion = new Sesion();
_cookieActual = _sesion.verificarValidez(Request.Cookies["PS"]);
if (_cookieActual == null) // Si la cookie expira redirecciona a la pantalla de Login
{
Response.Redirect("../Autentificacion/Login.aspx");
}
else // Volver a crear la cookie en el cliente, con el nuevo tiempo de expiración
{
Response.SetCookie(_cookieActual);
}
// Verificar que se haya selecionado un criterio de búsqueda
if (_ddlCriterio.SelectedIndex == 0)
{ //
_imgMensajeBusqueda.ImageUrl = "../Imagenes/Advertencia.png";
_lblMensajeBusqueda.Text = "Debe seleccionar un criterio de búsqueda";
_imgMensajeBusqueda.Visible = true;
_lblMensajeBusqueda.Visible = true;
return;
}
if (_ddlCriterio.SelectedIndex == 3) //si el criterio seleccionado es por rango de fechas
{
// Verificar que los campos de la busqueda estén llenos
if ((_txtValor2.Text.Equals("")) || (_txtValor3.Text.Equals("")))
{
_imgMensajeBusqueda.ImageUrl = "../Imagenes/Advertencia.png";
_lblMensajeBusqueda.Text = "Debe ingresar fechas para la busqueda";
_imgMensajeBusqueda.Visible = true;
_lblMensajeBusqueda.Visible = true;
return;
}
else
{
DateTime fechaInicial = DateTime.ParseExact(_txtValor2.Text, "dd/MM/yyyy", null);
DateTime fechaFinal = DateTime.ParseExact(_txtValor3.Text, "dd/MM/yyyy", null);
try
{
List <List <string> > contenido = _controladorSGC.crearArchivoUsuariosPorFecha(fechaInicial, fechaFinal);
string nombreArchivo = "Cuentas entre " + fechaInicial.Date.Day.ToString() + "-" + fechaInicial.Month.ToString() + "-" + fechaInicial.Year.ToString() + " y " + fechaFinal.Day.ToString() + "-" + fechaFinal.Month.ToString() + "-" + fechaFinal.Year.ToString();
ReporteExcel report = new ReporteExcel();
SpreadsheetGear.IWorkbook workbook = report.generarReporte(nombreArchivo, new List <string> {
"Login", "Carrera", "Fecha de Creación"
}, contenido);
Thread STAThread = new Thread(() =>
{
// Stream the Excel spreadsheet to the client in a format
// compatible with Excel 97/2000/XP/2003/2007/2010.
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=" + nombreArchivo + ".xls");
workbook.SaveToStream(Response.OutputStream, SpreadsheetGear.FileFormat.Excel8);
Response.End();
});
STAThread.SetApartmentState(ApartmentState.STA);
STAThread.Start();
STAThread.Join();
}
catch (Exception ex)
{
_imgMensajeBusqueda.ImageUrl = "../Imagenes/Advertencia.png";
_lblMensajeBusqueda.Text = ex.Message;
_imgMensajeBusqueda.Visible = true;
_lblMensajeBusqueda.Visible = true;
}
}
}
else
{
// Verificar que los campos de la busqueda estén llenos
if (_txtValor.Text.Equals(""))
{
_imgMensajeBusqueda.ImageUrl = "../Imagenes/Advertencia.png";
_lblMensajeBusqueda.Text = "Debe ingresar algún valor para la busqueda";
_imgMensajeBusqueda.Visible = true;
_lblMensajeBusqueda.Visible = true;
return;
}
// El criterio seleccionado es "Login"
if (_ddlCriterio.SelectedIndex == 1)
{
user = _controladorSGC.buscarUsuario(_txtValor.Text, true); // Login
}
if (_ddlCriterio.SelectedIndex == 2)
{
user = _controladorSGC.buscarUsuario(_txtValor.Text, false); //Carnet
_lblUsuario.Visible = true;
_txtUsuario.Visible = true;
}
// Se actualiza el contenido de la interfaz
if (user != null)
{
_txtCarnet.Text = user.Carnet;
_txtNombre.Text = user.Nombre;
_txtPApellido.Text = user.Apellidos.Split(' ')[0];
_txtSApellido.Text = user.Apellidos.Split(' ')[1];
_txtTelefono.Text = user.TelefonoCasa;
_txtCelular.Text = user.TelefonoCelular;
_txtCorreo.Text = user.Correo;
_txtUsuario.Text = user.UID;
foreach (ListItem carrera in _ddlCarrera.Items)
{
if (carrera.Text == user.Carrera)
{
_ddlCarrera.SelectedIndex = contador;
}
else
{
contador++;
}
}
if (user.Grupo == "Estudiante")
{
_rblUsarios.SelectedIndex = 0;
}
else
{
_rblUsarios.SelectedIndex = 1;
}
_imgMensajeBusqueda.Visible = false;
_lblMensajeBusqueda.Visible = false;
_bntModificar.Enabled = true;
_lblMensaje.Visible = false;
_imgMensaje.Visible = false;
_upConsultaUsuario.Update();
}
else
{
_imgMensajeBusqueda.ImageUrl = "../Imagenes/Error.png";
_lblMensajeBusqueda.Text = "Usuario no encontrado";
_imgMensajeBusqueda.Visible = true;
_lblMensajeBusqueda.Visible = true;
_txtCarnet.Text = "";
_txtNombre.Text = "";
_txtPApellido.Text = "";
_txtSApellido.Text = "";
_txtTelefono.Text = "";
_txtCelular.Text = "";
_txtCorreo.Text = "";
_txtPassword.Text = "";
_txtCPassword.Text = "";
_ddlCarrera.SelectedIndex = 0;
//if(_rblUsarios.SelectedItem.Selected)
// _rblUsarios.SelectedItem.Selected = false;
_bntModificar.Enabled = false;
}
}
}
public void Generate()
{
string ruc = string.Empty;
string name = string.Empty;
string email = string.Empty;
int beforeMonth = Month;
int currentYear = Year;
int valueInt = 0;
decimal valueDecimal = 0;
int counterWorked = 0;
SpreadsheetGear.IRange range = null;
SpreadsheetGear.IWorksheet wsSource = null;
SpreadsheetGear.IWorksheet wsTarget = null;
//Retrieving Template and Source
SpreadsheetGear.IWorkbook wbSource = SpreadsheetGear.Factory.GetWorkbook($@"{FileSource}");
List <string> codesSelected = new List <string>();
wsSource = wbSource.Worksheets[1];
if (Method == MethodReport.Random)
{
for (int i = 1; i <= RandomNumber; i++)
{
codesSelected.Add(wsSource.Cells[i, 0].Value.ToString());
}
//while (codesSelected.Count < RandomNumber)
//{
// int randonRowIndex = new Random().Next(1, 617);
// if (!codesSelected.Contains(wsSource.Cells[randonRowIndex, 0].Value.ToString()))
//}
}
else
{
codesSelected.Add(CodeStore);
}
wsSource = wbSource.Worksheets[0];
wsSource.Cells["G3"].Formula = wsSource.Cells["D3"].Formula.Replace("4", "3");
wsSource.Cells["H3"].Formula = wsSource.Cells["D3"].Formula.Replace("4", "36");
SpreadsheetGear.Drawing.Color basicColor = SpreadsheetGear.Drawing.Color.FromArgb(89, 89, 89);
SpreadsheetGear.Drawing.Color blueColor = SpreadsheetGear.Drawing.Color.FromArgb(0, 112, 192);
SpreadsheetGear.Drawing.Color orangeColor = SpreadsheetGear.Drawing.Color.FromArgb(255, 153, 51);
this.TotalWork = codesSelected.Count;
this.ProgressFinished = counterWorked;
foreach (string code in codesSelected)
{
SpreadsheetGear.IWorkbook wbTarget = SpreadsheetGear.Factory.GetWorkbook($@"{System.AppDomain.CurrentDomain.BaseDirectory}\Resources\{FileTemplate}");
wbSource.WorkbookSet.Calculation = SpreadsheetGear.Calculation.Manual;
wsSource = wbSource.Worksheets[0];
//Update Data from Excel
range = wsSource.Cells["C3"];
range.Value = code;
wbSource.WorkbookSet.Calculate();
wbSource.Save();
ruc = wsSource.Cells["G3"].Value?.ToString();
name = wsSource.Cells["D3"].Value?.ToString().Replace(".", string.Empty);
email = wsSource.Cells["H3"].Value?.ToString();
NameActual = name;
wsTarget = wbTarget.Worksheets[0];
wsTarget.Shapes["MAIN_WARNING1"].TextFrame.Characters.Font.Color = basicColor;
wsTarget.Shapes["MAIN_WARNING2"].TextFrame.Characters.Font.Color = basicColor;
// C12, C13, C14
beforeMonth = Month;
currentYear = Year;
valueInt = 0;
valueDecimal = 0;
for (int i = 14; i > 1; i--, beforeMonth--) // begin at Pos 14
{
if (beforeMonth == 0)
{
currentYear -= 1;
beforeMonth = 12;
}
range = wsSource.Cells[11, i]; // Row 11
range.Value = $"{beforeMonth}/{currentYear}";
}
#region Setting Info
wsTarget.Cells["AT7"].Value = FormatMonthYear(Month, Year);
wsTarget.Cells["J9"].Value = wsSource.Cells["D3"].Value.ToString();
wsTarget.Cells["J10"].Value = wsSource.Cells["E3"].Value.ToString() + ", " + wsSource.Cells["F3"].Value.ToString();
wsTarget.Cells["J11"].Value = $"Comercio: {code}";
#endregion
#region MainData
// Main
int index = 2;
valueDecimal = 0;
Decimal.TryParse(wsSource.Cells[5, 3].Value.ToString(), out valueDecimal);
wsTarget.Cells["P24"].Value = (valueDecimal).ToString("N0");
Decimal.TryParse(wsSource.Cells[8, 3].Value.ToString(), out valueDecimal);
wsTarget.Cells["P28"].Value = (valueDecimal).ToString("N0");
valueDecimal = 0;
Decimal.TryParse(wsSource.Cells[5, 4].Value.ToString(), out valueDecimal);
wsTarget.Cells["Y24"].Value = (valueDecimal).ToString("N0");
Decimal.TryParse(wsSource.Cells[8, 4].Value.ToString(), out valueDecimal);
wsTarget.Cells["Y28"].Value = (valueDecimal).ToString("N0");
valueDecimal = 0;
Decimal.TryParse(wsSource.Cells[5, 5].Value.ToString(), out valueDecimal);
wsTarget.Cells["AH24"].Value = (valueDecimal).ToString("N0");
Decimal.TryParse(wsSource.Cells[8, 5].Value.ToString(), out valueDecimal);
wsTarget.Cells["AH28"].Value = (valueDecimal).ToString("N0");
valueDecimal = 0;
Decimal.TryParse(wsSource.Cells[5, 6].Value.ToString(), out valueDecimal);
wsTarget.Cells["AQ24"].Value = (valueDecimal).ToString("N0");
Decimal.TryParse(wsSource.Cells[8, 6].Value.ToString(), out valueDecimal);
wsTarget.Cells["AQ28"].Value = (valueDecimal).ToString("N0");
wsTarget = wbTarget.Worksheets[2];
wbSource.WorkbookSet.Calculate();
wbSource.Save();
#endregion
#region Graphic 2
wsTarget = wbTarget.Worksheets[0];
wsTarget = wbTarget.Worksheets[2];
decimal lastYearmonth = 0;
decimal actualMonth = 0;
decimal sum3PreviousMonths = 0;
DateTime?dateValue = null;
for (var i = 2; i < 15; i++)
{
dateValue = ParseDateXlsToDateTime(int.Parse(wsSource.Cells[11, i].Value.ToString()));
wsTarget.Cells[4, i].Value = FormatMonthYear(dateValue.Value.Month, dateValue.Value.Year, true); // headerDates
valueDecimal = 0;
range = wsSource.Cells[12, i];
decimal.TryParse(range.Value.ToString(), out valueDecimal);
wsTarget.Cells[5, i].Value = valueDecimal;
if (i == 2)
{
lastYearmonth = valueDecimal;
}
else if (i == 14)
{
actualMonth = valueDecimal;
}
else if (i >= 11 && i < 14)
{
sum3PreviousMonths += valueDecimal; // sum of 3 previous months
}
valueDecimal = 0;
range = wsSource.Cells[13, i];
decimal.TryParse(range.Value.ToString(), out valueDecimal);
wsTarget.Cells[6, i].Value = valueDecimal;
}
wsTarget = wbTarget.Worksheets[0];
var advices2 = EvalueAdviceG2(wsTarget.Cells["AT7"].Value.ToString()
, sum3PreviousMonths, actualMonth, lastYearmonth, wsTarget.Cells["E48"].Value.ToString());
wsTarget.Cells["E48"].Value = advices2.Item1;
wsTarget.Cells["E51"].Value = advices2.Item2;
#endregion
#region Graphic 3
wsTarget = wbTarget.Worksheets[2];
lastYearmonth = 0;
actualMonth = 0;
sum3PreviousMonths = 0;
for (var i = 2; i < 15; i++)
{
dateValue = ParseDateXlsToDateTime(int.Parse(wsSource.Cells[11, i].Value.ToString()));
wsTarget.Cells[9, i].Value = FormatMonthYear(dateValue.Value.Month, dateValue.Value.Year, true); // headerDates
valueDecimal = 0;
range = wsSource.Cells[14, i];
decimal.TryParse(range.Value.ToString(), out valueDecimal);
wsTarget.Cells[10, i].Value = valueDecimal;
if (i == 2)
{
lastYearmonth = valueDecimal;
}
else if (i == 14)
{
actualMonth = valueDecimal;
}
else if (i >= 11 && i < 14)
{
sum3PreviousMonths += valueDecimal; // sum of 3 previous months
}
valueDecimal = 0;
range = wsSource.Cells[15, i];
decimal.TryParse(range.Value.ToString(), out valueDecimal);
wsTarget.Cells[11, i].Value = valueDecimal;
}
wsTarget = wbTarget.Worksheets[0];
var advices3 = EvalueAdviceG3(wsTarget.Cells["AT7"].Value.ToString()
, sum3PreviousMonths, actualMonth, lastYearmonth, wsTarget.Cells["AD48"].Value.ToString());
wsTarget.Cells["AD48"].Value = advices3.Item1;
wsTarget.Cells["AD51"].Value = advices3.Item2;
#endregion
#region Graphic 4
index = 1;
for (var i = 19; i < 24; i++, index++)
{
range = wsSource.Cells[19, 2];
int.TryParse(range.Value.ToString(), out valueInt);
wsTarget.Cells["E59"].Value = valueInt.ToString();
range = wsSource.Cells[19, 3];
decimal.TryParse(range.Value.ToString(), out valueDecimal);
wsTarget.Cells["H59"].Value = $"({(int)(valueDecimal * 100)}%)";
range = wsSource.Cells[20, 2];
int.TryParse(range.Value.ToString(), out valueInt);
wsTarget.Cells["E61"].Value = valueInt.ToString();
range = wsSource.Cells[20, 3];
decimal.TryParse(range.Value.ToString(), out valueDecimal);
wsTarget.Cells["H61"].Value = $"({(int)(valueDecimal * 100)}%)";
range = wsSource.Cells[21, 2];
int.TryParse(range.Value.ToString(), out valueInt);
wsTarget.Cells["E63"].Value = valueInt.ToString();
range = wsSource.Cells[21, 3];
decimal.TryParse(range.Value.ToString(), out valueDecimal);
wsTarget.Cells["H63"].Value = $"({(int)(valueDecimal * 100)}%)";
range = wsSource.Cells[22, 2];
int.TryParse(range.Value.ToString(), out valueInt);
wsTarget.Cells["E65"].Value = valueInt.ToString();
range = wsSource.Cells[22, 3];
decimal.TryParse(range.Value.ToString(), out valueDecimal);
wsTarget.Cells["H65"].Value = $"({(int)(valueDecimal * 100)}%)";
range = wsSource.Cells[23, 2];
int.TryParse(range.Value.ToString(), out valueInt);
wsTarget.Cells["E67"].Value = valueInt.ToString();
range = wsSource.Cells[23, 3];
decimal.TryParse(range.Value.ToString(), out valueDecimal);
wsTarget.Cells["H67"].Value = $"({(int)(valueDecimal * 100)}%)";
}
wsTarget = wbTarget.Worksheets[0];
#endregion
#region Graphic 5
wsTarget = wbTarget.Worksheets[2];
wbSource.WorkbookSet.Calculate();
List <(int, int)> mayor_days = new List <(int, int)>();
index = 7; // Begins on Sunday
int lessValueIndex = 0;
for (var i = 2; i < 9; i++, index--)
{
lessValueIndex = -1;
valueInt = 0;
int.TryParse(wsSource.Cells[33, i].Value.ToString(), out valueInt);
wsTarget.Cells[15, i].Value = valueInt;
if (valueInt > 0)
{
if (mayor_days.Count == 0)
{
mayor_days.Add((index, valueInt));
}
else
{
if (mayor_days.Count < 3)
{
mayor_days.Add(ValueTuple.Create(index, valueInt));
}
else
{
for (var pos = 0; pos < mayor_days.Count; pos++)
{
if (valueInt > mayor_days[pos].Item2)
{
lessValueIndex = pos;
}
}
if (lessValueIndex != -1)
{
mayor_days[lessValueIndex] = (index, valueInt);
}
}
}
}
}
wsTarget = wbTarget.Worksheets[0];
var advices5 = EvalueAdviceG5(mayor_days, wsTarget.Cells["AD70"].Value.ToString());
wsTarget.Cells["AD70"].Value = advices5.Item1;
#endregion
string nameTarget = $"{name}~{ruc}~{email}.xlsx";
using (MemoryStream file = new MemoryStream())
{
wbTarget.SaveToStream(file, SpreadsheetGear.FileFormat.OpenXMLWorkbook);
wbTarget.SaveAs($@"{FolderPath}\{nameTarget}", SpreadsheetGear.FileFormat.OpenXMLWorkbook);
GeneratePDF(file, $@"{FolderPath}\{nameTarget}", ruc.Trim());
}
counterWorked++;
this.ProgressFinished = counterWorked;
}
this.ProgressFinished = counterWorked;
this.WorkFinished = true;
}
