Importing an excel file and displaying it on a datagridview

  • (2 Pages)
  • +
  • 1
  • 2

18 Replies - 19747 Views - Last Post: 21 December 2013 - 06:04 AM Rate Topic: ***** 1 Votes

#1 MrHappEnose  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 01-April 13

Importing an excel file and displaying it on a datagridview

Posted 01 April 2013 - 07:53 PM

I'm writing an application that can save the information from a datagridview to an excel file (.xlsx ), and reload the data from the file to the datagridview. I have the save part done and working, but I'm not sure how to go about the loading part. Right now it saves to the documents folder by default. Any help?

Private Sub SaveButton_Click(sender As System.Object, e As System.EventArgs) Handles SaveButton.Click

        'Create an object of the ApplicationClass
        Dim ExcelApp As New Excel.ApplicationClass()

        'Add a new workbook to the object
        ExcelApp.Application.Workbooks.Add(Type.Missing)

        'Change properties of the Workbook 
        ExcelApp.Columns.ColumnWidth = 30

        'Copy the contents of the DataGridView object to the cells of the Excel Application object
        For i As Integer = 0 To DataGridView1.Rows.Count - 1
            Dim row As DataGridViewRow = DataGridView1.Rows(i)
            For j As Integer = 0 To row.Cells.Count - 1
                ExcelApp.Cells(i + 1, j + 1) = row.Cells(j).Value
            Next
        Next

        'Save the workbook at any suitable location
        ExcelApp.ActiveWorkbook.SaveCopyAs(NameBox.Text)
        ExcelApp.ActiveWorkbook.Saved = True

        'Exit the Excel Application and free up the resources 
        ExcelApp.Quit()
    End Sub


Is This A Good Question/Topic? 0
  • +

Replies To: Importing an excel file and displaying it on a datagridview

#2 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 06:20 AM

You could either reverse the logic of your for loops taking from the worksheet rows and columns to the datagridview rows and columns or you could load it as a datasource which would be a lot less effort.

Take the following example:
'Build a connection string template
Dim connectionStringTemplate As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                                 "Data Source={0};" & _
                                                 "Extended Properties=""Excel 12.0;" & _
                                                 "HDR=Yes;IMEX=1"""

'sFilename contains the path and file information to your workbook
'use string.format to fill in the placeholders of your connectionstringtemplate.
Dim connectionString As String = String.Format(connectionStringTemplate, sFileName)
'Create a select statement
Dim sqlSelect As String = "SELECT * FROM [IMPORT$];" ' Where you have a sheet named IMPORT 

Dim workbook As New DataSet 'Instantiate a dataset
'instantiate an adapter and pass it the sql statement and the connectionstring
Dim excelAdapter As System.Data.Common.DataAdapter = _
    New System.Data.OleDb.OleDbDataAdapter(sqlSelect, connectionString)

excelAdapter.Fill(workbook)'Use the adapter to fill the dataset from its query result.
'Set your datagridview's datasource to your datatable 
dgvImport.DataSource = worksheet.Tables(0)


Of course you can omit the template and just build a connectionstring statically. This was just an example of some code I wrote for a small application.

I have also removed the try...catch to make it easier to follow.

Make sure you are disposing of your objects

My code used
  releaseObject(excel)
  releaseObject(workbook)
  releaseObject(worksheet)

when finished with everything. Which called

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub


This is the only way I have found to cleanly remove the instances of the excel objects used.
Was This Post Helpful? 2
  • +
  • -

#3 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3515
  • View blog
  • Posts: 11,999
  • Joined: 12-December 12

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 07:55 AM

@CharlieMay I like your ReleaseObject method; I would plus your rep but my buttons aren't working..

I used the following recently:

        GC.Collect()
        GC.WaitForPendingFinalizers()

        GC.Collect()
        GC.WaitForPendingFinalizers()

        Marshal.FinalReleaseComObject(oWS)

        oWB.Close(SaveChanges:=False)
        Marshal.FinalReleaseComObject(oWB)

        oExcel.Quit()
        Marshal.FinalReleaseComObject(oExcel)


Do you know your method to be without issue (no offence :)) ?
Was This Post Helpful? 0
  • +
  • -

#4 MrHappEnose  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 01-April 13

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 08:55 AM

So here's what I've got so far:

    Private Sub SaveButton_Click(sender As System.Object, e As System.EventArgs) Handles SaveButton.Click

        'Create an object of the ApplicationClass
        Dim ExcelApp As New Excel.ApplicationClass()

        'Add a new workbook to the object
        ExcelApp.Application.Workbooks.Add(Type.Missing)

        'Change properties of the Workbook 
        ExcelApp.Columns.ColumnWidth = 30

        'Copy the contents of the DataGridView object to the cells of the Excel Application object
        For i As Integer = 0 To DataGridView1.Rows.Count - 1
            Dim row As DataGridViewRow = DataGridView1.Rows(i)
            For j As Integer = 0 To row.Cells.Count - 1
                ExcelApp.Cells(i + 1, j + 1) = row.Cells(j).Value
            Next
        Next

        'Save the workbook at any suitable location
        ExcelApp.ActiveWorkbook.SaveCopyAs(NameBox.Text)
        ExcelApp.ActiveWorkbook.Saved = True

        'Exit the Excel Application and free up the resources 
        ExcelApp.Quit()
    End Sub

    Private Sub RecallButton_Click(sender As System.Object, e As System.EventArgs) Handles RecallButton.Click
        DataGridView1.DataSource = Nothing

        'Build a connection string template
        Dim connectionStringTemplate As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                                                 "Data Source={0};" & _
                                                 "Extended Properties=""Excel 12.0;" & _
                                                 "HDR=Yes;IMEX=1"""

        'Filename contains the path and file information to your workbook
        Dim FileName As String = NameBox.Text

        'use string.format to fill in the placeholders of your connectionstringtemplate.
        Dim connectionString As String = String.Format(connectionStringTemplate, FileName)

        'Create a select statement
        Dim strSQL As String = "SELECT * FROM [Sheet1$]"

        Dim ds As New DataSet 'Instantiate a dataset

        'instantiate an adapter and pass it the sql statement and the connectionstring
        Dim excelAdapter As System.Data.Common.DataAdapter = _
            New System.Data.OleDb.OleDbDataAdapter(strSQL, connectionString)
        excelAdapter.Fill(ds) 'Use the adapter to fill the dataset from its query result.

        'Have to set the columns to invisible because 3 new ones are created to the right of the datagrid for some reason..
        DataGridView1.Columns(0).Visible = False
        DataGridView1.Columns(1).Visible = False
        DataGridView1.Columns(2).Visible = False
        DataGridView1.DataSource = ds.Tables("dTable").DefaultView

        ' dispose used objects
        ds.Dispose()
        excelAdapter.Dispose()
    End Sub
End Class


I'm getting an error at the excelAdapter.Fill(ds) part. It's saying OleDbException was unhandled. IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3515
  • View blog
  • Posts: 11,999
  • Joined: 12-December 12

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 09:35 AM

Try with:

DataGridView1.DataSource = ds.Tables(0).DefaultView

as this table hasn't been named "dTable".

You may need before this line:

DataGridView1.DataSource = Nothing

I got this to run but it is not inserting the rows in the right place: requires a little more work. Maybe .Rows.Clear or .Refresh (not sure at the moment).
Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3515
  • View blog
  • Posts: 11,999
  • Joined: 12-December 12

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 09:41 AM

Oh, I see that you already have .DataSource = Nothing earlier in your code.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3515
  • View blog
  • Posts: 11,999
  • Joined: 12-December 12

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 09:53 AM

I used

    DataGridView1.Rows.Clear()
    DataGridView1.Columns.Clear()

and set HDR=No in the connection-string as my saved Excel data had no header-row.

This post has been edited by andrewsw: 02 April 2013 - 09:55 AM

Was This Post Helpful? 0
  • +
  • -

#8 MrHappEnose  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 01-April 13

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 10:08 AM

I'm still getting an error at the excelAdapter.Fill(ds) part. It's saying OleDbException was unhandled. IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
Was This Post Helpful? 0
  • +
  • -

#9 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 10:16 AM

So in your initial save to excel. are you opening an existing spreadsheet, populating it and then saving it back?

If you look in task manager, is it actually closing out all instances of excel?

I did find one thing doing a search on the error about getting that with an incorrect filepath but I can't reproduce that as it just notifies me that the file wasn't found.

View Postandrewsw, on 02 April 2013 - 10:55 AM, said:

@CharlieMay I like your ReleaseObject method; I would plus your rep but my buttons aren't working..


Thanks, but I can't take credit for it, it was found in a search when I noticed that excel wasn't being shut down completely ;)
Was This Post Helpful? 1
  • +
  • -

#10 MrHappEnose  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 01-April 13

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 10:34 AM

No, creating a new one or overwriting the file with a new one.
Yes, after I close the program there are no instances of excel running.
Was This Post Helpful? 0
  • +
  • -

#11 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 10:47 AM

hmmm, I did see where this error came up with a sheet containing some obscure formulas so I thought I'd mention it. Would it be possible to attach the workbook or pm it to me so I can run it against my code? Not that my code is any different than yours though. Not sure what you have access to on the site with only 4 posts.
Was This Post Helpful? 0
  • +
  • -

#12 MrHappEnose  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 01-April 13

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 11:00 AM

I sent the zipped project to you via pm.
Was This Post Helpful? 0
  • +
  • -

#13 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3515
  • View blog
  • Posts: 11,999
  • Joined: 12-December 12

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 11:07 AM

My test version used the full file-path and name, including the .xlsx extension, to the file, and there was nothing in Sheet1 other than the data that it saved when pressing the Save button.

I also changed ApplicationClass() to Application.

ApplicationClass:

Quote

This API supports the .NET Framework infrastructure and is not intended to be used directly from your code.

http://msdn.microsof...office.14).aspx

This post has been edited by andrewsw: 02 April 2013 - 11:05 AM

Was This Post Helpful? 0
  • +
  • -

#14 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1605
  • View blog
  • Posts: 5,162
  • Joined: 25-September 09

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 11:14 AM

OK, I had to make a few changes by including the path in the filename.

It worked as expected.

Where you have Dim FileName as String = NameBox.Text
I used Dim FileName as string = DirBox.Text & NameBox.Text.

However, that doesn't make sense that it worked that way because it should have just saved to the path where the executable was at. When I initially ran, I went into the debug folder and there was no xlsx file to be found. I'm going to do a search to see where it actually stored it.

Anyway, by specifying the path and filename for save and retrieve, it pulled the information up using your application.

OK it saved to my MyDocuments folder
Suggestion, put a savefiledialog up to save the file to a location and and openfiledialog up to open the file.

If you want to do this without user interaction. At least specify where this file needs to be saved so that same path can be use to retrieve.

One method is to use the Application.StartupPath which is the folder where the executable resides.

This post has been edited by CharlieMay: 02 April 2013 - 11:19 AM

Was This Post Helpful? 0
  • +
  • -

#15 MrHappEnose  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 7
  • Joined: 01-April 13

Re: Importing an excel file and displaying it on a datagridview

Posted 02 April 2013 - 11:20 AM

I didn't want to include the whole filepath because I want it to save and load from the My Documents folder. The application is going to be run on different computers.

I was thinking that maybe later I could let the user specify the exact location they wanted. But right now I'm just trying to get the save/load part working, which is why I set the DirBox text to read-only.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2