CODEWITHSUNDEEP
excelvb.netdatagridviewexport-to-excel
Haddadi Abdraouf
Haddadi Abdraouf

Reputation: 15

How to save to datagridview data in one excel file

i am using microsoft.excel.introp.dll to save datagridview data to an excel file, my question is how to save two datagridview data in one file (sheet1 and sheet2)

i am using this code to save the first datagridview as excel file:

    Private Sub copyAlltoClipboard()
        DataGridView1.SelectAll()
        Dim dataObj As DataObject = DataGridView1.GetClipboardContent()
        If dataObj IsNot Nothing Then Clipboard.SetDataObject(dataObj)
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
            MessageBox.Show("Exception Occurred while releasing object " & ex.ToString())
        Finally
            GC.Collect()
        End Try
    End Sub

 Dim sfd As SaveFileDialog = New SaveFileDialog()
            sfd.Filter = "Microsoft Excel 97-2003 Workbook (*.xls)|*.xls"

            sfd.FileName = NomTextBox.Text & "_" & PrenomTextBox.Text

            If sfd.ShowDialog() = DialogResult.OK Then
                copyAlltoClipboard()
                Dim xlexcel As Excel.Application
                Dim xlWorkBook As Excel.Workbook
                Dim xlWorkSheet As Excel.Worksheet
                Dim misValue As Object = System.Reflection.Missing.Value
                xlexcel = New Excel.Application()
                xlexcel.Visible = False
                xlWorkBook = xlexcel.Workbooks.Add(misValue)
                xlWorkSheet = CType(xlWorkBook.Worksheets.Item(1), Excel.Worksheet)
                Dim CR As Excel.Range = CType(xlWorkSheet.Cells(1, 1), Excel.Range)
                CR.[Select]()
                xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, True)

 xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
                xlexcel.DisplayAlerts = True
                xlWorkBook.Close(True, misValue, misValue)
                xlexcel.Quit()
                releaseObject(xlWorkSheet)
                releaseObject(xlWorkBook)
                releaseObject(xlexcel)
                Clipboard.Clear()

This not my code i have take it from an other person and modified it.

Upvotes: 0

Views: 243

Answers (1)

JohnG
JohnG

Reputation: 9479

To start, you should be aware that using Interop is not recommended. There are other third-party libraries that work better in many ways and have some advantages. Namely, using interop requires that the target computer have the same Excel library as the code uses. However, I am aware that this may be required or an only option. If this is the case, then the code below may help.

One issue the current code has is that there is no control over what the “worksheet name” should be. IMHO, it is better to give the worksheet a “specific” name so that the code could later overwrite the file with new data. By simply adding new worksheets, you may end up with a bunch of worksheets with the same data.

Therefore, I created a method GetWorksheet that takes a Workbook and String name. This method will return a Worksheet that either “matches” the given name OR if the worksheet name is not found in the given workbook, then, it will return a new worksheet with the given name. This method may look something like…

Private Function GetWorksheet(wb As Excel.Workbook, name As String) As Excel.Worksheet
  For Each ws In wb.Sheets
    If ws.Name = name Then
      Return ws
    End If
  Next
  Dim newWS = wb.Worksheets.Add()
  newWS.Name = name
  Return newWS
End Function

The code above loops through all the worksheets in the given workbook. If a worksheet’s name is found that matches the given name, then that worksheet is returned. If the worksheet name is not found, then a new worksheet with the given name is returned. This will allow you to name the worksheets to something more intuitive than “Sheet1”, “Sheet2” etc. We can use this method to get an existing worksheet or create a new worksheet for each grid we want to add to the workbook.

Next, a second helper method called AddSheetToWorkbook that takes a workbook, a DataGridView and a worksheet name... may make things easier. In this method, the code would select all the cells in the given grid. Use the above method to get a worksheet with the given worksheet name from the given (OPEN) workbook. Then paste the copied cells into the worksheet. It may look something like…

Private Sub AddSheetToWorkbook(xlWorkBook As Excel.Workbook, dgv As DataGridView, wkSheetName As String)
  dgv.SelectAll()
  Dim dataObj As DataObject = dgv.GetClipboardContent()
  If dataObj IsNot Nothing Then Clipboard.SetDataObject(dataObj)
  Dim xlWorkSheet = GetWorksheet(xlWorkBook, wkSheetName)
  Dim CR As Excel.Range = CType(xlWorkSheet.Cells(1, 1), Excel.Range)
  CR.[Select]()
  xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, True)
End Sub

We can use this method to add each DataGridView to a different worksheet in the workbook.

Finally, putting all this together may look something like below. I used a little different approach using the Try/Catch/Finally to make sure the Excel COM objects are disposed of properly, however, in my test, the current code also works.

The code uses the same approach as the current code. Get the file name for the workbook using an SaveFileDialog. Create a new Excel app, add a workbook, then add the two grids to the workbook. Each grid will be on a sperate worksheet with the supplied worksheet name. The file is saved and Finally the COM objects are closed and released.

It is unclear “where” this code is called in the current posted code. In this example, the code is run by pressing a button on the form.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  Dim sfd As SaveFileDialog = New SaveFileDialog With {
    .Filter = "Microsoft Excel 97-2003 Workbook (*.xls)|*.xls",
    .FileName = NomTextBox.Text & "_" & PrenomTextBox.Text
  }
  If sfd.ShowDialog() = DialogResult.OK Then
    Dim xlexcel As Excel.Application = Nothing
    Dim xlWorkBook As Excel.Workbook = Nothing
    Dim misValue As Object = Reflection.Missing.Value
    Try
      xlexcel = New Excel.Application()
      xlWorkBook = xlexcel.Workbooks.Add()
      AddSheetToWorkbook(xlWorkBook, DataGridView1, "Grid1")
      AddSheetToWorkbook(xlWorkBook, DataGridView2, "Grid2")
      xlWorkBook.SaveAs(sfd.FileName, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue)
    Catch ex As Exception
      MessageBox.Show("Error: " + ex.Message)
    Finally
      If (xlWorkBook IsNot Nothing) Then
        xlWorkBook.Close()
        Runtime.InteropServices.Marshal.ReleaseComObject(xlWorkBook)
      End If
      If (xlexcel IsNot Nothing) Then
        xlexcel.Quit()
        Runtime.InteropServices.Marshal.ReleaseComObject(xlexcel)
      End If
      MessageBox.Show("Export Complete")
    End Try
  End If
End Sub

Lastly, I am guessing you do not need the column headers in the Excel file since an OS select all and copy command is not going to grab the grids column header text.

I hope this makes sense.

Upvotes: 1

Related Questions