How to Export DataTable to MS Excel 2007 (.xlsx) with C# .NET

These methods exports a datatable to Microsoft Excel 2007 format (.xlsx). All you have to do is to pass the following parameters:
sheetToCreate = File path,
dtToExport = The DataTable to be exported,
tableName = Sheet name in the Workbook

Cheers!!!

public void ExportToXLSX(string sheetToCreate, DataTable dtToExport, string tableName)
{
List<DataRow> rows = new List<DataRow>();
foreach (DataRow row in dtToExport.Rows) rows.Add(row);
ExportToXLSX(sheetToCreate, rows, dtToExport, tableName);
}

public void ExportToXLSX(string sheetToCreate, List<DataRow> selectedRows, DataTable origDataTable, string tableName)
{
char Space = ' ';
string dest = sheetToCreate;


if (File.Exists(dest))
{
File.Delete(dest);
}

sheetToCreate = dest;

if (tableName == null)
{
tableName = string.Empty;
}

tableName = tableName.Trim().Replace(Space, '_');
if (tableName.Length == 0)
{
tableName = origDataTable.TableName.Replace(Space, '_');
}

if (tableName.Length == 0)
{
tableName = "NoTableName";
}

if (tableName.Length > 30)
{
tableName = tableName.Substring(0, 30);
}

//Excel names are less than 31 chars
string queryCreateExcelTable = "CREATE TABLE [" + tableName + "] (";
Dictionary<string, string> colNames = new Dictionary<string, string>();

foreach (DataColumn dc in origDataTable.Columns)
{
//Cause the query to name each of the columns to be created.
string modifiedcolName = dc.ColumnName;//.Replace(Space, '_').Replace('.', '#');
string origColName = dc.ColumnName;
colNames.Add(modifiedcolName, origColName);

queryCreateExcelTable += "[" + modifiedcolName + "]" + " text,";

}

queryCreateExcelTable = queryCreateExcelTable.TrimEnd(new char[] { Convert.ToChar(",") }) + ")";

//adds the closing parentheses to the query string
if (selectedRows.Count > 65000 && sheetToCreate.ToLower().EndsWith(".xls"))
{
//use Excel 2007 for large sheets.
sheetToCreate = sheetToCreate.ToLower().Replace(".xls", string.Empty) + ".xlsx";
}

string strCn = string.Empty;
string ext = System.IO.Path.GetExtension(sheetToCreate).ToLower();
if (ext == ".xls") strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + sheetToCreate + "; Extended Properties='Excel 8.0;HDR=YES'";
if (ext == ".xlsx") strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Xml;HDR=YES' ";
if (ext == ".xlsb") strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0;HDR=YES' ";
if (ext == ".xlsm") strCn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + sheetToCreate + ";Extended Properties='Excel 12.0 Macro;HDR=YES' ";

System.Data.OleDb.OleDbConnection cn = new System.Data.OleDb.OleDbConnection(strCn);
System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(queryCreateExcelTable, cn);
cn.Open();
cmd.ExecuteNonQuery();
System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [" + tableName + "]", cn);
System.Data.OleDb.OleDbCommandBuilder cb = new System.Data.OleDb.OleDbCommandBuilder(da);

//creates the INSERT INTO command
cb.QuotePrefix = "[";
cb.QuoteSuffix = "]";
cmd = cb.GetInsertCommand();

//gets a hold of the INSERT INTO command.
foreach (DataRow row in selectedRows)
{
foreach (System.Data.OleDb.OleDbParameter param in cmd.Parameters)
{
param.Value = row[colNames[param.SourceColumn.Replace('#', '.')]];
}

cmd.ExecuteNonQuery(); //INSERT INTO command.
}
cn.Close();
cn.Dispose();
da.Dispose();
GC.Collect();
GC.WaitForPendingFinalizers();
}

How to solve the Layer Problem of Flash Objects

On a DHTML page containing several layers, a Flash object may display above all the layers, no matter what the "z-index" is. The fault is not the developers', but the browsers'.

This problem can be solved by using the "wmode" parameter to allow layering of Flash content with DHTML layers. The values of the "wmode" parameter can be "window" which is the default value, "opaque", or "transparent". "opaque" or "transparent" as the value of "wmode" parameter can prevent a Flash object from showing in the topmost layer.

The sample code:

<object>
<param name="wmode" value="transparent"></param>
<param name="movie" value="flashBanner.swf"></param>
<embed src="flashLeftBanner.swf" type="application/x-shockwave-flash" wmode="transparent"></embed>
</object>