5

I'm trying to accomplish export to Excel from a VB.NET (Windows Forms) application.

Unfortunately, I can't use Office Interops because the application should work on every machine - even if there is no Excel installed.

I found the following sample on the Net:

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

This code works and exports my dataset to an .xls file.

The problem: I can't open this file while my program is running. It seems my program is still having a handle on this file. I can see it whenever I use the Sysinternals Process Explorer. If I close my program, I can open this file without any problems.

I think I have to destroy some object or just close the file. Please could anyone help a noob to accomplish it?

6
  • With this way, you still need to have the provider installed on the machine. It seems to me that Jet is not present everywhere by default. Why not switch to OpenXml? .xlsx files can be now be opened with previous version of Excel with compatibility pack. Just a thought... Commented Sep 4, 2013 at 21:36
  • Ditto XML - the DataTable as a.WriteXML method. You need to supply a .Name to the table. The user can open the file from File Open or drag/drop on Excel. OR create a .CSV file which Excel to open easily. Kellerman Software has a cheap .Net CSV (and Excel I think) library. Commented Sep 4, 2013 at 23:58
  • @Chris: I like the idea with OpenXML; where could I find some [noob-aware] samples? Commented Sep 5, 2013 at 8:23
  • @Chris: you still need to have the provider installed on the machine On a fresh Windows XP SP3 without any updates and other software is the ODBC driver for Excel already in. And this code works on it (after installing of .NET 4). Commented Sep 5, 2013 at 11:56
  • I was not sure concerning the provider. Happy that it works:) I will update my answer as soon I can to provide links to openxml snippets if your are still interested. Commented Sep 5, 2013 at 12:54

1 Answer 1

4

I don't know if this is the problem, it could. You do not Dispose the OleDbCommand objects. It's possible that it maintains a reference to the file. Try this:

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

Also note that a form that you display with ShowDialog() method must be disposed too.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.