Export DataTable to Excel in Asp.Net without using excel interop – Part II

Posted on April 20th, 2012 at 1:38 pm

3 Comments »

In my earlier post i have shown a way to export a datatable  as a excel document. But when we open the exported document, the data and all would be as expected but the gridlines that excel displays by default would be hidden. So again see these the user had to go to ribbon View Tab and should check the GridLines Checkbox to view those gridlines. But my reuirement was to enable them by default so that user would be comfortable as it would be the defaukt behavior of excel. Also I had to set the worksheet name. So to achive these two functionalities I had to modify the method “ExportToExcel” from my previous implementation.

private void ExportToExcel(DataTable dt, string fileName, string worksheetName)
        {
            Response.Clear();
            Response.AddHeader("content-disposition", "attachment;filename="+ fileName + "");
            Response.ContentType = "application/vnd.ms-excel";

            StringWriter stringWriter = new StringWriter();
            HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWriter);
            DataGrid dataExportExcel = new DataGrid();
            dataExportExcel.ItemDataBound += new DataGridItemEventHandler(dataExportExcel_ItemDataBound);
            dataExportExcel.DataSource = dt;
            dataExportExcel.DataBind();
            dataExportExcel.RenderControl(htmlWrite);
            StringBuilder sbResponseString = new StringBuilder();
            sbResponseString.Append("<html xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:o=\"urn:schemas-microsoft-com:office:office\" xmlns:x=\"urn:schemas-microsoft-com:office:excel\" xmlns=\"http://www.w3.org/TR/REC-html40\"> <head><meta http-equiv=\"Content-Type\" content=\"text/html;charset=windows-1252\"><!--[if gte mso 9]><xml><x:ExcelWorkbook><x:ExcelWorksheets><x:ExcelWorksheet><x:Name>"+ worksheetName +"</x:Name><x:WorksheetOptions><x:Panes></x:Panes></x:WorksheetOptions></x:ExcelWorksheet></x:ExcelWorksheets></x:ExcelWorkbook></xml><![endif]--></head> <body>");
            sbResponseString.Append(stringWriter + "</body></html>");
            Response.Write(sbResponseString.ToString());
            Response.End();
        }

        void dataExportExcel_ItemDataBound(object sender, DataGridItemEventArgs e)
        {
            if (e.Item.ItemType == ListItemType.Header)
            {
                //Header Text Format can be done as follows
                e.Item.Font.Bold = true;

                //Adding Filter/Sorting functionality for the Excel
                int cellIndex = 0;
                while (cellIndex < e.Item.Cells.Count)
                {
                    e.Item.Cells[cellIndex].Attributes.Add("x:autofilter", "all");
                    e.Item.Cells[cellIndex].Width = 200;
                    e.Item.Cells[cellIndex].HorizontalAlign = HorizontalAlign.Center;
                    cellIndex++;
                }
            }

            if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
            {
                int cellIndex = 0;
                while (cellIndex < e.Item.Cells.Count)
                {
                    //Any Cell specific formatting should be done here
                    e.Item.Cells[cellIndex].HorizontalAlign = HorizontalAlign.Left;
                    cellIndex++;
                }
            }
        }

 

So the above method has three parameters. First one is the datatable whose data needs to be exported. Second is the filename of the document that is exported and the third is the worksheet name.

Source code of the demo app can be downloaded from below link

Download Source Code

Categories : ASP.Net, DotNet, MS Excel
Tags : , , ,
Author : Phani Bitra

Interested in this topic? You might enjoy another article we've written called

  1. Export DataTable to Excel in Asp.Net with sorting/filtering support without using excel interop
  2. Export data from ASP.NET application to WORD Document
  3. How to copy DataColumn from one DataTable to another
  4. Column already belongs to another DataTable – Error
  5. Basics – How to convert byte[] into file

3 Responses to “Export DataTable to Excel in Asp.Net without using excel interop – Part II”

  1. Praveen Says:
    May 29th, 2012 at 3:29 pm

    Getting Following Error

    The file you are trying to open, , is in a different format than that specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?

  2. Phani Bitra Says:
    June 20th, 2012 at 9:14 am

    Hey praveen, i was out for some time so couldn’t respond to you in time. But yes you’ll see that warning when you try to open the file because the content is html in the document and we gave the extension as xls. you can just accept and go ahead and open it shows up normally. Once you save that document then from thereafter it wont popup that warning cause the Html is then converted into excel native format on save. I did not find any way yet to avoid that warning.

  3. Andy Says:
    June 20th, 2012 at 4:02 pm

    Phani –

    You cannot make this message go away in MS office 2007 or later – this is a “feature” put in place by MS, in their inscrutable wisdom.

Leave a Reply