如何从数据表中导出数据以在asp.net c＃mvc3中实现excel？

Question

目前我正试图从数据表中导出数据到c＃asp.net mvc3中的excel表单。我在网上冲浪，发现一些代码将数据表导出到Excel表。它执行没有任何错误。但是不会创建Excel表格。任何建议要做到这一点。

  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(); 

Answer 1

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(); 
} 

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

Answer 2

U可以使用的Microsoft.Office.Interop.Excel libary使用的System.Reflection

using Microsoft.Office.Interop.Excel; 

;

 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(); 

文件下载。即时通讯使用搜索表单，所以当表单submited没有ajax IM返回Excel文件其他只是返回物品清单

[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(); 
     }    

    }