Exportar Excel ASP C#

publicado por: Anonymous

Buenos Dias quisiera que me ayuden estoy realizando
en c# exportar excel la verdad si me funciona todo lo que yo quisiera es tan solo presionar en mi botton me genere mi excel pero en las hojas se agregen los campos que estan en mi conbobox. como esta en la imagen :

ya que ahora cuando presiono uno cada uno si me genera pero yo quiero que tan solo un boton me genere todo en diferentes hojas

introducir la descripción de la imagen aquí

    protected void ExportExcel(object sender, EventArgs e)
    {

        string constr = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
        string query = "COMISION";

        //string query = "exec COMISION_DIC16 @TC, @Fecha_FDM, @OPC";
        //string query = "select top 5 NPtmo,Oficina,SecCod,SecNom,Otorgado from TCalificacion ";
       //query += "SELECT TOP 10  idorigencredito, segmentotasa, segmentocartera FROM credito..credito;";

        using (SqlConnection con = new SqlConnection(constr))
        {


            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.CommandTimeout = 90000000;
            cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.Add("TC", SqlDbType.VarChar, 50).Value = txtTC.Text;
             cmd.Parameters.Add("Fecha_FDM", SqlDbType.VarChar, 50).Value = txtFecha_FDM.Text;
              cmd.Parameters.Add("OPC", SqlDbType.VarChar, 50).Value = OPC.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 = "REPORTES";
                        //ds.Tables[1].TableName = "DetalleDesembolso";



                        using (XLWorkbook wb = new XLWorkbook())
                        {
                            foreach (DataTable dt in ds.Tables)
                            {
                                //Add DataTable as Worksheet.
                                wb.Worksheets.Add(dt);
                            }

                            //Export the Excel file.
                            Response.Clear();
                            Response.Buffer = true;
                            Response.Charset = "";
                            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                            Response.AddHeader("content-disposition", "attachment;filename=Reporte_Comision.xlsx");

                            using (MemoryStream MyMemoryStream = new MemoryStream())
                            {
                                wb.SaveAs(MyMemoryStream);
                                MyMemoryStream.WriteTo(Response.OutputStream);
                                Response.Flush();
                                Response.End();
                            }
                        }
                    }
                }
            }
        }
    }

solución

Solucion:

  protected void ExportExcel(object sender, EventArgs e)
    {

        string constr = ConfigurationManager.ConnectionStrings["con"].ConnectionString;
        string query = "COMISIONTODO"; //"COMISION"

        //string query = "exec COMISION_DIC16 @TC, @Fecha_FDM, @OPC";
        //string query = "select top 5 NPtmo,Oficina,SecCod,SecNom,Otorgado from TCalificacion ";
       //query += "SELECT TOP 10  idorigencredito, segmentotasa, segmentocartera FROM credito..credito;";

        using (SqlConnection con = new SqlConnection(constr))
        {


            using (SqlCommand cmd = new SqlCommand(query))
            {
                cmd.CommandTimeout = 90000000;
            cmd.CommandType = CommandType.StoredProcedure;
          cmd.Parameters.Add("TC", SqlDbType.VarChar, 50).Value = txtTC.Text;
            cmd.Parameters.Add("Fecha_FDM", SqlDbType.VarChar, 50).Value = txtFecha_FDM.Text;
             // cmd.Parameters.Add("OPC", SqlDbType.VarChar, 50).Value = OPC.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 = "DETALLE-DESEMBOLSO";
                        ds.Tables[1].TableName = "AGRUPADO-DESEMBOLSO";
                        ds.Tables[2].TableName = "DETALLE-CARTERA";
                        ds.Tables[3].TableName = "AGRUPADO-CARTERA";
                        ds.Tables[4].TableName = "DETALLE-CANCELADO";
                        ds.Tables[5].TableName = "AGRUPADO-CANCELADO";
                        ds.Tables[6].TableName = "DETALLE-CANC+2CUO";
                        ds.Tables[7].TableName = "AGRUPADO-CANC+2CUO";
                        ds.Tables[8].TableName = "DETALLE-RENOV_EXPRESS";
                        ds.Tables[9].TableName = "AGRUPADO-RENOV_EXPRESS";
                        ds.Tables[10].TableName = "COLOCACIONES - 2 MESES";
                        ds.Tables[11].TableName = "MONTO BRUTO";


                        using (XLWorkbook wb = new XLWorkbook())
                        {
                            foreach (DataTable dt in ds.Tables)
                            {
                                //Add DataTable as Worksheet.
                                wb.Worksheets.Add(dt);
                            }

                            //Export the Excel file.
                            Response.Clear();
                            Response.Buffer = true;
                            Response.Charset = "";
                            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                            Response.AddHeader("content-disposition", "attachment;filename=Reporte_Comision.xlsx");

                            using (MemoryStream MyMemoryStream = new MemoryStream())
                            {
                                wb.SaveAs(MyMemoryStream);
                                MyMemoryStream.WriteTo(Response.OutputStream);
                                Response.Flush();
                                Response.End();
                            }
                        }
                    }
                }
            }
        }
    }
Respondido por: Anonymous

Leave a Reply

Your email address will not be published. Required fields are marked *