Export Excel multiple asp c #

0

Good I would like you to help me I'm doing an export of an excel but with a multiple checkbox because one by one if I export but when I select two or more just throws me an excel. since I do not vote any error but I want that when you select several excel download several as I have selected.

this is my code:

string message = "";
foreach (ListItem item in lstFruits.Items)
{
  if (item.Selected)
  {
    //message += item.Text + " " + item.Value + "\n";
    string query = "SP_ReporteExcel";
    using (SqlConnection con = new SqlConnection(ObtenerCadenaConexion()))
    {
      using (SqlCommand cmd = new SqlCommand(query))
      {
        cmd.CommandTimeout = 90000000;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("opt", SqlDbType.Int).Value = 1;
        cmd.Parameters.Add("fechainicio", SqlDbType.DateTime).Value = txtFinicio.Text;
        cmd.Parameters.Add("fechafin", SqlDbType.DateTime).Value = txtFFin.Text;
        cmd.Parameters.Add("rubro", SqlDbType.VarChar, 250).Value = lstFruits.SelectedItem.Value;
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
          cmd.Connection = con;


          sda.SelectCommand = cmd;

          using (DataSet ds = new DataSet())
          {
            sda.Fill(ds);

            //Set Name of DataTables.

            ds.Tables[0].TableName = "Recibos-Energia Electrica";
            ds.Tables[1].TableName = "Recibos-Agua Potable";



            using (XLWorkbook wb = new XLWorkbook())
            {


              foreach (DataTable dt in ds.Tables)
              {

                DateTime d3 = DateTime.Parse(txtFinicio.Text);
                string Mes = d3.ToString("MMMM");

                DateTime d4 = DateTime.Parse(txtFinicio.Text);
                string Anio = d4.ToString("yyyy");

                wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
                wb.Style.Font.Bold = true;
                wb.Style.Font.FontColor = XLColor.Black;
                //wb.Style.Font.FontName = "calibri";
                //wb.Style.Font.FontSize = 13;
                wb.Style.Border.DiagonalBorderColor = XLColor.Red;
                wb.Style.Border.RightBorderColor = XLColor.Red;
                wb.Style.Border.LeftBorderColor = XLColor.Red;
                wb.Style.Border.BottomBorderColor = XLColor.Red;

                var ws = wb.Worksheets.Add(dt);

                ws.Row(1).InsertRowsAbove(1);
                ws.Cells("A2:H2").Style.Fill.BackgroundColor = XLColor.Black;
                ws.Cells("A2:H2").Style.Font.FontColor = XLColor.Yellow;

                ws.Cells("D1:H1").Style.Fill.BackgroundColor = XLColor.Black;
                ws.Cells("D1:H1").Style.Font.FontColor = XLColor.Yellow;
                string datetimeStringFileName = string.Format("{0:yyyy-MM-dd}", DateTime.Now);
                ws.Range("D1:H1").Merge();

                ws.Range("D1").Value = "Mes de " + Mes + " del " + Anio;
                //ws.Cells("D1:H1").Value = "Mes";

                //wb.Style.Fill.BackgroundColor = XLColor.Black;
                //wb.Worksheets.Add(dt);

              }



              Response.Clear();
              Response.Buffer = true;
              Response.Charset = "";


              Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
              //Response.AddHeader("content-disposition", "attachment;filename=\"Reporte_Mensualess.xlsx\"");
              Response.AddHeader("content-disposition", "attachment;filename=Reporte_Mensuales.xlsx");


              using (MemoryStream MyMemoryStream = new MemoryStream())
              {
                //var aux = Guid.NewGuid();
                wb.SaveAs(MyMemoryStream);
                //DateTime fechaActual = DateTime.Now;
                //CultureInfo ci = CultureInfo.InvariantCulture;

                //var aux = fechaActual.ToString("yyyyMMddhhmm.fff", ci);// formato 201703231842

                //  wb.SaveAs("C:\DatosSiga\Logistica\Reporte_Mensualess" + aux + ".xlsx");

                MyMemoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
                Response.Redirect("FrmLogeo.aspx");
              }
            }

          }
        }
      }
    }

  }
}
<script type="text/javascript">
    $(function () {
        $('[id*=lstFruits]').multiselect({
            includeSelectAllOption: true
        });
    });
</script>

  using (ZipFile zip = new ZipFile())
  {
    zip.AlternateEncodingUsage = ZipOption.AsNecessary;
    zip.AddDirectoryByName("Files");
    foreach (ListItem item in lstFruits.Items)
    {
      if (item.Selected)
      {
        string filePath = item.Text;
        zip.AddFile(filePath, "Files");
      }
    }
    Response.Clear();
    Response.BufferOutput = false;
    string zipName = String.Format("Zip_{0}.zip", DateTime.Now.ToString("yyyy-MMM-dd-HHmmss"));
    Response.ContentType = "application/zip";
    Response.AddHeader("content-disposition", "attachment; filename=" + zipName);
    zip.Save(Response.OutputStream);
    Response.End();
  }



  using (MemoryStream MyMemoryStream = new MemoryStream())
  {




    wb.SaveAs(MyMemoryStream);

    MyMemoryStream.WriteTo(Response.OutputStream);
    Response.Flush();


    Response.End();


  }
    
asked by PieroDev 28.03.2017 в 02:02
source

2 answers

1

The Response can only send a single answer file, if you need to send several you should previously make a zip that will join them in a single file and send this as a response to the page.

You have the class ZipFile to be able to join the generated excel

How to: Compress and Extract Files

The idea is to implement as explained here

Downloading multiple files in ASP.NET

In the article a temporary folder is compressed, it would be necessary to see if this can be done with the files in memory

    
answered by 28.03.2017 в 16:38
0

It's not that difficult, but you must understand the logic, you must first put everything in a folder, these folders are created in the server, that's why you mention @ leandro-tuttini that you should use temporary folders to generate a zip, I will I have done in some projects not to download Excel files, I think that pdfs, as well as generating excel, you have already solved it.

I'll explain to you step by step:

  • Create a new ASP.net project c #

  • In my project use an Ionic.Zip nuget, to add it:

  • Nuget Ionic.zip

    You have to put the line: Install-Package Unofficial.Ionic.Zip in the nugget console.

    Notice that the project is selected:

    We write Installa-Package Unofficial.Ionic.Zip and give enter .

    The dll will be added to the project references

  • I add the files and folders of the website, css, js
  • JQuery

    Bootstrap

    Code of the page:

    <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="EjemploDescargarZip.Default" %>
    
    <!DOCTYPE html>
    
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
        <title></title>
        <link href="css/bootstrap.css" rel="stylesheet" />
        <script src="js/jquery-3.2.0.min.js"></script>
        <script src="js/default.js"></script>
    </head>
    <body>
    <div class="container">
        <br /><br />
        <div class="panel panel-primary">
             <div class="panel-heading">
                  <h3 class="panel-title">Ejemplo descargar varios archivo en Zip</h3>
             </div> 
            <div class="panel-body"> 
                Haga click en el botón para descargar el zip.
                <button class="btn btn-success">Descargar zip</button>
            </div>
        </div>
    </div>
    </body>
    </html>
    
  • I add a jquery plugin to download files:
  • Jquery.FileDownload official page

    jqueyr.fileDownload.js

    I have the file added to the project and the reference on the page:

    * That order must be respected

  • In the file default.js I put the click event of the button btnDescargar , where we will use the plugin jquery.fileDownload
  • I test the button event:

    The code for the function call that generates the files and the download plugin is:

    function DescargarArchivo() {
        //alert("Clic al botón");
    
        //Genero una lista de documentos a descargar
        var documentos = [];
        documentos.push("Doc1");
        documentos.push("Ejemplo30");
        documentos.push("Nombre generico");
        documentos.push("ArchivoX");
        //-----------------------------------------------------
    
        //Hago la llamada al server para generar los documentos
        $.ajax({
            type: "POST",
            contentType: "application/json; charset=utf-8",
            url: "Default.aspx/Descargar",//Este metodo debe existir en la página Default.aspx.cs
            data: "{documentos:" + JSON.stringify(documentos) + "}", //Envio la lista de documentos que voy a generar
            dataType: "json",
            async: false,
            success: function (data, textStatus) {
                if (textStatus === "success") {
                    var datos = data.d;
    
                    //Muestro mensaje de error en caso de no ser satisfactorio
                    if (!datos.Success) {
                        alert(datos.Message);
                        return false;
                    }
    
                    //Regreso la ruta del zip, esta ruta será utilizada por jquery.fileDownload para descargarlo
                    var ruta = datos.rutaZip;
                    $.fileDownload("fileDownload.aspx", { data: { file: ruta } })//llamo a la página fileDownload.aspx
                    .done(function () {
                        alert("Documento descargado satisfactoriamente.");
                    })
                    .fail(function () {
                        alert("Error al descargar el zip.");
                        return false;
                    });
                }
                return true;
            },
            error: function (request, status, error) {
                alert(jQuery.parseJSON(request.responseText).Message);
            }
        });
    }
    
  • We have to create the Default.aspx/Descargar method

    public static Datos Descargar(String[] documentos)
    {
    
        Datos datos = new Datos();
    
        try
        {
    
            datos.rutaZip = Documentos.Obtener(documentos); //Retorno la ruta que es lo que necesito para descargar el archivo
            datos.Message = "";
            datos.Success = true;
    
        }
        catch (Exception ex)
        {
    
            datos.Message = ex.Message;
            datos.Success = false;
        }
        return datos;
    } public class Datos
    {
        public bool Success { get; set; }
        public string Message { get; set; }
        public string rutaZip { get; set; }
    }
    
  • I think the class Documentos.Obtener(documentos);

    public class Documentos
        {
            public static string Obtener(String[] documetnos)
            {
                string ruta = "";
                try
                {
                    const string rutaZip = "DocumentosZip";
                    string nombreArchivo = "";
                    const string carpeta = "DocumentosTemp";
    
                    string nombreArchivoZip = "DocumentosZip" + DateTime.Now.ToShortDateString().Trim().Replace("/", "") + Guid.NewGuid().ToString();
    
                    #region ValidacionDeCarpetas&Archivos
    
                    List<string> listaArchivos = new List<string>();
                    //Antes de Crear los archivos Elimino todo de la ruta
                    //Elimino de la ruta zip
                    if (System.IO.Directory.Exists(HttpContext.Current.Server.MapPath(rutaZip)))
                    {
                        //Elimino todos los archivos de la rtua
                        string[] ficherosCarpeta = Directory.GetFiles(HttpContext.Current.Server.MapPath(rutaZip));
                        foreach (string ficheroActual in ficherosCarpeta)
                            File.Delete(ficheroActual);
                    }
                    //Elimino de la ruta de los doc
                    if (System.IO.Directory.Exists(HttpContext.Current.Server.MapPath(carpeta)))
                    {
                        //Elimino todos los archivos de la ruta
                        string[] ficherosCarpeta = Directory.GetFiles(HttpContext.Current.Server.MapPath(carpeta));
                        foreach (string ficheroActual in ficherosCarpeta)
                            File.Delete(ficheroActual);
                    }
                    #endregion ValidacionDeCarpetas&Archivos
                    #region ListaDeDocumentosAGenerar
                    foreach (var d in documetnos)
                    {
                        //El nombre que tendrá el archivo
                        nombreArchivo = d + DateTime.Now.ToShortDateString().Trim().Replace("/", "") + Guid.NewGuid().ToString() + ".txt";
    
                        //ruta = EstadoCuenta.EstadoDeCuenta(solCre.Expediente, rutaPdf, nombreArchivoPdf);
    
                        var nameFile = carpeta + "/" + nombreArchivo;
                        string pathFile = HttpContext.Current.Server.MapPath(nameFile); //Aqui es donde se mapean los nombres reales del server
                        var directorio = HttpContext.Current.Server.MapPath(carpeta);
                        if (!System.IO.Directory.Exists(directorio))
                        {
                            System.IO.Directory.CreateDirectory(directorio);
                        }
                        //agrego el archivo al directorio
                        //Método crear el archivo----
                        CrearArchivo(pathFile);
                        //Esta lista guarda los nombres de archivos generados
                        listaArchivos.Add(nameFile);
                    }
                    #endregion ListaDeDocumentosAGenerar
                    #region GeneracionDelZip
                    if (listaArchivos.Count > 0)
                    {
                        //HttpContext.Current.Server.MapPath();
                        if (!System.IO.Directory.Exists(HttpContext.Current.Server.MapPath(rutaZip)))
                        {System.IO.Directory.CreateDirectory(HttpContext.Current.Server.MapPath(rutaZip));
                        }
                        //La clase Zipper genera los zips
                        ruta = Zipper.Comprimir(nombreArchivoZip, rutaZip, carpeta);
                    }
                    else
                    {
                        throw new Exception("Los archivos no fueron generados");
                    }
    
                    #endregion GeneracionDelZip
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
    
                return ruta;
            }
            private static void CrearArchivo(string rutaNombreArchivo)
            {
                try
                {
                    // Example #2: Write one string to a text file.
                    string text = "Texto de ejemplo en el archivo " + rutaNombreArchivo;
                    // WriteAllText creates a file, writes the specified string to the file,
                    // and then closes the file.    You do NOT need to call Flush() or Close().
                    System.IO.File.WriteAllText(rutaNombreArchivo, text);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message, ex);
                }
            }
        }
    
  • Create class and method Zipper.Comprimir(nombreArchivoZip, rutaZip, carpeta)

    public static string Comprimir(string nombreArchivo, string rutaZip, string rutaPdf)
            {
                try
                {
                   var rutaNombreZip = rutaZip + "/" + nombreArchivo + ".zip";
                    using (var zip = new Ionic.Zip.ZipFile())
                    {                     zip.AddDirectory(HttpContext.Current.Server.MapPath(rutaPdf));                        zip.Save(HttpContext.Current.Server.MapPath(rutaNombreZip));
                }
                return rutaNombreZip;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
        }
    
  • File file fileDownload.aspx

    protected void Page_Load(object sender, EventArgs e)
                        {
    
                    var archivo = Request.QueryString["file"];
    
                    var file = new FileInfo(Server.MapPath(archivo));
    
                    Response.Clear();
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + file.Name);
                    Response.AddHeader("Content-Length", file.Length.ToString());
                    Response.ContentType = "Application/pdf";
                    Response.TransmitFile(file.FullName);
                    Response.SetCookie(new HttpCookie("fileDownload", "true") { Path = "/" });
                    Response.End();
                }
    
  • TRIALS

    Finally, attach the file with the sample project:

    Sample project - Click to download

        
    answered by 29.03.2017 в 22:23