C# | Asp.Net | Convert DataTable to Excel File

C# | Asp.Net | Convert DataTable to Excel File

Here is an example, to convert fro existing DataTable to Excel file

Working fine to me...
Tested on Excel 2003 and partially on Excel 2007 :)

///

/// Converts excel file sheet content into DataTable.
///

///
///
///
public static DataTable ExcelFileToDataTable(string p_fileUrl, int p_sheetIndex)
{
DataTable dbSchema = new DataTable();
DataSet dataSet = new DataSet();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + p_fileUrl + ";" + "Extended Properties=Excel 8.0;Mode=Read;";

using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();

// Get all sheetnames from an excel file into data table
dbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

string sheetName = dbSchema.Rows[p_sheetIndex]["TABLE_NAME"].ToString();
string selectCommandText = string.Format("SELECT * FROM [{0}]", sheetName);

using (OleDbDataAdapter adapter = new OleDbDataAdapter(selectCommandText, conn))
{
adapter.Fill(dataSet);
}

return dataSet.Tables[0];
}
}

///

/// Converts DataTable to Excel-File
///

///
///
///
///
public static bool DataTableToExcelFile(DataTable p_dt, string p_filePath, EventHandler p_onRowHandled)
{
try
{
// fix table name
if (p_dt.TableName == "" || p_dt.TableName.Equals("Table", StringComparison.OrdinalIgnoreCase) == true)
{
p_dt.TableName = "NONAME";
}

// fix file path name
if (p_filePath.EndsWith(".xls") == false)
{
p_filePath += ".xls";
}

// insure file not exist!
System.IO.FileInfo fi = new System.IO.FileInfo(p_filePath);
if (fi.Name.Replace(".xls", "").Trim().Length <= 0)
{
p_filePath = p_filePath.Replace(".xls", p_dt.TableName + ".xls");
}
if (System.IO.File.Exists(p_filePath))
{
System.IO.File.Delete(p_filePath);
}

using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + p_filePath + ";Extended Properties=Excel 8.0"))
{
using (OleDbCommand command = new OleDbCommand())
{
command.Connection = con;
con.Open();

// create table
command.CommandText = GenerateSqlStatementCreateTable(p_dt);
command.ExecuteNonQuery();

DataRow dr;
OleDbParameter oleDbParameter;

// Create columns & values (as parameters) script
string columns, parameters;
GenerateColumnsString(p_dt, out columns, out parameters);

for (int i = 0; i < p_dt.Rows.Count; i++)
{
dr = p_dt.Rows[i];

// set insert statement parameters
command.Parameters.Clear();
for (int j = 0; j < p_dt.Columns.Count; j++)
{
oleDbParameter = new OleDbParameter();
oleDbParameter.ParameterName = "@p" + j;

if (dr.IsNull(j) == true)
{
oleDbParameter.Value = DBNull.Value;
}
else
{
oleDbParameter.Value = dr[j];
}
command.Parameters.Add(oleDbParameter);
}

command.CommandText = string.Format("INSERT INTO {0} ({1}) VALUES ({2})", p_dt.TableName, columns, parameters);
command.ExecuteNonQuery();

// Perform step...
if (p_onRowHandled != null)
{
p_onRowHandled(p_dt, EventArgs.Empty);
}
}
}

con.Close();
}
return true;
}
catch (Exception ex)
{
throw (ex);
}
}


///

/// Generates columns names string delimited by commas
/// also supply parameters for the given columns, for example:
///
/// out string p_columns = [columnname0],[columnname1],[columnname2]
/// out string p_params = @p0, @p1, @p2
///
///

///
///
///
private static void GenerateColumnsString(DataTable p_dt, out string p_columns, out string p_params)
{
StringBuilder sbColumns = new StringBuilder();
StringBuilder sbParams = new StringBuilder();
for (int i = 0; i < p_dt.Columns.Count; i++)
{
if (i != 0)
{
sbColumns.Append(',');
sbParams.Append(',');
}
sbColumns.AppendFormat("[{0}]", p_dt.Columns[i].ColumnName);
sbParams.AppendFormat("@p{0}", i);
}

p_columns = sbColumns.ToString();
p_params = sbParams.ToString();
}

///

/// Create SQL-Script for creating table which represent the given DataTable
///

///
///
private static string GenerateSqlStatementCreateTable(DataTable p_dt)
{
StringBuilder sbCreateTable = new StringBuilder();

DataColumn dc;
sbCreateTable.AppendFormat("CREATE TABLE {0} (", p_dt.TableName);
for (int i = 0; i < p_dt.Columns.Count; i++)
{
dc = p_dt.Columns[i];
if (i != 0)
{
sbCreateTable.Append(",");
}

string dataType = dc.DataType.Equals(typeof(double)) ? "DOUBLE" : "NVARCHAR";

sbCreateTable.AppendFormat("[{0}] {1}", dc.ColumnName, dataType);
}
sbCreateTable.Append(")");

return sbCreateTable.ToString();
}