如何用VB.NET将datagrid数据导入excel?

[flyzhuzhu]

如何用VB.NET将datagrid数据导入excel?.net新手请大侠帮帮忙!!!先谢了.

[ajex]

http://xml.sz.luohuedu.net/xml/ShowList.asp?id=1

[yuanylong]

1）首先在项目中注册OWC组件。（在工程中添加.COM引用）
2）在导出事件中写入如下函数：
'在运行时连接，并设置连接属性
MyConn = New System.Data.OleDb.OleDbConnection("Provider=MSDAORA.1;Data Source=oraerp9i;User ID=test;Password=test;")

Dim xlsheet As New OWC.SpreadsheetClass()

Dim selectconn As String = "select * from syslog"
Dim comsql As OleDb.OleDbCommand = New OleDb.OleDbCommand(selectconn, MyConn)
MyConn.Open()
Dim comsqlreader As OleDb.OleDbDataReader = comsql.ExecuteReader

Dim numcols As Integer = comsqlreader.FieldCount
Dim row As Integer = 1
Dim i As Integer

While comsqlreader.Read
For i = 0 To numcols - 1
xlsheet.ActiveCell.Cells(row, i + 1) = comsqlreader.GetValue(i).ToString()
Next
row += 1
End While

comsqlreader.Close()
MyConn.Close()
Try
xlsheet.ActiveSheet.Export（Server.MapPath(".")+"\\"+ & Me.TextBoxlname.Text & ".xls", OWC.SheetExportActionEnum.ssExportActionNone)
Me.RegisterStartupScript("pop", "<script>alert('成功导出数据，请查看相关文件');</script>")
Catch
Me.RegisterStartupScript("pop", "<script>alert('导出失败，请检查用户权限和文件夹是否存在');</script>")
End Try

[lihonggen0]

参考：

http://support.microsoft.com/default.aspx?scid=kb;en-us;q308247

HOW TO: Use ASP.NET to Query and Display Database Data in Excel by Using Visual Basic .NET

[树猫]

public class export
{
public export()
{
}
public void ToExcel(System.Web.UI.Control ctl)
{
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls");
HttpContext.Current.Response.Charset ="UTF-8";
HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default;
HttpContext.Current.Response.ContentType ="application/ms-excel";//image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword
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();
}

//

}


System.Web.UI.Control ctl为datagrid名称

/*----------------------------------------------------------*/
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=bffacb7f-f240-409e-a2a2-99fcd6e3b064
/===================================================================/
public void ToExcel(System.Web.UI.Control ctl,string FileNameStr)
{
HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=" + FileNameStr + ".xls");
HttpContext.Current.Response.Charset="UTF-8";
HttpContext.Current.Response.ContentEncoding=System.Text.Encoding.UTF8;
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();
}
/*--------------------------------------------------------------*/
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
if(this.Request.QueryString["ClassGuid"]!=null)
{
JBInfoBase.SingMems pSM=new JBInfoBase.SingMems();
string pFileName=this.Request.QueryString["ClassName"].ToString();
System.Data.DataTable pDateTable=pSM.SearchExcelDate(this.Request.QueryString["ClassGuid"].ToString().Trim());
HttpResponse rsp;
rsp=Page.Response;

rsp.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");//乱码问题
rsp.AppendHeader("Content-Disposition", "attachment;filename="+pFileName ); //'必要，做成下载文件

Excel.Application oExcel=null;


Excel.Workbook oBook;
Object oMissing=System.Reflection.Missing.Value ;
oExcel=new Excel.Application();
oBook=oExcel.Workbooks.Add(oMissing);
int rows;
int clumns;
// System.Globalization.CultureInfo pa=new System.Globalization.CultureInfo("zh-CHS");
//
// System.Globalization.NumberFormatInfo pN=new System.Globalization.NumberFormatInfo();

rows=pDateTable.Rows.Count-1;
clumns=pDateTable.Columns.Count-1;
for(int i=0;i<=rows;i++)
{
// oExcel.get_Range(oExcel.Cells[i,6],oExcel.Cells[i+1,6]).ClearFormats();
for(int j=0;j<=clumns;j++)
{
if(i==0)
{

oExcel.Cells[1,j+1]=pDateTable.Columns[j].ColumnName.ToString();
}

oExcel.Cells[i+2,j+1]=pDateTable.Rows[i][j].ToString();
}
}

oBook.Saved=true;
oExcel.UserControl=false;


string file=Server.MapPath(".")+"\\" + pFileName;//Server.MapPath(".")服务器保存地址
oExcel.ActiveWorkbook.SaveCopyAs(file);
//oExcel.Quit();

rsp.WriteFile(file);
oExcel.Quit();
oExcel=null;

// rsp.Flush();
// rsp.End();
//JBInfoBase.MakeExcel pME=new JBInfoBase.MakeExcel();
//pME.GenerateFile(this.Page,this.Request.QueryString["ClassName"].ToString(),pDT);
}
}

[guoyan19811021]

http://xml.sz.luohuedu.net/xml/ShowDetail.asp?id=BF0A54F9-C7C7-4200-BD9A-802AC1F5DE50