请教高手:怎么样将DataGrid中的数据导入到excel中?

samzheng 2003-07-21 02:39:21
我现在想实现打印的功能,我想如果能把数据导入excel中,这样就简单多了,请问怎么样实现?或者如果有更好实现打印的方法也可以,这里多谢了!!!!!!!!!!!!!!
...全文
54 11 打赏 收藏 举报
写回复
11 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
venjiang 2003-07-21
  • 打赏
  • 举报
回复
将DataGrid中数据倒出Excel文件并下载
http://www.hidotnet.com/Forum/View.aspx?fbId=1&Id=914
lihonggen0 2003-07-21
  • 打赏
  • 举报
回复
http://www.csdn.net/develop/read_article.asp?id=16987
ylaoei 2003-07-21
  • 打赏
  • 举报
回复
学习,up
tzsimple 2003-07-21
  • 打赏
  • 举报
回复
up
gqxm 2003-07-21
  • 打赏
  • 举报
回复
摘自孟子E章,供你参考:

DataGridToExcel.aspx

<%@ 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>


后台代码:
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

cyp503 2003-07-21
  • 打赏
  • 举报
回复
这是一个比较完整的转换代码

你编译一下就可以用
cyp503 2003-07-21
  • 打赏
  • 举报
回复

/// <summary>
/// Clears the child controls of a Datagrid to make sure all controls are LiteralControls
/// </summary>
/// <param name="dg">Datagrid to be cleared and verified</param>
protected void ClearChildControls(DataGrid dg)
{

for(int i = dg.Columns.Count -1 ; i>=0; i--)
{
DataGridColumn column = dg.Columns[i];
if (column is ButtonColumn)
{
dg.Columns.Remove(column);
}
}

this.RecursiveClear(dg);

}

}

/// <summary>
/// HTML Encodes an entire DataGrid.
/// It iterates through each cell in the TableRow, ensuring that all
/// the text being displayed is HTML Encoded, irrespective of whether
/// they are just plain text, buttons, hyperlinks, multiple controls etc..
/// </summary>
public class CellFormater
{
/// <summary>
/// Constructs an instance of the CellFormater class.
/// </summary>
public CellFormater()
{
//
// TODO: Add constructor logic here
//

}

/// <summary>
/// Method that HTML Encodes an entire DataGrid.
/// It iterates through each cell in the TableRow, ensuring that all
/// the text being displayed is HTML Encoded, irrespective of whether
/// they are just plain text, buttons, hyperlinks, multiple controls etc..
/// <seealso cref="System.Web.UI.WebControls.DataGrid.ItemDataBound">DataGrid.ItemDataBound Event</seealso>
/// </summary>
/// <param name="item">
/// The DataGridItem that is currently being bound in the calling Web
/// Page's DataGrid.ItemDataBound Event.
/// </param>
/// <remarks>
/// This method should be called from the
/// <c>DataGrid_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)</c>
/// event in the respective Web View Codebehind.
/// </remarks>
/// <example>
/// We want to HTMLEncode a complete DataGrid (all columns and all
/// rows that may/do contain characters that will require encoding
/// for display in HTML) called dgIssues.
/// Use the following code for the ItemDataBound Event:
/// <code>
/// private void dgIssues_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
/// {
/// WebMethod wm = new WebMethod();
/// wm.DataGrid_ItemDataBound_HTMLEncode((DataGridItem) e.Item);
/// }//dgIssues_ItemDataBound
/// </code>
/// </example>
public void AdHocHTMLEncode(System.Web.UI.WebControls.DataGridItem item)
{
bool doHTMLEncode = false;
switch (item.ItemType)
{
#region DataBound
//The following case statements are in ascending TableItemStyle order.
//See ms-help://MS.VSCC/MS.MSDNVS/cpref/html/frlrfsystemwebuiwebcontrolsdatagridclassitemstyletopic.htm for details.
case System.Web.UI.WebControls.ListItemType.Item:
{
doHTMLEncode = true;
break;
}//ListItemType.Item
case System.Web.UI.WebControls.ListItemType.AlternatingItem:
{
doHTMLEncode = true;
break;
}//ListItemType.AlternatingItem
case System.Web.UI.WebControls.ListItemType.SelectedItem:
{
doHTMLEncode = true;
break;
}//ListItemType.SelectedItem
case System.Web.UI.WebControls.ListItemType.EditItem:
{
//These should not be prone to this as TextBoxes aren't.
doHTMLEncode = false;
break;
}//ListItemType.EditItem
#endregion DataBound
#region Non-DataBound
//The remainder are the other ListItemTypes that are non-Data-bound.
case System.Web.UI.WebControls.ListItemType.Header:
{
//We might have specified Headers like "<ID>".
doHTMLEncode = true;
break;
}//ListItemType.Header
case System.Web.UI.WebControls.ListItemType.Footer:
{
//Similarly for the Footer as with the Header.
doHTMLEncode = true;

break;
}//ListItemType.Footer
case System.Web.UI.WebControls.ListItemType.Pager:
{
//With just numbers or buttons, none is required.
//However, for buttons, this is not strictly true as you
//need to specify the text on the buttons. But the Property
//Builder for the DataGrid hints in its defaults that these
//need to be HTMLencoded anyway.
doHTMLEncode = false;
break;
}//ListItemType.Pager
case System.Web.UI.WebControls.ListItemType.Separator:
{
doHTMLEncode = false;
break;
}//ListItemType.Separator
#endregion Non-DataBound
default:
{
//This will never be executed as all ItemTypes are listed above.
break;
}//default
}//switch

if (doHTMLEncode)
{
//Encode the cells dependent on the type of content
//within (e.g. BoundColumn, Hyperlink), taking into account
//that there may be more than one (or even zero) control in
//each cell.
System.Web.UI.WebControls.TableCellCollection cells = (System.Web.UI.WebControls.TableCellCollection)item.Cells;
foreach (System.Web.UI.WebControls.TableCell cell in cells)
{
if (cell.Controls.Count != 0)
{
foreach (System.Web.UI.Control ctrl in cell.Controls)
{
if (ctrl is Button)
{
Button btn = (Button) ctrl;
btn.Text = HttpUtility.HtmlEncode(btn.Text);
}//if
else if (ctrl is HyperLink)
{
HyperLink hyp = (HyperLink) ctrl;
hyp.Text = HttpUtility.HtmlEncode(hyp.Text);
//hyp.NavigateUrl = HttpUtility.UrlEncode(hyp.NavigateUrl);
}//else if
else if (ctrl is LinkButton)
{
LinkButton lb = (LinkButton) ctrl;
lb.Text = HttpUtility.HtmlEncode(lb.Text);
}//else if
// this check is for to change the forecolor of REJECTED activities to red
else if(ctrl is Label)
{
Label objL = (Label)ctrl;
if(objL.Text == "REJECTED")
objL.ForeColor = System.Drawing.Color.Red;
}//else if
}//foreach
}//if
else
{
//The cell is a BoundColumn.
if (cell.Text.ToLower().Trim()!=" ")
cell.Text = HttpUtility.HtmlEncode(cell.Text);

}//else
}//foreach
}//if
}//DataGrid_ItemDataBound_HTMLEncode
}
}
cyp503 2003-07-21
  • 打赏
  • 举报
回复
using System;
using System.Web.UI.WebControls;
using System.Web;
using System.Web.UI;
using System.IO;
using System.Web.Util;


namespace Web.Generic.DataGridTools
{
/// <summary>
/// Serves as the base class that defines the methods, properties and events common
/// to all datagrid exporters in the Web.Generic.DataGridTools
/// </summary>
public abstract class DataGridExporterBase
{
/// <summary>
/// Holds a reference to the datagrid being exported
/// </summary>
protected DataGrid MyDataGrid;

/// <summary>
/// Holds a reference to the page where the datagrid locates
/// </summary>
protected Page CurrentPage;

/// <summary>
/// Overloaded. Initializes a new instance of the DataGridExporterBase class.
/// </summary>
/// <param name="dg">The datagrid to be exported</param>
/// <param name="pg">The page to which the datagrid is to be exported</param>
public DataGridExporterBase(DataGrid dg, Page pg)
{
MyDataGrid = dg;
CurrentPage = pg;
}

/// <summary>
/// Overloaded. Initializes a new instance of the DataGridExporterBase class.
/// </summary>
/// <param name="dg">The datagrid to be exported</param>
public DataGridExporterBase(DataGrid dg):this(dg, dg.Page)
{
}

/// <summary>
/// Exports the current datagrid
/// </summary>
public abstract void Export();
}

/// <summary>
/// Exports a datagrid to a excel file.
/// </summary>
/// <requirements>Microsoft Excel 97 or above should be installed on the client machine in order to make
/// this function work
/// </requirements>
public class DataGridExcelExporter:DataGridExporterBase
{

/// <summary>
/// CSS file for decoration, se it if any or dont use it
/// </summary>
private const string MY_CSS_FILE = "./css/MDF.css";

/// <summary>
/// Overloaded. Initializes a new instance of the DataGridExcelExporter class.
/// </summary>
/// <param name="dg">The datagrid to be exported</param>
/// <param name="pg">The page to which the datagrid is to be exported</param>
public DataGridExcelExporter(DataGrid dg, Page pg):base(dg, pg)
{
}

/// <summary>
/// Overloaded. Initializes a new instance of the DataGridExcelExporter class.
/// </summary>
/// <param name="dg">The datagrid to be exported</param>
public DataGridExcelExporter(DataGrid dg):base(dg)
{
}

/// <summary>
/// Overloaded. Exports a datagrid to an excel file, the title of which is empty
/// </summary>
public override void Export()
{
Export(String.Empty);
}

/// <summary>
/// Renders the html text before the datagrid.
/// </summary>
/// <param name="writer">A HtmlTextWriter to write html to output stream</param>
protected virtual void FrontDecorator(HtmlTextWriter writer)
{
writer.WriteFullBeginTag("HTML");
writer.WriteFullBeginTag("Head");
writer.RenderBeginTag(HtmlTextWriterTag.Style);
writer.Write("<!--");

StreamReader sr = File.OpenText(CurrentPage.MapPath(MY_CSS_FILE));
String input;
while ((input=sr.ReadLine())!=null)
{
writer.WriteLine(input);
}
sr.Close();
writer.Write("-->");
writer.RenderEndTag();
writer.WriteEndTag("Head");
writer.WriteFullBeginTag("Body");
}

/// <summary>
/// Renders the html text after the datagrid.
/// </summary>
/// <param name="writer">A HtmlTextWriter to write html to output stream</param>
protected virtual void RearDecorator(HtmlTextWriter writer)
{
writer.WriteEndTag("Body");
writer.WriteEndTag("HTML");
}

/// <summary>
/// Exports the datagrid to an Excel file with the name of the datasheet provided by the passed in parameter
/// </summary>
/// <param name="reportName">Name of the datasheet.
/// </param>
public virtual void Export(string reportName)
{
ClearChildControls(MyDataGrid);
MyDataGrid.EnableViewState = false;//Gets rid of the viewstate of the control. The viewstate may make an excel file unreadable.


CurrentPage.Response.Clear();
CurrentPage.Response.Buffer = true;

//This will make the browser interpret the output as an Excel file
CurrentPage.Response.AddHeader( "Content-Disposition", "filename="+reportName);
CurrentPage.Response.ContentType="application/vnd.ms-excel";

//Prepares the html and write it into a StringWriter
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
FrontDecorator(htmlWriter);
MyDataGrid.RenderControl(htmlWriter);
RearDecorator(htmlWriter);

//Write the content to the web browser
CurrentPage.Response.Write(stringWriter.ToString());
CurrentPage.Response.End();
}

/// <summary>
/// Iterates a control and its children controls, ensuring they are all LiteralControls
/// <remarks>
/// Only LiteralControl can call RenderControl(System.Web.UI.HTMLTextWriter htmlWriter) method. Otherwise
/// a runtime error will occur. This is the reason why this method exists.
/// </remarks>
/// </summary>
/// <param name="control">The control to be cleared and verified</param>
private void RecursiveClear(Control control)
{
//Clears children controls
for (int i=control.Controls.Count -1; i>=0; i--)
{
RecursiveClear(control.Controls[i]);
}

//
//If it is a LinkButton, convert it to a LiteralControl
//
if (control is LinkButton)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text = ((LinkButton)control).Text;
control.Parent.Controls.Remove(control);
}
//We don't need a button in the excel sheet, so simply delete it
else if(control is Button)
{
control.Parent.Controls.Remove(control);
}

//If it is a ListControl, copy the text to a new LiteralControl
else if(control is ListControl)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
try
{
literal.Text = ((ListControl)control).SelectedItem.Text;
}
catch
{
}
control.Parent.Controls.Remove(control);

}
//You may add more conditions when necessary

return;
}
elfzzf 2003-07-21
  • 打赏
  • 举报
回复
Private Sub excel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

With Response
.Buffer = True
.ContentType = "application/vnd.ms-excel"
.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8")
End With

DataGrid_CXMONEY.EnableViewState = False
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
DataGrid_CXMONEY.RenderControl(hw)
Response.Write(tw.ToString())
Response.End()

end sub
visualcpu 2003-07-21
  • 打赏
  • 举报
回复
no try

help you up
panyee 2003-07-21
  • 打赏
  • 举报
回复
以前有很多这种贴子了, 你在.net的asp.net版块搜索一下Datagrid
相关推荐
发帖
.NET社区

6.1w+

社区成员

.NET技术交流专区
javascript云原生 企业社区
社区管理员
  • ASP.NET
  • .Net开发者社区
  • R小R
加入社区
帖子事件
创建了帖子
2003-07-21 02:39
社区公告

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

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