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]

Show current line number in multiline textbox (C#)

I use the KeyUp and MouseUp events to trigger a quick lookup to show the current line number where the insertion point is in a multiline textbox.  In the following example, my multiline textbox is called txt_FileContents and I wrote a single method (UpdateCurrentLineNumber) that fetches the current line number, the total number of lines in the textbox, and then updates a label (lbl_LineNumber) to show the information.  Happy coding!

[csharp]

void txt_FileContents_KeyUp(object sender, System.Windows.Forms.KeyEventArgs e)
{
UpdateCurrentLineNumber();
}

void txt_FileContents_MouseUp(object sender, System.Windows.Forms.MouseEventArgs e)
{
UpdateCurrentLineNumber();
}

private void UpdateCurrentLineNumber()
{
int index = this.txt_FileContents.SelectionStart;
int line = this.txt_FileContents.GetLineFromCharIndex(index) + 1;

lbl_LineNumber.Text = ” Ln ” + line.ToString(“N0″) + ” / ” + this.txt_FileContents.Lines.Count().ToString(“N0”);
}

[/csharp]

Populate a DataGridView with SqlDataReader

In order to display the data in a SqlDataReader object in a DataGridView control, you first need to load the data into a DataTable object. The code below is in C# and the Connection String is for a SQL Server database connection.

[csharp]
using System.Data.SqlClient;

SqlConnection con = new SqlConnection();
SqlCommand cmd = new SqlCommand();
SqlDataReader dr;

// set the connection string
con.ConnectionString = “Data Source=SqlServerName;Initial Catalog=DbName;Integrated Security=True”
con.Open();

string SQL = “SELECT * FROM tbl_Users”;

// create the SQL command
cmd = new SqlCommand(SQL, con);

// execute the SQL
dr = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);

DataTable table = new DataTable();
table.Load(dr);

reader.Close();
reader.Dispose();
con.Close();
con.Dispose();
cmd.Dispose();
cmd = null;

// Display the data in the DataGridView control…
DataGridView1.DataSource = table;
[/csharp]

Create a File Picker That Defaults to The Desktop (C#)

[csharp]
OpenFileDialog fd = new OpenFileDialog();

fd.InitialDirectory = Path.GetFullPath(Environment.GetFolderPath(Environment.SpecialFolder.Desktop));

fd.ShowDialog();

MessageBox.Show(fd.FileName); // show the selected File path
[/csharp]

How to check/uncheck “child” nodes in a TreeView (C# & VB.NET)

Call this method from within your TreeView’s “AfterCheck” event. Pass it the AfterCheck event’s “sender” and “e” parameters.

C#
[csharp]
private void CheckChildren(object sender, TreeViewEventArgs e)
{
bool boolChecked = e.Node.Checked;

if (e.Node.Parent == null) // parent node
{
foreach (TreeNode node in e.Node.Nodes)
node.Checked = boolChecked;
}
}
[/csharp]

VB.NET:
[vbnet]
Private Sub CheckChildren(sender As Object, e As TreeViewEventArgs)
Dim boolChecked As Boolean = e.Node.Checked

If (e.Node.Parent Is Nothing) Then ‘ parent node
For Each tn As TreeNode In e.Node.Nodes
tn.Checked = boolChecked
Next
End If
End Sub
[/vbnet]

Sequentially run all VB Script (.vbs) files in a directory

Create a new C# Console Application in Visual Studio and compile the code.  Copy the compiled .exe file to the directory containing the .vbs scripts you want to run in sequence.

using System;

using System.Collections.Generic;

using System.Text;

using System.IO;

using System.Diagnostics;

/*————————————————————————–

* Developer: Tom Gee

* Date: April 6, 2010

* Description: This .exe file will run every .VBS file in the same directory

*              as the .exe, but will wait for the current .VBS script to end

*              before beginning the next.

————————————————————————–*/

namespace RunAllVBSinDirectory

{

class Program

{

static void Main(string[] args)

{

try

{

string[] scripts = Directory.GetFiles(Directory.GetCurrentDirectory(), “*.vbs”);

int i = 0;

foreach (string script in scripts)

{

i++;

Console.WriteLine(“Running: ” + script);

using (Process exeProcess = Process.Start(script))

{

exeProcess.WaitForExit();

exeProcess.Close();

exeProcess.Dispose();

}

}

Console.WriteLine(“Ran all ” + i + ” script files in current directory (” + Directory.GetCurrentDirectory() + “)“);

Console.ReadLine();

}

catch (Exception ex)

{

Console.WriteLine(“ERROR! ” + ex.Message + Environment.NewLine + “Inner Exception: ” + ex.InnerException + Environment.NewLine + “Stack Trace: ” + ex.StackTrace);

Console.ReadLine();

}

}

}

}

How to generate a random number in C#

Here’s a quick and dirty way to use Microsoft’s “Random” class for generating a random (or as you will soon find out, not-so-random) number.

Because I believe in code reuse, I started by creating a new function that would return the random number as an integer datatype.

static int RandomNumber(int min, int max)

{

Random random = new Random();

return random.Next(min, max);

}

The only things you have to pass to the function are the upper and lower boundaries that you want the random number to fall between.

int zip = RandomNumber(10000, 99999);

In the above example, a random number between 10000 and 99999 will be assigned to the “zip” variable.

As I stated above, the random number isn’t actually very random and, therefore, might not be a perfect fit for what your trying to use it for.  However, the Random class serves its purpose for quick data generation for application testing.

Happy coding!