Export datagridview to excel using gembox spreadsheet

#Export datagridview to excel using gembox spreadsheet code

If Trim(xlWorkSheet.Cells(e.RowIndex + 2, 1).value) = dataGridView1.Rows(e.RowIndex).Cells(0).Value Then If dataGridView1.Rows(e.RowIndex).Cells(0). ' THIS IS TO ENSURE THAT YOU MODIFY EXISTING DATA IN EXCEL. XlWorkSheet = xlWorkBook.Worksheets('Sheet1') Columns(e.ColumnIndex) Is DataGridViewButtonColumn AndAlso e.RowIndex >= 0 Then ' SAVE THE DATA IN EXCEL AFTER CLICKING THE BUTTON.Дim ourGrid = DirectCast(sender, DataGridView) ' EVERY ROW HAS A BUTTON AT THE LAST COLUMN. Private Sub dataGridView1_CellContentClick(sender As Object, _Д® As ) _ ' SAVE MODIFIED OR NEW DATA TO THE EXCEL SHEET. Return MyBase.ProcessCmdKey(msg, keyData) SendKeys.Send('') ' MOVE NEXT CELL WHEN YOU PRESS ENTER KEY. Protected Overrides Function ProcessCmdKey(ByRef msg As, _Д«yVal keyData As ) As Boolean ReleaseComObject(xlWorkSheet) : xlWorkSheet = Nothing ReleaseComObject(xlWorkBook) : xlWorkBook = Nothing ReleaseComObject(xlApp) : xlApp = Nothing ' ADD A NEW ROW TO THE GRID USING THE ARRAY DATA. XlWorkSheet.Cells(iRow, 2).value.ToString(), _ ' CREATE A STRING ARRAY USING THE VALUES IN EACH ROW OF THE SHEET. If Trim(xlWorkSheet.Cells(iRow, 1).value) = '' ThenД®xit For ' BAIL OUT IF REACHED THE LAST ROW. ' ADD A BUTTON AT THE LAST COLUMN IN EVERY ROW.

If Trim(xlWorkSheet.Cells(1, iCol).value) = '' ThenД®xit For ' BAIL OUT IF REACHED THE LAST COL.Дim col = New DataGridViewTextBoxColumn()Д¬ol.HeaderText = xlWorkSheet.Cells(1, iCol).valueДim colIndex As Integer = dataGridView1. ' FIRST, CREATE THE DataGridView COLUMN HEADERS.ДЇor iCol = 1 To xlWorkSheet. Worksheets('Sheet1') ' THE SHEET WITH THE DATA. Open(sFile) ' WORKBOOK TO OPEN THE EXCEL FILE. Private Sub Excel2Grid(ByVal sFile As String) Filter = 'Excel File|*.xlsx *.xls' ' FILTER ONLY EXCEL FILES IN FILE TYPE. Title = 'Excel File to Edit' ' DIALOG BOX TITLE. Private Sub cmdSelect_Click(sender As System.Object, e As System.EventArgs) Handles cmdSelect.Click The color will differentiate between the existing and new data. Therefore, when you select and import ExcelвЂ™s data to the DataGridView, it would set the first cellвЂ™s value as Read only and change the fore color to Gray. I am using the dataGridView1_RowStateChanged event to identify exiting data in Excel. Else, it would save the record in a new row in the Excel sheet. If yes, then this rows data already exists in the Excel sheet. It checks if the first cell value is Read only (the values are Gray in Color). If itвЂ™s a button, it will extract data from that row and save the data in the Excel sheet. Clicking the button will call this event and it would first check the control that was clicked. The last cell in each row has a Button, to save the data back to Excel file. The dataGridView1_CellContentClick is called when you click inside a GridвЂ™s cell (any cell in any row). Using this method IвЂ™ll override the Enter keys default behavior and convert it to Tab key (function), which moves the cursor horizontally that is, the cursor moves to the next cell. Usually, when you press the Enter key on a DataGridView cell, it would go to the next row. Next, it will add buttons to the last column of each row.ДataGridViewButtonColumn btn = new DataGridViewButtonColumn() ДЇinally, it creates and adds the rows in the DataGridView, using data from the Excel sheet. It first creates the headers for the Grid. This procedure is called after you open the file dialog and select an Excel file. Private void dataGridView1_RowStateChanged(object sender, DataGridViewRowStateChangedEventArgs e)Д®.Row.Cells. MAKE THE VALUES REALONLY (CANNOT CHANGE). CHANGE THE COLOR OF VALUES IN THE FIRST COLUMN. =Д // SECOND COLUMN.Д // THIRD COLUMN.Д THIS IS TO ENSURE THAT YOU MODIFY EXISTING DATA IN EXCEL. CHECK IF THE FIRST COLUMN IS ReadOnly. XlWorkSheet = xlWorkBook.Worksheets // THE SHEET WITH THE DATA. XlWorkBook = (sFileName) // WORKBOOK TO OPEN THE EXCEL FILE.

FIRST, CREATE THE DataGridView COLUMN HEADERS. Open(sFile) // WORKBOOK TO OPEN THE EXCEL FILE. IMPORT DATA FROM EXCEL AND POPULATE THE GRID. OpenFileDialog1.Filter = 'Excel File|*.xlsx *.xls' OpenFileDialog1.Title = 'Excel File to Edit' Private void cmdSelect_Click(object sender, EventArgs e) OPEN FILE DIALOG AND SELECT AN EXCEL FILE. DataGridViewRowStateChangedEventHandler(dataGridView1_RowStateChanged) Д®xcel.Application xlApp = new Excel.Application()

#Export datagridview to excel using gembox spreadsheet code

YOU CAN ALSO ADD THIS CODE IN YOUR '' FILE. ADD THIS CODE TO MAKE 'dataGridView1_RowStateChanged' EVENT WORK PROPERLY. Using System.Drawing // FOR COLOR OPTION.