How can i export the data from a data table to excel in asp.net c# mvc3?

Question

Currently i am trying to export the data from the data table to the excel sheet in c# asp.net mvc3. I surf on the internet and found some codes to export the datatable to the excel sheet. It executes with out any error. But no excel sheet will be created. Any suggestions to do this.

            string filename = "DownloadMobileNoExcel.xls";
            DataGrid dgGrid = new DataGrid();
            dgGrid.DataSource = dt;
            dgGrid.DataBind();
            Response.Clear();
            Response.AddHeader("content-disposition", "attachment;filename=" 
              + filename + "");
            Response.Charset = "";
            Response.Cache.SetCacheability(HttpCacheability.NoCache);
            Response.ContentType = "application/vnd.ms-excel";

            //Convert the rendering of the gridview to a string representation 
            StringWriter sw = new StringWriter();
            HtmlTextWriter htw = new HtmlTextWriter(sw);
            dgGrid.RenderControl(htw);

            //Open a memory stream that you can use to write back to the response
            byte[] byteArray = Encoding.ASCII.GetBytes(sw.ToString());
            MemoryStream s = new MemoryStream(byteArray);
            StreamReader sr = new StreamReader(s, Encoding.ASCII);

            //Write the stream back to the response
            Response.Write(sr.ReadToEnd());
            Response.End();

Viral Jain · Accepted Answer · 2012-09-17 06:33:23Z

3

protected void ExportToExcel(object sender, EventArgs e)
{

    //Get the data from database into datatable
    string strQuery = "select CustomerID, ContactName, City, PostalCode" +" from customers";
    SqlCommand cmd = new SqlCommand(strQuery);
    DataTable dt = GetData(cmd);
    //Create a dummy GridView
    GridView GridView1 = new GridView();
    GridView1.AllowPaging = false;
    GridView1.DataSource = dt;
    GridView1.DataBind();
    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition","attachment;filename=DataTable.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);
    for (int i = 0; i < GridView1.Rows.Count; i++)
    {
        //Apply text style to each Row
        GridView1.Rows[i].Attributes.Add("class", "textmode");
    }
    GridView1.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();
}

Source: http://www.aspsnippets.com/Articles/Export-DataSet-or-DataTable-to-Word-Excel-PDF-and-CSV-Formats.aspx

answered Sep 17, 2012 at 6:33

Viral Jain

1,0141 gold badge15 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Arun Kumar T Over a year ago

Sorry Daredev. This also is executing well but no excel sheet will be created :(

Viral Jain Over a year ago

@ArunKumarT: But it works for me... :) Is dataTable really having any data? Try binding your datatble to a pre-existing gridview to make sure that data exists

Arun Kumar T Over a year ago

Ya it consists of data. I watched it with the help of break point. And i am using MVC. Are you tried in MVC?

Viral Jain Over a year ago

@ArunKumarT: Ohh... yeah... missed the MVC & Response disconnection... Try this: if it could help in someway: public class MyActionResult : ActionResult { public override void ExecuteResult(ControllerContext context) { context.HttpContext.Response.Write("something"); } } Make appropraite changes... :)

Viral Jain Over a year ago

ArunKumarT: Try this as well... billsternberger.net/asp-net-mvc/…

|

maxs87 · Accepted Answer · 2012-09-17 09:07:00Z

1

U can use Microsoft.Office.Interop.Excel libary

using Microsoft.Office.Interop.Excel;

using System.Reflection;

     Microsoft.Office.Interop.Excel.Application ObjExcel = new Microsoft.Office.Interop.Excel.Application();
            Microsoft.Office.Interop.Excel.Workbook ObjWorkBook;
            Microsoft.Office.Interop.Excel.Worksheet ObjWorkSheet;
            ObjWorkBook = ObjExcel.Workbooks.Add(System.Reflection.Missing.Value);
            ObjWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ObjWorkBook.Sheets[1];

            int counter = 1;
            foreach (var item in SomeDataCollection)
            {
                ObjWorkSheet.Cells[counter, 1] = item.some_property1;
                ObjWorkSheet.Cells[counter, 2] = item.some_property2;
                ObjWorkSheet.Cells[counter, 3] = item.some_property3;     
                counter++;
            }
            ObjWorkSheet.Columns.AutoFit();
            ObjWorkSheet.Columns.get_Range("M1", "M60").ColumnWidth = 25;
    string filename;    
            string[] file_parts = Convert.ToString(DateTime.Now).Split(' ');
            filename = file_parts[0].Replace("/", ".") + "(" + file_parts[1].Replace(":", "-") + ")" + "-file.xls";
            string save_path = AppDomain.CurrentDomain.BaseDirectory + "Content/files/" + filename;

            ObjWorkBook.SaveAs(save_path, Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal,
                                            System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                            System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
                                            System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                            System.Reflection.Missing.Value, System.Reflection.Missing.Value,
                                            System.Reflection.Missing.Value);

            ObjWorkBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            ObjExcel.Workbooks.Close();
            ObjExcel.Quit();

file download. im using search form, so when form submited with no ajax im return excel file else just returl list of items

[HttpPost]
    public ActionResult Search(MyModel model)
    {
        model.GetCollection();
        if (Request.IsAjaxRequest())
        {
            return PartialView("_listItems", model.SomeCollection);
        }
        else
        {           
            string filename = model.GetExcel();
            string save_path = AppDomain.CurrentDomain.BaseDirectory + "Content/Personal/" + filename;
            HttpContext.Response.Clear();
            HttpContext.Response.ClearContent();
            HttpContext.Response.ClearHeaders();
            HttpContext.Response.AddHeader("content-disposition", string.Format("attachment; filename={0}", filename));
            HttpContext.Response.ContentType = "application/excel";
            HttpContext.Response.WriteFile(save_path);
            HttpContext.Response.End();
            System.IO.File.Delete(save_path);
            return new EmptyResult();
        }              

    }

edited Sep 17, 2012 at 9:07

answered Sep 17, 2012 at 6:38

maxs87

2,2242 gold badges19 silver badges25 bronze badges

10 Comments

Arun Kumar T Over a year ago

sorry maxs87. I cant able to access the "using Microsoft.Office.Interop.Excel;" alternatively i can able to access only outlook.

Arun Kumar T Over a year ago

How can i access the "using Microsoft.Office.Interop.Excel;" i am using excel 2007. Weather i need to install any thing?

maxs87 Over a year ago

Sorry i cant remember from where i get this lib. this assembly loads from C:\Program Files\Microsoft Visual Studio 10.0\Visual Studio Tools for Office\PIA\Office12\Microsoft.Office.Interop.Excel.dll

maxs87 Over a year ago

this will only works if request is not ajax so before submit u need to set ajax property in ur form tag to false with javascript or use another way. For example load exel from some link.

maxs87 Over a year ago

try $("#form_id").attr("data-ajax", "false").attr("method", "post");$("#form_id").submit(); for excel only

|

Collectives™ on Stack Overflow

How can i export the data from a data table to excel in asp.net c# mvc3?

2 Answers 2

7 Comments

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related