Export Excel ASP C #

Question

Export Excel ASP C #

Navigation

#1 by (1 votes)

1

Buenos Dias would like you to help me I'm doing in c # export excel the truth if it works everything I would like is just to press on my botton I will generate my excel but in the sheets the fields that are in my conbobox are added. how is it in the image:

since now when I press one each one if it generates me but I want that only one button generates everything in different sheets

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

c# asp.net

asked by PieroDev 01.03.2017 в 18:34

source

1 answer

Why when running my code does not make any changes on the screen? Run Activity while displaying a Fragment

score 1 · Accepted Answer

Solution:

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