如何将数据表中的数据导出到 asp.net c# mvc3 中的 Excel？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12453819/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can i export the data from a data table to excel in asp.net c# mvc3?
提问by Arun Kumar T
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.
目前我正在尝试将数据表中的数据导出到 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();
采纳答案by Viral Jain
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
回答by maxs87
U can use Microsoft.Office.Interop.Excel libary
你可以使用 Microsoft.Office.Interop.Excel 库
using Microsoft.Office.Interop.Excel;
using System.Reflection;
使用 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
文件下载。我使用搜索表单，所以当表单提交时没有 ajax 我返回 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();
}
}
