Click here to Skip to main content
Click here to Skip to main content

Tagged as

Go to top

Export GridView Data to Excel using OpenXml

, 15 Apr 2012
Rate this:
Please Sign up or sign in to vote.
ASP.NET GridView to Excel conversion.

Introduction

This article tells how to export ASP.NET GridView data to an Excel file using Office OpenXML SDK 2.0.

Background

Exporting GridView data to Excel is a common functionality you would have come across while working in web forms. There are many ways to do this. Indeed, couple of approaches require Microsoft ACE engine to be installed on the server. I thought why not we try do this using Office OpenXML?

Using the code

So I started with creating an ASPX page (would look like the below one…)

An Export button click event would do:

DataTable table = new DataTable();   // create an empty data table
CreateTable(grdvTest, ref table);    // fill it with grid view data 
// ExportToExcel(table);
// copy this file from Temp directory to the current working directory
// Response.Redirect(localCopy); 
CreateTable(grdvTest, ref table);    // fill it with grid view data 
// ExportToExcel(table);
// copy this file from Temp directory to the current working directory 
// Response.Redirect(localCopy);

Let’s see what these method internally do!

  1. CreateTable will create columns and populates the row.
  2. for (int i = 0; i < grdvTest.HeaderRow.Cells.Count; i++)
        table.Columns.Add(grdvTest.HeaderRow.Cells[i].Text);             
    // fill rows             
    foreach (GridViewRow row in grdvTest.Rows)             
    {                 
        DataRow dr;                 
        dr = table.NewRow();                 
        for (int i = 0; i < row.Cells.Count; i++)
        {
              dr[i] = row.Cells[i].Text.Replace("&nbsp;", "");
        }
        table.Rows.Add(dr);
    }
  3. Export to Excel will create a file in systems temporary location and returns the file name.
  4. string excelfile = Path.GetTempPath() +
                            Guid.NewGuid().ToString() + ".xlsx";            
    using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Create(
        excelfile,
        DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
        {
          CreateExcelParts(excelDoc, table);
        }
    return excelfile;
  5. Once the temporary file ready, stream it to client using Response.Redirect. The Excel file would look like:

Points of Interest

This method actually creates an Excel file on server (without using interops) and streams the file to client.

History

This article can further be extended to include style information during data export.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

pramodhegde88
Software Developer
India India
No Biography provided

Comments and Discussions

 
QuestionI want to ask for extra sheet is not active PinmemberMember 1058710413-Feb-14 16:23 
QuestionExport usinng open xml Pinmembernilesh319-Dec-13 7:02 
QuestionMake XL Cells readonly Pinmember$aikumar$ Cilamkoti18-Nov-13 21:20 
QuestionHow to add sheets??? Pinmemberfense5-Mar-13 5:41 
Questionloop thru dropdownlist of gridview Pinmembersayedalishakeel20-Apr-12 3:37 
AnswerRe: loop thru dropdownlist of gridview Pinmemberpramodhegde8821-Apr-12 0:04 
GeneralRe: loop thru dropdownlist of gridview Pinmembersayedalishakeel23-Apr-12 19:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.141002.1 | Last Updated 15 Apr 2012
Article Copyright 2012 by pramodhegde88
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid