如何将datagrid中得内容导出成excel,access等文件？

ppxeng 2004-06-22 11:24:08
如何将datagrid中得内容导出成excel,access等文件，下载储存到客户端？
...全文
420 25 打赏 收藏 转发到动态 举报
写回复
用AI写文章
25 条回复
切换为时间正序
请发表友善的回复…
发表回复
goody9807 2004-06-30
  • 打赏
  • 举报
 回复
<%@ Page Language="vb" EnableViewState="False" AutoEventWireup="false" Codebehind="DataGridToExcel.aspx.vb"
Inherits="aspxWeb.mengxianhui.com.DataGridToExcel"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title id="mengxianhui" runat="server"></title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout" style="FONT-SIZE:9pt">
<form id="Form1" method="post" runat="server">
<asp:Label id="Label1" runat="server"></asp:Label>
<asp:TextBox ID="xlfile" Runat="server"></asp:TextBox>
<br>
<br>
<asp:Button ID="ExportDataBase2Excel" Runat="server" />
<asp:Button ID="ExportDataGrid2Excel" Runat="server" />
<br>
<asp:DataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False" BorderColor="#CC9966"
BorderStyle="None" BorderWidth="1px" BackColor="White" CellPadding="4">
<ItemStyle ForeColor="#330099" BackColor="White"></ItemStyle>
<HeaderStyle Font-Bold="True" ForeColor="#FFFFCC" BackColor="#990000"></HeaderStyle>
<Columns>
<asp:BoundColumn DataField="Title"></asp:BoundColumn>
<asp:BoundColumn DataField="Author"></asp:BoundColumn>
</Columns>
</asp:DataGrid>
</form>
</body>
</HTML>

DataGridToExcel.aspx.vb

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports OWC

Public Class DataGridToExcel
Inherits System.Web.UI.Page
Protected WithEvents xlfile As System.Web.UI.WebControls.TextBox
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Protected WithEvents ExportDataGrid2Excel As System.Web.UI.WebControls.Button
Protected WithEvents ExportDataBase2Excel As System.Web.UI.WebControls.Button
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected mengxianhui As New HtmlGenericControl()

Private cnn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source="_
+ Server.MapPath("Test.mdb"))
Private sql As OleDbCommand = New OleDbCommand("SELECT TOP 50 Title,Author FROM Document", cnn)

#Region " Web Form Designer Generated Code "

'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

End Sub

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs)_
Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub

#End Region

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)_
Handles MyBase.Load
Label1.Text = "请输入要保存得文件名字："
ExportDataGrid2Excel.Text = "由DataGrid生成Excel文件"
ExportDataBase2Excel.Text = "数据库直接生成Excel文件"
DataGrid1.Columns(0).HeaderStyle.HorizontalAlign = HorizontalAlign.Center
DataGrid1.Columns(0).HeaderText = "文章名称"
DataGrid1.Columns(1).HeaderText = "作者"
DataGrid1.Columns(0).HeaderStyle.Font.Bold = True
DataGrid1.Style.Add("font-size", "9pt")
mengxianhui.InnerText = "【孟宪会之精彩世界】－ 将DataGrid输出到Excel文件"
Me.BindDataGrid()
End Sub

Private Sub BindDataGrid()
cnn.Open()
Dim reader As OleDbDataReader = sql.ExecuteReader()
Me.DataGrid1.DataSource = reader
Me.DataGrid1.DataBind()
reader.Close()
cnn.Close()
End Sub

Private Sub WriteDataGrid2Excel()
Dim xlsheet As New SpreadsheetClass()
cnn.Open()
Dim reader As OleDbDataReader = Me.sql.ExecuteReader()
Dim numbercols As Integer = reader.FieldCount
Dim row As Integer = 2
Dim i As Integer = 0
' 输出标题
For i = 0 To numbercols - 1
xlsheet.ActiveSheet.Cells(1, i + 1) = reader.GetName(i).ToString()
Next

' 输出字段内容
While (reader.Read())
For i = 0 To numbercols - 1
xlsheet.ActiveSheet.Cells(row, i + 1) = reader.GetValue(i).ToString()
Next
row = row + 1
End While
reader.Close()
cnn.Close()
Try
xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\Images\" + Me.xlfile.Text,_
OWC.SheetExportActionEnum.ssExportActionNone)
Catch e As System.Runtime.InteropServices.COMException
Response.Write("错误：" + e.Message)
End Try
End Sub

Private Sub WriteDataGrid2Excel2()
Dim xlsheet As New SpreadsheetClass()
Dim i As Integer = 0
Dim j As Integer = 0
'Response.End()
' 输出标题
Dim oItem As DataGridColumn
For Each oItem In DataGrid1.Columns
xlsheet.ActiveSheet.Cells(1, i + 1) = oItem.HeaderText
'xlsheet.ActiveSheet.Range(xlsheet.ActiveSheet.Cells(1, 1),_
xlsheet.ActiveSheet.Cells(1, i + 1)).Font.Bold = True
'设置格式
xlsheet.Range(xlsheet.Cells(1, 1), xlsheet.Cells(1, i + 1)).Font.Bold = True
xlsheet.Range(xlsheet.Cells(1, 1), xlsheet.Cells(1, i + 1)).Font.Color = "red"
i = i + 1
Next

Dim numbercols As Integer = DataGrid1.Items.Item(0).Cells.Count
' 输出字段内容
For j = 0 To DataGrid1.Items.Count - 1
For i = 0 To numbercols - 1
xlsheet.Range(xlsheet.Cells(2, 2), xlsheet.Cells(j + 2, i + 1)).Font.Color = "blue"
'xlsheet.Range("A2:B14").WrapText = True
xlsheet.Range(xlsheet.Cells(2, 1), xlsheet.Cells(j + 2, i + 1)).AutoFitColumns()
xlsheet.ActiveSheet.Cells(j + 2, i + 1) = DataGrid1.Items.Item(j).Cells(i).Text.Replace(" ", " ")
Next
Next
Try
xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\Images\" + Me.xlfile.Text,_
OWC.SheetExportActionEnum.ssExportActionNone)
Catch e As System.Runtime.InteropServices.COMException
Response.Write("错误：" + e.Message)
End Try
End Sub

Private Sub ExportDataGrid2Excel_Click(ByVal sender As Object,_
ByVal e As System.EventArgs) Handles ExportDataGrid2Excel.Click
If (Me.xlfile.Text.Trim() <> "") Then
Me.WriteDataGrid2Excel2()
End If
End Sub

Private Sub ExportDataBase2Excel_Click(ByVal sender As Object, _
ByVal e As System.EventArgs) Handles ExportDataBase2Excel.Click
If (Me.xlfile.Text.Trim() <> "") Then
Me.WriteDataGrid2Excel()
End If
End Sub

End Class

ILoveProgramer 2004-06-30
  • 打赏
  • 举报
 回复
顶
wczwcg 2004-06-29
  • 打赏
  • 举报
 回复
使用这种方法只能导出当前页的内容，其他的导出不了。
ppxeng 2004-06-28
  • 打赏
  • 举报
 回复
大家用过这个页面上的“保存”功能吗？会出现一个对话框选择保存位置和文件类型输入文件名。我希望datagrid得保存也像这样显出现对话框保存到本地，有人做过这种吗？
另外谁能解释一下这个页的保存功能是怎么做的？
常修溢 2004-06-23
  • 打赏
  • 举报
 回复
关注,学习
andrawsky 2004-06-23
  • 打赏
  • 举报
 回复
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";//octet-stream
Response.AddHeader("Content-Disposition","attachment; filename=Report.xls");
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.Default;
System.IO.StringWriter tw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);
DataGrid1.RenderControl(hw);
Response.Write(tw.ToString());
Response.End(
xiangxiang2000 2004-06-23
  • 打赏
  • 举报
 回复
顶！
shoutor 2004-06-23
  • 打赏
  • 举报
 回复
public void Export2Excel(Object Sender, EventArgs E)
{
// 将当前查询的数据导入Excel中
SqlConnection SqlConn = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);

// 设置为true,则在当前的数据上追加数据,否则,重写当前数据
StreamWriter w = new StreamWriter(Server.MapPath("Data\\EmpInfoStaticResult.xls"), false, Encoding.Default);
SqlConn.Open();

try
{
SqlCommand SqlCmd = SqlConn.CreateCommand();
SqlCmd.CommandText = Request["Param"];
SqlDataReader Reader = SqlCmd.ExecuteReader();
for (int i = 0; i < Reader.FieldCount; ++i)
{
w.Write(Reader.GetName(i));
w.Write('\t');
}
w.Write("\r\n");
object[] values = new object[Reader.FieldCount];
while (Reader.Read())
{
Reader.GetValues(values);
for (int i = 0; i < values.Length; ++i)
{
w.Write( values[i].ToString());
w.Write('\t');
}
w.Write("\r\n");
}
w.Flush();
w.Close();
Reader.Close();
SqlConn.Close();
Response.Redirect ("Data\\EmpInfoStaticResult.xls");
}
catch
{
w.Close();
SqlConn.Close();
return;
}
}
goody9807 2004-06-23
  • 打赏
  • 举报
 回复
必须引入microsoft的microsoft office webComponent control 9.0
bitsbird 2004-06-23
  • 打赏
  • 举报
 回复
:)
easyinter 2004-06-23
  • 打赏
  • 举报
 回复
ppxeng (小鹏) ：
真是狗咬吕洞宾，不识好人心，我看你才烦呢！
guying999 2004-06-23
  • 打赏
  • 举报
 回复
参考：
http://dotnet.aspx.cc/ShowDetail.aspx?id=BF0A54F9-C7C7-4200-BD9A-802AC1F5DE50
sunny6281 2004-06-23
  • 打赏
  • 举报
 回复
关注
xiangxiang2000 2004-06-23
  • 打赏
  • 举报
 回复
To:zengzhi318:我的那段代码就跟你写的是一样的，但是在你调用HttpContext.Current.Response.End();后，你下载保存execl内容，然后继续点击这个按扭或页面其他事件，都会有脚本错误的，但只要刷新页面就可以了，不知有什么办法解决这个问题？继续关注！
hhyhappy 2004-06-23
  • 打赏
  • 举报
 回复
关注！帮顶
xiangxiang2000 2004-06-23
  • 打赏
  • 举报
 回复
to andrawsky:我的那段代码就是这样写的，但是点 下载保存后，你再次点击该按牛或其他事件按牛，他会破坏脚本代码！是不是还有什么方法呀？继续关注！
付长松_华为云MVP 2004-06-22
  • 打赏
  • 举报
 回复
Response.ContentType = "application/vnd.ms-excel "
' 从Content-Type header中去除charset设置
Response.Charset = ""
Response.AddHeader("Content-Disposition", "attachment; filename=Export.xls")

' 关闭 ViewState
Me.EnableViewState = False
Dim tw As New System.IO.StringWriter
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
' 获取control的HTML
DataGrid1.RenderControl(hw)
' 把HTML写回浏览器
Response.ContentEncoding = System.Text.Encoding.UTF8
Response.Write(tw.ToString())
Response.End()
easyinter 2004-06-22
  • 打赏
  • 举报
 回复
一下是asp.net代码：
<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="TestExeclFromDataGrid.WebForm1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>WebForm1</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio 7.0">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:DataGrid id=DataGrid1 style="Z-INDEX: 101; LEFT: 88px; POSITION: absolute; TOP: 88px" runat="server" DataSource="<%# dsProdcuts1 %>" DataKeyField="CategoryID" DataMember="Products" AutoGenerateColumns="False">
<Columns>
<asp:BoundColumn DataField="ProductID" SortExpression="ProductID" HeaderText="ProductID"></asp:BoundColumn>
<asp:BoundColumn DataField="ProductName" SortExpression="ProductName" HeaderText="ProductName"></asp:BoundColumn>
<asp:BoundColumn DataField="SupplierID" SortExpression="SupplierID" HeaderText="SupplierID"></asp:BoundColumn>
<asp:BoundColumn DataField="CategoryID" SortExpression="CategoryID" HeaderText="CategoryID"></asp:BoundColumn>
<asp:BoundColumn DataField="UnitPrice" SortExpression="UnitPrice" HeaderText="UnitPrice"></asp:BoundColumn>
<asp:BoundColumn DataField="UnitsInStock" SortExpression="UnitsInStock" HeaderText="UnitsInStock"></asp:BoundColumn>
</Columns>
</asp:DataGrid>
<asp:Button id="Button1" style="Z-INDEX: 102; LEFT: 264px; POSITION: absolute; TOP: 280px" runat="server" Text="Button"></asp:Button>
<asp:Label id="Label1" style="Z-INDEX: 103; LEFT: 200px; POSITION: absolute; TOP: 32px" runat="server" Width="264px">将DataGrid的数据导为excel</asp:Label>
</form>
</body>
</HTML>
easyinter 2004-06-22
  • 打赏
  • 举报
 回复
以下是c#代码文件：
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace TestExeclFromDataGrid
{
/// <summary>
/// WebForm1 的摘要说明。
/// </summary>
public class WebForm1 : System.Web.UI.Page
{
protected System.Data.SqlClient.SqlDataAdapter sqlDataAdapter1;
protected System.Data.SqlClient.SqlCommand sqlSelectCommand1;
protected System.Data.SqlClient.SqlConnection sqlConnection1;
protected TestExeclFromDataGrid.dsProdcuts dsProdcuts1;
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.DataGrid DataGrid1;

private void Page_Load(object sender, System.EventArgs e)
{
this.sqlDataAdapter1.Fill(this.dsProdcuts1);
if(!Page.IsPostBack)
{
this.DataGrid1.DataBind();
}// 在此处放置用户代码以初始化页面
}

public void DataGridToExcel()
{
string filesName=this.File1.Value;
OWC.SpreadsheetClass XlsSheet = new OWC.SpreadsheetClass();//实例化execl中的一张表
int tempColumn = 0;
foreach(System.Web.UI.WebControls.DataGridColumn Oitem in this.DataGrid1.Columns)//遍历整个datagrid的每一列,添加表头
{
XlsSheet.ActiveSheet.Cells[1,tempColumn+1]=Oitem.HeaderText;//添加表头
tempColumn++;
}
for(int i=1;i <= this.DataGrid1.Items.Count;i++)//遍历datagrid中的每一行，
{
for(int j=1;j<=this.DataGrid1.Items[i-1].Cells.Count;j++)
{
XlsSheet.ActiveSheet.Cells[i+1,j]=this.DataGrid1.Items[i-1].Cells[j-1].Text;//给刚才定义好的excel表没格负值
}
}
XlsSheet.ActiveSheet.Export(filesName,OWC.SheetExportActionEnum.ssExportActionNone);//execl表格的导出
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN：该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.sqlDataAdapter1 = new System.Data.SqlClient.SqlDataAdapter();
this.sqlSelectCommand1 = new System.Data.SqlClient.SqlCommand();
this.sqlConnection1 = new System.Data.SqlClient.SqlConnection();
this.dsProdcuts1 = new TestExeclFromDataGrid.dsProdcuts();
((System.ComponentModel.ISupportInitialize)(this.dsProdcuts1)).BeginInit();
this.Button1.Click += new System.EventHandler(this.Button1_Click);
//
// sqlDataAdapter1
//
this.sqlDataAdapter1.SelectCommand = this.sqlSelectCommand1;
this.sqlDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "Products", new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("ProductID", "ProductID"),
new System.Data.Common.DataColumnMapping("ProductName", "ProductName"),
new System.Data.Common.DataColumnMapping("SupplierID", "SupplierID"),
new System.Data.Common.DataColumnMapping("CategoryID", "CategoryID"),
new System.Data.Common.DataColumnMapping("QuantityPerUnit", "QuantityPerUnit"),
new System.Data.Common.DataColumnMapping("UnitPrice", "UnitPrice"),
new System.Data.Common.DataColumnMapping("UnitsInStock", "UnitsInStock"),
new System.Data.Common.DataColumnMapping("UnitsOnOrder", "UnitsOnOrder"),
new System.Data.Common.DataColumnMapping("ReorderLevel", "ReorderLevel"),
new System.Data.Common.DataColumnMapping("Discontinued", "Discontinued")})});
//
// sqlSelectCommand1
//
this.sqlSelectCommand1.CommandText = "SELECT ProductID, ProductName, SupplierID, CategoryID, QuantityPerUnit, UnitPrice" +
", UnitsInStock, UnitsOnOrder, ReorderLevel, Discontinued FROM Products";
this.sqlSelectCommand1.Connection = this.sqlConnection1;
//
// sqlConnection1
//
this.sqlConnection1.ConnectionString = "data source=LVYIWEN;initial catalog=Northwind;integrated security=SSPI;persist se" +
"curity info=False;workstation id=LVYIWEN;packet size=4096";
//
// dsProdcuts1
//
this.dsProdcuts1.DataSetName = "dsProdcuts";
this.dsProdcuts1.Locale = new System.Globalization.CultureInfo("zh-CN");
this.dsProdcuts1.Namespace = "http://www.tempuri.org/dsProdcuts.xsd";
this.Load += new System.EventHandler(this.Page_Load);
((System.ComponentModel.ISupportInitialize)(this.dsProdcuts1)).EndInit();

}
#endregion

private void Button1_Click(object sender, System.EventArgs e)
{
DataGridToExcel();
}
}
}


easyinter 2004-06-22
  • 打赏
  • 举报
 回复
要实现讲datagrid中的数据导为excel,必须引入microsoft的microsoft office webComponent(OWC),从vs.net中引入OWC后，就可以实现将datagrid导为excel.具体的方法如下：
加载更多回复（5）
easyui datagrid 数据导出到Excel
两种方法将easyui datagrid 中的数据导出到Excel中，均以验证可以正常使用
【WPF】将DataGrid内容导出到Excel
引言在完成将DataGrid的内容导出到Excel的时候，参考了一个印度小伙的文章http://www.codeproject.com/Articles/120480/Export-to-Excel-Functionality-in-WPF-DataGrid，很清晰，结合着自己的实际项目写了这篇文章，分享给大家。步骤一：创建窗体，绘制datagrid这里不多讲，直接拖控件就行。二：为datagrid
Java整合JQueryEasyUI详解
这是一套技术比较全面的EasyUI视频,由浅入深的几乎涵盖了所有EasyUI常用的组件,本课程后台Java端采用的是传统JDBC的技术,因为这套课程主要突出的是EasyUI的使用,所有后台这块技术大家可以忽略,只要有Java框架基础的同学,那后台是可以随意换的.本门课程的技术呢比较陈旧,视频中的有些杂音,如果你们公司用不着这个技术你就别学了,因为这技术确实已经挺老的了,可以学一些新的前端技术,例如BootStrap或者Vue,或者angularJS啦什么都可以.课程目标:1.熟练掌握EasyUI介绍和环境搭建2.熟练掌握Messager和window组件使用3.熟练掌握Dialog组件使用.4.熟练掌握表单验证,numberbox等组件使用5.熟练掌握layout布局组件6.熟练掌握datagrid组件增删改查详解7.熟练掌握可编辑表格Combo组件详解8.熟练掌握combo组件详解,省市县级联菜单9.熟练掌握tree组件详解,拖拽,增删改查
WPF将DataGrid内容导出到Excel
WPF将DataGrid内容导出到Excel最近在维护一个项目程序的时候需要新添加一些需求，其中一个需求就是把WPF中的datagird中的内容导出为Excel。查了很多网上的资料，最后结合将DataGridView导出到Excel的方法做了一些调整之后实现了功能需求。 !首先在VS里引用，前提电脑里必须要有Excel否则不能引用 我是通过点击button控件进行保存的。直接贴代
WPF中DataGrid的数据导出到Excel
【代码】WPF中DataGrid的数据导出到Excel。
.NET社区

62,046

社区成员

669,049

社区内容

发帖
与我相关
我的任务
社区描述
.NET技术交流专区
javascript云原生 企业社区
社区管理员
  • ASP.NET
  • .Net开发者社区
  • R小R
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告

.NET 社区是一个围绕开源 .NET 的开放、热情、创新、包容的技术社区。社区致力于为广大 .NET 爱好者提供一个良好的知识共享、协同互助的 .NET 技术交流环境。我们尊重不同意见，支持健康理性的辩论和互动，反对歧视和攻击。

希望和大家一起共同营造一个活跃、友好的社区氛围。

试试用AI创作助手写篇文章吧

+ 用AI写文章