VS2003: Using Microsoft Excel and ASP.NET

To create an export to Excel you can use the following functions below. Then simply call the function exportToExcel and pass the datatable you want to export and the filename.

private static void exportToExcel(DataTable dt, string fileName)
{
//if the file already exists then delete it
System.IO.FileInfo fi = new System.IO.FileInfo(fileName);
if (fi.Exists)
fi.Delete();

//set the table name if its not there
if (dt.TableName == string.Empty)
dt.TableName = "Sheet1";
else//remove $ from table name if it is there
dt.TableName = dt.TableName.Replace("$", string.Empty);


string sql;
OleDbConnection connection = GetConnection(fileName);
OleDbCommand command = new OleDbCommand();
command.Connection = connection;

try
{
// Create table
command.CommandText = GetTableCreationSql(dt);
command.Connection.Open();
command.ExecuteNonQuery();
command.Dispose();


// Insert Into Table

sql = GetInsertSql(dt);

foreach (DataRow row in dt.Rows)
{
command = new OleDbCommand();
command.Connection = connection;
command.CommandText = sql;
SetParametersInCommand(row, command);
command.ExecuteNonQuery();
command.Dispose();
}
}
finally
{
//Garbage cleaning
connection.Close();
connection.Dispose();
}

}

///
/// create a string which should be the sqlCommand for creating Table in oleDB
///
/// create table [tableName] ( Column1 type, ..., Columnn Type)
private static string GetTableCreationSql(DataTable dt)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("create table [");
sqlBuilder.Append(dt.TableName);
sqlBuilder.Append("] ( ");

//Create a list of column names and their data types, e.g
//[ColumnName1] nvarchar, [ColumnName2] nvarchar,...[ColumnNamen] nvarchar
foreach (DataColumn col in dt.Columns)
{
if (col.Ordinal == 0)
sqlBuilder.Append("[");
else
sqlBuilder.Append(", [");

sqlBuilder.Append( col.ColumnName.Trim());

sqlBuilder.Append("] nvarchar");
}

sqlBuilder.Append(" )");

return sqlBuilder.ToString();
}

///
/// form a insert statement to insert data into oleDB
///
/// Insert Into tableName ([ColumnName1], [ColumnName2] ,...[ColumnNamen]) values (?,?,..,?)
private static string GetInsertSql(DataTable dt)
{
StringBuilder sqlBuilder = new StringBuilder();
StringBuilder paramMarks = new StringBuilder();
sqlBuilder.Append("Insert Into [");
sqlBuilder.Append(dt.TableName);
sqlBuilder.Append("] ( ");


//Create a list of column names and their place holders
//[ColumnName1] , [ColumnName2] ,...[ColumnNamen]
// ?,?,...?
foreach (DataColumn col in dt.Columns)
{
if (col.Ordinal == 0)
{
sqlBuilder.Append("[");
paramMarks.Append("?");
}
else
{
sqlBuilder.Append(", [");
paramMarks.Append(",?");
}

sqlBuilder.Append(col.ColumnName.Trim());
sqlBuilder.Append("] ");
}

sqlBuilder.Append(" ) values (");
sqlBuilder.Append(paramMarks.ToString());
sqlBuilder.Append(")");

return sqlBuilder.ToString();
}


private static void SetParametersInCommand(DataRow row, OleDbCommand command)
{
UnicodeEncoding en = new UnicodeEncoding();
string argumentName = string.Empty;
int index = 0;
foreach (DataColumn col in row.Table.Columns)
{
argumentName = "@Args" + index;
command.Parameters.Add(new OleDbParameter(argumentName, OleDbType.VarBinary)).Value =
en.GetBytes(row[col].ToString());
index++;
}
}
private static OleDbConnection GetConnection(string filePath)
{
return new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=YES;\"");
}