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]

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!