asp. net implements the method of importing data from DataTable to Excel file and creating tables

This article illustrates how asp. net can import data from DataTable to Excel files and create tables. Share it for your reference, as follows:

/// <summary>
///  Putting data from DataTable Import to Excel In the file 
/// </summary>
/// <param name="dataTable"> Data source </param>
/// <param name="AbsoluteExcelFilePath">Excel Absolute path of file </param>
/// <param name="TblColName">TBL The corresponding column name in the </param>
/// <param name="ColumnName">Excel The corresponding column name in the </param>
/// <returns> Successful operation returned True, Failure return False</returns>
public static bool ExportDataToExcel(DataTable dataTable, string AbsoluteExcelFilePath, string[] TblColName, string[] ColumnName)
{
  int k = 0;
  if (dataTable == null) return false;
  OleDbConnection Conn = new OleDbConnection();
  try
  {
   string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + AbsoluteExcelFilePath + ";Mode=Share Deny None;Extended Properties=Excel 8.0;Jet OLEDB:Create System Database=True";
   Conn = new OleDbConnection(strConn);
   Conn.Open();
   OleDbCommand command = Conn.CreateCommand();
   string strSQL = "";
   if (dataTable.Columns != null)
   {
    // Table building 
    strSQL = "CREATE TABLE " + dataTable.TableName + "(";
    for (int i = 0; i < ColumnName.Length; i++)
    {
     strSQL += ColumnName[i] + " TEXT,";
    }
    strSQL = strSQL.Substring(0, strSQL.Length - 1);
    strSQL += ")";
    command.CommandText += strSQL;
    command.ExecuteNonQuery();
    if (dataTable.Rows.Count > 0)
    {
     // Import data 
     foreach (DataRow row in dataTable.Rows)
     {
      strSQL = "insert into " + dataTable.TableName + "(";
      for (k = 0; k < TblColName.Length; k++)
      {
       strSQL += ColumnName[k] + ",";
      }
      strSQL = strSQL.Substring(0, strSQL.Length - 1);
      strSQL += ") values( ";
      for (k = 0; k < TblColName.Length; k++)
      {
       strSQL += "'" + row[TblColName[k]] + "',";
      }
      strSQL = strSQL.Substring(0, strSQL.Length - 1);
      strSQL += ")";
      command.CommandText = strSQL;
      command.ExecuteNonQuery();
     }
    }
   }
  }
  catch (Exception ex)
  {
   Conn.Close();
   throw new Exception(ex.Message);
   return false;
  }
  Conn.Close();
  return true;
}

Invoke the method:

DataSet ds = (DataSet)Session["listMobile"];// Get the value of the table to export 
if (ds.Tables[0].Rows.Count <= 0)
{
 Page.RegisterStartupScript("", "<mce:script type="text/javascript"><!--
alert(' You can't export without content! ')
// --></mce:script>");
}
else
{
 //EXCEL Name of the page 
 string[] tableName = { "["+DateTime.Now.ToString("yyyyMMddhhmmss")+"]" };
 string fileName = tools.CreateID() + ".xls";
 string filePath = Server.MapPath("..//DownloadFiles//" + fileName);
 if (tools.ExportDataToExcel(ds, filePath, tableName)==true)
 {
  Response.Clear();
  Response.Buffer = true;
  Response.Charset = "GB2312";
  Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
  Response.ContentType = "application/vnd.ms-excel";
  this.EnableViewState = false;
  Response.WriteFile(filePath);
  Response.Flush();
  if (System.IO.File.Exists(filePath)) System.IO.File.Delete(filePath);
  Response.Redirect(this.Request.UrlReferrer.AbsoluteUri, true);
  Response.End();
 }
}

I hope this article is helpful to everyone's asp. net programming.