VB.net exporting datagridview to Excel

Question

I have a DataGridView and a button that exports the values of the DataGridView to excel. The question is how can I set the values to non editable or read only when it is sent to the excel? And what is the code to set the default cell sizes of the value where it will be displayed?

Here is my code of the Button:

 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim App_xls As Object
    Dim Lig_cpt, Col_cpt As Integer
    App_xls = CreateObject("Excel.Application")
    App_xls.workbooks.add()

    App_xls.visible = True

    Try

        For Col_cpt = 0 To DataGridView1.ColumnCount - 1
            App_xls.ActiveSheet.cells(1, Col_cpt + 1).value = DataGridView1.Columns(Col_cpt).HeaderText
        Next
        For Lig_cpt = 0 To DataGridView1.Rows.Count - 1
            For Col_cpt = 0 To DataGridView1.ColumnCount - 1
                If IsNumeric(DataGridView1.Item(Col_cpt, Lig_cpt).Value) Then
                    App_xls.ActiveSheet.cells(Lig_cpt + 2, Col_cpt + 1).value = CDbl(DataGridView1.Item(Col_cpt, Lig_cpt).Value)
                Else
                    App_xls.ActiveSheet.cells(Lig_cpt + 2, Col_cpt + 1).value = DataGridView1.Item(Col_cpt, Lig_cpt).Value

                End If
            Next
        Next

    Catch ex As Exception

    End Try


End Sub

`

Answer 1

By default all cells are set as not editable (locked) for a worksheet. But the feature becomes active only when the sheet is protected. Optionally you can set also a password for protection.

The function that can be used for this purpose is Excel.Worksheet.Protect.

If you need any cells to be editable, you must unlocked those cells.

Answer 2

Imports System.Data Imports System.Data.SqlClient Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1

  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click



Dim xlApp As Excel.Application
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim j As Integer

xlApp = New Excel.ApplicationClass
xlWorkBook = xlApp.Workbooks.Add(misValue)
xlWorkSheet = xlWorkBook.Sheets("sheet1")

For i = 0 To DataGridView1.RowCount - 2
  For j = 0 To DataGridView1.ColumnCount - 1
    xlWorkSheet.Cells(i + 1, j + 1) = _
      DataGridView1(j, i).Value.ToString()
  Next
Next

xlWorkSheet.SaveAs("C:\vbexcel.xlsx")
xlWorkBook.Close()
xlApp.Quit()

releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)

MsgBox("You can find the file C:\vbexcel.xlsx")

End Sub

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

Answer 3

To export from DGV to excel, try the script below.

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Data.OleDb

'~~> Define your Excel Objects

Public Class Form1

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value
        Dim i As Integer
        Dim j As Integer

        xlApp = New Excel.Application
        xlWorkBook = xlApp.Workbooks.Add(misValue)
        xlWorkSheet = xlWorkBook.Sheets("sheet1")

        For Each col As DataGridViewColumn In Me.DataGridView1.Columns
            xlWorkSheet.Cells(1, col.Index + 1) = col.HeaderText.ToString
        Next
        Try
            For CurrentRowIndex = 0 To DataGridView1.RowCount - 1 'current row index
                'For j = 0 To Me.DataGridView1.ColumnCount

                For CurrentColumnIndex = 0 To DataGridView1.ColumnCount - 1 'current column index within row index
                    xlWorkSheet.Cells(2, CurrentColumnIndex + 1) = DataGridView1.Columns(CurrentColumnIndex).HeaderText 'display header
                    xlWorkSheet.Cells(CurrentRowIndex + 3, CurrentColumnIndex + 1) = DataGridView1(CurrentColumnIndex, CurrentRowIndex).Value.ToString()
                Next

                'xlWorkSheet.Cells(2, CurrentColumnIndex + 1) = DataGridView1.Columns(CurrentColumnIndex).HeaderText 'display header
                'xlWorkSheet.Cells(i + 2, j + 1) = Me.DataGridView1(j, i).Value.ToString()
                'xlWorkSheet.Cells(2, CurrentColumnIndex + 1) = DataGridView1.Columns(CurrentColumnIndex).HeaderText 'display header
                'Next
            Next

        Catch ex As Exception
            MsgBox("Unable to extract data" & ex.Message, MsgBoxStyle.Critical)
            Exit Sub
        End Try

        xlWorkBook.Activate()

        '//get path
        Me.FolderBrowserDialog1.ShowDialog()
        Dim path As String = Me.FolderBrowserDialog1.SelectedPath

        xlWorkBook.SaveAs(path & "\Excel_With_Headers.xls")
        'xlWorkSheet.SaveAs("burn permit export.xls")

        xlWorkBook.Close()
        xlApp.Quit()

        'releaseObject(xlApp)
        'releaseObject(xlWorkBook)
        'releaseObject(xlWorkSheet)

        MsgBox("You can find your report at " & path & "\burn permit export.xls")
    End Sub
End Class

You can try this as well.

Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click

    Dim xlApp As Excel.Application
    Dim xlWorkBook As Excel.Workbook
    Dim xlWorkSheet As Excel.Worksheet
    Dim misValue As Object = System.Reflection.Missing.Value
    Dim i As Integer
    Dim j As Integer

    xlApp = New Excel.Application
    xlWorkBook = xlApp.Workbooks.Add(misValue)
    xlWorkSheet = xlWorkBook.Sheets("sheet1")

    Try
        For CurrentRowIndex = 0 To DataGridView1.RowCount - 1 'current row index
            'xlWorkSheet.Cells(1, 1) = "With Headers"
            For CurrentColumnIndex = 0 To DataGridView1.ColumnCount - 1 'current column index within row index
                xlWorkSheet.Cells(2, CurrentColumnIndex + 1) = DataGridView1.Columns(CurrentColumnIndex).HeaderText 'display header
                xlWorkSheet.Cells(CurrentRowIndex + 3, CurrentColumnIndex + 1) = DataGridView1(CurrentColumnIndex, CurrentRowIndex).Value.ToString()
            Next
        Next
    Catch ex As Exception
        MsgBox("Unable to extract data" & ex.Message, MsgBoxStyle.Critical)
        Exit Sub
    End Try
End Sub