ListView not exporting data correctly to Excel

Question

I am getting an error in excel relating to the data I am trying to export from a listview (ASP & C#).

To clarify, I am converting the ListView to a DataTable as I was getting errors before hand.

All of this is handled on a button click, the excel document is created but when opening it there is an error and only the headers are shown.

Any idea as to what I'm doing wrong? My suspicion is that adding the dt in LoadFromDataTable is causing this, but nothing is coming up in debugging as an error - any pointers would be gratefully received.

Excel Error: We found a problem with some content in 'Test_List.xlsx'. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes.

C# Code behind

protected void csv_Click(object sender, EventArgs e)
    {
        DataTable dt = new DataTable();
        dt.Columns.Add(new DataColumn("Ref", typeof(string)));
        dt.Columns.Add(new DataColumn("Company", typeof(string)));
        dt.Columns.Add(new DataColumn("Email", typeof(string)));
        dt.Columns.Add(new DataColumn("Telephone", typeof(string)));

        foreach(ListViewDataItem li in ListView1.Items)
        {
            if (li.ItemType == ListViewItemType.DataItem)
            {
                DataRow dr = dt.NewRow();
                dr["Ref"] = ((Label)li.FindControl("lblRef")).Text;
                dr["Company"] = ((Label)li.FindControl("lblCmp")).Text;
                dr["Email"] = ((Label)li.FindControl("lblEmail")).Text;
                dr["Telephone"] = ((Label)li.FindControl("lblTele")).Text;
                dt.Rows.Add(dr);
            }
        }

        using (ExcelPackage pck = new ExcelPackage())
        {
            // creating worksheet
            ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Test_List");

            // load database to sheet, start from A1, print column names on row 1
            ws.Cells["A1"].LoadFromDataTable(dt, true);
            //loop through rows in datatable to rows in excel

            Response.ContentType = "application/vnd.openxmlformats-officedocument,spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment; filename=Test_List.xlsx");
            Response.BinaryWrite(pck.GetAsByteArray());
        }
    }

Answer 1

The problem I would go with in your example is that the listview isn't correctly converting into a data table, which may or may not be why your spreadsheeet is throwing that useless error.

If you debug and burrow deep down on the dr["ref"] I don't think the string is containing the label text.

Cut out the listview to datatable bit and go from SQL database to DataTable to CSV.

Like so, although you can use xlsx if you so wish:

protected void Csv_Click(object sender, EventArgs e)
    {
        using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalConnection"].ConnectionString))
        {
            con.Open();
//stored procs are easier to modify than query strings
                using (SqlCommand cmd = new SqlCommand("csvProc", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    using (SqlDataAdapter sda = new SqlDataAdapter())
                    {
                        sda.SelectCommand = cmd;
                        using (DataTable dt = new DataTable())
                        {
                            sda.Fill(dt);
                            // build csv as comma sep string
                            string csv = string.Empty;

                            foreach(DataColumn col in dt.Columns)
                            {
                                // header row for csv
                                csv += col.ColumnName + ',';
                            }
                            // new line
                            csv += "\r\n";

                            foreach(DataRow row in dt.Rows)
                            {
                                foreach(DataColumn col in dt.Columns)
                                {
                                    // add the data rows
                                    csv += row[col.ColumnName].ToString().Replace(",", ";") + ',';
                                }
                                csv += "\r\n";
                            }

                            // download the csv file
                            Response.Clear();
                            Response.Buffer = true;
                            Response.AddHeader("content-disposition", "attachment;filename=TestList.csv");
                            Response.Charset = "";
                            Response.ContentType = "application/text";
                            Response.Output.Write(csv);
                            Response.Flush();
                            Response.End();
                        }
                    }
                }
            }
        }