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; }