Export Data Grid (datagridview) to Excel in VB.NET

Create the following method and pass it the DataGridView that you want to export.  This will export all grid contents (including column headers) to Excel and “freeze” the top row.

Note: The “prog_Progress” object is a ProgressBar I added to the application to display the progress of the export to the user.

[csharp]

private void ExportDataGridViewToExcel(DataGridView dgv)
{
// exit method if there are no records to export
if (dgv.RowCount == 0)
{
MessageBox.Show(“No data to export.”, “No Data”, MessageBoxButtons.OK, MessageBoxIcon.Stop);
return;
}

SaveFileDialog fd = new SaveFileDialog();

fd.OverwritePrompt = false;
fd.FileName = “Export-” + DateTime.Now.ToString(“yyyyMMdd.hhmm”) + “.xls”; // default file name
fd.Filter = “Excel (*.xls) |*.xls;*.xlsx”;

if (fd.ShowDialog() == DialogResult.OK) // only continue if user specified/selected a file
{
UpdateStatus(“Exporting data to Excel…”);

// create the Excel app
Microsoft.Office.Interop.Excel._Application xlApp = new Microsoft.Office.Interop.Excel.Application();

// create the Workbook
Microsoft.Office.Interop.Excel._Workbook xlWB = xlApp.Workbooks.Add();

// create the Worksheet
Microsoft.Office.Interop.Excel._Worksheet xlWS = (Microsoft.Office.Interop.Excel._Worksheet)xlWB.Worksheets[1];

try
{
// show the progress bar
prog_Progress.Visible = true;
prog_Progress.Style = ProgressBarStyle.Blocks;
prog_Progress.Maximum = dgv.RowCount – 1;

// export the column headers
for (int c = 0; c < dgv.ColumnCount; c++)
{
xlWS.Cells[1, c + 1] = dgv.Columns[c].HeaderText;
}

// bold and underline the first row
Microsoft.Office.Interop.Excel.Range rng = (Microsoft.Office.Interop.Excel.Range)xlWS.Rows[1];
rng.EntireRow.Font.Bold = true;
rng.EntireRow.Font.Underline = true;

// freeze the top row
xlApp.ActiveWindow.SplitRow = 1;
xlApp.ActiveWindow.FreezePanes = true;

// export the data in the DataGridView
for (int r = 0; r <= dgv.RowCount – 1; r++)
{
Application.DoEvents(); // prevent the app from “Not Responding”…
prog_Progress.Value = r; // update the Progress Bar

for (int c = 0; c <= dgv.ColumnCount – 1; c++)
{
xlWS.Cells[r + 2, c + 1] = dgv[c, r].Value;
}
}

xlWS.Columns.AutoFit(); // autofit the columns

xlWB.SaveAs(fd.FileName); // save the file

prog_Shares.Visible = false;

xlWB.Close();
xlWS = null;
xlWB = null;
xlApp.Quit();
fd = null;

MessageBox.Show(“Excel Workbook Created Successfully”, “Export Complete”, MessageBoxButtons.OK, MessageBoxIcon.Information);
UpdateStatus(“Export to Excel complete!”);
}
catch (Exception ex)
{
xlWB.SaveAs(fd.FileName); // save the file
xlApp.Quit(); // quit Excel
prog_Progress.Value = prog_Shares.Maximum;
MessageBox.Show(“Error exporting data to Excel.” + Environment.NewLine + ex.Message, “Export error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
prog_Shares.Visible = false;
}
}
}

[/csharp]

Setting a Control’s “BackColor” to a System color

[vbnet]

controlname.BackColor = Drawing.Color.FromKnownColor(Drawing.KnownColor.Control)

[/vbnet]

How to fetch data from using a BackgroundWorker (VB.NET)

BackgroundWorkers (which I will refer to as bgWorker) are great for doing background processing in your application, thus preventing your program from going into a “Not Responding” status.  One downfall, however, is that you cannot pass or reference data in a separate thread from the bgWorker.  The following code shows how to get around this by utilizing Global/Public variables because they are referenced between all threads of the application.

Global/Public Variables
[vbnet] ‘ public variables
Public dr As OdbcDataReader
Public conn As OdbcConnection

Dim miliSecond As Long = 1000
Dim timerSeconds As Long
Dim startDttm As Date
Dim endDttm As Date
Dim sqlComm As String
Dim dt As New DataTable
Dim da As New OdbcDataAdapter
Dim strConnString As String
Dim comm As OdbcCommand
[/vbnet]
How to call the BackgroundWorker
[vbnet]
bgWorker = New BackgroundWorker
bgWorker.WorkerReportsProgress = True
bgWorker.WorkerSupportsCancellation = True
bgWorker.RunWorkerAsync() ‘ run the background worker
[/vbnet]
Run the BackgroundWorker and fetch the data
[vbnet]
Private Sub FetchData(ByVal sender As Object, ByVal e As DoWorkEventArgs) Handles bgWorker.DoWork
Try
Dim conn As New OdbcConnection(strConnString)
startDttm = Now()

comm = New OdbcCommand(sqlComm)
comm.Connection = conn
comm.Connection.Open()

dt = New DataTable
da = New OdbcDataAdapter
da.SelectCommand = comm ‘ Utilize the SELECT statement
da.Fill(dt) ‘ Fill the Data Table

Catch ex As Exception
MsgBox(“ERROR: ” & ex.Message, MsgBoxStyle.Critical, “Error running SQL”)
dbClose()
End Try
End Sub
[/vbnet]
What to do when the BackgroundWorker is finished
[vbnet]
Private Sub bgWorkerCompleted(ByVal sender As Object, ByVal e As RunWorkerCompletedEventArgs) Handles bgWorker.RunWorkerCompleted
Try
UpdateDataGrid()

Catch ex As Exception
bgWorker.CancelAsync()
End Try
End Sub
[/vbnet]
Update the DataGridView (dg)
[vbnet]
Private Sub UpdateDataGrid()
Try
Dim intCount As Integer
intCount = dt.Rows.Count ‘ number of rows returned by SQL

Select Case intCount
Case Is < 1000 ‘ fill if less than 1000 rows
Me.dg.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
dg.DataSource = dt ‘ Populate the Data Grid with the Data Table values
dr = comm.ExecuteReader
Case Else
If MsgBox(“This will display ” & Format(dt.Rows.Count, “###,###,##0″) & ” rows. Would you like to continue?”, MsgBoxStyle.YesNo, “Continue?”) = MsgBoxResult.Yes Then
Me.dg.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
dg.DataSource = dt ‘ Populate the Data Grid with the Data Table values
dr = comm.ExecuteReader
End If
End Select

dbClose()

dg.AutoResizeColumns()

If comm.ExecuteNonQuery >= 0 Then
intCount = comm.ExecuteNonQuery
End If

AddToSummary(Format(intCount, “###,###,##0″) & IIf(intCount = 1, ” record found”, ” records found”), False)

Dim dur As Global.System.TimeSpan = Now.Subtract(startDttm)
AddToSummary(” – ” & dur.Seconds & “.” & dur.Milliseconds & ” seconds”, True)

Catch ex As Exception
MessageBox.Show(“Error updating data grid”, “Error”, MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
[/vbnet]
Close the Database Connection

[vbnet]
Private Sub dbClose()
Try ‘ Close the Data Reader
dr.Close()
dr = Nothing
Catch ex As Exception
‘MsgBox(“Error closing the data reader” & vbCrLf & ex.Message, MsgBoxStyle.Critical, “Data Reader Close Issue”)
End Try

Try ‘ Close the Connection
conn.Dispose()
conn = Nothing
Catch ex As Exception
MsgBox(“Error closing the connection” & vbCrLf & ex.Message, MsgBoxStyle.Critical, “ODBC Connection Close Issue”)
End Try
End Sub
[/vbnet]
Add to Summary method – I use a TextBox (txt_Status) on my Form for displaying application status to the user
[vbnet]
Private Sub AddToSummary(ByVal strText As String, ByVal Append As Boolean)
If Append Then
Me.txt_Status.Text += strText
Else
Me.txt_Status.Text = strText
End If
End Sub
[/vbnet]

Copy Selected DataGridView Cells to Clipboard

To copy selected cells to the Clipboard without Column Headers:

If dg.GetClipboardContent Is Nothing Then

MessageBox.Show(“Nothing selected to copy to clipboard.”)

Else

Me.dg.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableWithoutHeaderText

Clipboard.SetDataObject(Me.dg.GetClipboardContent)

End If

 

To copy selected cells to the Clipboard with Column Headers requires a bit more work because there is a leading tab/column that needs to be removed:

If dg.GetClipboardContent Is Nothing Then

MessageBox.Show(“Nothing selected to copy to clipboard.”)

Else

Me.dg.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText

Dim clipData As String = dg.GetClipboardContent.GetText()

Dim lines As String() = Regex.Split(clipData, “rn”)

Dim newClipData As New StringBuilder

Dim tab As Char = vbTab

For Each line As String In lines

newClipData.Append(line.Substring(line.IndexOf(tab) + 1) + vbCrLf)

Next

Clipboard.SetText(newClipData.ToString(), TextDataFormat.Text)

End If

Password Mask an unbound TextBox column in a DataGridView (VB.NET)

If you have ever wanted to mask the contents in a TextBox column on a DataGridView (DGV) control, you will notice that there is no pre-defined “password” format that you can apply.  I have gotten around this by setting the DataPropertyName attribute of the column and then checking the related property in the CellFormatting event.  It’s actually pretty simple and doesn’t involve very much coding at all!

DataPropertyName = "password"

After you have set the DataPropertyName for the related column(s) in your DGV, you need to add code that will evaluate this property when the data is loaded.  For this, I will use the CellFormatting event.

Before we begin, I want to set the character that will be displayed instead of actual value.  For this, I will use an asterisk (*).

Private pwd As Char = “*”

In the below example, my DataGridView is named dg_Divisions.  This routine will store the value of any field with the DataPropertyName of “password” into the related cell’s Tag property and will change each character in the Value to an asterisk.  If the field is not a “password” field, it will set the Tag to Nothing (null).

Private Sub dgv_PwdFormatting(ByVal sender As Object, ByVal e As DataGridViewCellFormattingEventArgs) Handles dg_Divisions.CellFormatting

If dg_Divisions.Columns(e.ColumnIndex).DataPropertyName = “password” And e.Value <> Nothing Then

dg_Divisions.Rows(e.RowIndex).Tag = e.Value

e.Value = New String(pwd, e.Value.ToString.Length)

Else

dg_Divisions.Rows(e.RowIndex).Tag = Nothing

End If

End Sub

After the Tags and Values have been changed, we want the user to be able to see and modify the value if they begin editing the cell.  To do this, I will use the EditingControlShowing event.

Private Sub dgv_PwdEditing(ByVal sender As Object, ByVal e As DataGridViewEditingControlShowingEventArgs) Handles dg_Divisions.EditingControlShowing

If (dg_Divisions.CurrentRow.Tag <> Nothing) Then

e.Control.Text = dg_Divisions.CurrentRow.Tag.ToString()

End If

End Sub

Value displayed as asterisks

Editing the cell displays the underlying contents

That’s all there is to it!  Happy coding!

Move an unbound DataGridView row up or down via code (VB.NET)

Doing something as simple as moving an unbound DataGridView (dgv) row up or down via code really shouldn’t be as difficult of a task as Visual Studio makes it.

I started out by creating two buttons on a Windows Form called btn_Up and btn_Down, respectively.  I then created a single sub routine that would be passed in the number of rows (+1 or -1) that the current row should be moved.  For example, to move a row “up” in dgv, it’s row index will be one less than it currently is (before the move)

Private Sub btn_Up_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Up.Click

        ‘If already on the first row, don’t try to move the row up

        If Me.dgv_FinishingScheduleColumns.CurrentCell.RowIndex = 0 Then

            Exit Sub

        End If

MoveRow(-1) ‘ move up in the datagridview (row index is 1 less)

End Sub

Private Sub btn_Down_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Down.Click

‘If already on the bottom row, don’t try to move the row down

        If Me.dgv_FinishingScheduleColumns.CurrentCell.RowIndex = Me.dgv_FinishingScheduleColumns.RowCount – 1 Then

            Exit Sub

End If

MoveRow(1) ‘ move down in the datagridview (row index is 1 more)

End Sub

Private Sub MoveRow(ByVal i As Integer)

Try

If (Me.dgv.SelectedCells.Count > 0) Then

Dim curr_index As Integer = Me.dgv.CurrentCell.RowIndex

Dim curr_col_index As Integer = Me.dgv.CurrentCell.ColumnIndex

Dim curr_row As DataGridViewRow = Me.dgv.CurrentRow

Me.dgv.Rows.Remove(curr_row)

Me.dgv.Rows.Insert(curr_index + i, curr_row)

Me.dgv.CurrentCell = Me.dgv(curr_col_index , curr_index + i)

End If

Catch ex As Exception

‘ do nothing if error encountered while trying to move the row up or down

End Try

End Sub

In addition to moving the row that contains the selected cell up or down, the MoveRow sub routine also selects the related cell in its “new” location after it has been moved.  I kept trying to set the CurrentCell.Selected value, but in the end, I needed to actually specify what the CurrentCell actually was.

Happy programming!

Private Sub btn_Up_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Up.Click

MoveRow(-1) ‘ move up in the datagridview (row index is 1 less)

End Sub

Private Sub btn_Down_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_Down.Click

MoveRow(1) ‘ move down in the datagridview (row index is 1 more)

End Sub

Private Sub MoveRow(ByVal i As Integer)

Try

If (Me.dg_Environments.SelectedCells.Count > 0) Then

Dim curr_index As Integer = Me.dg_Environments.CurrentCell.RowIndex

Dim curr_cell_index As Integer = Me.dg_Environments.CurrentCell.ColumnIndex

Dim curr_row As DataGridViewRow = Me.dg_Environments.CurrentRow

Me.dg_Environments.Rows.Remove(curr_row)

Me.dg_Environments.Rows.Insert(curr_index + i, curr_row)

Me.dg_Environments.CurrentCell = Me.dg_Environments(curr_cell_index, curr_index + i)

End If

Catch ex As Exception

‘ do nothing if error encountered while trying to move the row up or down

End Try

End Sub