Home > C# > Export DataGridView into Excel without using COM

Export DataGridView into Excel without using COM

This feature will allow to you export data into excel without using or refering any COM

static Boolean ExportToExcel(string FilePath, DataGridView dataGridView, out Exception ex)
{
ex = null;
string strFileNameWithPath = FilePath;
try
{
string connectionString = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” + strFileNameWithPath + “;Extended Properties=Excel 8.0”;
OleDbConnection objConnection = new OleDbConnection(connectionString);
objConnection.Open();
OleDbDataAdapter objAdapter = new OleDbDataAdapter();
//Dim objCommand As New OleDbCommand(“CREATE TABLE [Employees] ( EmployeeID int,LastName nvarchar(20),FirstName nvarchar(10),Title nvarchar(30))”, objConnection)
//generate command
string createText = “CREATE TABLE [” + dataGridView.Name + “] (“;
for (int i = 0; i <= dataGridView.Columns.Count – 1; i++)
{
createText += dataGridView.Columns[i].HeaderText +” varchar(255), “;
}
if (createText.EndsWith(“, “))
{
createText = createText.Substring(0, createText.Length – 2);
}
createText +=”)”;
OleDbCommand objCommand = new OleDbCommand(createText, objConnection);
objCommand.ExecuteNonQuery();
//Dim objExportCommand As New OleDbCommand(“INSERT INTO Employees (EmployeeID, LastName,FirstName,Title) VALUES (@EmployeeID,@LastName, @FirstName,@Title)”, objConnection)
string insertText = “INSERT INTO [” + dataGridView.Name + “] (“;
for (int i = 0; i <= dataGridView.Columns.Count – 1; i++)
{
insertText += dataGridView.Columns[i].HeaderText +”,”;
}
insertText = insertText.Substring(0, insertText.Length – 1) +
“)”;
//’ add parameter
for (int i = 0; i < dataGridView.Rows.Count; i++)
{
string commandInsert = “”;
for (int j = 0; j < dataGridView.Columns.Count; j++)
{
commandInsert +=”‘” + Convert.ToString(dataGridView.Rows[i].Cells[j].Value).Trim() + “‘,”;
}
if (commandInsert.EndsWith(“,”))
{
commandInsert = commandInsert.Substring(0, commandInsert.Length – 1);
}
commandInsert = insertText +” VALUES(” + commandInsert + “)”;
OleDbCommand objCommandInsert = new OleDbCommand(commandInsert, objConnection);
objCommandInsert.ExecuteNonQuery();
}
objConnection.Dispose();
return true;
}
catch (Exception exc)
{
ex = exc;
return false;
}
}

Sample how to use it.

saveFileDialog1.ShowDialog();
Exception ex;
ExportToExcel(saveFileDialog1.FileName, dataGridView1, out ex);

Advertisements
Categories: C#
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: