CODEWITHSUNDEEP
vb.netexceldatagridview
Arun
Arun

Reputation: 11

VB.Net How to export every 'n' number of rows from DataGridView to different tabs in Excel

I have a DGV with 500 rows and 15 columns. I have 5 team members. I have to allot equal amount of rows to my members. So, in the above e.g. I have to send 100 rows to each of my team member from the DGV. First 100 rows will go to Emp#1, Rows 101 - 200 will go to Emp#2, and so on..

I checked this and this but I am not completely able to even try some code to get this logic working.

I am looking for help in vb.net only.

Thx in advance.

Edit: we're little tight on budget, so will not be able to invest in a plugin for now.

Upvotes: 1

Views: 460

Answers (1)

alex.pulver
alex.pulver

Reputation: 2123

You can use EasyXLS Excel library to export an Excel file with 5 sheets and 100 rows each:

' Create an instance of the class that exports Excel files, having 5 sheets
Dim xls As New ExcelDocument(5)

Dim n as Integer = dataGridView.Rows.Count()/5        

For sheet As Integer = 0 To 4
    ' Set sheet names
    xls.easy_getSheetAt(sheet).setSheetName("Emp#" & (sheet+1))

    ' Get the sheet table that stores the data
    Dim xlsTab As ExcelWorksheet = xls.easy_getSheetAt(sheet)
    Dim xlsTable = xlsTab.easy_getExcelTable()
    Dim tableRow = 0

    ' Add data in cells
    For row As Integer = 0 To n - 1
        For column As Integer = 0 To dataGridView.Columns.Count() - 1
            xlsTable.easy_getCell(tableRow, column).setValue( _
                         dataGridView.Rows(n*sheet + row).Cells(column).Value.ToString())
        Next
        tableRow = tableRow + 1
    Next
Next

' Export Excel file
xls.easy_WriteXLSXFile("C:\Samples\Excel.xlsx")

For more details about formatting and how to use this library, you can start from this link that explains how to export a DataGridView to Excel.

Upvotes: 1

Related Questions