高分求ASP.NET导出EXCEL代码???

goodfeng110 2006-04-21 02:51:38

ASP.NET用EXCEL时,进程杀不了,
用HTML转换成EXCEL时,格式不好控制,
那位有好的方法!

...全文

994 28 打赏收藏转发到动态举报

写回复

用AI写文章

28 条回复

切换为时间正序

请发表友善的回复…

发表回复

greki 2006-07-08

打赏
举报

关注

bevan806 2006-06-28

打赏
举报

cuizhanjun1981(城市稻草人) 用了你的方法可以导出，但中文名还是不行！！

lk919 2006-06-20

打赏
举报

mark

cuizhanjun1981 2006-06-20

打赏
举报

其实，用datagrid导出也可以吧。
用datagrid 来控制显示样式，用dataset做数据源。 (datagrid设置一下就可以避免分页错误了)
#region 导出Excel

/// <summary>
/// 导出Datagrid里所有数据到Office
/// </summary>
/// <param name="grdTemp">要导出的Datagrid</param>
/// <param name="dsTemp">Datagrid的数据源</param>
public void DataGridToExcel(DataGrid grdTemp,DataSet dsTemp)
{
grdTemp.AllowPaging=false; //设置不能分页

grdTemp.DataSource=this.dsTemp; //重新绑定数据源
grdTemp.DataBind();

//常规导出方法

System.IO.StringWriter SW = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter HTW=new System.Web.UI.HtmlTextWriter(SW);
grdTemp.RenderControl(HTW);

//Page为要导出的对象,当前是Page,如果是DataGrid,DataList等都可以
Response.Buffer=true;
Response.Clear();
Response.ClearContent();
Response.ClearHeaders();
Response.ContentType = "application/vnd.ms-excel";
//Response.ContentType是输出流的 HTTP MIME 类型
//Response.ContentType --- word文件
//application/vnd.ms-excel --- excel文件
//...
Response.Charset="utf-8";
Response.ContentEncoding=System.Text.Encoding.GetEncoding("utf-8");
Response.AddHeader("Content-Disposition", "attachment;filename=aaa.xls");
//attachment --- 作为附件下载
//inline --- 在线打开
//filename如过是中文,则可以用HttpUtility.UrlEncode(fileName,System.Text.Encoding.UTF8)
//进行进行编码,以解决文件名乱码的问题
Response.Write(SW.ToString());
Response.Flush();
Response.Close();
}

#endregion

caomike 2006-06-20

打赏
举报

终极解决方案：将DataTable导出为excel

好，让我们快点结束这篇无聊的post。一般来说，页面上的datagrid是以查询得到的一个DataTable为数据源的。那么为了把全部数据导入excel中，我们只要把DataTable数据源输出为excel就可以了。

1/**//// <summary>
2 /// 把DataTable内容导出伟excel并返回客户端
3 /// </summary>
4 /// <param name="dgData">待导出的DataTable</param>
5 /// 创建人：陈文凯
6 /// 创建日期：2005年10月08日
7 /// 修改人：
8 /// 修改日期：
9 public static void DataTable2Excel(System.Data.DataTable dtData)
10 {
11 System.Web.UI.WebControls.DataGrid dgExport = null;
12 // 当前对话
13 System.Web.HttpContext curContext = System.Web.HttpContext.Current;
14 // IO用于导出并返回excel文件
15 System.IO.StringWriter strWriter = null;
16 System.Web.UI.HtmlTextWriter htmlWriter = null;
17
18 if (dtData != null)
19 {
20 // 设置编码和附件格式
21 curContext.Response.ContentType = "application/vnd.ms-excel";
22 curContext.Response.ContentEncoding =System.Text.Encoding.UTF8;
23 curContext.Response.Charset = "";
24
25 // 导出excel文件
26 strWriter = new System.IO.StringWriter();
27 htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
28
29 // 为了解决dgData中可能进行了分页的情况，需要重新定义一个无分页的DataGrid
30 dgExport = new System.Web.UI.WebControls.DataGrid();
31 dgExport.DataSource = dtData.DefaultView;
32 dgExport.AllowPaging = false;
33 dgExport.DataBind();
34
35 // 返回客户端
36 dgExport.RenderControl(htmlWriter);
37 curContext.Response.Write(strWriter.ToString());
38 curContext.Response.End();
39 }
40 }
需要注意的是，导出excel之前要把datatable的列名更改为客户要求的文字，就ok了。因为是从DataTable导出的，所以这种方法解决了分页数据的问题，堪称终极解决方案。

摘自：http://www.seaskyer.net/Index/Catalog11/242.html

纷享刘晨 2006-06-20

打赏
举报

找一个第三方的组件吧，或者可以用xslt控制输出格式

aliketen 2006-06-20

打赏
举报

用这个样式保证好看
http://aliketen.cnblogs.com/articles/381486.html

caomike 2006-06-20

打赏
举报

下面是调用的例子

1 /**
2 * 导出dgData中0-3列的数据到excel文件中
3 **/
4 function ToExcel()
5 {
6 DataGrid2Excel("使用javascript导出excel的例子", document.getElementsById("dgData"), 0, 3);
7 }
这种方法的缺点是：
（1）了能够在客户端调用Excel.Application，需要把IE的安全级别设为“低”。
（2）与方法一相同，还是只能导出当前显示在datagrid里面的数据，无法导出分页的数据。

caomike 2006-06-20

打赏
举报

客户端导出excel

1 /*
2 * 将DataGrid导出为Excel文件
3 *
4 * @param strTitle 文件标题
5 * @param dgData 待导出的DataGrid
6 * @param iStartCol 起始列序号
7 * @param iEndCol 结束列序号
8 *
9 * 创建人: calvin
10 * 创建日期: 2005-10-08
11 * 修改人:
12 * 修改日期:
13 **/
14 function DataGrid2Excel(strTitle, dgData, iStartCol, iEndCol)
15 {
16 // 定义Excel Applicaiton Object
17 var appExcel = null;
18 // 当前激活的工作簿
19 var currentWork = null;
20 var currentSheet = null;
21
22 try
23 {
24 // 初始化application
25 appExcel = new ActiveXObject("Excel.Application");
26 appExcel.Visible = true;
27 }
28 catch(e)
29 {
30 window.alert("Please Install Excel First");
31
32 return;
33 }
34
35 // 获取当前激活的工作部
36 currentWork = appExcel.Workbooks.Add();
37 currentSheet = currentWork.ActiveSheet;
38
39 // 填充excel内容
40 // 设置标题
41 currentSheet.Cells(1,1).Value = strTitle;
42 currentSheet.Cells(1,1).Value = dgData.innerText;
43 window.alert(dgData.innerHTML);
44
45 // 填充内容
46 for (var iRow = 0; iRow < dgData.rows.length - 1; iRow++)
47 {
48 // 显示指定列的内容
49 for (var iCol = iStartCol; iCol <= iEndCol; iCol++)
50 {
51 currentSheet.Cells(iRow + 2, iCol + 1).Value =
52 dgData.rows[iRow].cells[iCol].innerText;
53 }
54 }
55 }

caomike 2006-06-20

打赏
举报

服务器端实现DataGrid导出为excel

这是网上出现的最多的做法：

1/**//// <summary>
2 /// 把DataGrid内容导出伟excel并返回客户端
3 /// </summary>
4 /// <param name="dgData">待导出的DataGrid</param>
5 /// 创建人：calvin
6 /// 创建日期：2005年10月08日
7 /// 修改人：
8 /// 修改日期：
9 public static void DataGrid2Excel(System.Web.UI.WebControls.DataGrid dgData)
10 {
11 // 当前对话
12 System.Web.HttpContext curContext = System.Web.HttpContext.Current;
13 // IO用于导出并返回excel文件
14 System.IO.StringWriter strWriter = null;
15 System.Web.UI.HtmlTextWriter htmlWriter = null;
16
17 if (dgData != null)
18 {
19 // 设置编码和附件格式
20 curContext.Response.ContentType = "application/vnd.ms-excel";
21 curContext.Response.ContentEncoding =System.Text.Encoding.UTF8;
22 curContext.Response.Charset = "";
23
24 // 导出excel文件
25 strWriter = new System.IO.StringWriter();
26 htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);
27
28 // 返回客户端
29 dgData.RenderControl(htmlWriter);
30 curContext.Response.Write(strWriter.ToString());
31 curContext.Response.End();
32 }
33 }

nimeide1234567890 2006-04-21

打赏
举报

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient ;
using System.Text;
using System.Configuration;

namespace DataGrid_import_WordExcel
{

public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button BtnImportWord;
protected System.Web.UI.WebControls.Button Btn_Import_Excel;
protected System.Web.UI.WebControls.DataGrid DataGrid1;
public DataRow dr;
private DataSet myDS =new DataSet();

private void Page_Load(object sender, System.EventArgs e)
{
//CreateDataSet();
Data_Load();
if(!IsPostBack)
{
//DataBind();
}
}

#region Web 敦极扢數?汜傖腔測鎢
override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}

private void InitializeComponent()
{
this.Btn_Import_Excel.Click += new System.EventHandler(this.Btn_Import_Excel_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion

private void ExportDataGrid(string FileType, string FileName) //植DataGrid絳堤
{
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");

Response.AppendHeader("Content-Disposition", "attachment;filename=" +HttpUtility.UrlEncode(FileName,Encoding.UTF8).ToString());
Response.ContentType = FileType;
this.EnableViewState =false;
StringWriter tw = new StringWriter();
HtmlTextWriter hw =new HtmlTextWriter(tw);
DataGrid1.RenderControl(hw);
Response.Write(tw.ToString());
Response.End();
}
private void Btn_Import_Excel_Click(object sender, System.EventArgs e)
{
ExportDataGrid("application/ms-excel", "Excel.xls"); //絳善Excel
}

private void Data_Load()
{
string mySQLstr=Session["sql4"].ToString();
SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["car"]);
SqlDataAdapter myDataAdapter = new SqlDataAdapter(mySQLstr,myConnection);
myConnection.Open();
myDataAdapter.Fill(myDS,"lw_carodd");
DataGrid1.DataSource=myDS;
DataGrid1.DataBind();
}

DataView CreateDataSource()
{
string nowDSN=ConfigurationSettings.AppSettings["car"];
SqlConnection myConnection=new SqlConnection(nowDSN);
SqlDataAdapter myCommand=new SqlDataAdapter(Session["sql4"].ToString(),myConnection);
DataSet ds=new DataSet();
myCommand.Fill(ds, "lw_carodd");
return ds.Tables["lw_carodd"].DefaultView;
myConnection.Close();
Page.DataBind();
}

void DataBind()
{
DataView source=CreateDataSource();
if(!IsPostBack)
{
}
DataGrid1.DataSource = source;
DataGrid1.DataBind();
}

private void CreateDataSet()
{
DataSet myDataSet = new DataSet("aNewDataSet");
DataTable table1 = MakeTable("采購單號","供應商","料號", "料品名稱規格","單位","數量", "驗收總數","單价","交貨日期","訂單號碼");
myDataSet.Tables.Add(table1);
DataGrid1.DataSource=myDataSet;
DataGrid1.DataBind();
}

private DataTable MakeTable(String c1Name,String c2Name,String c3Name,String c4Name,String c5Name,String c6Name,String c7Name,String c8Name,String c9Name,String c10Name)
{
int i;
DataTable myTable=new DataTable();
DataColumn myColumn;

myColumn = new DataColumn(c1Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c2Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c3Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c4Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c5Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c6Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c7Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c8Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c9Name,typeof(string));
myTable.Columns.Add(myColumn);
myColumn = new DataColumn(c10Name,typeof(string));
myTable.Columns.Add(myColumn);

DataRow Dr;

string mySQLstr=Session["sql4"].ToString();
SqlConnection myConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["car"]);
SqlDataAdapter myDataAdapter = new SqlDataAdapter(mySQLstr,myConnection);
myConnection.Open();
DataSet ds=new DataSet();
myDataAdapter.Fill(ds,"lw_carodd");
dr = ds.Tables["lw_carodd"].Rows[0];
int num=ds.Tables[0].Rows.Count;

}
}

nimeide1234567890 2006-04-21

打赏
举报

<%@ Page language="c#" Src="excel.aspx.cs" AutoEventWireup="false" Inherits="DataGrid_import_WordExcel.WebForm1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>OutPutExcel</title>
</HEAD>
<link rel="stylesheet" href="Style.css" type="text/css">
<body leftmargin=0 topmargin=0 marginwidth=0 marginheight=0>
<form width="250%" id="Form1" method="post" runat="server">
<table width="250%"><tr><td>
<asp:datagrid
id="DataGrid1"
bgcolor="#efefef"
HeaderStyle-BackColor="#718BD6"
HeaderStyle-ForeColor="#FFFF66"
AlternatingItemStyle-BackColor="#FFFFFF"
itemstyle-backcolor="#FFFFFF"
runat="server">
<ItemStyle HorizontalAlign="center" Height="20"></ItemStyle>
<Columns>

</Columns>
</asp:datagrid>
</td></tr></table>
<P>
<asp:button id="Btn_Import_Excel" runat="server" Text="轉到EXCEL"></asp:button></P>
</form>
</body>
</HTML>

aliketen 2006-04-21

打赏
举报

XML样式很好控制啊

http://community.csdn.net/Expert/topic/4701/4701620.xml?temp=.5343134

CodeAnyWhere 2006-04-21

打赏
举报

99414325ALe 2006-04-21

打赏
举报

Private Sub ToFile(ByVal Ctl As System.Web.UI.Control)
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=ArticlePay.xls")
HttpContext.Current.Response.Charset = "UTF-8"
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default
HttpContext.Current.Response.ContentType = "vnd.ms-excel/msword" ' //image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
Ctl.Page.EnableViewState = False

Dim Tw As New System.IO.StringWriter
Dim Hw As New System.Web.UI.HtmlTextWriter(Tw)

Ctl.RenderControl(Hw)

HttpContext.Current.Response.Write(Tw.ToString())
HttpContext.Current.Response.End()
End Sub

gsk99 2006-04-21

打赏
举报

To：webwait(webwei) ( ) 信誉：100

DataGrid导出Excel只能导出当前页吧，有没有办法导出说有页呢？

objectprogram 2006-04-21

打赏
举报

private void DataGrid1_ItemDataBound(object sender, DataGridItemEventArgs e)
{
if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
e.Item.Cells[0].Attributes.Add("style","vnd.ms-excel.numberformat:@");
e.Item.Cells[1].Attributes.Add("style","vnd.ms-excel.numberformat:@");
e.Item.Cells[2].Attributes.Add("style","vnd.ms-excel.numberformat:@");
e.Item.Cells[3].Attributes.Add("style","vnd.ms-excel.numberformat:@");
e.Item.Cells[5].Attributes.Add("style","vnd.ms-excel.numberformat:@");

if(e.Item.Cells[4].Text.Trim().Length==6 || e.Item.Cells[4].Text.Trim().Length==7) //出版日期
{
e.Item.Cells[4].Attributes.Add("style","vnd.ms-excel.numberformat:yyyy/m");
}
else
{
e.Item.Cells[4].Attributes.Add("style","vnd.ms-excel.numberformat:yyyy/m/d");
}
}
}

goodfeng110 2006-04-21

打赏
举报

我是直接通过数据库里导出,但导出后要有格式!

活靶子哥哥 2006-04-21

打赏
举报

导出
http://www.aspxboy.com/private/showthread.asp?threadid=206
样式
http://www.aspxboy.com/private/showthread.asp?threadid=341
http://www.aspxboy.com/private/showthread.asp?threadid=401

goodfeng110 2006-04-21