如何在Asp .net（C＃）中的Excel分隔表中导出多个Gridview [英] how to export multple Gridview in Excel Seperated Sheets in Asp .net (C#)
本文介绍了如何在Asp .net（C＃）中的Excel分隔表中导出多个Gridview的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我的页面上有2个网格视图，我需要在excel中导出相同的
Gridview1到Excel（工作表1）
Gridview1到Excel（工作表2）
请帮帮我...
Awaitng积极回应。
问候，
Rohini G
Pune
解决方案
你可以用EPPLUS（免费）创建excel文件
（你可以用nuget下载它）
你可以制作一个检索数据源的通用方法
并将它用于网格生成和excel生成
或
你可以声明一个每次填写网格时，ExcelPackage（epplus excel文件）和空+填充
所以你的导出按钮只会在响应上放一个完整的文件。
示例：
http://epplus.codeplex.com/wikipage ？标题= WebapplicationExample [ ^ ]
有什么问题？
Single Gridview：将Gridview数据导出到ASP.NET中的Excel [ ^ ]
多个Gridview：多个GRIDVIEW进入多个工作表 [ ^ ]
- Amy
CLient Side ：
----------------------------- -----------
< pre lang =xml>& lt; form id =& quot; form1& quot; RUNAT =安培; QUOT;服务器&安培; QUOT;&安培; GT;
& lt; div& gt;
& lt; table& gt;
& lt; tr& gt;
& lt; td& gt;
& lt; asp：GridView ID =& quot; gdvreport1& quot; RUNAT =安培; QUOT;服务器&安培; QUOT;&安培; GT;
& lt; / asp：GridView& gt;
& lt; / td& gt;
& lt; / tr& gt;
& lt; br /& gt;& lt; br /& gt;
& lt; tr& gt;
& lt; td& gt;
& lt; asp：GridView ID =& quot; grvreport2& quot; RUNAT =安培; QUOT;服务器&安培; QUOT;&安培; GT;
& lt; / asp：GridView& gt;
& lt; / td& gt;
& lt; / tr& gt;
& lt; tr& gt;
& lt; td& gt;
& lt; asp：按钮ID =& quot; btnget& quot; RUNAT =安培; QUOT;服务器&安培; QUOT;文本=安培; QUOT;&的GetData放大器; QUOT;的OnClick =安培; QUOT; btnget_Click&安培; QUOT; /&安培; GT;
& lt; / td& gt;
& lt; td& gt;
& lt; asp：按钮ID =& quot; btnexport& quot; RUNAT =安培; QUOT;服务器&安培; QUOT;文本=安培; QUOT;出口与安培; QUOT;的OnClick =安培; QUOT; btnexport_Click&安培; QUOT; /&安培; GT;
& lt; / td& gt;
& lt; / tr& gt;
& lt; / table& gt;
& lt; / div& gt;
& lt; / form& gt;< / pre>
服务器端：
----------------------- --------------------
< pre> using System;
使用System.Collections.Generic;
使用System.Linq;
使用System.Web;
使用System.Web.UI;
使用System.Web.UI.WebControls;
使用MDIndia.ClassLibrary.ApplicationSupport;
使用System.Data;
使用Microsoft.Office.Interop.Excel;
使用Excel = Microsoft.Office.Interop.Excel;使用System.Reflection
;
使用System.IO;
使用System.Text;
使用System.Web.UI.HtmlControls;
公共部分类NewTwo：System.Web.UI.Page
{
protected void Page_Load（object sender，EventArgs e ）
{
}
protected void btnget_Click（object sender，EventArgs e）
{
try
{
}
catch（例外）
{
throw;
}
}
private void GetconnectToAuth（）
{
DBManager.CurrentDatabaseProvider = DatabaseProvider.MSSQLServer;
DBManager.ConnectionString =数据源= MDICENTRAL \\ AUTHORISATION;初始目录= PIMS;用户ID = mdindia1;密码= md！nd！@@ 123;
}
protected void btnexport_Click（object sender，EventArgs e）
{
GetconnectToAuth（）;
string query1 =从ICMaster中选择ID，ICName;
System.Data.DataTable dt = new System.Data.DataTable（）;
dt = DBManager.SelectDataTable（query1）;
System.Data.DataTable dt2 = new System.Data.DataTable（）;
query1 =从LabMaster中选择LabID，LabName;
dt2 = DBManager.SelectDataTable（query1）;
dt2.TableName =B;
DataSet dataset = new DataSet（）;
dataset.Tables.Add（dt.Copy（））;
dataset.Tables.Add（dt2.Copy（））;
Excel.Application excel = new Excel.Application（）;
var workbook =（Excel._Workbook）（excel.Workbooks.Add（Missing.Value））;
for（var i = 0; i& lt; dataset.Tables.Count; i ++）
{
if（workbook.Sheets.Count& lt; = i） ）
{
workbook.Sheets.Add（Type.Missing，Type.Missing，Type.Missing，
Type.Missing）;
}
var currentSheet =（Excel._Worksheet）workbook.Sheets [i + 1];
switch（Convert.ToInt32（i））
{
case 0：
currentSheet.Name =Projects;
休息;
case 1：
currentSheet.Name =Employees;
休息;
}
for（var j = 0; j& lt; dataset.Tables [i] .Columns.Count; j ++）
{
currentSheet.Cells [1，j + 1] = dataset.Tables [i] .Columns [j] .ToString（）;
}
for（var y = 0; y& lt; dataset.Tables [i] .Rows.Count; y ++）
{
for（var x = 0; x& lt; dataset.Tables [i] .Rows [y] .ItemArray.Count（）; x ++）
{
currentSheet.Cells [y + 2，x + 1] = dataset.Tables [i] .Rows [y] .ItemArray [x];
}
}
}
workbook.SaveAs（Server.MapPath（Test.xlsx），Type.Missing，Type.Missing，Type.Missing，
Type.Missing，Type.Missing，Excel.XlSaveAsAccessMode.xlNoChange，
Type.Missing，Type.Missing，Type.Missing，Type.Missing，Type.Missing）;
workbook.Close（true，Type.Missing，Type.Missing）;
excel.Quit（）;
var filePath = Server.MapPath（Test.xlsx）;
FileInfo targetFile = new System.IO.FileInfo（filePath）;
try
{
FileInfo objFileInfo = new FileInfo（filePath）;
Response.Clear（）;
Response.Cache.SetCacheability（System.Web.HttpCacheability.Private）;
Response.AddHeader（Content-Disposition，（attachment; filename =+ objFileInfo.Name））;
Response.AddHeader（Content-Length，objFileInfo.Length.ToString（））;
Response.ContentType =application / octet-stream;
Response.WriteFile（filePath）;
Response.End（）;
}
catch（例外情况）
{
}
}
}< / pre>
I have 2 gridviews on my page which I need to export the same in excel as
Gridview1 to Excel (Sheet 1)
Gridview1 to Excel (Sheet 2)
Please help me on this ...
Awaitng for positive response.
Regards,
Rohini G
Pune
解决方案
you can use EPPLUS (free) to create the excel file
(you can download it with nuget)
you can make a generic method to retrieve the datasource
and use it both for grid generation and excel generation
OR
you can declare a ExcelPackage (the epplus excel file) and empty+fill every time you fill the grids
so your "export" button will only put a full file on the response.
Example :
http://epplus.codeplex.com/wikipage?title=WebapplicationExample[^]
What is the problem in that?
Single Gridview : Export Gridview Data to Excel in ASP.NET[^]
Multiple Gridview : MULTIPLE GRIDVIEW INTO MULTIPLE WORKSHEET[^]
--Amy
CLient Side :
----------------------------------------
<pre lang="xml"><form id="form1" runat="server">
<div>
<table>
<tr>
<td>
<asp:GridView ID="gdvreport1" runat="server">
</asp:GridView>
</td>
</tr>
<br /><br />
<tr>
<td>
<asp:GridView ID="grvreport2" runat="server">
</asp:GridView>
</td>
</tr>
<tr>
<td>
<asp:Button ID="btnget" runat="server" Text="GetData" OnClick="btnget_Click" />
</td>
<td>
<asp:Button ID="btnexport" runat="server" Text="Export" OnClick="btnexport_Click" />
</td>
</tr>
</table>
</div>
</form></pre>
Server Side :
-------------------------------------------
<pre>using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using MDIndia.ClassLibrary.ApplicationSupport;
using System.Data;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
using System.Text;
using System.Web.UI.HtmlControls;
public partial class NewTwo : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnget_Click(object sender, EventArgs e)
{
try
{
}
catch (Exception)
{
throw;
}
}
private void GetconnectToAuth()
{
DBManager.CurrentDatabaseProvider = DatabaseProvider.MSSQLServer;
DBManager.ConnectionString = "Data Source=MDICENTRAL\\AUTHORISATION;Initial Catalog=PIMS;User ID=mdindia1;Password=md!nd!@@123";
}
protected void btnexport_Click(object sender, EventArgs e)
{
GetconnectToAuth();
string query1 = "Select ID,ICName from ICMaster";
System.Data.DataTable dt = new System.Data.DataTable();
dt = DBManager.SelectDataTable(query1);
System.Data.DataTable dt2 = new System.Data.DataTable();
query1 = "Select LabID,LabName from LabMaster";
dt2 = DBManager.SelectDataTable(query1);
dt2.TableName = "B";
DataSet dataset = new DataSet();
dataset.Tables.Add(dt.Copy());
dataset.Tables.Add(dt2.Copy());
Excel.Application excel = new Excel.Application();
var workbook = (Excel._Workbook)(excel.Workbooks.Add(Missing.Value));
for (var i = 0; i < dataset.Tables.Count; i++)
{
if (workbook.Sheets.Count <= i)
{
workbook.Sheets.Add(Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
}
var currentSheet = (Excel._Worksheet)workbook.Sheets[i + 1];
switch (Convert.ToInt32(i))
{
case 0:
currentSheet.Name = "Projects";
break;
case 1:
currentSheet.Name = "Employees";
break;
}
for (var j = 0; j < dataset.Tables[i].Columns.Count; j++)
{
currentSheet.Cells[1, j + 1] = dataset.Tables[i].Columns[j].ToString();
}
for (var y = 0; y < dataset.Tables[i].Rows.Count; y++)
{
for (var x = 0; x < dataset.Tables[i].Rows[y].ItemArray.Count(); x++)
{
currentSheet.Cells[y + 2, x + 1] = dataset.Tables[i].Rows[y].ItemArray[x];
}
}
}
workbook.SaveAs(Server.MapPath("Test.xlsx"), Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
excel.Quit();
var filePath = Server.MapPath("Test.xlsx");
FileInfo targetFile = new System.IO.FileInfo(filePath);
try
{
FileInfo objFileInfo = new FileInfo(filePath);
Response.Clear();
Response.Cache.SetCacheability(System.Web.HttpCacheability.Private);
Response.AddHeader("Content-Disposition", ("attachment; filename=" + objFileInfo.Name));
Response.AddHeader("Content-Length", objFileInfo.Length.ToString());
Response.ContentType = "application/octet-stream";
Response.WriteFile(filePath);
Response.End();
}
catch (Exception ex)
{
}
}
}</pre>这篇关于如何在Asp .net（C＃）中的Excel分隔表中导出多个Gridview的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
