Archive

Archive for August, 2014

Export DataGridView into Excel without using COM

August 7, 2014 Leave a comment

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);

Categories: C#