Exporting from repeater to excel

Question

I have been able to populate a repeater but the only problem I'm having is to export it to an excel sheet. There are no data displayed in the excel file . What I'm thinking is that because of some postback when I click the export button, the data on the repeater gets deleted or something. Here is the code :

        Response.Clear();
        Response.Buffer = true;

        Response.AddHeader("content-disposition",
         "attachment;filename=GridViewExport.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);
        Table tb = new Table();
        TableRow tr1 = new TableRow();
        TableCell cell1 = new TableCell();
        cell1.Controls.Add(Repeater1);
        tr1.Cells.Add(cell1);
        tb.Rows.Add(tr1);
        tb.RenderControl(hw);

        //style to format numbers to string
        string style = @"<style> .textmode { mso-number-format:\@; } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();

possible duplicate of [Inserting data into an excel sheet from a DataTable](http://stackoverflow.com/questions/10547619/inserting-data-into-an-excel-sheet-from-a-datatable) — Jakub Konecki, May 16 '12 at 23:25

score 1 · Answer 1 · answered May 16 '12 at 22:33

For exporting data in Excel format I would recommend using EPPlus library instead of writing strings.

EPPlus is a .net library that reads and writes Excel 2007/2010 files using the Open Office Xml format (xlsx).

EPPlus supports: Cell Ranges, Cell styling (Border, Color, Fill, Font, Number, Alignments), Charts, Pictures, Shapes, Comments, Tables, Protection, Encryption, Pivot tables, Data validation

score 0 · Answer 2 · answered May 17 '12 at 05:32

I think "Repeater1" and table "tb" must be attached to Page (Page.Controls.Add(tb)). Try to:

render controls to string var rendered=RenderControlToString(tb);
then clear response Response.Clear();

then write rendered string Response.Write(rendered);

public static string RenderControlToString(Control c)
{
    StringBuilder sb = new StringBuilder();
    StringWriter sw = new StringWriter(sb);
    HtmlTextWriter htmlWriter = new HtmlTextWriter(sw);
    c.RenderControl(htmlWriter);
    sw.Close();
    htmlWriter.Close();
    return sb.ToString();

}

Exporting from repeater to excel

2 Answers2