asp.net实现数据从DataTable导入到Excel文件并创建表的方法（IIS必须启动32位）

本文实例讲述了asp.net实现数据从DataTable导入到Excel文件并创建表的方法。分享给大家供大家参考，具体如下：

/// <summary>
/// 把数据从DataTable导入到Excel文件里
/// </summary>
/// <param name="dataTable">数据源</param>
/// <param name="AbsoluteExcelFilePath">Excel文件的绝对路径</param>
/// <param name="TblColName">TBL里对应的列名</param>
/// <param name="ColumnName">Excel中对应的列名</param>
/// <returns>操作成功返回True,失败返回False</returns>
public static bool ExportDataToExcel(DataTable dataTable, stringAbsoluteExcelFilePath, string[] TblColName,string[] ColumnName)
{
  intk = 0;
  if(dataTable == null)return false;
  OleDbConnection Conn =new OleDbConnection();
  try
  {
   stringstrConn = "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();
   stringstrSQL = "";
   if(dataTable.Columns != null)
   {
    //建表
    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)
    {
     //导入数据
     foreach(DataRow row indataTable.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();
   thrownew Exception(ex.Message);
   returnfalse;
  }
  Conn.Close();
  returntrue;
}

调用方法:

DataSet ds = (DataSet)Session["listMobile"];//获得要导出的表格的值
if (ds.Tables[0].Rows.Count <= 0)
{
 Page.RegisterStartupScript("","<mce:script type="text/javascript"><!--
alert('没有内容不能导出！')
// --></mce:script>");
}
else
{
 //EXCEL页面的名称
 string[] tableName = {"["+DateTime.Now.ToString("yyyyMMddhhmmss")+"]"};
 stringfileName = tools.CreateID() + ".xls";
 stringfilePath = 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();
 }
}

希望本文所述对大家asp.net程序设计有所帮助。