lundi 20 avril 2015

ListView not exporting data correctly to Excel

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

Aucun commentaire:

Enregistrer un commentaire