另外,把dataset中的数据直接保存到excel中Imports System.Data Imports System.Data.OleDb Public Class Form1 Inherits System.Windows.Forms.Form Private connstr, selectcmd As String Private conn As OleDbConnection Private cmd As OleDbCommand Private myreader As OleDbDataReader
Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim excelApp As Object excelApp = CreateObject("Excel.Application") Dim excelBook As Object excelBook = excelApp.Workbooks.Add Dim excelWorksheet As Object excelWorksheet = excelBook.Worksheets(1) excelApp.Visible = False SaveFileDialog1.Title = "将结果保存为" SaveFileDialog1.Filter = "excel *.xls|*.xls" If SaveFileDialog1.ShowDialog = DialogResult.OK Then If SaveFileDialog1.FileName <> "" Then Dim n As Integer Dim i As Integer = 2 With excelWorksheet Do While myreader.Read() For n = 0 To myreader.FieldCount - 1 .Range(Chr(65 + n) & "1").value = myreader.GetName(n) .Range(Chr(65 + n) & "1").ColumnWidth = 15 .Range(Chr(65 + n) & i.ToString).value = myreader.Item(n) Next n i += 1 Loop End With excelBook.saveas(SaveFileDialog1.FileName) End If End If excelApp.quit() excelApp = Nothing MsgBox("恭喜") conn.close() End Sub Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load connstr = "provider=microsoft.jet.oledb.4.0;data source=d:\dl\dl.mdb" selectcmd = "select * from users" conn = New OleDbConnection(connstr) conn.Open() cmd = New OleDbCommand(selectcmd, conn) myreader = cmd.ExecuteReader End Sub
http://www.cnblogs.com/wangsaokui/articles/9662.aspx但是这个方法在数据量很大的时就变得不可用了。
Imports System.Data.OleDb Public Class Form1
Inherits System.Windows.Forms.Form
Private connstr, selectcmd As String
Private conn As OleDbConnection
Private cmd As OleDbCommand
Private myreader As OleDbDataReader
Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim excelApp As Object
excelApp = CreateObject("Excel.Application")
Dim excelBook As Object
excelBook = excelApp.Workbooks.Add
Dim excelWorksheet As Object
excelWorksheet = excelBook.Worksheets(1)
excelApp.Visible = False
SaveFileDialog1.Title = "将结果保存为"
SaveFileDialog1.Filter = "excel *.xls|*.xls"
If SaveFileDialog1.ShowDialog = DialogResult.OK Then
If SaveFileDialog1.FileName <> "" Then
Dim n As Integer
Dim i As Integer = 2
With excelWorksheet
Do While myreader.Read()
For n = 0 To myreader.FieldCount - 1
.Range(Chr(65 + n) & "1").value = myreader.GetName(n)
.Range(Chr(65 + n) & "1").ColumnWidth = 15
.Range(Chr(65 + n) & i.ToString).value = myreader.Item(n)
Next n
i += 1
Loop
End With
excelBook.saveas(SaveFileDialog1.FileName)
End If
End If
excelApp.quit()
excelApp = Nothing
MsgBox("恭喜")
conn.close()
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
connstr = "provider=microsoft.jet.oledb.4.0;data source=d:\dl\dl.mdb"
selectcmd = "select * from users"
conn = New OleDbConnection(connstr)
conn.Open()
cmd = New OleDbCommand(selectcmd, conn)
myreader = cmd.ExecuteReader
End Sub
End Class
在搞一个datagrid,visible=false
绑定同样的内容,关闭分页
{
if(!IsPostBack)
{
string TheQueryType = Request["TheType"].ToString();
switch(TheQueryType)
{
case "Sex":
BindDataGrid(DataGridForSex);
ToExcel(DataGridForSex);
break;
case "Chronic":
BindDataGrid(DataGridForChronic);
ToExcel(DataGridForChronic);
break;
case "DieReason":
BindDataGrid(DataGridForDieReason);
ToExcel(DataGridForDieReason);
break;
case "Infect":
BindDataGrid(DataGridForInfect);
ToExcel(DataGridForInfect);
break;
case "FaRe":
BindDataGrid(DataGridForFaRe);
ToExcel(DataGridForFaRe);
break;
case "FuXie":
BindDataGrid(DataGridForFaRe);
ToExcel(DataGridForFaRe);
break;
case "Login":
BindDataGrid(DatagridForLogin);
ToExcel(DatagridForLogin);
break;
}
}
} private void BindDataGrid(DataGrid tmpDataGrid)
{
bgObj=new BasePage((SqlConnection)Application["SqlCon"]);
DataSet ds=new DataSet();
SqlParameter[] param=
{
new SqlParameter("@SqlCmd",SqlDbType.VarChar,5000)
};
param[0].Value=Session["TheQuerySql"];
ds=bgObj.ExecDS("执行传入的Sql语句",param);
if(ds.Tables.Count>0)
{
DataColumn id=new DataColumn("ID");
id.DataType=System.Type.GetType("System.String");
ds.Tables[0].Columns.Add(id);
for(int i=1;i<=ds.Tables[0].Rows.Count;i++)
{
ds.Tables[0].Rows[i-1]["ID"]=i;
}
tmpDataGrid.DataSource=ds.Tables[0];
tmpDataGrid.DataBind();
}
} public void ToExcel(Control ctl)
{
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Export.xls");
HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.GetEncoding("GB2312");
HttpContext.Current.Response.ContentType ="application/ms-excel";
ctl.Page.EnableViewState =false;
System.IO.StringWriter tw = new System.IO.StringWriter() ;
System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
ctl.RenderControl(hw);
HttpContext.Current.Response.Write(tw.ToString());
HttpContext.Current.Response.End();
}
#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.Load += new System.EventHandler(this.Page_Load); }
#endregion
}上面代码的实现方式是实际上就是从新绑定了一个DATAGRID!
但是没有分页!绑定的数据集是一样的!