导出Dataset数据到Excel表格…加班中…解决既送分过期作废

phsam 2004-04-22 09:05:24

哪位仁兄帮我写个写个关于Excel出的方法

Friend Sub sAddColumn(参数是一个Dataset数据集)
//实现两个功能1、可以在客户端进行用Excel打开并查看数据（难），不是嵌套 IE里打开（需用Excel表打开）；
//2。如果不选择直接打开则可以选择另存为保存到Excel表格中。

end sub

写过的给我源程序，最好注解一下，第一次写.NET的程序，不好意思。

不要给我提供其它网站上写的，我已经看过N遍，，没效果。

今晚急需，，，，加班中………………在线等待各位的帮助

解决问题继续加分。

...全文

139 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

phsam 2004-04-22

打赏
举报

哪位最好能给我VB的源代码，，谢谢！

shenlongju 2004-04-22

打赏
举报

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;
using OWC;
using System.Configuration;
using System.IO;

namespace QueuePay
{
/// <summary>
/// Report 的摘要说明。
/// </summary>
public class Report : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button ToExcel;
protected System.Web.UI.WebControls.Label Label1;
SqlConnection cnn;
SqlCommand sql;
protected System.Web.UI.WebControls.HyperLink down;
protected System.Web.UI.WebControls.Button delete;
protected System.Web.UI.WebControls.DataGrid DGall;
string str;//=ConfigurationSettings.AppSettings["dsn"];
data mydata=new data();
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
if(Session["UserName"]==null)
Response.Redirect("ok.aspx?text=请重新登陆！");
if(Session["level"].ToString()!="P"&Session["level"].ToString()!="a")
Response.Redirect("ok.aspx?text=您没有此权限！");
if(!IsPostBack)
{
str=ConfigurationSettings.AppSettings["dsn"];
cnn =new SqlConnection(str);
//sql=new SqlCommand("SELECT * FROM QueueContract where examine=1 order by eDate,id", cnn);
DateTime theDate=DateTime.Now;
//"select * from QueueContract where examine=1 and Endprotect>0 order by eDate,id";
//sql=new SqlCommand("select * from QueueContract where examine=1 and Endthis>0 and((protectDate<='"+theDate+"' and Endprotect=0) or (protectDate>='"+theDate+"' or protectDate is null) order by eDate,id", cnn);
sql=new SqlCommand("select * from QueueContract where examine=1 and Endprotect>0 order by eDate,id", cnn);
BindDataGrid();
//Addnum();
}
}

#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.delete.Click += new System.EventHandler(this.delete_Click);
this.DGall.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.DGall_ItemDataBound);
this.DGall.SelectedIndexChanged += new System.EventHandler(this.DGall_SelectedIndexChanged);
this.ToExcel.Click += new System.EventHandler(this.ToExcel_Click);
this.Load += new System.EventHandler(this.Page_Load);

}
#endregion

private void Addnum()
{
int rows=DGall.Items.Count;
for(int i=0;i<rows;i++)
{
Label Lblnum=(Label)DGall.Items[i].Cells[0].FindControl("xh");
int num=i+1;
Lblnum.Text =num.ToString();
}
}
private void ToExcel_Click(object sender, System.EventArgs e)
{
WriteDataGrid2Excel2();
down.NavigateUrl = "mmm.xls";
down.Visible =true;
//Response.WriteFile(Server.MapPath(".") + "\\mmm.xls");
}

private void WriteDataGrid2Excel2()
{
SpreadsheetClass xlsheet=new SpreadsheetClass();
int i=0,j=0;
//' 输出标题
foreach(DataGridColumn oItem in DGall.Columns)
{
xlsheet.ActiveSheet.Cells[1, i + 1] = oItem.HeaderText;
//'设置格式
//xlsheet.Range(xlsheet.Cells(1, 1), xlsheet.Cells(1, i + 1)).Font.Bold = True;
//xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, i + 1]).Font =true;;
//xlsheet.get_Range(xlsheet.Cells[1, 1], xlsheet.Cells[1, i + 1]).Font.Color = "red";
i = i + 1;
}
int numbercols=DGall.Items[0].Cells.Count;
// ' 输出字段内容
for(j=0;j<DGall.Items.Count;j++)
{
for(i=0;i<numbercols;i++)
{
//xlsheet.get_Range(xlsheet.Cells[2, 2], xlsheet.Cells[j + 2, i + 1]).Font.Color = "blue";
// 'xlsheet.Range("A2:B14").WrapText = True
xlsheet.get_Range(xlsheet.Cells[2, 1], xlsheet.Cells[j + 2, i + 1]).AutoFitColumns();
xlsheet.ActiveSheet.Cells[j + 2, i + 1] = DGall.Items[j].Cells[i].Text.Replace(" ", " ");//DataGrid1.Items.Item(j).Cells(i).Text.Replace();
//xlsheet.ActiveSheet.Cells[j + 2, 1] =Convert.ToString(j+1);
}
}
try
{
//xlsheet.ActiveSheet.Export("D:\\my.xls",SheetExportActionEnum.ssExportActionNone);
//xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\\Images\\" + Me.xlfile.Text + ".xls", OWC.SheetExportActionEnum.ssExportActionNone);
File.Delete(Server.MapPath(".") + "\\mmm.xls");
xlsheet.ActiveSheet.Export(Server.MapPath(".") + "\\mmm.xls", OWC.SheetExportActionEnum.ssExportActionNone);
}
catch
{
}
}
private void BindDataGrid()
{
cnn.Open();
SqlDataReader reader= sql.ExecuteReader();
DGall.DataSource = reader;
DGall.DataBind();
reader.Close();
cnn.Close();
}

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

}

private void DGall_SelectedIndexChanged(object sender, System.EventArgs e)
{
int index=DGall.SelectedIndex;
string str=DGall.Items[index].Cells[1].Text;
}

private void delete_Click(object sender, System.EventArgs e)
{
CheckBox chkExport;
string Sql="";
foreach(DataGridItem item in DGall.Items )
{
chkExport=(CheckBox)item.FindControl("CB");
if(chkExport.Checked)
{
int index =(int)item.ItemIndex;
string str=DGall.DataKeys[index].ToString();
Sql+=" or(id='"+str+"')";
}
}
if(Sql.Length >2)
{
Sql=Sql.Remove(0,3);
Sql="update QueuePay set examine=null where "+Sql;
mydata.updataClick(Sql);//效率低，需要改进, 已经改进
BindDataGrid();
}
}

private void DGall_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if( e.Item.ItemIndex!= -1)
{
//int i=e.Item.ItemIndex + 1;
e.Item.Cells[0].Text =Convert.ToString(e.Item.ItemIndex + 1);
}

if(e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
//delBttn.Attributes.Add("onclick","javascript:return confirm('确定删除合同：" + ((HyperLink)e.Item.Cells[0].FindControl("ConNom")).Text + "吗？');");
//颜色交替
e.Item.Attributes.Add("onmouseover","this.style.backgroundColor='#0000cc'");
if(e.Item.ItemType == ListItemType.Item)
{
e.Item.Attributes.Add("onmouseout", "this.style.backgroundColor='LightGoldenrodYellow'");
}

if(e.Item.ItemType ==ListItemType.AlternatingItem)
{
e.Item.Attributes.Add("onmouseout", "this.style.backgroundColor='PaleGoldenrod'");
}
}
}
}
}
这是代码

shenlongju 2004-04-22

打赏
举报

<%@ Page language="c#" Codebehind="Report.aspx.cs" AutoEventWireup="false" Inherits="QueuePay.Report" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>所有排队合同</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="C#" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5" name="vs_targetSchema">

</HEAD>
<body leftmargin="0" topmargin="0">
<form id="Form1" method="post" runat="server">
<FONT face="宋体">
<TABLE id="Table1" style="FONT-SIZE: 9pt; Z-INDEX: 101; LEFT: 4px; FONT-FAMILY: 新宋体; TOP: 4px; HEIGHT: 196px"
cellSpacing="0" cellPadding="0" width="100%" border="0">
<TR>
<TD bgColor="darkgray" height="15"><FONT face="宋体"><B>
<asp:label id="Label1" runat="server" DESIGNTIMEDRAGDROP="19">打印数据</asp:label>预览
<asp:Button id="delete" runat="server" Text="删除选择项" Visible="False"></asp:Button></B></FONT></TD>
<TD bgColor="darkgray" height="15"><FONT face="宋体"> </FONT></TD>
<TD bgColor="darkgray" height="15"></TD>
</TR>
<TR>
<TD vAlign="top" colSpan="3"><FONT face="宋体">
<P>
<asp:datagrid id="DGall" runat="server" ShowFooter="True" AutoGenerateColumns="False" BorderColor="Tan"
BorderWidth="1px" BackColor="LightGoldenrodYellow" CellPadding="2" GridLines="Vertical" ForeColor="Black"
Font-Size="9pt" Font-Names="宋体" DataKeyField="id" Width="100%">
<SelectedItemStyle ForeColor="GhostWhite" BackColor="DarkSlateBlue"></SelectedItemStyle>
<AlternatingItemStyle BackColor="PaleGoldenrod"></AlternatingItemStyle>
<ItemStyle HorizontalAlign="Right"></ItemStyle>
<HeaderStyle Font-Bold="True" HorizontalAlign="Center" BackColor="Tan"></HeaderStyle>
<FooterStyle BackColor="Tan"></FooterStyle>
<Columns>
<asp:TemplateColumn HeaderText="排队序号">
<HeaderStyle Width="55px"></HeaderStyle>
<ItemTemplate>
<asp:Label id="xh" runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateColumn>
<asp:BoundColumn DataField="contract" HeaderText="合同编号">
<ItemStyle HorizontalAlign="Left"></ItemStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="provider" HeaderText="公司名称">
<ItemStyle HorizontalAlign="Left"></ItemStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="Allmoney" HeaderText="合同金额(含税)"></asp:BoundColumn>
<asp:BoundColumn DataField="proMoney" HeaderText="质保金"></asp:BoundColumn>
<asp:BoundColumn DataField="protectDate" HeaderText="质保期" DataFormatString="{0:d}">
<ItemStyle Wrap="False" HorizontalAlign="Left"></ItemStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="EnterDate" HeaderText="最后入账日期" DataFormatString="{0:d}">
<ItemStyle Wrap="False" HorizontalAlign="Left"></ItemStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="EnterMoney" HeaderText="入账金额"></asp:BoundColumn>
<asp:BoundColumn DataField="noEnterMoney" HeaderText="未入账金额"></asp:BoundColumn>
<asp:BoundColumn DataField="payDate" HeaderText="最后付款日期" DataFormatString="{0:d}">
<ItemStyle Wrap="False" HorizontalAlign="Left"></ItemStyle>
</asp:BoundColumn>
<asp:BoundColumn DataField="PayMoney" HeaderText="付款金额"></asp:BoundColumn>
<asp:BoundColumn DataField="leftMoney" HeaderText="余额"></asp:BoundColumn>
</Columns>
<PagerStyle HorizontalAlign="Center" ForeColor="DarkSlateBlue" BackColor="PaleGoldenrod"></PagerStyle>
</asp:datagrid>
</FONT></P></TD>
</TR>
<TR>
<TD vAlign="top" colSpan="3"></TD>
</TR>
<TR>
<TD vAlign="top" colSpan="3">
<asp:Button id="ToExcel" runat="server" Text="导出为Excel" CssClass="button" Width="112px" Height="24px"></asp:Button>
<asp:HyperLink id="down" runat="server" Visible="False">保存到指定位置</asp:HyperLink></TD>
</TR>
<TR>
<TD vAlign="top" colSpan="3"></TD>
</TR>
</TABLE>
</FONT>
</form>
</body>
</HTML>
上面是HTML页面

smoothwood 2004-04-22

打赏
举报

imports system.text

Protected Overrides Sub Render(ByVal writer As HtmlTextWriter)
Response.ContentType = "application/ms-excel"
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312")
Response.AppendHeader("content-disposition", "inline;filename=test.xls")
Dim conn As New SqlConnection("Server=localhost;user id=sa;password=ycm119;database=Northwind;")
Dim ds As New DataSet
Dim da As New SqlDataAdapter("Select * from Categories", conn)
da.Fill(ds, "Categories")
DataGrid1.DataSource = ds.Tables("Categories")
DataGrid1.DataBind()
DataGrid1.RenderControl(writer)
End Sub
——————————————————————————————————————
在另外一页，用一个链接，链到加有上面代码的页即可

liuyu202 2004-04-22

打赏
举报

关注！

kob 2004-04-22

打赏
举报

TableToExcel(ds.Tables[0],"c:\\1.xls",true);

FileInfo fi = new FileInfo("c:\\1.xls");
if (!fi.Exists)
return;
string ext = fi.Extension;
Response.Clear();
Response.ClearHeaders();
Response.Buffer = true;
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.Default;
//保证下载文件名为中文
Response.Charset = "GB2312";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.AddHeader("Content-Disposition", "attachment;filename="+HttpUtility.UrlEncode(fi.Name)) ;
//下载文件
Response.WriteFile(fi.FullName,0,fi.Length);

Response.Flush();
Response.End();

kob 2004-04-22

打赏
举报

//给你一个DataTable的你参考
public bool TableToExcel(DataTable dt, string fileName, bool showTitle)
{
bool boolResult = false;
System.IO.FileStream fsobj = null;
System.IO.StreamWriter _sw = null;

try
{

fsobj = new FileStream(fileName,System.IO.FileMode.Create,FileAccess.ReadWrite);//生成一个文件流

_sw = new StreamWriter(fsobj,System.Text.UnicodeEncoding.Unicode); //生成一个写入器

//写列标题
if(showTitle)
{
for(int i=0;i<dt.Columns.Count;i++)
{
_sw.Write(dt.Columns[i].ColumnName+"\t");
}
_sw.Write("\r");
}
//写数据
for(int i=0;i<dt.Rows.Count;i++)
{
for(int j=0;j<dt.Columns.Count;j++)
{

string jj = dt.Rows[i][j].ToString() + " ";

_sw.Write(jj + "\t");

}
_sw.Write("\r");
}

_sw.Close();
fsobj.Close();
boolResult = true;
}
catch(Exception er)
{
string a = er.Message;
if(_sw!=null)
{
_sw.Close();
}
if(fsobj!=null)
{
fsobj.Close();
}
boolResult = false;
}

return boolResult;
}