as I export a datagrid to excel

Question

as I export a datagrid to excel

Navigation

#1 by (0 votes)
#2 by (0 votes)

0

I want to export a datagrid, its button works but when I open it in excel, it tells me that the extension has another format.

otherwise, if I accept the excel, the sheet shows nothing

this is the code

    public class CustomersBe
    {
        public string Almacen { get; set; }
        public string reftallacolor { get; set; }
        public string referencia { get; set; }
        public string talla { get; set; }
        public string color { get; set; }
        public int  idcurva { get; set; }
        public string CantUnidades { get; set; }
        public string CantModificadas { get; set; }
        public string Zona { get; set; }
        public string coleccion { get; set; }
        public string clasifica { get; set; }
        public string silueta { get; set; }
        public int Cantinv { get; set; }
        public double UnidadesFactu { get; set; }
        public string rotacionSemana { get; set; }
        public string rotacionMes { get; set; }
        public string rotacion2Meses { get; set; }
        public string Sugerido { get; set; }
    }

    public static List<CustomersBe> lista = new List<CustomersBe>();
    //private object runat;

    public object server { get; private set; }

    public void CargarData()

    {

       SqlConnection conexion = new SqlConnection();
        conexion.ConnectionString = "Data Source=192.168.1.200;Initial Catalog=LAURASA;User Id=sa;Password=laurasa";
        conexion.Open();
        try
        {
            string consulta = @"select almacen,RefTallaColor,Referencia,Talla,color,IDCurva,CantUnidades,CantModificadas from Analisis_Curva  WHERE Referencia = '" + ddlReferencia.SelectedValue + "' and almacen  = '" + ddlAlmacen.SelectedValue + "'";
                var cmd = new SqlCommand(consulta, conexion);
                SqlDataReader lector = cmd.ExecuteReader();
                while (lector.Read())
                {
                    var customers = new CustomersBe();
                    customers.Almacen = (string)lector[0];
                    customers.reftallacolor = (string)lector[1];
                    customers.referencia = (string)lector[2];
                    customers.talla = (string)lector[3];
                    customers.color = (string)lector[4];
                    customers.idcurva = (int)lector[5];
                    customers.CantUnidades = (string)lector[6];
                    customers.CantModificadas = (string)lector[7];

                    lista.Add(customers);
                }
                grvAnalisis.DataSource = lista;
                grvAnalisis.DataBind();
                conexion.Close();

        }
       catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

    }
    protected void grvAnalisis_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        grvAnalisis.PageIndex = e.NewPageIndex;
        CargarData();

    }
    protected void btnGridviewToExcel_Click(object sender, EventArgs e)
     {

        grvAnalisis.EnableViewState = false;

        Response.Clear();
        Response.Buffer = true;
        Response.ContentEncoding = System.Text.ASCIIEncoding.UTF8;
        Response.AddHeader("content-disposition", "attachment;filename=AnalisisDeCurva.xls");
       // Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
        StringBuilder sb = new StringBuilder();
        StringWriter sw = new StringWriter(sb);
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        Page page = new Page();
        Form HtmlForm = new Form();
        Page.Controls.Add(Form);
        Form.Controls.Add(grvAnalisis);

        //Page.RenderControl(hw);

        grvAnalisis.AllowPaging = true;
            this.CargarData();

       //grvAnalisis.HeaderRow.BackColor = Color.White;

        foreach(TableCell cell in grvAnalisis.HeaderRow.Cells)
        {
              cell.BackColor=grvAnalisis.HeaderStyle.BackColor;
         }
            foreach (GridViewRow row in grvAnalisis.Rows)
            {
                row.BackColor = Color.White;
                foreach (TableCell cell in row.Cells)btn
                {
                    if (row.RowIndex % 2 == 0)
                    {
                        cell.BackColor = grvAnalisis.AlternatingRowStyle.BackColor;
                    }
                    else
                    {
                        //cell.BackColor = grvAnalisis.RowStyle.BackColor;
                        cell.BackColor = Color.White;
                    }
                    cell.CssClass = "textmode";
                }
            }  
       // grvAnalisis.RenderControl(hw);

       // string style = @"<style> .textmode { } </style>";
        Response.Charset = "iso-8859-1";
        Response.Charset = "UTF-8";
        //Response.ContentEncoding = Encoding.Default;
        Response.Write(sw);
        Response.Output.Write(sw.ToString());
        //Response.Flush();
        Response.End(); 
    }
}

the problem is in the btnGridviewToExcel

asp.net

asked by Daniela Gomez Sepulveda 19.02.2018 в 15:36

source

2 answers

the code must read a file type .txt and put it in cipher cease, the problem is that it takes away the spaces like invoking a report multiple times in a single pdf with reportviewer c #

score 0 · Answer 1

How it worked for me is with the extension of xlsx and with this contentType application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet , this happens with the new versions of office and it costs more to make them compatible with the contentType

protected void btnGridviewToExcel_Click(object sender, EventArgs e){

    grvAnalisis.EnableViewState = false;

    Response.Clear();
    Response.Buffer = true;
    Response.ContentEncoding = System.Text.ASCIIEncoding.UTF8;
    Response.AddHeader("content-disposition", "attachment;filename=AnalisisDeCurva.xlsx");
   // Response.Charset = "";
    Response.ContentType = "application/application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
    StringBuilder sb = new StringBuilder();
    StringWriter sw = new StringWriter(sb);
    HtmlTextWriter hw = new HtmlTextWriter(sw);
    Page page = new Page();
    Form HtmlForm = new Form();
    Page.Controls.Add(Form);
    Form.Controls.Add(grvAnalisis);

    //Page.RenderControl(hw);

    grvAnalisis.AllowPaging = true;
        this.CargarData();

   //grvAnalisis.HeaderRow.BackColor = Color.White;

    foreach(TableCell cell in grvAnalisis.HeaderRow.Cells)
    {
          cell.BackColor=grvAnalisis.HeaderStyle.BackColor;
     }
        foreach (GridViewRow row in grvAnalisis.Rows)
        {
            row.BackColor = Color.White;
            foreach (TableCell cell in row.Cells)btn
            {
                if (row.RowIndex % 2 == 0)
                {
                    cell.BackColor = grvAnalisis.AlternatingRowStyle.BackColor;
                }
                else
                {
                    //cell.BackColor = grvAnalisis.RowStyle.BackColor;
                    cell.BackColor = Color.White;
                }
                cell.CssClass = "textmode";
            }
        }  
   // grvAnalisis.RenderControl(hw);

   // string style = @"<style> .textmode { } </style>";
    Response.Charset = "iso-8859-1";
    Response.Charset = "UTF-8";
    //Response.ContentEncoding = Encoding.Default;
    Response.Write(sw);
    Response.Output.Write(sw.ToString());
    //Response.Flush();
    Response.End(); 
}

score 0 · Answer 2

One option that is quite useful is to use the library ClosedXML downloads the dll you import into the references of your project and you can create The following procedure to send your dataset with you fill the datagrid so that it generates a perfectly functional Excel book.

First you import the spaced names to use:

using ClosedXML.Excel;
using System.Data;
using System.IO;

We create the function that will make use of the ClosedXML library:

 public void ExportToExcel(DataTable dt)
    {
        XLWorkbook wb = new XLWorkbook();

        using (wb) {
            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=data.xlsx");

            MemoryStream MyMemoryStream = new MemoryStream();

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

Then you would invoke it from your code as follows (Note: you must fill in your dataset first) :

 protected void Page_Load(object sender, EventArgs e)
    {
        DataSet ds = new DataSet();

        //Dataset debe ser llenado antes
        ExportToExcel(ds.Tables[0]);
    }