Export to Excel - C#

public static void exportToExcel( System.Windows.Forms.DataGridView dg, string filePath, string sheetName )
{
    string stmt = "";
    System.Data.OleDb.OleDbConnection connection = null;
    try { System.IO.File.Delete( filePath ); } catch ( Exception ) { }
    try {
        connection = ToolsDB.CreateOleDbConnectionToExcell( filePath );
        connection.Open();

        // Create the colum names
        string tableName = sheetName;
        stmt = "CREATE TABLE " + tableName + " (";
        foreach ( System.Windows.Forms.DataGridViewColumn column in dg.Columns ) {
            if ( column.Visible ) {
                stmt += column.HeaderText;

                switch ( column.ValueType.ToString() ) {
                    case "System.Int32":
                    case "System.Int16":
                    case "System.Int64":
                        stmt += " int,";
                        break;
                    case "System.Double":
                    case "System.Decimal":
                    case "System.Float":
                        stmt += " float,";
                        break;
                    case "System.DateTime":
                        stmt += " date,";
                        break;
                    default:
                        stmt += " char(255),";
                        break;
                }
            }
        }
        stmt = stmt.Substring( 0, stmt.Length - 1 );
        stmt += ")";
        System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand( stmt, connection );
        cmd.ExecuteNonQuery();

        // copy data
        foreach ( System.Windows.Forms.DataGridViewRow row in dg.Rows ) {
            string stmt1 = "INSERT INTO [" + tableName + "] (";
            string stmt2 = " VALUES (";
            foreach ( System.Windows.Forms.DataGridViewColumn column in dg.Columns ) {
                if ( column.Visible ) {

                    stmt1 += column.HeaderText + ",";

                    switch ( column.ValueType.ToString() ) {
                        case "System.Int32":
                        case "System.Int16":
                        case "System.Int64":
                            stmt2 += row.Cells[column.Name].Value.ToString() + ",";
                            break;
                        case "System.Double":
                        case "System.Decimal":
                        case "System.Float":
                            stmt2 += String.Format( "{0,2:f2}", row.Cells[column.Name].Value ).Replace( ",", "." ) + ",";
                            break;
                        case "System.DateTime":
                            stmt2 += "'" + ((DateTime)row.Cells[column.Name].Value).ToString( "dd/MM/yyyy" ) + "',";
                            break;
                        default:
                            string text = row.Cells[column.Name].Value.ToString();
                            if ( text.Length > 254 ) text = text.Substring( 0, 254 );
                            stmt2 += "'" + text + "',";
                            break;
                    }
                }
            }
            stmt1 = stmt1.Substring( 0, stmt1.Length - 1 );
            stmt2 = stmt2.Substring( 0, stmt2.Length - 1 );
            stmt1 += ")";
            stmt2 += ")";
            stmt = stmt1 + stmt2;
            cmd = new System.Data.OleDb.OleDbCommand( stmt, connection );
            cmd.ExecuteNonQuery();
        }

    } catch ( Exception ex ) {
    }
    connection.Close();
}

Select allOpen in new window