首页 新闻 搜索 专区 学院

数据库和EXCEL的互导

0
[已解决问题] 解决于 2008-11-05 11:38

正在做个软件要实现SQL Server导成EXECEL 或txt的文件,还要实现这些文件导入数据库

小琳的主页 小琳 | 初学一级 | 园豆:200
提问于:2008-11-04 11:55
< >
分享
最佳答案
0

可以解决的:我这里是.NET代码,可以改其他代码,差不多的。。

sql导入excel函数: 

 Public Function XBWexcel(ByVal xbw2 As String) As Integer
        Dim myDataSet As New DataSet
        Try
            Dim myConn As New SqlConnection(strconn)
            myConn.Open()
            Dim strCom As String = xbw2  ’导出时的sql查询语句
            Dim myCommand As New SqlDataAdapter(strCom, myConn)
            myCommand.Fill(myDataSet, "xbwtable")
            myConn.Close()
        Catch ey As Exception
            MessageBox.Show("连接错误! " + ey.ToString(), "错误")
        End Try
        Dim table As DataTable = myDataSet.Tables("xbwtable")
        Dim AppExcel As Excel.Application = New Excel.Application '创建一个空的Excel电子表格文档
        AppExcel.Workbooks.Add(True)
        Dim colIndex As Integer = 0 '读取数据的字段名称,并在产生的Excel表格的第一行显示出来
        Dim col As DataColumn = New DataColumn
        For Each col In table.Columns
            colIndex = colIndex + 1
            AppExcel.Cells(1, colIndex) = col.ColumnName
        Next
        Dim rowIndex As Integer = 1
        Dim row As DataRow
        For Each row In table.Rows
            rowIndex = rowIndex + 1
            colIndex = 0
            Dim col1 As DataColumn
            For Each col1 In table.Columns
                colIndex = colIndex + 1
                AppExcel.Cells(rowIndex, colIndex) = row(col1.ColumnName).ToString()
            Next
        Next
        AppExcel.Visible = True
    End Function

执行上面函数时指定xbw2的参数,比如 xbw2 as straing="select * from [表] where [条件1]。。。"  然后执行XBWexcel(xbw2)就可以了。

下面是把excel导入sql数据库:

Dim OpenFileDialog As New OpenFileDialog
        OpenFileDialog.InitialDirectory = My.Application.Info.DirectoryPath()
        OpenFileDialog.Filter = "EXCEL文件(*.xls)|*.xls"
        If (OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
            Dim FileName As String = OpenFileDialog.FileName
            Dim xbwmsg As String
            xbwmsg = MsgBox("文件:'" & FileName & "'你是否确定导入!?", 1)
            If xbwmsg <> vbOK Then Exit Sub
            Dim MstrConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & FileName & "';Extended Properties=Excel 8.0;HDR=YES; IMEX=1"
            Dim myDataset As New DataSet
            Dim da As New OleDbDataAdapter("select * from [$偏差报告]", MstrConn)
            Me.Cursor = Cursors.WaitCursor
            PictureBox1.Visible = True
            Label21.Visible = True
            Label21.Text = "数据读取,请稍候……10%"
            Me.Enabled = False
            Try
                da.Fill(myDataset)
                DataGrid1.DataSource = myDataset.Tables.Item(0).DefaultView
            Catch ex As Exception
                MsgBox(ex.Message.ToString)
                Exit Sub
            End Try
            Label21.Text = "数据读取,请稍候……100%"

到这里已经差不多了,相信你可以把刚才导入新建的数据库表格自己导入sql数据库了。这样做的好处是灵活机动,可以修改导出的数据库文件,也可以增加数据库文件。。。。。。自己去体会吧。。。

XBW | 菜鸟二级 |园豆:404 | 2008-11-04 20:30
其他回答(2)
0

我想不可能吧,可能的话,也之用到xml了

Toby Xu | 园豆:275 (菜鸟二级) | 2008-11-04 13:07
0

c#有专门支持读取execel的类,也有支持读取数据库的类。用dataset

天堂口 | 园豆:514 (小虾三级) | 2008-11-04 13:41
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册