export gridview data

Question

What is the best way to export a gridview into an Excel spreadsheet? This seems easy

except that my Gridview doesn't have an export attribute. What is the quickest way to do this?

Answer 1

I used CarlosAg.ExcelXmlWriter link.

I looped through all GridViews HeaderCells and then through all rows. The only thing is that if you allow paging and you've more than one page you have to set the PageSize to a high value (I set to 10000000) then DataBind the GridView again and do your work. Afterwards set the old PageSize value back. If someone knows a better solution, you're welcome.

EDIT: The try/catch is there because for some reason it is not possible to check the control type and then cast to Label or LinkButton ==> control.GetType().

Here's my code.

 public static Workbook CreateWorkbook(GridView gridView)
    {
        int pageSize = gridView.PageSize;
        gridView.PageSize = 10000000;
        gridView.DataBind();

        Workbook workbook = new Workbook();
        Worksheet sheet = workbook.Worksheets.Add("Export");

        WorksheetStyle style = workbook.Styles.Add("headerStyle");
        style.Font.Bold = true;
        style = workbook.Styles.Add("defaultStyle");
        style.Alignment.WrapText = true;
        style = workbook.Styles.Add("infoStyle");
        style.Font.Color = "Red";
        style.Font.Bold = true;

        sheet.Table.Rows.Add(new WorksheetRow());

        WorksheetRow headerRow = new WorksheetRow();
        foreach (DataControlFieldHeaderCell cell in gridView.HeaderRow.Cells)
        {
            if (!string.IsNullOrEmpty(cell.Text))
                headerRow.Cells.Add(cell.Text, DataType.String, "headerStyle");
            else
                foreach (Control control in cell.Controls)
                {
                    LinkButton linkButton = new LinkButton();
                    try
                    {
                        linkButton = (LinkButton)control;
                    }
                    catch { }

                    if (!string.IsNullOrEmpty(linkButton.Text))
                        headerRow.Cells.Add(linkButton.Text, DataType.String, "headerStyle");
                    else
                    {
                        Label label = new Label();
                        try
                        {
                            label = (Label)control;
                        }
                        catch { }
                        if (!string.IsNullOrEmpty(label.Text))
                            headerRow.Cells.Add(label.Text, DataType.String, "headerStyle");
                    }
                }
        }

        sheet.Table.Rows.Add(headerRow);

        foreach (GridViewRow row in gridView.Rows)
        {
            WorksheetRow wrow = new WorksheetRow();
            foreach (TableCell cell in row.Cells)
            {
                foreach (Control control in cell.Controls)
                {
                    if (control.GetType() == typeof(Label))
                    {
                        wrow.Cells.Add(((Label)control).Text, DataType.String, "defaultStyle");
                    }
                }
            }
            sheet.Table.Rows.Add(wrow);
        }

        gridView.PageSize = pageSize;

        return workbook;
    }

Answer 2

This method goes straight into Excel format without requiring XML be installed on the server or by using XML.

        Protected Sub ExportToExcel()

        Dim gv1 As GridView = FindControlRecursive(objPlaceHolder, "GridView1")
        If Not gv1 Is Nothing Then
            Response.ClearHeaders()
            Response.ClearContent()

            ' Set the content type to Excel
            Response.ContentType = "application/vnd.ms-excel"

            ' make it open the save as dialog
            Response.AddHeader("content-disposition", "attachment; filename=ExcelExport.xls")

            'Turn off the view state 
            Me.EnableViewState = False

            'Remove the charset from the Content-Type header 
            Response.Charset = String.Empty

            Dim myTextWriter As New System.IO.StringWriter
            Dim myHtmlTextWriter As New System.Web.UI.HtmlTextWriter(myTextWriter)
            Dim frm As HtmlForm = New HtmlForm()
            Controls.Add(frm)
            frm.Controls.Add(gv1)

            'Get the HTML for the control 
            frm.RenderControl(myHtmlTextWriter)

            'Write the HTML to the browser 
            Response.Write(myTextWriter.ToString())
            'End the response 
            Response.End()
        End If
    End Sub

Private Function FindControlRecursive(ByVal root As Control, ByVal id As String) As Control
    If root.ID = id Then
        Return root
    End If
    Dim c As Control
    For Each c In root.Controls
        Dim t As Control = FindControlRecursive(c, id)
        If Not t Is Nothing Then
            Return t
        End If
    Next
    Return Nothing
End Function

Answer 3

I've done this several times. Excel has a XML version. It ends up with the .xml extension, but you can just change the file's extension to .xls and the XML formatted file will open in excel just fine.

The biggest hurdle of this approach is the excel XML format. I usually create an excel file in excel in the approximate format that I desire. I then save the Excel file to XML format, and open it up in a text editor.

I usually create a template file from this Excel sample page. Then when I am exporting the information in the Gridview, I only have to create the xml for the section that contains the cells I plan on populating, I just prepend, and append the text already in the template file.

Once you open up the xml formatted excel file, you'll figure out the needed XML relatively easily. The hardest part to understand is the way the cells reference the formatting options, which are at the top of the XML file.

Good luck, let me know if you need more clarification.

Edit: You will only need to create the template excel file once, just to get a feel for the needed xml that you'll need to generate. Once you've generated the xml use the following code to send it to the user:

string fileName = "ExportedFile.xls";
Response.Clear();
Response.Buffer = true;
Response.ContentType = "text/xml";
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
ExportToExcel(HttpContext.Current.Response.OutputStream, testUID);
Response.End();



public static void ExportToExcel(Stream outputStream)
{
    XmlTextWriter xmlSink = new XmlTextWriter(outputStream, Encoding.Default);

    //ExcelHeaderString and ExcelStylesString are from the template
    xmlSink.WriteRaw(ExcelHeaderString);
    xmlSink.WriteRaw(ExcelStylesString);

    //write your elements here
    xmlSink.WriteElement("YourElements");

    //ExcelFooterString is from the template
    xmlSink.WriteRaw(ExcelFooterString);
}

Answer 4

Private exportToExcel As Boolean = False

Private Sub LoadInExcel()
    Me.Response.ClearContent()
    Me.Response.AddHeader("content-disposition", "attachment; filename=MyFile.xls")
    Me.Response.ContentType = "application/ms-excel"
    Dim sw1 As New IO.StringWriter
    Dim htw1 As HtmlTextWriter = New HtmlTextWriter(sw1)
    GridView1.RenderControl(htw1)
    Response.Write(sw1.ToString())
    Response.End()
End Sub

Public Overrides Sub VerifyRenderingInServerForm(ByVal control As Control)
    ' Confirms that an HtmlForm control is rendered for the specified ASP.NET
    ' server control at run time.
End Sub

Protected Overrides Sub Render(ByVal writer As System.Web.UI.HtmlTextWriter)
    If exportToExcel Then
        LoadInExcel()
    End If

    MyBase.Render(writer)
End Sub

Protected Sub btnPrint_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnPrint.Click
    exportToExcel = True
End Sub

You must have excel installed and reference the Office interop libraries in your project. Add:

Imports Microsoft.Office.Core, Imports Microsoft.Office.Interop

The solution above takes the gridview and pulls the html out of it. Then writes it to excel. The html coming from the grid will include style attributes such as padding & color. It will also make sortable column headings look like links. When I used this I wrote a custom parser to strip all the unwanted styling out to give me just the raw data. I will leave that task up to you since it is specific to each grid.

It is very important that you include the override to VerifyRenderingInServerForm, even though there is not any code inside of it.

Answer 5

There's probably something out there for this, but if you want to do it yourself you could just write some code that walks the GridView.Rows collection, and then the GridViewRow.Cells collection inside of that.

It should be pretty easy to build a CSV file from there, and Excel could read it no problem.

CSV files are just text files with values inside of quotes, seperated by commas. Like this:

"value", "value", "value"
"value", "value", "value"

You can just pop Notepad open and build one by hand to try it out.

Answer 6

This library for .net has worked very well for our use cases.

This library allows you to generate Excel Workbooks using XML, it is built 100% in C# and does not requires Excel installed at all to generate the files. It exposes a simple object model to generate the XML Workbooks.

There's no built-in integration with a GridView control, but writing a generic adapter is easy enough and would be reusable in other projects.