这个问题已经问过很多次了!应该是这种导出方法本身就有问题!我用了一个星期的时间也没找出改进的方法!现在给你提供另外一种方法!我已经解决了问题,但效率比较差,你可以参考一下!
<%@ Page language="C#" Debug="true" %>
<%@ Import Namespace="System.Drawing" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %> <script Language="C#" runat="server">
private void Button1_Click(object sender, System.EventArgs e)
{
//export to excel Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false; System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); this.ClearControls(dg);
dg.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End();
} private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{ SqlConnection conn = new SqlConnection ("data source=(local);initial catalog=Northwind;Pwd=p@ssw0rd;User ID=sa");
SqlCommand cmd = new SqlCommand ("Select LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country from Employees", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
dg.DataSource = ds.Tables[0];
dg.DataBind();
} } private void ClearControls(Control control)
{
for (int i=control.Controls.Count -1; i>=0; i--)
{
ClearControls(control.Controls[i]);
} if (!(control is TableCell))
{
if (control.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
try
{
literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control,null);
}
catch { } control.Parent.Controls.Remove(control);
} else if (control.GetType().GetProperty("Text") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control,null);
control.Parent.Controls.Remove(control);
}
}
return;
}
</script>
<html>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<form id="frm" runat="server">
<asp:Button id="Button1" runat="server" Text="Export to Excel"
OnClick="Button1_Click"></asp:Button><BR>
<asp:Datagrid id="dg" runat="server" AutoGenerateColumns="True"
AllowSorting="true" AllowPaging="true"
CellPadding="3" PageSize=3>
<columns>
<asp:TemplateColumn>
<ItemTemplate>
<asp:LinkButton runat="server" CommandName="Edit"
CausesValidation="false" ID="btnView"
Text="Edit"/>
</ItemTemplate>
</asp:TemplateColumn> </columns>
</asp:datagrid> <BR> </form>
</body>
</html>
<%@ Page language="C#" Debug="true" %>
<%@ Import Namespace="System.Drawing" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %> <script Language="C#" runat="server">
private void Button1_Click(object sender, System.EventArgs e)
{
//export to excel Response.Clear();
Response.Buffer= true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false; System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter); this.ClearControls(dg);
dg.RenderControl(oHtmlTextWriter); Response.Write(oStringWriter.ToString()); Response.End();
} private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{ SqlConnection conn = new SqlConnection ("data source=(local);initial catalog=Northwind;Pwd=p@ssw0rd;User ID=sa");
SqlCommand cmd = new SqlCommand ("Select LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country from Employees", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
dg.DataSource = ds.Tables[0];
dg.DataBind();
} } private void ClearControls(Control control)
{
for (int i=control.Controls.Count -1; i>=0; i--)
{
ClearControls(control.Controls[i]);
} if (!(control is TableCell))
{
if (control.GetType().GetProperty("SelectedItem") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
try
{
literal.Text = (string)control.GetType().GetProperty("SelectedItem").GetValue(control,null);
}
catch { } control.Parent.Controls.Remove(control);
} else if (control.GetType().GetProperty("Text") != null)
{
LiteralControl literal = new LiteralControl();
control.Parent.Controls.Add(literal);
literal.Text = (string)control.GetType().GetProperty("Text").GetValue(control,null);
control.Parent.Controls.Remove(control);
}
}
return;
}
</script>
<html>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<form id="frm" runat="server">
<asp:Button id="Button1" runat="server" Text="Export to Excel"
OnClick="Button1_Click"></asp:Button><BR>
<asp:Datagrid id="dg" runat="server" AutoGenerateColumns="True"
AllowSorting="true" AllowPaging="true"
CellPadding="3" PageSize=3>
<columns>
<asp:TemplateColumn>
<ItemTemplate>
<asp:LinkButton runat="server" CommandName="Edit"
CausesValidation="false" ID="btnView"
Text="Edit"/>
</ItemTemplate>
</asp:TemplateColumn> </columns>
</asp:datagrid> <BR> </form>
</body>
</html>
解决方案 »
- js 验证各种格式类型的正则表达式
- aspx使用Alert会改变布局
- 返回查询页面保持原来的查询条件和数据。
- 有文件总共字节,当前已传输字节数,如何计算传输速度啊
- 在Asp.net中使用session的几点体会,愿与大侠们交流
- SolpartMenu 问题求救----高分
- 大家来帮我找一下错误!!!
- 关于ObjectDataSource的问题,对IEumerable迷惑
- 在线等~客户端的access数据如何通过web导入到服务端SQL server 2000 数据库
- 哪位大侠帮我把asp.net中用window.open打开窗体的最小化,关闭按钮去掉 50分
- datagrid控件的问题,高手帮忙看一下!
- 自动生成SQL 存储过程的小软件 [公测]
取出Table,再导出,一定没问题!!
<SCRIPT LANGUAGE="javascript">
<!--
function AutomateExcel()
{
// Start Excel and get Application object.
var oXL = new ActiveXObject("Excel.Application");
// Get a new workbook.
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var table = document.all.PowerTable;
var hang = table.rows.length;var lie = table.rows(0).cells.length; // Add table headers going cell by cell.
for (i=0;i<hang;i++)
{
for (j=0;j<lie;j++)
{
oSheet.Cells(i+1,j+1).value = table.rows(i).cells(j).innerText;
}}
oXL.Visible = true;
oXL.UserControl = true;
}
//-->
</SCRIPT>
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Import Namespace="tuha3.databaseoperation"%>
<%
DbConnector dbcon=DbConnector.CreateInstance();
OleDbConnection con=new OleDbConnection();
OleDbDataReader reader=null;
string strSql;
if(dbcon.ConnectToOracle())
{
con=dbcon.GetConnection();
OleDbCommand cmdsel=new OleDbCommand();
if(Request["sql"]!=null)
strSql=Request["sql"].ToString();
cmdsel.CommandText="select * from sys_dic01";
cmdsel.Connection=con;
reader=cmdsel.ExecuteReader();
Response.Write("正在导出数据到Excel...");
}
else
{
reader.Close();
%>
<script> window.alert('连接数据库出错,导出数据中断!')
window.close();
</script>
<%
Response.End();
}
%>
<script>
//function AutomateExcel()
{
// Start Excel and get Application object.
try
{
var oXL = new ActiveXObject("Excel.Application");
// Get a new workbook.
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
}
catch(e)
{
alert("您机器上没有安装Office,不能导出数据到Excel!");
window.close();
}// Add table headers going cell by cell.
<%
int i=0;
while(reader.Read())
{
for(int j=0;j< reader.FieldCount;j++)
{%>
oSheet.Cells(<%=(i+1)%>,<%=(j+1)%>).value = "<%=reader[j]%>";
<%}
i++;
}
reader.Close();%>
oXL.Visible = true;
oXL.UserControl = true;
}
window.alert("导出数据成功!")
window.close();
//-->
</script>