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]

VB: Disable all controls on a form

I use this code to cycle through all controls on a form and disable them.  After each control has been disabled, you can then specify individual controls that you wish to be enabled.  This is a handy way to force users to click specific buttons or enter data in specific fields before the rest of the controls on a form become active (enabled).

Private Sub LockControls()
‘ Disable all the controls on the form
Dim ctrl As Control

For Each ctrl In Me.Controls
ctrl.Enabled = False
Next

Me.txt_Log.Enabled = True ‘ re-enable the log box
Me.progBar.Enabled = True ‘ re-enable the progress bar
Me.lst_ItemGroups.Enabled = True ‘ re-enable the listbox
End Sub

To enable all controls on the form, use the following function:

Private Sub UnlockControls()
‘ Enable all the controls on the form
Dim ctrl As Control

For Each ctrl In Me.Controls
ctrl.Enabled = True
Next
End Sub

‘ Disable all the controls on the form

VB.NET LEFT and RIGHT functions

VBA and VB6 developers are used to the familiar LEFT and RIGHT string manipulation functions.  However, VB.NET no longer has these functions built-in. As a result, when I create a new VB.NET application, I create the following two functions which I can then use to replicate the “old” LEFT and RIGHT functionality.

Public Function Left(ByVal Value As String, ByVal Length As Integer) As String
‘ Rereate a LEFT function for string manipulation
If Value.Length >= Length Then
Return Value.Substring(0, Length)
Else
Return Value
End If
End Function

Public Function Right(ByVal Value As String, ByVal Length As Integer) As String
‘ Recreate a RIGHT function for string manipulation
If Value.Length >= Length Then
Return Value.Substring(Value.Length – Length, Length)
Else
Return Value
End If
End Function