C# 将网格视图导出到 excel 并将 excel 文件保存到文件夹
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10411520/
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
Export Grid view to excel and save excel file to folder
提问by R.D.
I want to save excel file which export data of grid view. I have written code to export gridview data to excel but I don't know how to save exported file.
我想保存导出网格视图数据的excel文件。我已经编写了将 gridview 数据导出到 excel 的代码，但我不知道如何保存导出的文件。
Following is my code to export gridview into excel :
以下是我将 gridview 导出到 excel 的代码：
Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment;filename=MyFiles.xls");
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
gvFiles.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
采纳答案by npclaudiu
You can do this:
你可以这样做：
private void ExportGridView()
{
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
// Render grid view control.
gvFiles.RenderControl(htw);
// Write the rendered content to a file.
string renderedGridView = sw.ToString();
System.IO.File.WriteAllText(@"C:\Path\On\Server\ExportedFile.xlsx", renderedGridView);
}
回答by Remko Jansen
You're asking how to save the exported file... Your code puts the rendered contents of the gridview (HTML) into the Response. In this case your browser (client-side) will receive this response and pops-up a dialog asking where to save it. So your browserwill save the file for you.
您在询问如何保存导出的文件...您的代码将 gridview (HTML) 的呈现内容放入响应中。在这种情况下，您的浏览器（客户端）将收到此响应并弹出一个对话框，询问将其保存在哪里。所以你的浏览器会为你保存文件。
If you want to save it server side you must not put the rendered gridview into the response. Write it to a file on the local hard disc instead (like the answer above shows).
如果您想将其保存在服务器端，则不得将呈现的 gridview 放入响应中。将其写入本地硬盘上的文件（如上面的答案所示）。
Remember that, in an environment other than your own development machine (i.e. a production environment), the ASP.NET worker process might not have enough access rights to write to the specified location on the hard disc. Here are a couple of answers that adresses that problem:
请记住，在您自己的开发机器以外的环境（即生产环境）中，ASP.NET 工作进程可能没有足够的访问权限来写入硬盘上的指定位置。以下是解决该问题的几个答案：
ASP.net user account permissions
ASP.NET + Access to the path is denied
System.UnauthorizedAccessException: Access to the path is denied
ASP.net 用户帐户权限
ASP.NET + 对路径的访问被拒绝
System.UnauthorizedAccessException：对路径的访问被拒绝
回答by writeToBhuwan
this may help you//
这可能对你有帮助//
protected void exporttoexcel_Click(object sender, EventArgs e)
{
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" attachment" + ".xls");
Response.Charset = "";
// If you want the option to open the Excel file without saving than
// comment out the line below
// Response.Cache.SetCacheability(HttpCacheability.NoCache);
Response.ContentType = "application/vnd.xls";
System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite =
new HtmlTextWriter(stringWrite);
GridView1.RenderControl(htmlWrite);
Response.Write(stringWrite.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
// Confirms that an HtmlForm control is rendered for the
//specified ASP.NET server control at run time.
}
回答by Mansi
Folowed the link: C# Excel file OLEDB read HTML IMPORT
按照链接：C# Excel 文件 OLEDB 读取 HTML 导入
Use the Extended Properties=\"HTML Import;HDR=No;IMEX=1 the select * from [tablename],
tablename is returned from GetOleDbSchemaTable.
使用Extended Properties=\"HTML Import;HDR=No;IMEX=1 the select * from [tablename]，表名是从 返回的GetOleDbSchemaTable。
Note: This would not load the normal excel. For that use Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\, where table name will be with $sign.
注意：这不会加载普通的 excel。对于该用途Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\，表名将带有$符号。
Check sample code: Can't read excel file after creating it using File.WriteAllText() function
回答by user2089688
public partial class exportgridtoexcel : System.Web.UI.Page
{
SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString.ToString());
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
GetData();
}
}
public void GetData()
{
SqlDataAdapter sda = new SqlDataAdapter("select * from EmpData", con);
DataTable dt = new DataTable();
sda.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
protected void btnDownload_Click(object sender, EventArgs e)
{
GetData();
exporttoexcel("Report.xls", GridView1);
GridView1 = null;
GridView1.Dispose();
}
public void exporttoexcel(string filename,GridView gv)
{
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment;filename=" + filename);
Response.ContentType = "applicatio/excel";
StringWriter sw = new StringWriter(); ;
HtmlTextWriter htm=new HtmlTextWriter(sw);
gv.RenderControl(htm);
Response.Write(sw.ToString());
Response.End();
}
public override void VerifyRenderingInServerForm(Control control)
{
}
}
}
}
回答by Ankur
One another solution for this problem solved here : http://codentq.wordpress.com/2014/02/13/write-excel-file-from-dataset/
这里解决了这个问题的另一种解决方案：http: //codentq.wordpress.com/2014/02/13/write-excel-file-from-dataset/
you have to get your grid view data in data set by method mention on this article : http://codentq.wordpress.com/2014/02/13/get-data-from-grid-to-datatable-or-dataset/
您必须通过本文中提到的方法在数据集中获取网格视图数据：http: //codentq.wordpress.com/2014/02/13/get-data-from-grid-to-datatable-or-dataset/
回答by santhana rajagopal
first add EPPLUS reference library into application and add using OfficeOpenXml;
首先将EPPLUS参考库添加到应用程序中，并使用OfficeOpenXml添加；
//business object class
//业务对象类
class bocls {
类 bocl {
string name;
public string NAME
{
get { return name; }
set { name = value; }
}
string id;
public string ID
{
get { return id; }
set { id = value; }
}
public bocls() { }
public bocls(string name, string id)
{
this.name = name;
this.id = id;
}
//in export button click event
//在导出按钮点击事件中
protected void lbtnExport_Click(object sender, EventArgs e) {
受保护的无效 lbtnExport_Click（对象发送者，EventArgs e）{
List<bocls> list6 = new List<bocls>();
//copy the grid view values into list
list6 = (from row in dataGridView1.Rows.Cast<DataGridViewRow>()
from cell in row.Cells.Cast<DataGridViewCell>()
select new
{
//project into your new class from the row and cell vars.
}).ToList();
}
ExcelPackage excel = new ExcelPackage();
var workSheet = excel.Workbook.Worksheets.Add("Products");
var totalCols = GridView1.Rows[0].Cells.Count;
var totalRows = GridView1.Rows.Count;
var headerRow = GridView1.HeaderRow;
for (var i = 1; i <= totalCols; i++)
{
workSheet.Cells[1, i].Value = headerRow.Cells[i - 1].Text;
}
for (var j = 1; j <= totalRows; j++)
{
for (var i = 1; i <= totalCols; i++)
{
var item = list6.ElementAt(j - 1);
workSheet.Column(1).Width = 13;
workSheet.Column(2).Width = 10;
workSheet.Cells[j + 1, i].Style.WrapText = true;
if (headerRow.Cells[i - 1].Text == "ID")
workSheet.Cells[j + 1, i].Value = item.GetType().GetProperty("id").GetValue(item, null);
else if (headerRow.Cells[i - 1].Text == "NAME")
workSheet.Cells[j + 1, i].Value = item.GetType().GetProperty("name").GetValue(item, null);
workSheet.Cells[j + 1, i].Value = workSheet.Cells[j + 1, i].Value.ToString().Replace("<br/>", "");
}
}
using (var memoryStream = new MemoryStream())
{
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
string filename = Guid.NewGuid().ToString() + ".xlsx";
Response.AddHeader("content-disposition", "attachment; filename=" + filename);
excel.SaveAs(memoryStream);
//add your destination folder
FileStream fileStream = new FileStream(@"C:\Users\karthi\Downloads\New folder\" + filename, FileMode.Create,FileAccess.Write,FileShare.Write);
memoryStream.WriteTo(fileStream);
fileStream.Close();
memoryStream.WriteTo(Response.OutputStream);
memoryStream.Close();
memoryStream.WriteTo(Response.OutputStream);
Response.Flush();
Response.End();
}
}
