Winform 用 DataGrid 导出 EXCEL!!!!!!!!!!!!!!!!!!!!!!!!!大家帮帮忙~

kitome 2009-02-04 12:46:13

是通过一个按钮来导出EXCEL的,我本来有个是WEB 上的导出EXCEL 代码如下: Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
conn.Open()
Dim commandString As String = "Select id,name,number From chanpin"
Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(commandString, conn)
dataAdapter.Fill(ds)
'Dim dataTable1 As DataTable = ds.Tables("Table")
Dim dataTable1 As DataTable = ds.Tables(0)
Dim gd As GridView = New GridView()
Response.Clear()
Response.BufferOutput = True
Response.Charset = "GB2312"
Response.AppendHeader("Content-Disposition", "attachment;filename=123.xls")
Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312")
Response.ContentType = "application/ms-excel"
EnableViewState = False
Dim cultureInfo As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("ZH-CN", True)
Dim stringWriter As System.IO.StringWriter = New System.IO.StringWriter(cultureInfo)
Dim textWriter As System.Web.UI.HtmlTextWriter = New System.Web.UI.HtmlTextWriter(stringWriter)
gd.DataSource = dataTable1
gd.DataBind()
gd.RenderControl(textWriter)
Response.Write(stringWriter.ToString())
Response.End()

End Sub
现在我想把它改成WINFORM下的导出麻烦各位大侠帮我改下把!谢谢了!

...全文

192 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

_NET2004 2009-02-04

打赏
举报

该论坛上某位朋友的代码



  Public   Sub   DStoXls(ByVal   Table   As   DataTable,   ByVal   DefFileName   As   String) 

    

                Dim   MyOleDbCn   As   New   System.Data.OleDb.OleDbConnection 

                Dim   MyOleDbCmd   As   New   System.Data.OleDb.OleDbCommand 

                Dim   MyTable   As   New   DataTable 

                Dim   intRowsCnt,   intColsCnt   As   Integer 

                Dim   strSql   As   String,   strFlName   As   String 

                Dim   Fso   As   New   System.Object 

                If   Table   Is   Nothing   Then 

                        MessageBox.Show( "未取得數據，無法導出 ",   "導出錯誤 ",   MessageBoxButtons.OK,   MessageBoxIcon.Error) 

                        Exit   Sub 

                End   If 

                MyTable   =   Table 

                If   MyTable.Rows.Count   =   0   Then 

                        MessageBox.Show( "未取得數據，無法導出 ",   "導出錯誤 ",   MessageBoxButtons.OK,   MessageBoxIcon.Error) 

                        Exit   Sub 

                End   If 

                Dim   FileName   As   String 

                Dim   SaveFileDialog   As   New   SaveFileDialog 

                SaveFileDialog.InitialDirectory   =   My.Computer.FileSystem.SpecialDirectories.MyDocuments 

                SaveFileDialog.Title   =   "保存為 " 

                SaveFileDialog.Filter   =   ".xls|*.xls " 

                SaveFileDialog.FileName   =   DefFileName 

                If   (SaveFileDialog.ShowDialog()   =   System.Windows.Forms.DialogResult.OK)   Then 

                        FileName   =   SaveFileDialog.FileName 

                        '   TODO:   在此加入開啟檔案的程式碼。 

                End   If 

                If   FileName   =   " "   Then   Exit   Sub 

                strFlName   =   FileName 

                If   Dir(FileName)   <>   " "   Then 

                        Kill(FileName) 

                End   If 

                Try 

                        Windows.Forms.Cursor.Current   =   System.Windows.Forms.Cursors.WaitCursor 



                        MyOleDbCn.ConnectionString   =   "Provider=Microsoft.Jet.OleDb.4.0; "   &   _ 

                        "Data   Source= "   &   strFlName   &   "; "   &   _ 

                        "Extended   ProPerties= " "Excel   8.0;HDR=Yes; " " " 

                        MyOleDbCn.Open() 

                        MyOleDbCmd.Connection   =   MyOleDbCn 

                        MyOleDbCmd.CommandType   =   CommandType.Text 



                        '第一行插入列标题 

                        strSql   =   "CREATE   TABLE   "   &   DefFileName   &   "( " 

                        For   intColsCnt   =   0   To   MyTable.Columns.Count   -   1 

                                If   intColsCnt   <>   MyTable.Columns.Count   -   1   Then 

                                        strSql   =   strSql   &   ChangeChar(MyTable.Columns(intColsCnt).Caption)   &   "   text, " 

                                Else 

                                        strSql   =   strSql   &   ChangeChar(MyTable.Columns(intColsCnt).Caption)   &   "   text) " 

                                End   If 

                        Next 

                        MyOleDbCmd.CommandText   =   Replace(strSql,   ". ",   "_ ")   '創建表的時候，不允許有“.”的字段名，所以要用“ˍ”來替換 

                        MyOleDbCmd.ExecuteNonQuery() 



                        '插入各行 

                        For   intRowsCnt   =   0   To   MyTable.Rows.Count   -   1 

                                strSql   =   "INSERT   INTO   "   &   DefFileName   &   "   VALUES( ' " 

                                For   intColsCnt   =   0   To   MyTable.Columns.Count   -   1 

                                        If   intColsCnt   <>   MyTable.Columns.Count   -   1   Then 

                                                strSql   =   strSql   &   ChangeChar(MyTable.Rows(intRowsCnt).Item(intColsCnt))   &   " ', ' " 

                                        Else 

                                                strSql   =   strSql   &   ChangeChar(MyTable.Rows(intRowsCnt).Item(intColsCnt))   &   " ') " 

                                        End   If 

                                Next 

                                MyOleDbCmd.CommandText   =   strSql 

                                MyOleDbCmd.ExecuteNonQuery() 

                        Next 

                        MessageBox.Show( "数据已经成功导入EXCEL文件 "   &   strFlName,   "数据导出 ",   MessageBoxButtons.OK,   MessageBoxIcon.Information) 

                Catch   ErrCode   As   Exception 

                        MsgBox( "错误信息： "   &   ErrCode.Message   &   vbCrLf   &   vbCrLf   &   _ 

                        "引发事件： "   &   ErrCode.TargetSite.ToString,   MsgBoxStyle.OkOnly   +   MsgBoxStyle.Information,   "错误来源： "   &   ErrCode.Source) 

                        Exit   Sub 

                        'Finally 

                        MyOleDbCmd.Dispose() 

                        MyOleDbCn.Close() 

                        MyOleDbCn.Dispose() 

                End   Try 



        End   Sub

kitome 2009-02-04

打赏
举报

结贴了~

kitome 2009-02-04

打赏
举报

现在好了谢谢你呵呵~

_NET2004 2009-02-04

打赏
举报

你的strFileName是空值

Dim strFileName As String

你改成这样看看

Dim strFileName As String=sfd.FileName

看你代码很累

kitome 2009-02-04

打赏
举报

我现在重新写了下代码是这样的
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sfd As New SaveFileDialog

sfd.Filter = ".xls|*.xls "
sfd.FilterIndex = 1
sfd.Title = "保存文件"
sfd.FileName = 123

Dim strconn As String = "data source=192.168.1.253\SQL2005;initial catalog=product;User ID=test;password=searching"
Dim conn As New SqlConnection(strconn)
Try
conn.Open()
Dim ds As New DataSet
Dim commandString As String = "Select id,name,number From chanpin"
Dim dataAdapter As SqlDataAdapter = New SqlDataAdapter(commandString, conn)
dataAdapter.Fill(ds)
'Dim dataTable1 As DataTable = ds.Tables("Table")

Dim sw As StreamWriter
Dim strFileName As String
sw = New StreamWriter(strFileName, True, System.Text.Encoding.Default) Dim dataTable1 As DataTable = ds.Tables(0)
Dim dg As DataGrid = New DataGrid()
Dim strCol As String

For i As Integer = 0 To ds.Tables("table").Columns.Count - 1

strCol &= ds.Tables("table").Columns(i).ColumnName & ","

Next

sw.WriteLine(strCol)

For i As Integer = 0 To ds.Tables("table").Rows.Count - 1

strCol = Nothing

For j As Integer = 0 To ds.Tables("table").Columns.Count - 1
strCol &= ds.Tables("table").Rows(i).Item(j) & ","
Next

sw.WriteLine(strCol)

Next

sw.Flush()
sw.Close()

sw = Nothing

conn.Close()

MessageBox.Show("导出成功！")

Catch ex As Exception
MessageBox.Show(ex.ToString(), "错误!", MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try
End Sub
但是提示我错误说红色字部分有错误~这个是什么问题呢

_NET2004 2009-02-04