CODEWITHSUNDEEP
vb.netexceldatagridviewexport
CaffeinatedMike
CaffeinatedMike

Reputation: 1607

Looking to export datagridview

I have a scrolling datagridview that dyanmically changes it's number of columns and rows. I'm looking for a way to export the datagridview as it is when the user clicks a button. I want it to export as something that cannot be edited (so not excel file). I've tried using iTextSharp to export as pdf and can't seem to come up with a dynamically changing loop that would suit it. Also, I haven't been able to find any sample code using iTextSharp that included also exporting row headers along with column headers. I've also tried one solution (that I can't seem to find at the moment) on the microsoft forums that takes advantage of a the add-on functionality of Excel to write to PDF after the datagridview contents are exported to it. The problem I have with that is it creates way to many pages as well as still shows rows that have been hidden or deleted from the datagridveiw. Any idea of how I can accomplish this feat?

I'll include two pictures to show the way in which the datagridview is dynamically populated and changes

It can have up to 66 rows and 12 columns (not including row or column headers) datagridview example 1 datagridview example 2

Upvotes: 1

Views: 2794

Answers (2)

Bipul Singh
Bipul Singh

Reputation: 1

This is My Coding Tested And Output is Perfect :

    Dim ExcelApp As Object, ExcelBook As Object
    Dim ExcelSheet As Object
    Dim i As Integer
    Dim j As Integer

    'create object of excel
    ExcelApp = CreateObject("Excel.Application")
    ExcelBook = ExcelApp.WorkBooks.Add
    ExcelSheet = ExcelBook.WorkSheets(1)

    With ExcelSheet
        For Each col As DataGridViewColumn In Me.DataGridView1.Columns
            ExcelSheet.Cells(1, col.Index + 1) = col.HeaderText.ToString
            For i = 1 To Me.DataGridView1.RowCount
                ExcelSheet.cells(i + 1, 1) = Me.DataGridView1.Rows(i - 1).Cells("First Column Name").Value
                For j = 1 To DataGridView1.Columns.Count - 1
                    ExcelSheet.cells(i + 1, j + 1) = DataGridView1.Rows(i - 1).Cells(j).Value
                Next
            Next
        Next
    End With
    ExcelApp.Visible = True
    ExcelSheet = Nothing
    ExcelBook = Nothing
    ExcelApp = Nothing

Upvotes: 0

Vland
Vland

Reputation: 4262

You can use EPPlus to create an password-protected (locked) excel file that can be read, but not edited. I can show you how to start from a situation like this:

enter image description here

and get a protected file like this:

enter image description here

First you must download and include the EPPlus Library in your project. Don't forget to check the great EPPlusSamples Project

Then you need a method to extract your DataGridView VISIBLE data (you don't want the invisible column to be exported). I did it using a 2d array. This function accept a DataGridView parameter and returns a 2d array:

Function GetDataGridView2DArray(ByVal dataGridView As DataGridView) As String(,)
    'Save list of visible columns
    Dim nrVisibleColumns = (From c As DataGridViewColumn In DataGridView1.Columns
               Where c.Visible
               Select c).ToList()

    'create 2d-array to store values, dimensions given by number of rows and visible columns
    Dim dgvArray(nrVisibleColumns.Count, DataGridView1.Rows.Count) As String

    'create the first row with Column Headers text
    For Each col As DataGridViewColumn In nrVisibleColumns
        dgvArray(col.Index + 1, 0) = col.HeaderText
    Next

    'create Rows, including Row Header text
    For Each row As DataGridViewRow In DataGridView1.Rows
        Dim rowNumber = row.Index + 1
        dgvArray(0, rowNumber) = DataGridView1.Rows(row.Index).HeaderCell.Value 'save rowheader cell value 

        For Each col As DataGridViewColumn In nrVisibleColumns
            dgvArray(col.Index + 1, rowNumber) = DataGridView1(col.Index, row.Index).Value
        Next
    Next

    Return dgvArray
End Function

Now that you have your array you can create an Excel File and fill it with data. Plus, before saving, we'll lock it with a password to prevent user editing.

Private Sub CreateXls()
    Dim fi = New FileInfo("C:\temp\output.xlsx")
    Dim package As New ExcelPackage()

    Dim ws = package.Workbook.Worksheets.Add("Dgv Output") 'create sheet

    'get array of datagridview data
    Dim dataArray = GetDataGridView2DArray(DataGridView1)

    'loop my 2d array and fill my excel file
    For iColumn As Integer = dataArray.GetLowerBound(0) To dataArray.GetUpperBound(0)
        For iRow As Integer = dataArray.GetLowerBound(1) To dataArray.GetUpperBound(1)
            ws.Cells(iRow + 1, iColumn + 1).Value = dataArray(iColumn, iRow)
            ws.Cells(iRow + 1, iColumn + 1).Style.Locked = True 'lock the cell
        Next 
    Next

    ws.Cells.AutoFitColumns() 'resize columns to fit
    ws.Protection.AllowFormatColumns = True 'let user resize columns if he wants
    ws.Protection.SetPassword("1") 'protect the sheet from editing

    package.SaveAs(fi) 'save file
End Sub

Now you should be able to easily export your dynamic DataGridView with one click.

Upvotes: 1

Related Questions