How can you export csv file with asp.net c #

Question

How can you export csv file with asp.net c #

Navigation

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

0

Good I would like that I can be doing an application for which has the functionality to download a file in File Format of values separated by commas Microsoft Excel (.csv) already defined the columns in that file once you download the user with that already template you have to write and save.

The problem is that when I downloaded the excel. this comes out as a preview that is on the right

But when I enter the same file it appears fine. Now that file I enter it and I show it in my gridview

Of course, something strange happens in the preview in the gridview, the same thing also happens

I'm using the dll ClosedXML.Excel;

I attach the image

The code I'm doing is the following to create the csv file

 var wb = new XLWorkbook();
            var ws = wb.Worksheets.Add("Contacts");
            ws.Cell("A1").Value = "Fecha_Nacimiento";
            ws.Cell("B1").Value = "Número";
            ws.Cell("C1").Value = "Familia";
            ws.Cell("D1").Value = "Dni";
            ws.Cell("E1").Value = "Ruc";
            ws.Cell("F1").Value = "Apellido_Paterno";
            ws.Cell("G1").Value = "Apellido_Materno";
            ws.Cell("H1").Value = "Nombres";
            ws.Cell("I1").Value = "Celular";
            ws.Cell("J1").Value = "Correo";
            ws.Cell("K1").Value = "Dirección";
            Dtset.Tables[0].TableName = "Persona";

            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;filename=Reporte.csv");

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

Code to show the grid.

 string csvPath = Server.MapPath("~/MyFolder/") + Path.GetFileName(inputGroupFile01.PostedFile.FileName);
                        inputGroupFile01.SaveAs(csvPath);
                        Label1.Text = inputGroupFile01.FileName + "\'s Información Cargada del excel.";
                        System.Data.DataTable dt = new System.Data.DataTable();


                        dt.Columns.AddRange(new DataColumn[11] { 
                        new DataColumn("Fecha_Nacimiento", typeof(string)),  
                        new DataColumn("Número", typeof(string)),  
                        new DataColumn("Familia", typeof(string)),  
                        new DataColumn("Dni", typeof(string)),  
                        new DataColumn("Ruc",typeof(string)),
                        new DataColumn("Apellido_Paterno",typeof(string)),
                         new DataColumn("Apellido_Materno",typeof(string)),
                         new DataColumn("Nombres",typeof(string)),
                         new DataColumn("Celular",typeof(string)),
                         new DataColumn("Correo",typeof(string)),
                          new DataColumn("Dirección",typeof(string))
                        });

                        string csvData = File.ReadAllText(csvPath, Encoding.GetEncoding("iso-8859-1"));

                        foreach (string row in csvData.Split('\n'))
                        {
                            if (!string.IsNullOrEmpty(row))
                            {
                                dt.Rows.Add();
                                int i = 0;
                                foreach (string cell in row.Split(','))
                                {
                                    dt.Rows[dt.Rows.Count - 1][i] = HttpUtility.HtmlDecode(cell);
                                    i++;
                                }
                            }
                        }


                        DataSet ds = new DataSet();
                        ds.Tables.Add(dt);

                        try
                        {
                            var results = from row in ds.Tables["Table1"].AsEnumerable() where row.Field<string>("NOMBRES") == "NOMBRE" select row;
                            foreach (DataRow row in results)
                            {
                                ds.Tables["Table1"].Rows.Remove(row);
                            }
                        }
                        catch { }

                        GridView1.DataSource = dt;
                        GridView1.DataBind();

c# asp.net excel

asked by PieroDev 22.09.2018 в 19:33

source

2 answers

-1

At the moment of creating the file, do everything inside the memory stream.

    List<byte[]> lista = new List<byte[]>();
    using(var exportData = new MemoryStream())
                {
                    this.Response.Clear();
                    package.SaveAs(exportData);
                    lista.Add(exportData.ToArray());
                    this.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                    this.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", package.Workbook.Worksheets.FirstOrDefault().Name + ".xlsx"));
                    this.Response.BinaryWrite(exportData.ToArray());
                    this.Response.End();
                    return exportData;                   
                }

this is the driver.

answered by 22.09.2018 в 19:38

How to print from java code on a Brother-ql720nw printer Obtain path of a selected image from the file browser

score 0 · Accepted Answer

Solution to export with the CSV format

what I had to do or what was failing was that also when exporting with csv that is determined or separated by commas we must make the same format to the columns that are to be exported to make this code.

 builder.Append(string.Join(",", columnNames.ToArray())).Append("\n");

            foreach (DataRow row in dataTable.Rows)
            {
                List<string> currentRow = new List<string>();

                foreach (DataColumn column in dataTable.Columns)
                {
                    object item = row[column];

                    currentRow.Add(item.ToString());
                }

                rows.Add(string.Join(",", currentRow.ToArray()));
            }

            builder.Append(string.Join("\n", rows.ToArray()));





private System.Data.DataTable GetData()
        {
            System.Data.DataTable dt = new System.Data.DataTable("Plantilla");
            dt.Columns.Add("Fecha_Nacimiento");
            dt.Columns.Add("Numero");
            dt.Columns.Add("Familia");
            dt.Columns.Add("Dni");
            dt.Columns.Add("Ruc");
            dt.Columns.Add("Apellido_Paterno");
            dt.Columns.Add("Apellido_Materno");
            dt.Columns.Add("Nombres");
            dt.Columns.Add("Celular");
            dt.Columns.Add("Correo");
            dt.Columns.Add("Direccion");
            dt.Columns.Add("Razon_Social");

            return dt;
        }



var dataTable = GetData();
            StringBuilder builder = new StringBuilder();
            List<string> columnNames = new List<string>();
            List<string> rows = new List<string>();

            foreach (DataColumn column in dataTable.Columns)
            {
                columnNames.Add(column.ColumnName);
            }

            builder.Append(string.Join(",", columnNames.ToArray())).Append("\n");

            foreach (DataRow row in dataTable.Rows)
            {
                List<string> currentRow = new List<string>();

                foreach (DataColumn column in dataTable.Columns)
                {
                    object item = row[column];

                    currentRow.Add(item.ToString());
                }

                rows.Add(string.Join(",", currentRow.ToArray()));
            }

            builder.Append(string.Join("\n", rows.ToArray()));

            Response.Clear();
            Response.ContentType = "text/csv";
            Response.AddHeader("Content-Disposition", "attachment;filename=Plantilla.csv");
            Response.Write(builder.ToString());
            Response.End();