vb.net – 没有Interop的Excel导出

发布时间：2020-12-17 00:05:45 所属栏目：大数据来源：网络整理

导读：我正在尝试从VB.NET( Windows窗体)应用程序完成导出到Excel. 不幸的是,我不能使用Office Interops,因为应用程序应该可以在每台机器上运行 – 即使没有安装Excel也是如此. 我在网上发现了以下示例： Public Sub ExportDatasetToExcel(ByVal ds As DataSet,Opt

我正在尝试从VB.NET( Windows窗体)应用程序完成导出到Excel.

不幸的是,我不能使用Office Interops,因为应用程序应该可以在每台机器上运行 – 即使没有安装Excel也是如此.

我在网上发现了以下示例：

Public Sub ExportDatasetToExcel(ByVal ds As DataSet,Optional ByVal strHeader As String = "Save As")

    'Proudly copied from:
    'http://www.daniweb.com/software-development/vbnet/threads/368400/write-into-excel-using-oledb-connection#post1583200

    Dim fileSave As New SaveFileDialog()

    fileSave.Filter = "Excel 97-2003 Workbook (*.xls)|*.xls"
    fileSave.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
    fileSave.Title = strHeader
    fileSave.ShowDialog()

    Dim xlsFilePath As String = fileSave.FileName

    If xlsFilePath = "" Then
        Exit Sub
    End If

    System.IO.File.Copy(storagePath & "" & "empty.xls",xlsFilePath)

    Cursor.Current = Cursors.WaitCursor

    Dim conn As New OleDb.OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}';" & "Extended Properties='Excel 8.0;HDR=YES;'",xlsFilePath))

    conn.Open()

    Dim strTableQ(ds.Tables.Count) As String

    Dim i As Integer = 0

    'making table query
    For i = 0 To ds.Tables.Count - 1

        strTableQ(i) = "CREATE TABLE [" & ds.Tables(i).TableName & "]("

        Dim j As Integer = 0
        For j = 0 To ds.Tables(i).Columns.Count - 1
            Dim dCol As DataColumn
            dCol = ds.Tables(i).Columns(j)
            strTableQ(i) &= " [" & dCol.ColumnName & "] varchar(255),"
        Next
        strTableQ(i) = strTableQ(i).Substring(0,strTableQ(i).Length - 2)
        strTableQ(i) &= ")"

        Dim cmd As New OleDb.OleDbCommand(strTableQ(i),conn)
        cmd.ExecuteNonQuery()

    Next

    'making insert query
    Dim strInsertQ(ds.Tables.Count - 1) As String
    For i = 0 To ds.Tables.Count - 1
        strInsertQ(i) = "Insert Into " & ds.Tables(i).TableName & " Values ("
        For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
            strInsertQ(i) &= "@" & ds.Tables(i).Columns(k).ColumnName & ","
        Next
        strInsertQ(i) = strInsertQ(i).Substring(0,strInsertQ(i).Length - 2)
        strInsertQ(i) &= ")"
    Next

    'Now inserting data
    For i = 0 To ds.Tables.Count - 1
        For j As Integer = 0 To ds.Tables(i).Rows.Count - 1
            Dim cmd As New OleDb.OleDbCommand(strInsertQ(i),conn)
            For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
                cmd.Parameters.AddWithValue("@" & ds.Tables(i).Columns(k).ColumnName.ToString(),ds.Tables(i).Rows(j)(k).ToString())
            Next
            cmd.ExecuteNonQuery()
            cmd.Parameters.Clear()
        Next
    Next
    conn.Close()
    conn.Dispose()
    Cursor.Current = Cursors.Default
End Sub

此代码可以工作并将我的数据集导出到.xls文件.

问题：我的程序运行时无法打开此文件.看来我的程序仍然处理这个文件.每当我使用Sysinternals Process Explorer时,我都能看到它.如果我关闭我的程序,我可以毫无问题地打开这个文件.

我想我必须销毁一些对象或者只是关闭文件.请有人帮助一个菜鸟完成它吗？

我不知道这是不是问题,它可以.您不要处置OleDbCommand对象.它可能会维护对文件的引用.试试这个：

Public Sub ExportDatasetToExcel(ByVal ds As DataSet,Optional ByVal strHeader As String = "Save As")

    'Proudly copied from:
    'http://www.daniweb.com/software-development/vbnet/threads/368400/write-into-excel-using-oledb-connection#post1583200

    Using fileSave As New SaveFileDialog()
        fileSave.Filter = "Excel 97-2003 Workbook (*.xls)|*.xls"
        fileSave.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop)
        fileSave.Title = strHeader

        If fileSave.ShowDialog() = Windows.Forms.DialogResult.OK Then
            Dim xlsFilePath As String = fileSave.FileName
            If xlsFilePath = "" Then Exit Sub

            System.IO.File.Copy(storagePath & "" & "empty.xls",xlsFilePath)

            Cursor.Current = Cursors.WaitCursor

            Using conn As New OleDb.OleDbConnection(String.Format("provider=Microsoft.Jet.OLEDB.4.0; Data Source='{0}';" & "Extended Properties='Excel 8.0;HDR=YES;'",xlsFilePath))
                conn.Open()
                Dim strTableQ(ds.Tables.Count) As String
                Dim i As Integer = 0

                'making table query
                For i = 0 To ds.Tables.Count - 1
                    strTableQ(i) = "CREATE TABLE [" & ds.Tables(i).TableName & "]("

                    Dim j As Integer = 0
                    For j = 0 To ds.Tables(i).Columns.Count - 1
                        Dim dCol As DataColumn
                        dCol = ds.Tables(i).Columns(j)
                        strTableQ(i) &= " [" & dCol.ColumnName & "] varchar(255),"
                    Next
                    strTableQ(i) = strTableQ(i).Substring(0,strTableQ(i).Length - 2)
                    strTableQ(i) &= ")"

                    Using cmd As New OleDb.OleDbCommand(strTableQ(i),conn)
                        cmd.ExecuteNonQuery()
                    End Using
                Next

                'making insert query
                Dim strInsertQ(ds.Tables.Count - 1) As String
                For i = 0 To ds.Tables.Count - 1
                    strInsertQ(i) = "Insert Into " & ds.Tables(i).TableName & " Values ("
                    For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
                        strInsertQ(i) &= "@" & ds.Tables(i).Columns(k).ColumnName & ","
                    Next
                    strInsertQ(i) = strInsertQ(i).Substring(0,strInsertQ(i).Length - 2)
                    strInsertQ(i) &= ")"
                Next

                'Now inserting data
                For i = 0 To ds.Tables.Count - 1
                    For j As Integer = 0 To ds.Tables(i).Rows.Count - 1
                        Using cmd As New OleDb.OleDbCommand(strInsertQ(i),conn)
                            For k As Integer = 0 To ds.Tables(i).Columns.Count - 1
                                cmd.Parameters.AddWithValue("@" & ds.Tables(i).Columns(k).ColumnName.ToString(),ds.Tables(i).Rows(j)(k).ToString())
                            Next
                            cmd.ExecuteNonQuery()
                            cmd.Parameters.Clear()
                        End Using
                    Next
                Next
                conn.Close()
                Cursor.Current = Cursors.Default

            End Using
        End If
    End Using
End Sub

另请注意,必须同时处理使用ShowDialog()方法显示的表单.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!