CODEWITHSUNDEEP
vb.netexcelasp.net-2.0export-to-excel
Joe Phillips
Joe Phillips

Reputation: 51200

Export to Excel

I would like to export a GridView to excel, which is easy enough. But above the grid, in Excel, I would like some other information for identification. Can I somehow export things other than gridviews while then putting in the gridview below?

Edit: For some reason when the GridView1 is visible and I try to export, the entire page exports and not just the gridview. Not sure why!

Protected Sub btnExport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExport.Click
    'Create a StringWriter and HtmlTextWriter
    Dim sw As System.IO.StringWriter = New System.IO.StringWriter()
    Dim htw As New System.Web.UI.HtmlTextWriter(sw)

    'Clear the Response object's content and specify the header for the HTML response and type of application file to create
    Response.ClearContent()
    Response.AddHeader("content-disposition", "attachment; filename=SaveFile.xls")
    Response.ContentType = "application/vnd.ms-excel"
    Response.Charset = ""
    EnableViewState = False

    htw.WriteLine("Test, test, test")

    Try
        'Check for the number of GridView rows
        If GridView1.Rows.Count < 65535 Then
            'Turn sorting and paging off and rebind the GridView control
            GridView1.AllowSorting = False
            GridView1.AllowPaging = False
            GridView1.PageSize = GridView1.Rows.Count
            GridView1.AutoGenerateSelectButton() = False
            GridView1.DataBind()


            'Render the GridView1 as HTML - this will cause an error that will fire the VerifyRenderingInServerForm event -- this event is trapped by the Overriding sub procedure given at the end of the program listing
            GridView1.RenderControl(htw)

            'Write the response
            Response.Write(sw.ToString())
            Response.End()

            'Turn sorting and paging on and rebind the GridView control
            GridView1.AllowSorting = True
            GridView1.AllowPaging = True
            '.GridView1.PageSize = 10
            GridView1.AutoGenerateSelectButton() = True
            GridView1.DataBind()
        End If
    Catch ex As Exception

    End Try

End Sub

Upvotes: 1

Views: 3693

Answers (5)

Daniel Benitez
Daniel Benitez

Reputation: 236

Opening this file in Excel will generate a warning message. I would use one of the open-source export libraries like NPOI. http://npoi.codeplex.com/

If you still prefer to use the HTML output, you may consider downloading Microsoft's documentation on the Office HTML format from this link: http://msdn.microsoft.com/en-us/library/aa155477%28office.10%29.aspx

You only need the CHM file from this archive (packed in EXE).

Good luck.

Upvotes: 1

Mike Gledhill
Mike Gledhill

Reputation: 29213

If your GridView is filled using data from a DataTable, DataSet or List<> then the following library will let you export it to an Excel 2007 (.xlsx) file simply by calling one "CreateExcelDocument" function.

// Step 1: Create a DataSet, and put some sample data in it
DataSet ds = CreateSampleData();

// Step 2: Create the Excel .xlsx file
try
{
    string excelFilename = "C:\\Sample.xlsx";
    CreateExcelFile.CreateExcelDocument(ds, excelFilename);
}
catch (Exception ex)
{ 
    MessageBox.Show("Couldn't create Excel file.\r\nException: " + ex.Message);
    return;
}

The full source code is provided, so you could adapt it, to add your extra rows of data, at the top of one or more of the worksheets.

This library uses the Open XML libraries, so it's completely free. http://www.mikesknowledgebase.com/pages/CSharp/ExportToExcel.htm

Upvotes: 0

Christian Ernst Rysgaard
Christian Ernst Rysgaard

Reputation: 133

Here is my code for doing the same

protected void ExportExcel_OnClick(object sender, EventArgs e) {
    Response.Clear();
    Response.AddHeader("content-disposition", "attachment;filename=brugere.xls");
    Response.Charset = "windows-1252";
    Response.ContentType = "application/vnd.xls";
    using (StringWriter stringWrite = new StringWriter())
    using (HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite)) {
        GridView1.AllowPaging = false;
        GridView1.DataBind();
        GridView1.RenderControl(htmlWrite);
        string html = stringWrite.ToString();
        string result = Replacer.Replace(html, "");
        Response.Write(result);
    }
    Response.End();
}

Notice that im trimming the resulting html using a regular expression to avoid formatting, images, divs and whatnots.

static readonly Regex Replacer = new Regex("(<input[^<>]*>)|"+
  "(class=\"[^\"]*\")|(style=\"[^\"]*\")|"+
  "(<a[^]*>)|(</a>)|(<div>)|(</div>)|" +
  "(cellspacing=\"[^\"]*\")|(cellpadding=\"[^\"]*\")|" +
  "(id=\"[^\"]*\")|(border=\"[^\"]*\")", RegexOptions.IgnoreCase);

Remember to override the following to ensure that grid will render outside a Page

public override void VerifyRenderingInServerForm(Control control) {
    return; 
}

Upvotes: 1

flalar
flalar

Reputation: 1221

If you want to export your content to ExcelML check out the RadGrid from Telerik

You can also insert header info into the grid etc

Upvotes: 1

Eppz
Eppz

Reputation: 3226

Yes you can.

Do something like this:

HttpContext.Current.Response.Write("some string value")

before you pass your gridview.

Upvotes: 3

Related Questions